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

On 24 September 2015 at 12:28, Alex Magnum <magnum11200(at)gmail(dot)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