I want to GRANT USAGE
to a user/role for a given database. The database has many schemas.
I know there's an ON ALL TABLES IN SCHEMA
, but I want "all schemas". I tried GRANT USAGE .. ON DATABASE
, but that's obviously wrong (it doesn't actually exist).
This is for Postgres 9.3 or 9.4, it happens to be a server that is on AWS RDS.
Best Answer
You have at least two options.
The first one makes use of a small query and a text editor. We have to collect the schemata of our interest:
You can add a
WHERE
clause if you want to limit the scope. Copy the output and amend it, so you get a number ofGRANT USAGE ON SCHEMA ... TO your_role;
commands. Then just feed it topsql
, for example:A usual variant of this could be a shell script that loops over the collected names and calls
psql
, passing the constructedGRANT
statement to the-c
option.The other solution does basically the same in one pl/pgsql block, building a dynamic query. The core is the same - we have to collect the schemata. Then we loop over all of them, granting the permissions schema by schema:
Notes:
format()
with the%I
format specifier to have the object name properly quoted if necessary. This approach is far more readable than building the query with concatenation of string constants and somequote_ident()
calls.pg_namespace
can be found in thepg_catalog
schema. Check out the other objects in there - they store every aspect of your schemas, tables and so on.