How to grant all table privileges to all schemas in a Postgres database to a user/role?
The below command grants only to specific schema not whole database.
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO username;
For example,
Database: test Schema : schema1, schema2 inside test table : schema1.table1,schema1.table2,schema2.table1,schema2.table2
How to provide all table privileges to all tables found in all schemas found inside Database.
You would have to have the specific schema. Also see this for further more usage on GRANT. Also, Grant all of the privileges available for the object's type. The PRIVILEGES key word is optional in PostgreSQL, though it is required by strict SQL.
So you can basically use all for a particular schema that all the tables belong to.
So
grant all on all tables in schema "schema_name" to user
should do it for you but you need to specify the schema name.
See this for more info: link