Postgresql: what does GRANT ALL PRIVILEGES ON DATABASE do?

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.

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 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.

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