Grant access for all all tables in Postgres

user_7580804 0
Viewed 5516

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.

postgresql
2 Answers

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 yo specify the schema name.

See this for more info: link

user_128307510

Using psql:

SELECT format(
          'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %I TO username;',
          nspname
       )
FROM pg_namespace
WHERE nspname NOT LIKE ANY
         (ARRAY['pg_catalog','information_schema','pg_temp%'])::name[]
\gexec

\gexec executes the query result as SQL statements.

user_64643080
Related