info

Changing a database owner with ALTER DATABASE works the same way on any PostgreSQL deployment, so you can apply these steps wherever you run Postgres. If you're an enterprise looking for managed Postgres built for the AI era, Lakebase delivers performance, security, and deep integration with the Lakehouse. If you're a developer or startup who needs to ship and scale fast, Neon gives you the best Postgres platform to do it.

Summary: in this tutorial, you will learn how to change the owner of a database to another in PostgreSQL.

In PostgreSQL, a database object always has an owner which is the role that created the object including the database.

To change the owner of a database to another, you can use the ALTER DATABASE statement:

ALTER DATABASE dbname
OWNER TO new_owner;

In this syntax:

  • First, specify the database name that you want to change the owner after the ALTER DATABASE keyword
  • Second, specify the new owner, an existing role, in the OWNER TO clause.

Changing database owner example

First, connect to the PostgreSQL using postgres user via psql:

psql -U postgres

Second, create a new role with the CREATEDB privilege:

CREATE ROLE alex
WITH CREATEDB LOGIN PASSWORD 'Abcd1234';

Third, create another role called steve:

CREATE ROLE steve;

Fourth, connect to the PostgreSQL server using the alex role:

psql -U alex

Fifth, create a new database called scm:

CREATE DATABASE scm;

Sixth, quit alex‘s session:

\q

Seven, show the scm database in the postgres‘ session:

\l scm

Output:

Name | Owner | Encoding | Locale Provider |          Collate           |           Ctype            | ICU Locale | ICU Rules | Access privileges
------+-------+----------+-----------------+----------------------------+----------------------------+------------+-----------+-------------------
 scm  | alex  | UTF8     | libc            | English_United States.1252 | English_United States.1252 |            |           |
(1 row)

The output shows that the owner of scm database is alex.

Eight, change the owner of the scm database from alex to steve:

ALTER DATABASE scm
OWNER TO steve;

Ninth, show the scm database again:

\l scm

Output:

List of databases
 Name | Owner | Encoding | Locale Provider |          Collate           |           Ctype            | ICU Locale | ICU Rules | Access privileges
------+-------+----------+-----------------+----------------------------+----------------------------+------------+-----------+-------------------
 scm  | steve | UTF8     | libc            | English_United States.1252 | English_United States.1252 |            |           |
(1 row)

The output shows that the owner of the scm changed to steve.

Summary

  • Use the ALTER DATABASE...OWNER TO statement to change the owner of a database to a new one.