On 24 September 2015 at 12:28, Alex Magnum <magnum11200@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
