Permission denied after GRAND ALL in PostgreSQL
The usual behavior if you need to create a new database with a new user in PostgreSQL is to do something like this:
sudo -u postgres psql # or docker exec -it postgres_container psql -Upostgres
postgres=# CREATE DATABASE mydb;
postgres=# CREATE USER mydb_user WITH ENCRYPTED PASSWORD 'mydb_user_pass';
postgres=# GRANT ALL PRIVILEGES ON DATABASE mydb to mydb_user;
GRANT
And it works all the time I have worked with PostgreSQL. But not now. With trying to create a table I receive the following error: permission denied for schema public:
postgres=# \c mydb mydb_user; # switch user and database;
You are now connected to database "mydb" as user "mydb_user".
mydb=> CREATE TABLE foo (id int);
ERROR: permission denied for schema public
LINE 1: CREATE TABLE foo (id int);
^
WTF? Maybe I should GRANT ALL to Public schema? Ok, let’s try:
mydb=> \c mydb postgres; # switch user to postgres
You are now connected to database "mydb" as user "postgres".
mydb=# GRANT ALL ON SCHEMA public TO mydb_user;
GRANT
mydb=# \c mydb mydb_user; # switch user to mydb_user
You are now connected to database "mydb" as user "mydb_user".
mydb=> CREATE TABLE foo (id int);
ERROR: permission denied for schema public
LINE 1: CREATE TABLE foo (id int);
^
Hmmm… After some googling, I found that CREATE command is eligible for only database owners! This feature was introduced in October 2022 with PostgreSQL version 15. You can find it here
in “Other Notable Changes”.
So now, to create tables, user should be the owner of the database in PostgreSQL 15. To set ownership you need to execute this command:
ALTER DATABASE mydb OWNER TO myuser;
Or, in my case:
mydb=> \c mydb postgres # switch user to postgres
You are now connected to database "mydb" as user "postgres".
mydb=# ALTER DATABASE mydb OWNER TO mydb_user;
mydb=> \c mydb mydb_user # switch user to mydb_user
You are now connected to database "mydb" as user "mydb_user".
mydb=> CREATE TABLE foo (id int);
CREATE TABLE
Wow! It Works!