How to Change the Owner of a PostgreSQL Database

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;Code language: SQL (Structured Query Language) (sql)

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 postgresCode language: SQL (Structured Query Language) (sql)

Second, create a new role with the CREATEDB privilege:

CREATE ROLE alex 
WITH CREATEDB LOGIN PASSWORD 'Abcd1234';Code language: SQL (Structured Query Language) (sql)

Third, create another role called steve:

CREATE ROLE steve;Code language: SQL (Structured Query Language) (sql)

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

psql -U alexCode language: SQL (Structured Query Language) (sql)

Fifth, create a new database called scm:

CREATE DATABASE scm;Code language: SQL (Structured Query Language) (sql)

Sixth, quit alex‘s session:

\qCode language: SQL (Structured Query Language) (sql)

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

\l scmCode language: SQL (Structured Query Language) (sql)

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)Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Ninth, show the scm database again:

\l scmCode language: SQL (Structured Query Language) (sql)

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)Code language: SQL (Structured Query Language) (sql)

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.