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
shard124 all have a table
public.my_table is the parent of both
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