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.