Skip to content
Advertisement

Grant connect to all DATABASES

I’ve seen there’s a way to grant select to all tables within a database, however I can’t seem to find a way to grant connect to all databases.

I can’t manually do:

GRANT CONNECT ON DATABASE X TO readonly;
GRANT CONNECT ON DATABASE Y TO readonly;
GRANT CONNECT ON DATABASE Z TO readonly; 

so on so forth, as I have many databases.

Is there an equivalent to something like: GRANT CONNECT ON DATABASE * TO readonly;

My overall hope, is to grant connect and read only (select access) to the readonly user of this database to certain members of my organisation.

Best Regards, Neil D.

Advertisement

Answer

With psql, you can use gexec:

SELECT format('GRANT CONNECT ON DATABASE %I TO readonly;', datname) FROM pg_database gexec

gexec interprets each line of the query result as an SQL statement and executes it.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement