Postgresql: Give All Permissions to a User on a Postgresql Database

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

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 / table etc.

A role needs access to the database, obviously. That's granted to PUBLIC by default. Else:

GRANT CONNECT ON DATABASE my_db TO my_user;

Basic privileges for Postgres 14 or later

Postgres 14 adds the predefined, non-login roles pg_read_all_data / pg_write_all_data.

They have SELECT / INSERT, UPDATE, DELETE privileges for all tables, views, and sequences. Plus USAGE on schemas. We can GRANT membership in these 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 the EXECUTE privilege for functions!). The manual:

pg_read_all_data

Read all data (tables, views, sequences), as if having SELECT rights
on those objects, and USAGE rights on all schemas, even without
having it explicitly. This role does not have the role attribute
BYPASSRLS set. If RLS is being used, an administrator may wish to
set 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 on
all schemas, even without having it explicitly. This role does not
have the role attribute BYPASSRLS set. If RLS is being used, an
administrator may wish to set BYPASSRLS on roles which this role is
GRANTed to.

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 to PUBLIC, you are covered. Else:

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;

Alternatively, you could use the "Grant Wizard" of pgAdmin 4 to work with a GUI.

This covers privileges for existing objects. To also cover future objects, set DEFAULT PRIVILEGES. See:

• Grant privileges for a particular database in PostgreSQL
• How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

There are some other objects, the manual for GRANT has the complete list. As of Postgres 14:

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:

• Grant privileges for a particular database in PostgreSQL
• How to grant all privileges on views to arbitrary user

Consider upgrading to a current version.

How to grant all privileges in one line to a created user on PostgreSQL

The syntax is slighly off. It is CREATEDB and CREATEROLE. Besides, if you are superuser, you have everything else automatically.

So this is enough:

ALTER ROLE faouzi SUPERUSER;

How to make permissions to a user on all new tables in Postgres?

You can do this by creating default privileges:

ALTER DEFAULT PRIVILEGES
FOR USER user1
GRANT ALL ON TABLES TO user2

grant all privileges on db.* to user?

how can I grant bob privileges at the schema level

You your looking for the option on all tables in schema

grant all privileges on all tables in schema public to bob;
grant all privileges on all tables in schema reporting to bob;

You probably also want to change the default privileges so that this is also applied for tables created in the future.

Postgresql 2 users with different permissions on a specific schema

Thank you @JGH, that was it. : the user2 had to give himself access to user1 to schema some_shema.
What i did :

psql
CREATE USER user2 WITH PASSWORD '***';
\c my_database
CREATE SCHEMA some_schema AUTHORIZATION user2;
\c my_database user2
create table some_schema.test(id int);
insert into some_schema.t(100);
GRANT USAGE ON SCHEMA some_schema TO user1;
GRANT SELECT ON ALL TABLES IN SCHEMA some_schema TO user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema GRANT SELECT ON TABLES TO user1;

then :

\c my_database user1
select * from some_schema.t;

=> shows result 100 :)

---

Related Topics