I recently upgraded the PostgreSQL server on one my server from version 14 to 16. Why the big jump? This was due to the OS running on this server was upgraded from Ubuntu 22.04 to 24.04.

When the database was restored from the database dump, the application that requires that the database detects the new version and tried to perform the database migration. That’s when it failed and produced the following error message:

[2024-06-20 13:54:25.873][panic][ERROR] thread 'main' panicked at 'Error running migrations: 
DatabaseError(Unknown, "permission denied for schema public")': src/db/mod.rs:487

I have had similar issue the other day with my own Rust program that talks to PostgreSQL 15. I solved the issue with the help from the internet, and kind of forgot about it. If only had I wrote down what I did, I might not have to write this blog post. :P

Anyway, according to this [1], starting from PostgreSQL 15 and probably applies to 16 also, there has been a change in the way table creation permissionis are handle for user. I’m not a database amin and my interaction with the database is very limited. Full credit go to the stackoverflow discussion at [1].

There are two noticeble solutions from [1].

  1. Get the owner of the schema to run:
GRANT USAGE ON SCHEMA public TO your_user;
  1. Set the owner of the database to your_user:
ALTER DATABASE your_database OWNER TO your_user;

I’m going to pick the second option, and will apply to my database.

First, let’s see who is the owner of my database appdb.

postgres=# \l appdb
                                                      List of databases
    Name     |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |    Access privileges
-------------+----------+----------+-----------------+---------+---------+------------+-----------+--------------------------
 appdb       | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =Tc/postgres            +
             |          |          |                 |         |         |            |           | postgres=CTc/postgres   +
             |          |          |                 |         |         |            |           | appdb=CTc/postgres
(1 row)

The above output indicates that postgres user is the owner of appdb. Let’s list all users (roles) before updating the new owner for the appdb database.

postgres=# \du
                              List of roles
  Role name  |                         Attributes
-------------+------------------------------------------------------------
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS
 appdb       |

So, I also named the user appdb (for the lack of creativity). We can set the owner of the database appdb to appdb as below:

postgres=# ALTER DATABASE appdb OWNER TO appdb;
ALTER DATABASE

postgres=# \l appdb
                                                         List of databases
    Name     |    Owner    | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |      Access privileges
-------------+-------------+----------+-----------------+---------+---------+------------+-----------+-----------------------------
 appdb       | appdb       | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =Tc/appdb            +
             |             |          |                 |         |         |            |           | appdb=CTc/appdb
(1 row)

Now… the moment of truth… let me restart my application.

# podman ps  | grep appdb
5c60d83e6ae7  docker.io/xxxxx/yyyy:latest  /start.sh             44 minutes ago     Up 5 minutes  127.0.0.1:8000->80/tcp    appdb

That did the trick! My container survived more than 5 minutes already.

A quick summary, if you migrate from PostgreSQL 14 or older to version 15 or newer, and received the same error message, it’s likely that you need to either change the owner of the database or grant the usage on schema to the database user.

Reference: