Postgresql 9.6, create a user with only select permissions for a given database/schema

Question

I want to create a user that has:

• has its default privileges revoked when I create it
• can only perform selects against a given database/schema

Is this possible?

Answer 1

Please read below mentioned official documentation from Postgres

Official Documentation of REVOKE:

https://www.postgresql.org/docs/9.6/sql-revoke.html

Official Documentation of GRANT:

https://www.postgresql.org/docs/9.6/sql-grant.html

To grant select privilege to schema you need to run following query

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO user_name;

For more details read following Post

https://dba.stackexchange.com/questions/91953/grant-access-to-all-tables-of-a-database