PostgreSQL Permissions – GRANT ON ALL TABLES IN SCHEMA

GRANT ALL PRIVILEGES ON .... is MySQL privs syntax.

You need to do:

GRANT ALL ON SCHEMA.TABLE TO USER 

See the documentation.

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