Postgres grant select access on all tables in a database

It is not possible to create a user with read only access on a particular database. Instead we can do it at schema level.

1) Grant connect to database

GRANT CONNECT ON DATABASE <db name> to <user name>;

2) Grant usage on schema

GRANT USAGE ON SCHEMA <schema name> TO <user name>;

3) Grant select on all tables in a schema using

GRANT SELECT ON ALL TABLES IN SCHEMA <schema name> TO <user name>;

4) Also make this privilege available for new tables added in future using

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema name> GRANT SELECT ON TABLES TO <user name>;

 

So, if we have many schemas we can simply generate ‘GRANT’ statements as mentioned below¬†:

1) SELECT 'GRANT USAGE ON SCHEMA ' || nspname || ' TO <user name>;' FROM pg_namespace WHERE nspname not like 'pg%';

2) SELECT 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || nspname || ' TO <user name>;'  FROM pg_namespace WHERE nspname not like 'pg%';

3) SELECT 'ALTER DEFAULT PRIVILEGES IN SCHEMA ' || nspname || ' GRANT SELECT ON TABLES TO <user name>;' FROM pg_namespace WHERE nspname not like 'pg%';

Leave a Reply

Your email address will not be published. Required fields are marked *