I would like to give a user all the permissions on a database without making it an admin.
The reason why I want to do that is that at the moment DEV and PROD are different DBs on the same cluster so I don't want a user to be able to change production objects but it must be able to change objects on DEV.
I tried:
grant ALL on database MY_DB to group MY_GROUP;
but it doesn't seem to give any permission.
Then I tried:
grant all privileges on schema MY_SCHEMA to group MY_GROUP;
and it seems to give me permission to create objects but not to query\delete objects on that schema that belong to other users
I could go on by giving USAGE permission to the user on MY_SCHEMA but then it would complain about not having permissions on the table …
So I guess my question is: is there any easy way of giving all the permissions to a user on a DB?
I'm working on PostgreSQL 8.1.23.
Best Answer
All commands must be executed while connected to the right database cluster. Make sure of it.
Roles are objects of the database cluster. All databases of the same cluster share the set of defined roles. Privileges are granted / revoked per database / schema / tables etc.
A role needs access to the database, obviously. If that is granted to
PUBLIC
, you are covered. Else:Basic privileges for Postgres 14 or later
Postgres 14 adds the predefined, non-login roles
pg_read_all_data
andpg_write_all_data
to give read-only / write-only access to all objects. We canGRANT
membership in those roles:This covers all basic DML commands (but not DDL, and not some special commands like
TRUNCATE
or theEXECUTE
privilege for functions!). The manual:All privileges without using predefined roles (any Postgres version)
Commands must be executed while connected to the right database. Make sure of it.
The role needs (at least) the
USAGE
privilege on the schema. Again, if that's granted toPUBLIC
, you are covered. Else:Or grant
USAGE
on all custom schemas:Then, all permissions for all tables (requires Postgres 9.0 or later).
And don't forget sequences (if any):
Alternatively, you could use the "Grant Wizard" of pgAdmin 4 to work with a GUI.
There are some other objects, the manual for
GRANT
has the complete list. As of Postgres 12:But the rest is rarely needed. More details:
Consider upgrading to a current version.