select_grant_pg

select_grant_pg_.txt

postgres@db:~$ dropdb example
postgres@db:~$
postgres@db:~$ createdb example
postgres@db:~$ psql example
psql (14beta1)
Type "help" for help.

example=# create schema example;
CREATE SCHEMA
example=# create table example.t(id bigserial);
CREATE TABLE
example=# create table public.t(id bigserial);
CREATE TABLE
example=# insert into example.t values (DEFAULT);
INSERT 0 1
example=# insert into public.t values (DEFAULT);
INSERT 0 1
example=# set role shiva;  -- for now no select priv
SET
example=> select * from example.t;
ERROR:  permission denied for schema example
LINE 1: select * from example.t;
                      ^
example=> select * from public.t;
ERROR:  permission denied for table t
example=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)

example=> set role postgres;
SET
example=# grant USAGE on SCHEMA example TO shiva;
GRANT
example=# grant USAGE on SCHEMA public TO shiva;
GRANT
example=# grant SELECT on ALL tables in schema public to shiva;
GRANT
example=# grant SELECT on ALL tables in schema example to shiva;
GRANT
example=# set role shiva; -- now query as shiva
SET
example=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)
example=> select * from public.t;
 id
----
  1
(1 row)

example=> select * from example.t;
 id
----
  1
(1 row)



example=# create table x(id int);
CREATE TABLE
example=# insert into x select 1;
INSERT 0 1
example=# set role shiva;
SET
example=> select * from x;
ERROR:  permission denied for table x
example=> set role postgres;
SET
example=# ALTER DEFAULT PRIVILEGES GRANT SELECT ON tables TO shiva;
ALTER DEFAULT PRIVILEGES
example=# ALTER DEFAULT PRIVILEGES GRANT USAGE ON schemas TO shiva;
ALTER DEFAULT PRIVILEGES
example=# set role shiva;
SET
example=> select * from x; -- table was created before default priv
ERROR:  permission denied for table x
example=> set role postgres;
SET
example=# drop table x;
DROP TABLE
example=# create table x(id int); -- owned by postgres
CREATE TABLE
example=# insert into x select 1;
INSERT 0 1
example=# set role shiva;
SET
example=> select * from x;  --was able to query due to default priv without explicit select grant
 id
----
  1
(1 row)



example=# \dpp example.*
                                    Access privileges
 Schema  |   Name   |   Type   |     Access privileges     | Column privileges | Policies
---------+----------+----------+---------------------------+-------------------+----------
 example | t        | table    | postgres=arwdDxt/postgres+|                   |
         |          |          | shiva=r/postgres          |                   |
 example | t_id_seq | sequence |                           |                   |
 example | x        | table    | postgres=arwdDxt/postgres+|                   |
         |          |          | shiva=r/postgres          |                   |
(3 rows)

example=# \dpp public.*
                                    Access privileges
 Schema |   Name   |   Type   |     Access privileges     | Column privileges | Policies
--------+----------+----------+---------------------------+-------------------+----------
 public | t        | table    | postgres=arwdDxt/postgres+|                   |
        |          |          | shiva=r/postgres          |                   |
 public | t_id_seq | sequence |                           |                   |
 public | x        | table    | postgres=arwdDxt/postgres+|                   |
        |          |          | shiva=r/postgres          |                   |
(3 rows)


SELECT grantee, privilege_type,table_schema, table_name
FROM information_schema.role_table_grants 
where table_schema = ANY( '{public,example}') 
and privilege_type = 'SELECT';

 grantee  | privilege_type | table_schema | table_name
----------+----------------+--------------+------------
 postgres | SELECT         | public       | t
 shiva    | SELECT         | public       | t
 postgres | SELECT         | example      | t
 shiva    | SELECT         | example      | t
 postgres | SELECT         | public       | x
 shiva    | SELECT         | public       | x
 postgres | SELECT         | example      | x
 shiva    | SELECT         | example      | x
(8 rows)


----------------------------------------------------------------
ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO tom;
ALTER DEFAULT PRIVILEGES GRANT ALL ON SEQUENCES to tom;
ALTER DEFAULT PRIVILEGES GRANT ALL ON FUNCTIONS to tom;
ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS to tom;
ALTER DEFAULT PRIVILEGES GRANT ALL ON TYPES to tom;
ALTER DEFAULT PRIVILEGES GRANT USAGE ON schemas TO shiva;
ALTER DEFAULT PRIVILEGES GRANT SELECT ON tables TO shiva;

smartdb=# set role tom;
SET
smartdb=> ALTER DEFAULT PRIVILEGES GRANT USAGE ON schemas TO shiva;  -- these needs to be run as user tom to grant shiva
ALTER DEFAULT PRIVILEGES 
smartdb=> ALTER DEFAULT PRIVILEGES GRANT SELECT ON tables TO shiva;   -- these needs to be run as user tom to grant shiva
ALTER DEFAULT PRIVILEGES
smartdb=> create table t(id int);
CREATE TABLE
smartdb=> insert into t select 1;
INSERT 0 1
smartdb=> select * from t;
 id
----
  1
(1 row)

smartdb=> set role shiva;
SET
smartdb=> select * from t;
 id
----
  1
(1 row)


-- from pg_dump
ALTER DEFAULT PRIVILEGES FOR ROLE tom GRANT USAGE ON SCHEMAS  TO shiva;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON TABLES  TO shiva;
ALTER DEFAULT PRIVILEGES FOR ROLE tom GRANT SELECT ON TABLES  TO shiva;