Thread: Grant SELECT/Execute to View/Function but not underlying Table
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
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 thisAlex
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).
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/
Senior Postgresql DBA
http://www.postgresql-consulting.ru/
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 thisAlex
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
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> 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.
David J.