In this tutorial, we will see How to change owner of database in PostgreSQL. We can change the database owner by using ALTER DATABASE command. If we change the owner name, only database owner name will cnage. But not objects(tables, views,…) owners.
Syntax to change the owner of the database in PostgreSQL:
ALTER DATABASE database_name owner to new_user;
Examples:
Lets verify the list of databases and their owners by using meta-command i.e \l or \list
postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- dvdnew | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | dvdrental | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | mydb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | r2schools | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres
1. From the above list, we are going to change owner of database ‘mydb’ to user ‘david’
ALTER DATABASE owner to david;
2. Now verify the owner of the database ‘mydb’ using \l database_name
postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- dvdnew | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | dvdrental | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | mydb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | r2schools | david | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres