Postgresql – How to GRANT for all tables across all schemas

permissionspostgresql-9.2

I want to

GRANT SELECT
    ON ALL TABLES
    TO foo;

But postgres tells me I have a syntax error at "TO". It expects a GRANT like:

GRANT SELECT
    ON ALL TABLES
    IN SCHEMA public
    TO foo;

We have a handful of tables that have been sharded such that public, shard123, and shard124 all have a table my_table, and public.my_table is the parent of both shard123.my_table and shard124.my_table. I've GRANTed SELECT to foo for public schema, but foo doesn't have permission for any of the shard schemas.

I've also done:

ALTER DEFAULT PRIVILEGES
    FOR ROLE dba
    GRANT SELECT
    ON TABLES
    TO foo;

…which should automatically grant for any new tables and schemas made from now on.

There are 1000 < n < 10000 shards/schemas, so GRANTing separately for each shard/schema is impractical.

How can I GRANT to a role for all existing tables across all existing schemas (all shards)?

Or even better, can I GRANT for all schemas LIKE 'shard%'?

Best Answer

If you need to do this only once, the quickest way is probably the following.

Find all the schemas you want to touch by querying, for example, the pg_namespace system catalog:

SELECT nspname 
FROM pg_namespace
WHERE nspname LIKE 'shard%'; -- tweak this to your needs

Then we can expand the query output into GRANT statements:

SELECT 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || nspname || ' TO foo;'
FROM pg_namespace
WHERE nspname LIKE 'shard%';

This will give you an enormous amount of statements, which you can just copy over and run. As you have a big number of schemas, the easiest is possibly the following (running it in psql):

\copy (SELECT 'GRANT ...' ...) TO some_local_file.sql

\i some_local_file.sql

Of course, if you prefer, you could do this using dynamic SQL, too - I find the above solution slightly better, as it leaves a file behind with the actions taken, that can then be checked in under version control.

If you happen to use a psql version 9.6 (or later), there is just another possibility using \gexec:

SELECT 'GRANT SELECT ...'
... -- you can even omit the semicolon here 
\gexec

Notes:

  • I've written an answer about a similar issue a while ago - you may find useful details there.
  • there is no possibility of doing this from GRANT, unfortunately. As it is not uncommon what you need here, I'd expect this functionality appearing sooner or later.
  • just to emphasize, the above commands will do the trick for the already existing tables. Future tables need the default privileges (which you've set, for a given role).
Related Question