Posted By: Anonymous
I would like to give an 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.
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 querydelete 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.
All commands must be executed while connected to the right database in the right database cluster. Make sure of it.
The user needs access to the database, obviously:
GRANT CONNECT ON DATABASE my_db TO my_user;
And (at least) the
USAGE privilege on the schema:
GRANT USAGE ON SCHEMA public TO my_user;
USAGE on all custom schemas:
DO $$ BEGIN -- RAISE NOTICE '%', ( -- use instead of EXECUTE to see generated commands EXECUTE ( SELECT string_agg(format('GRANT USAGE ON SCHEMA %I TO my_user', nspname), '; ') FROM pg_namespace WHERE nspname <> 'information_schema' -- exclude information schema and ... AND nspname NOT LIKE 'pg_%' -- ... system schemas ); END $$;
Then, all permissions for all tables (requires Postgres 9.0 or later).
And don’t forget sequences (if any):
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO my_user;
For older versions you could use the "Grant Wizard" of pgAdmin III (the default GUI).
There are some other objects, the manual for
GRANT has the complete list as of Postgres 12:
privileges on a database object (table, column, view, foreign table, sequence, database, foreign-data wrapper, foreign server, function, procedure, procedural language, schema, or tablespace)
But the rest is rarely needed. More details:
- How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?
- Grant privileges for a particular database in PostgreSQL
- How to grant all privileges on views to arbitrary user
Consider upgrading to a current version.