Schema privileges vs Database privileges in PostgreSQL-CodePudding

In a PostgreSQL server, I want to create a database (db1) and give all privileges on that database to a user (user1). I run these commands:

CREATE USER user1 WITH PASSWORD 'password';
CREATE DATABASE db1;
\c db1
CREATE SCHEMA user1;
DROP SCHEMA public;

Now the database (db1) has only the schema user1. The next step is to grant all privileges to the user (user1).

If I run the following commands, and try to create a table as user1, it works:

GRANT ALL PRIVILEGES ON SCHEMA user1 TO user1;
\c db1 user1
CREATE TABLE t1(a int);

If I only grant privileges on the database (and not the schema), it does not work:

GRANT ALL PRIVILEGES ON DATABASE db1 TO user1;
\c db1 user1
CREATE TABLE t1(a int);

The create table will fail with these errors:

db1=> CREATE TABLE t1(a int);
ERROR:  no schema has been selected to create in
LINE 1: CREATE TABLE t1(a int);
                     ^
db1=> CREATE TABLE user1.t1(a int);
ERROR:  permission denied for schema user1
LINE 1: CREATE TABLE user1.t1(a int);
                     ^

So, my questions are: Is the GRANT ALL PRIVILEGES ON DATABASE really needed here? What are the privileges granted by that command?

CodePudding user response：

What are the privileges granted by that command?

According to the privileges documentation, a GRANT ALL on the DATABASE level encompasses:

CREATE: allows new schemas and publications to be created within the database, and allows trusted extensions to be installed within the database.
CONNECT: Allows the grantee to connect to the database. This privilege is checked at connection startup (in addition to checking any restrictions imposed by pg_hba.conf).
TEMPORARY: Allows temporary tables to be created while using the database.

By default, anyone (PUBLIC) has CONNECT and TEMPORARY privileges already, so GRANT ALL PRIVILEGES ON DATABASE db1 TO user1; will only affect the CREATE privilege. Judge yourself whether that's really needed.

Instead of creating the schema and granting all privileges on it to user1, you may want to grant the CREATE privilege on the database, and let the user1 create their schema themselves (so that they become its owner and will thereby get all the privileges on it). It will allow them to also create any other schema then.

CodePudding user response：

Thanks @Bergi it is clear now. Using the GRANT ALL PRIVILEGES ON DATABASE the following works:

postgres=# CREATE USER user1 WITH PASSWORD 'password';
CREATE ROLE
postgres=# CREATE DATABASE db1;
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON DATABASE db1 TO user1;
GRANT
postgres=# \c db1 user1
You are now connected to database "db1" as user "user1".
db1=> CREATE SCHEMA user1;
CREATE SCHEMA
db1=> DROP SCHEMA public;
ERROR:  must be owner of schema public
db1=> CREATE TABLE t1(a int);
CREATE TABLE
db1-> \dt
       List of relations
 Schema | Name | Type  | Owner 
-------- ------ ------- -------
 user1  | t1   | table | user1
(1 row)

db1-> \dn
  List of schemas
  Name  |  Owner   
-------- ----------
 public | postgres
 user1  | user1
(2 rows)

Observe that the user (user1) cannot delete the schema public because the owner is postgres, even the user has all privileges on the database (db1).