PostgreSQL – How to GRANT SELECT on All Schemas in a Database

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

You'd need to have select access on the specific table granted to you with the admin option

GRANT SELECT ON <<table name>> 
   TO dwhManager
 WITH ADMIN OPTION;

That would need to be run for each table. You could do that in a loop with dynamic SQL

BEGIN
  FOR t IN (SELECT * 
              FROM dba_tables
             WHERE owner = <<schema owner>>)
  LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON ' ||
                         t.owner || '.' || t.table_name ||
                         ' TO dwhManager WITH ADMIN OPTION';
  END LOOP;
END;

You'd need to do a new grant every time you create a new table.