Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
select_grant_pg
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment