How to Change the Owner of the PostgreSQL Database

We can use the REASSIGN OWNED query to change the ownership of the existing database. The new user can perform all the queries that the previous owner could do.

Change the Ownership of a Database in Psql

For demonstration purposes, let’s create a new database with an old owner and a new user. Then, assign the new user as the owner of the new database.

Creating a database:

postgres=# create DATABASE SELL;
CREATE DATABASE
postgres=#

Creating a new user:

postgres=# CREATE USER TEST_USER WITH PASSWORD '~test~';
CREATE ROLE
postgres=#

The current owner of the Sell table:

SELECT dbs.datname, roles.rolname
FROM pg_catalog.pg_database dbs, pg_catalog.pg_roles roles
WHERE dbs.datdba = roles.oid;

Initially, we logged in as postgres; this was our user name. Now, if we run the above query, then it will show me the owner of the existing database inside Postgres:

  datname  | rolname
-----------+----------
 template0 | postgres
 template1 | postgres
 sell      | postgres
 postgres  | postgres
(4 rows)

We can see that table Sell has the owner postgres. Let’s change the owner with the ’test_user'.

Here’s how we will do it:

ALTER DATABASE SELL OWNER TO TEST_USER;

And here’s the Output:

postgres=# ALTER DATABASE SELL OWNER TO TEST_USER;
ALTER DATABASE
postgres=# SELECT dbs.datname, roles.rolname
postgres-# FROM pg_catalog.pg_database dbs, pg_catalog.pg_roles roles
postgres-# WHERE dbs.datdba = roles.oid;
  datname  |  rolname
-----------+-----------
 template0 | postgres
 template1 | postgres
 postgres  | postgres
 sell      | test_user
(4 rows)


postgres=#

You can see that the database owner named sell has been changed to test_user.

To know more about the altering database command, follow the official documentation.