How To Change User to Superuser in PostgreSQL

Last updated on August 14th, 2024 at 10:06 am

Often database administrators need to change user permissions from one role to another. Sometimes you may need to change a regular user to superuser in PostgreSQL. A superuser has all privileges and permissions while a regular user has restricted permissions. In this article, we will look at how to modify user to superuser in PostgreSQL.

How To Change User to Superuser in PostgreSQL

It is really easy to change user to superuser as well as rollback superuser to regular user in PostgreSQL. You can do this using ALTER USER as well as ALTER ROLE statements.

1. Using ALTER USER

You can use ALTER USER command to change user to superuser in PostgreSQL. Here is its syntax.

ALTER USER user_name WITH SUPERUSER;

Here is an example to demonstrate it. Log into PostgreSQL and run the following ALTER USER command to change user test_user to superuser. Replace test_user with username as per your requirement.

postgres-# ALTER USER test_user WITH SUPERUSER;

In the above command, we use WITH SUPERUSER clause, after username, to modify user to superuser.

Similarly, run the following ALTER USER command to remove superuser privilege from a user. In this case use WITH NOSUPERUSER clause.

postgres-# ALTER USER test_user WITH NOSUPERUSER;

ALTER USER statement can also be used to change user password in PostgreSQL.

2. Using ALTER ROLE

Similarly, you can also use ALTER ROLE command to upgrade regular user to a superuser. Here is its syntax.

postgres-# ALTER ROLE user_name WITH SUPERUSER;

Here is the command to modify user test_user to superuser. Change test_user to the username which you need to change to superuser.

postgres-# ALTER ROLE test_user WITH SUPERUSER;

Similarly, if you want to convert superuser to a regular user, then add WITH NOSUPERUSER clause after username.

postgres-# ALTER ROLE test_user WITH NOSUPERUSER;

Conclusion

That’s it. As you can see it is really easy to modify user to superuser and a superuser to regular user in PostgreSQL. You can use either ALTER USER or ALTER ROLE depending on your requirement. Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.