How to change the owner of database in PostgreSQL

If we want to alter(change) the owner, we must own the database and also be a direct or indirect member of the new owning role, and we must have the CREATEDB privilege.

How to change the owner of database in PostgreSQL

1. Check the current owner of the database. Run the below query to get owner of database.

SELECT d.datname as "Database Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner" FROM pg_catalog.pg_database d WHERE d.datname = 'company' ORDER BY 1;

Output

Database Name | Owner ---------------+---------- company | postgres

2. Change the owner of the database ‘company’ from user ‘postgres’ to user(role) ‘k2admin’.

postgres=# alter database company owner to k2admin; ALTER DATABASE

3. Verify the owner of the database ‘company’.

SELECT d.datname as "Database Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner" FROM pg_catalog.pg_database d WHERE d.datname = 'company' ORDER BY 1;

Output

Database Name | Owner ---------------+--------- company | k2admin

or simply run \l database_name. It will also give the owner of database.

postgres=# \l company
List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges ---------+---------+----------+----------------------------+----------------------------+------------------- company | k2admin | UTF8 | English_United States.1252 | English_United States.1252 |

Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: