PostgreSQL: Give all permissions to a user on a PostgreSQL database

All commands must be executed while connected to the right database in the right database cluster. Make sure of it.

The role needs access to the database, obviously:

GRANT CONNECT ON DATABASE my_db TO my_user;

Basic privileges for Postgres 14 (currently beta)

Postgres 14 adds the predefined, non-login roles pg_read_all_data and pg_write_all_data to give read-only / write-only access to all objects. We can GRANT membership in those roles:

GRANT pg_read_all_data TO my_user;GRANT pg_write_all_data TO my_user;

This covers all basic DML commands (but not DDL, and not some special commands like TRUNCATE or EXECUTE for functions!). The manual:

pg_read_all_data

Read all data (tables, views, sequences), as if having SELECT rightson those objects, and USAGE rights on all schemas, even withouthaving it explicitly. This role does not have the role attributeBYPASSRLS set. If RLS is being used, an administrator may wish toset BYPASSRLS on roles which this role is GRANTed to.

pg_write_all_data

Write all data (tables, views, sequences), as if having INSERT,UPDATE, and DELETE rights on those objects, and USAGE rights onall schemas, even without having it explicitly. This role does nothave the role attribute BYPASSRLS set. If RLS is being used, anadministrator may wish to set BYPASSRLS on roles which this role isGRANTed to.

All privileges

The role needs (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 (then 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.

GRANT ALL PRIVILEGES ON DATABASE "my_db" to my_user;

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.