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 of
GRANT USAGE ON SCHEMA ... TO your_role; commands. Then just feed it to
psql, for example:
psql -f multigrant.sql
A usual variant of this could be a shell script that loops over the collected names and calls
psql, passing the constructed
GRANT statement to the
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:
FOR sch IN SELECT nspname FROM pg_namespace
EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO your_role $$, sch);
- unlike for tables, sequences, functions and types, one cannot set default privileges for schemata (as of 9.4). You will have to grant this privilege for any newly added schema manually.
- here I am using dollar quoting when building the dynamic query. This allows me to use 'normal' syntax, as opposed to multiplicating single quotes, for example (not present in this example). This way most editors will highlight the statements nicely.
- I also use
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 some
pg_namespace can be found in the
pg_catalog schema. Check out the other objects in there - they store every aspect of your schemas, tables and so on.