securitypostgresqlpermissionsdatabase-permissions

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

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?

Solution

  • 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;