Search code examples
databasepostgresqlprivileges

How do I grant privilges on all tables in a schema in a particular database?

First, here is an overview of how my Postgres is set up

I have 3 Databases and within each database there is a schema which shares the same name across all 3 databases.

I have written a script to create a role and give it some privileges for the schemas in each database.

This is how I grant the privileges to the role:

\c Database1
GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA "schemaName" TO roleName;
\c Database2
GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA "schemaName" TO roleName;
\c Database3
GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA "schemaName" TO roleName;

Is there a way to explicitly state which database I want to affect within the GRANT statement?

Solution

  • No it is not possible : each database in a instance/cluster is isolated and there is no possibility to reference a database different from the current one (unless you use some extension like dblink or postgres_fdw).