Give all permissions to a user on a PostgreSQL database

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

User · Answer

GRANT ALL PRIVILEGES ON DATABASE  my db  to my user

User · Answer

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    Database queries snapshot

User · Answer

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   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  lt  gt   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  quot Grant Wizard quot  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

User · Answer

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

User · Answer

GRANT USAGE ON SCHEMA schema name TO user

[sql] Give all permissions to a user on a PostgreSQL database

Examples related to sql

Examples related to postgresql

Examples related to ddl

Examples related to privileges

Examples related to grant