PostgreSQL provides an ALTER DATABASE command that allows us to modify a database. For instance, using ALTER DATABASE command, you can alter the database name, attributes, owner, etc. In Postgres, the ALTER DATABASE command uses the OWNER TO clause to change/modify the database owner.
Using practical examples, this Postgres tutorial will show you how to change/alter the database owner. So, let’s get started!
How to Change Database OWNER in Postgres?
To change/modify the database owner, users must follow the below syntax:
ALTER DATABASE db_name OWNER TO new_owner_name;
To change the database owner:
- Specify the ALTER DATABASE command followed by the database name.
- After that, specify the new owner's name in the OWNER TO clause.
Example: How Do I Change the Database Owner in PostgreSQL?
In this example, we will guide you step-by-step on how to change the database owner:
Step 1: Check Database Owners
Firstly, execute the “\l” command from SQL Shell to check the list of databases along with their respective owners:
\l
The above snippet shows that the owner of the “emp_db” is “command_prompt”.
Step 2: Change Database Owner
Execute the below command to change/modify the database owner from “command_prompt” to “postgres”:
ALTER DATABASE emp_db OWNER TO postgres;
In the above snippet:
- ALTER DATABASE is used to modify the “emp_db” database.
- The OWNER TO clause is used to specify the name of the new database owner, i.e., “postgres”:
The output clarifies that the database owner has been changed successfully.
Step 3: Verify Database Owner
To verify the database owner, we will execute the “\l” command one more time:
\l
The output proves that the owner of the “exp_db” database has been changed from “command_prompt” to “postgres”.
Note: Only superusers can change the owner of the Postgres database.
That’s it from this Postgres guide.
Conclusion
PostgreSQL's ALTER DATABASE command is used in conjunction with the OWNER TO clause to change the database owner. For this purpose, specify the ALTER DATABASE command followed by the database name and after that, specify the OWNER TO clause followed by the name of the new owner. This blog post has explained how to change the database owner in PostgreSQL using practical examples.
