Advertisement
Advertisement


Give all the permissions to a user on a DB


Question

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.

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.

2019/09/03
1
211
9/3/2019 10:38:04 AM

Accepted Answer

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;

Or grant 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:

Consider upgrading to a current version.

2020/01/03
278
1/3/2020 3:02:34 AM


In PostgreSQL 9.0+ you would do the following:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA MY_SCHEMA TO MY_GROUP;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA MY_SCHEMA TO MY_GROUP;

If you want to enable this for newly created relations too, then set the default permissions:

ALTER DEFAULT PRIVILEGES IN SCHEMA MY_SCHEMA
  GRANT ALL PRIVILEGES ON TABLES TO MY_GROUP;
ALTER DEFAULT PRIVILEGES IN SCHEMA MY_SCHEMA
  GRANT ALL PRIVILEGES ON SEQUENCES TO MY_GROUP;

However, seeing that you use 8.1 you have to code it yourself:

CREATE FUNCTION grant_all_in_schema (schname name, grant_to name) RETURNS integer AS $$
DECLARE
  rel RECORD;
BEGIN
  FOR rel IN
    SELECT c.relname
    FROM pg_class c
    JOIN pg_namespace s ON c.namespace = s.oid
    WHERE s.nspname = schname
  LOOP
    EXECUTE 'GRANT ALL PRIVILEGES ON ' || quote_ident(schname) || '.' || rel.relname || ' TO ' || quote_ident(grant_to);
  END LOOP;
  RETURN 1;
END; $$ LANGUAGE plpgsql STRICT;
REVOKE ALL ON FUNCTION grant_all_in_schema(name, name) FROM PUBLIC;

This will set the privileges on all relations: tables, views, indexes, sequences, etc. If you want to restrict that, filter on pg_class.relkind. See the pg_class docs for details.

You should run this function as superuser and as regular as your application requires. An option would be to package this in a cron job that executes every day or every hour.

2014/03/19

I did the following to add a role 'eSumit' on PostgreSQL 9.4.15 database and provide all permission to this role :

CREATE ROLE eSumit;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO eSumit;

GRANT ALL PRIVILEGES ON DATABASE "postgres" to eSumit;

ALTER USER eSumit WITH SUPERUSER;

Also checked the pg_table enteries via :

select * from pg_roles; enter image description here

Database queries snapshot : enter image description here

2018/03/19