Re: [GENERAL] Grant SELECT/Execute to View/Function but not underlying Table

[Maxim Boguk]

On Thu, Sep 24, 2015 at 9:28 PM, Alex Magnum  wrote:

> Hi,
> is it possible to grant select to views and functions without the need to
> also grant the user the SELECT privileges to the Tables used in the views
> or functions?
>
> That way I could create read only users on a website and limit their
> access to the bare minimum.
>
> Thanks in advance for any advise on this
>
> Alex
>


​Hi,

For functions it's possible (read about SECURITY DEFINER), for view no it
isn't possible (view is query text stored in database for future use and
nothing more).
​



-- 
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/ 

Re: [GENERAL] Grant SELECT/Execute to View/Function but not underlying Table

[David G. Johnston]

On Thursday, September 24, 2015, Maxim Boguk  wrote:

>
>
> On Thu, Sep 24, 2015 at 9:28 PM, Alex Magnum  > wrote:
>
>> Hi,
>> is it possible to grant select to views and functions without the need to
>> also grant the user the SELECT privileges to the Tables used in the views
>> or functions?
>>
>> That way I could create read only users on a website and limit their
>> access to the bare minimum.
>>
>>

> for view no it isn't possible (view is query text stored in database for
> future use and nothing more).
>
>
This is not how views are stored nor how they work.  The are implemented as
rules and thus the following section of the documentation applies.

 http://www.postgresql.org/docs/9.4/interactive/rules-privileges.html

David J.

[GENERAL] Grant SELECT/Execute to View/Function but not underlying Table

[Alex Magnum]

Hi,
is it possible to grant select to views and functions without the need to
also grant the user the SELECT privileges to the Tables used in the views
or functions?

That way I could create read only users on a website and limit their access
to the bare minimum.

Thanks in advance for any advise on this

Alex

Re: [GENERAL] Grant SELECT/Execute to View/Function but not underlying Table

[David G. Johnston]

On Thursday, September 24, 2015, Alex Magnum  wrote:

> Hi,
> is it possible to grant select to views and functions without the need to
> also grant the user the SELECT privileges to the Tables used in the views
> or functions?
>
> That way I could create read only users on a website and limit their
> access to the bare minimum.
>
> Thanks in advance for any advise on this
>
> Alex
>

Views work this way by default.  You can specify "security definer" to get
similar behavior when you create a function.

David J.

Re: [GENERAL] Grant SELECT/Execute to View/Function but not underlying Table

[Thom Brown]

On 24 September 2015 at 12:28, Alex Magnum  wrote:
> Hi,
> is it possible to grant select to views and functions without the need to
> also grant the user the SELECT privileges to the Tables used in the views or
> functions?
>
> That way I could create read only users on a website and limit their access
> to the bare minimum.
>
> Thanks in advance for any advise on this

Yes.  For views, you just need to provide select access to the user,
but revoke general permissions from the public pseudo role.

Example:

postgres=# create user limited_user;
CREATE ROLE

postgres=# create table mydata (id serial primary key, content text);
CREATE TABLE

postgres=# insert into mydata (content) values ('blue'),('red'),('green');
INSERT 0 3

postgres=# revoke all on mydata from public;
REVOKE

postgres=# create view v_mydata as SELECT content from mydata;
CREATE VIEW

postgres=# grant select on v_mydata to limited_user;
GRANT

postgres=# \c - limited_user
You are now connected to database "postgres" as user "limited_user".

postgres=> select * from mydata;
ERROR:  permission denied for relation mydata

postgres=> select * from v_mydata;
 content
-
 blue
 red
 green
(3 rows)


With functions, you just set them up with the label SECURITY DEFINER.
This means that the function runs as the owner of the function, rather
than whomever is calling it:

postgres=# \c - postgres

postgres=# CREATE or replace FUNCTION get_colour(colour_id int)
returns text as $$
declare
  colour_name text;
begin
  select content into colour_name from mydata where id = colour_id;
  return colour_name;
end; $$ language plpgsql SECURITY DEFINER;

postgres=# revoke all on function get_colour(int) from public;
REVOKE

postgres=# grant execute on function get_colour(int) to limited_user;
GRANT

postgres=# \c - limited_user
You are now connected to database "postgres" as user "limited_user".

postgres=> select get_colour(2);
 get_colour

 red
(1 row)


Thom


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general