I have a fresh postgresql (16.6) install in which I would like to create a database, a role account for that database, and then use that role account to create a table in the database:

CREATE DATABASE test_db;
CREATE USER testuser WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE test_db TO testuser;

Upon listing databases (\l) I can see that the user has some kind of access for the database:

                                                        List of databases
    Name     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges   
-------------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 postgres    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | 
 test_db     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =Tc/postgres         +
             |          |          |                 |             |             |            |           | postgres=CTc/postgres+
             |          |          |                 |             |             |            |           | testuser=CTc/postgres

However, after logging in as that user into that database, I am unable to create a table:

test_db=> CREATE TABLE test_table (id SERIAL PRIMARY KEY);
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE test_table (id SERIAL PRIMARY KEY);
                     ^

If I GRANT ALL PRIVILEGES ON [ALL TABLES IN] SCHEMA public TO testuser, creating the table still doesn't work. I also tried various forms of ALTER DEFAULT PRIVILEGES [IN SCHEMA public] GRANT ALL ON (TABLES|SCHEMAS) TO testuser but nothing seems to work.

If I ALTER DATABASE test_db OWNER TO testuser, I am then able to CREATE TABLE as that user as you'd expect, even without GRANTing any privileges (or even REVOKEing existing ones).

Why is GRANT ALL PRIVILEGES insufficient here? If I grant privileges on schema public as some other answers seem to suggest, and it does work, doesn't this potentially give permissions in databases other than test_db? What's considered to be best practice when handling the permissions of a role account like this?

I have a fresh postgresql (16.6) install in which I would like to create a database, a role account for that database, and then use that role account to create a table in the database:

CREATE DATABASE test_db;
CREATE USER testuser WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE test_db TO testuser;

Upon listing databases (\l) I can see that the user has some kind of access for the database:

                                                        List of databases
    Name     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges   
-------------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 postgres    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | 
 test_db     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =Tc/postgres         +
             |          |          |                 |             |             |            |           | postgres=CTc/postgres+
             |          |          |                 |             |             |            |           | testuser=CTc/postgres

However, after logging in as that user into that database, I am unable to create a table:

test_db=> CREATE TABLE test_table (id SERIAL PRIMARY KEY);
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE test_table (id SERIAL PRIMARY KEY);
                     ^

If I GRANT ALL PRIVILEGES ON [ALL TABLES IN] SCHEMA public TO testuser, creating the table still doesn't work. I also tried various forms of ALTER DEFAULT PRIVILEGES [IN SCHEMA public] GRANT ALL ON (TABLES|SCHEMAS) TO testuser but nothing seems to work.

If I ALTER DATABASE test_db OWNER TO testuser, I am then able to CREATE TABLE as that user as you'd expect, even without GRANTing any privileges (or even REVOKEing existing ones).

Why is GRANT ALL PRIVILEGES insufficient here? If I grant privileges on schema public as some other answers seem to suggest, and it does work, doesn't this potentially give permissions in databases other than test_db? What's considered to be best practice when handling the permissions of a role account like this?

• postgresql

• 1 You need to read the doc section Privileges in particular at point that starts The available privileges are:. Basically GRANT ALL PRIVILEGES ON DATABASE is not doing what you think it is. Also you will need to GRANT CREATE on the schema. Read the link posted a couple of times at least, there is lot covered in there. – Adrian Klaver Commented 7 hours ago
• I have vaguely understood that SCHEMAta exist orthogonal to the DATABASEs declared within my server, and that to create a table I need privileges on both the database and the schema I'm trying to create the table in; yet no combination of GRANT ... ON (DATABASE|SCHEMA) nor ALTER DEFAULT PRIVILEGES ... that I've tried seems to work. Is GRANT CREATE not subsumed within GRANT ALL ON SCHEMA public? If that isn't the right schema to be addressing, do I also need to explicitly create one for my database, despite the assertion that the public schema exists everywhere? – tallow_amnesty Commented 7 hours ago
• Read the link I posted and then come back with a clear definition of what you did. In other words add the question text the exact commands you used to get at your current state. Right now you have a mess of improper commands that make troubleshooting the problem pretty much impossible. – Adrian Klaver Commented 7 hours ago

3 Answers 3

From the manual, the section about "database":

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] } ON DATABASE database_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ] GRANTED BY role_specification ]

The only permissions you could give to someone are CREATE, CONNECT, TEMPORARY, and TEMP. That's it. This command doesn't touch any permission on any other object in the database. ALL is just a shortcut for all these four permissions.

Without the permission to use a SCHEMA, you can't create a table in that SCHEMA. But without permission to create a TABLE, you wouldn't be able to create one anyway.

when you GRANT ALL PRIVILEGES ON [ALL TABLES IN] SCHEMA public TO testuser in fact does not grant you all privileges, including CREATE privileges, it gives you permission to access existing tables not to create new. Changing the owner ALTER DATABASE test_db OWNER TO testuser, enables other privileges (including CREATE). To solve this I think you can do GRANT USAGE, CREATE ON SCHEMA public TO testuser . let me know you have more doubts
You can read more about Privileges here

My issue was that I misunderstood the public schema as a single schema across all databases in the cluster, rather than as a schema named public created separately and by default for all new databases. When I was running GRANT ALL ON SCHEMA public as the admin user, I was doing it in the postgres database and not test_db. Thus the solution I was looking for was to run as admin:

postgres=# \c test_db;
test_db=# GRANT ALL ON SCHEMA public TO testuser;