Postgresql: what does GRANT ALL PRIVILEGES ON DATABASE do?

Question

I'm trying to grant all privileges on all tables of a given database to a new postgres user (not the owner). It seems that GRANT ALL PRIVILEGES ON DATABASE my_db TO new_user; does not do that. After running said command successfully (as the postgres user), I get the following as new_user:

$ psql -d my_db
my_db => SELECT * FROM a_table_in_my_db;
ERROR:  permission denied for relation a_table_in_my_db

Two questions:

1) What does the command above do, then, if not granting all permissions on all tables on my_db?

2) What's the proper way to grant all permissions on all tables to a user? (including on all tables created in the future)

Answer 1

The answers to your questions come from the online PostgreSQL 8.4 docs.

1. GRANT ALL PRIVILEGES ON DATABASE grants the CREATE, CONNECT, and TEMPORARY privileges on a database to a role (users are properly referred to as roles). None of those privileges actually permits a role to read data from a table; SELECT privilege on the table is required for that.

2. I'm not sure there is a "proper" way to grant all privileges on all tables to a role. The best way to ensure a given role has all privileges on a table is to ensure that the role owns the table. By default, every newly created object is owned by the role that created it, so if you want a role to have all privileges on a table, use that role to create it.

   PostgreSQL 9.0 introduces the following syntax that is almost what you want:

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

   The rub is that if you create tables in schemas outside the default "public" schema, this GRANT won't apply to them. If you do use non-public schemas, you'll have to GRANT the privileges to those schemas separately.

Answer 2

It is possible to set up multiple logins to act as the database owner:

• Create a "nologin" role to act as the owner: CREATE ROLE dbowner NOLOGIN
• Change the owner of your database to this: ALTER DATABASE mydb OWNER TO dbowner
• Grant all your logins to this new role: GRANT dbowner TO user1, user2

Now, if either user1 or user2 login, they have all permissions on "mydb" without any further grants required.

However, I would consider this solution carefully. It is tempting to have your web application use one of these logins to avoid the pain of creating additional grants whenever the schema is updated, but you're removing a very useful form of protection this way. Use the above solution if you really do want multiple "admins", but stick with the "grant all privileges on all tables in schema ..." pattern above for the login for your "normal use" application.

Answer 3

In PostgreSQL 12 and later, it is possible to grant all privileges of a table in a database to a role/user/account.

The syntax is:

GRANT ALL ON table_name TO role_name;

If you want to grant it to all tables in the database then the syntax will be:

GRANT ALL ON ALL TABLES TO role_name;

If you want to grant it to all tables of a schema in the database then the syntax will be:

GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO role_name;

Here's how I did it:

First, I logged into the Postgres database server:

psql -U postgres

Output

psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
Type "help" for help.

Next, I created a database user/role/account:

CREATE ROLE alice4 WITH LOGIN PASSWORD 'securePass1';

Output

CREATE ROLE

Next, I created a table in the postgres database:

create table candidates (
    candidate_id int generated always as identity,
    first_name varchar(100) not null,
    last_name varchar(100) not null,
    email varchar(255) not null unique,
    phone varchar(25) not null,
    primary key(candidate_id)
);

Output

CREATE TABLE

Next, I granted all privileges on the candidates table to the account/user/role alice4

GRANT ALL ON candidates TO alice4;

Output

GRANT

Next, logged out of the postgres database and the postgres account/user/role:

exit

Next, I logged into the Postgres database using the alice4 account/user/role:

psql -U alice4 -W postgres

Output

Password: 
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
Type "help" for help.

Next, I inserted data into the candidates table that was previously created:

INSERT INTO candidates(first_name, last_name, email, phone)
VALUES('Joe','Com','[email protected]','408-111-2222');

Output

INSERT 0 1

Next, I selected all data in the candidates table that was previously created:

SELECT * FROM candidates;

Output

 candidate_id | first_name | last_name |        email        |    phone     
--------------+------------+-----------+---------------------+--------------
            1 | Joe        | Com       | [email protected] | 408-111-2222
(1 row)

Resources: PostgreSQL GRANT

That's all

I hope this helps

Answer 4

since PostgreSQL 11 there is an improvement to Chris Cogdon's answer:

1. create a role that will own the database objects with NOLOGIN (in his examle: dbowner)
2. grant it to the desired users
3. To alter the schema, at the begin of each session switch to role that should own: set role dbowner;

Then all created database object will belong to dbowner (also sequences,...), and therefore also to the other admins, without the need to change the ower explicitly.

If dbrole has less permissions than your current user, you will have to re-connect to regain rights.

Resources: PostgreSQL: SET ROLE