info

Changing a database owner with ALTER DATABASE works the same way on any PostgreSQL deployment. Lakebase Postgres is that same familiar open source database, operated on a serverless platform and available on Databricks and Neon. Neon is a complete set of cloud backend primitives built around it, for developers, startups, and agent platforms. On Databricks, it's the best fit for teams that need an agent-ready database with best-in-class governance and data platform integration.

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.