Grant SELECT/Execute to View/Function but not underlying Table

On Thu, Sep 24, 2015 at 9:28 PM, Alex Magnum <magnum11200@gmail.com> 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/ <http://www.postgresql-consulting.com/&gt;

On Thursday, September 24, 2015, Alex Magnum <magnum11200@gmail.com> 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.

On 24 September 2015 at 12:28, Alex Magnum <magnum11200@gmail.com> 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

On Thursday, September 24, 2015, Maxim Boguk <maxim.boguk@gmail.com> wrote:

On Thu, Sep 24, 2015 at 9:28 PM, Alex Magnum <magnum11200@gmail.com
<javascript:_e(%7B%7D,'cvml','magnum11200@gmail.com');>> 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.