CODEWITHSUNDEEP
postgresql
u tyagi
u tyagi

Reputation: 922

GRANT all permissions on ALL DATABASES and their tables

I doubt myself, this question is unanswered or I am not able to find it. I have a PostgreSQL 9.5 and need to create a non super-user who can connect to all databases and query their tables. Querying includes normal DML (SELECT, UPDATE, DELETE, DROP ) kind of. I want equivalent of following MySQL Syntax:

GRANT ALL PRIVILEGES ON *.* TO <USER> IDENTIFIED BY <PASSWORD>;

What should be done to give privilege to a user say 'dbuser' so that it can connect all databases and query their tables (single SQL Statement).

Edit:

It is already known that it can be achieved for a single database in a 2-step manner:

psql -U postgres -d <DBNAME>;
GRANT ALL on ALL TABLES IN SCHEMA public TO <DBUSER>;

Given the DBNAMES are multiple, Do we need to repeat these 2 statements for every DBNAME. I want to achieve it in a single statement for all DBNAMES.

Upvotes: 1

Views: 4614

Answers (1)

user330315
user330315

Reputation:

The connect privilege is granted to the "public" role by default. So you don't need to do anything there, except for checking that pg_hba.conf allows that as well.

For granting select privileges you need two steps: grant them for existing tables and future tables. For existing tables you need to do this for each schema in each database, e.g:

grant select on all tables in schema public to read_all_user;
grant select on all tables in schema second_schema to read_all_user;
grant select on all tables in schema third_schema to read_all_user;

Of course you can generate those statement through a SQL query and run them using \gexec in psql.

If you also want to grant select for all future tables, you will need to alter the default privileges - again for all schemas in all databases.

Note that what MySQL calls a "database" is in reality a schema in terms of the SQL standard (and in the PostgreSQL world). If you only use a single schema in each database (e.g. public), then obviously you only need to do this for one schema in each database.

If you were using the most recent Postgres version (9.5 is no longer supported or maintained), you could simply grant the predefined role pg_read_all_data to the user in question.

Upvotes: 3

Related Questions