How do I GRANT for all tables across all schemas

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%'?

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).

StackExchange Database Administrators Q#121522, answer score: 4