How to change database owner in PostgreSQL

Owner of the login is the user who created the database. Later, if we want to change the name of the database owner, we can change with alter database db_name owner to new_owner_name;.

Here, we have created the database ‘test’ and enteprisedb is owner of the database. Now, we are changing the owner of the database to k2admin. Please go through 3 steps.

1. To check current of the database.

edb=# \l

List of databases Name | Owner | Encoding | Collate | Ctype | ICU | Access privileges -----------+--------------+----------+-------------+-------------+-----+------------------------------- edb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | k2info | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | postgres | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | template0 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb + | | | | | | enterprisedb=CTc/enterprisedb template1 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb + | | | | | | enterprisedb=CTc/enterprisedb test | enterprisedb

| UTF8 | en_US.UTF-8 | en_US.UTF-8 | | (6 rows)

2. Change the database owner.

edb=# alter database test owner to k2admin;
ALTER DATABASE

3. Cross check the status of the database after moodification.

edb=# \l

                                           List of databases
 

Name | Owner | Encoding | Collate | Ctype | ICU | Access privileges -----------+--------------+----------+-------------+-------------+-----+------------------------------- edb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | k2info | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | postgres | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | template0 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb + | | | | | | enterprisedb=CTc/enterprisedb template1 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb + | | | | | | enterprisedb=CTc/enterprisedb test | k2admin

| UTF8 | en_US.UTF-8 | en_US.UTF-8 | | (6 rows)

You can leave a response, or trackback from your own site.

Leave a Reply

Powered by k2schools