How to create a user with readonly privileges for all databases in Postgresql?

Question

I want to create a user with only select privilege for all tables in all databases. I thought that I could get a list of databases and apply the following command for each database:

GRANT select ON DATABASE dbname to user1; 

But I got the following error:

ERROR:  invalid privilege type SELECT for database 

When I googled people advised to do the grant select operation for all tables. But new tables are being added always. So this is not an acceptable solution for me. Does anyone know any workarounds?

Answer 1

You cannot do this on database level, only on schema level.

Assuming you are only using the public schema in each database, you can do this:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user; 

Answer 2

You need to do 2 things: firstly, allow access to existing objects; and secondly, set the default access for new objects created from now on.

Note that granting access to "TABLES" includes views, but does not include sequences (such as the auto-increment function for "SERIAL" columns), so you'll probably want to grant access to those as well.

The below assumes you want to do everything in the public schema. The ALTER DEFAULT PRIVILEGES statement can act on the entire database by omitting the IN SCHEMA ... clause; the GRANT has to be run once for each schema.

-- Grant access to current tables and views GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1; -- Now make sure that's also available on new tables and views by default ALTER DEFAULT PRIVILEGES     IN SCHEMA public -- omit this line to make a default across all schemas     GRANT SELECT ON TABLES  TO user1;  -- Now do the same for sequences GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO user1; ALTER DEFAULT PRIVILEGES     IN SCHEMA public -- omit this line to make a default across all schemas     GRANT SELECT, USAGE ON SEQUENCES  TO user1; 

PostgreSQL manual

• http://www.postgresql.org/docs/current/interactive/sql-grant.html
• http://www.postgresql.org/docs/current/interactive/sql-alterdefaultprivileges.html