A superuser account with broad privileges is required for system administration. Superusers in Postgres have access to high-level privileges beyond ordinary users. To create a superuser in Postgres, use the CREATE ROLE or CREATE USER statement with the SUPERUSER attribute. However, if you have already created a user without the SUPERUSER attribute, you can modify it using the ALTER USER or ALTER ROLE command.

This Postgres blog will teach you how to change a user to a superuser via practical examples. The below concepts will be covered in this blog post:

• Create an Ordinary User
• How to Change an Ordinary User to a Superuser Using ALTER USER Statement?
• How to Change an Ordinary User to a Superuser Using ALTER ROLE Statement?

Create an Ordinary User

Firstly, let’s create a user without SUPERUSER privileges using the CREATE USER command:

CREATE USER Joe;

Run the \du command to verify the user creation:

\du;

The above snippet shows that a user named “joe” has been created without the “SUPERUSER” attribute.

How to Change an Ordinary User to a Superuser Using ALTER USER Statement?

To change an ordinary user to a superuser in Postgres, use the ALTER USER command with the SUPERUSER attribute:

ALTER USER user_name WITH SUPERUSER;

In the above syntax, user_name represents a user to be altered, while the “WITH” option is used to specify the SUPERUSER attribute.

Example: How Do I Change an Ordinary User to Superuser in Postgres?

Run the “ALTER USER” command using the “WITH” clause to change a user named “joe” to a superuser:

ALTER USER joe WITH SUPERUSER;

Run the below command to check if the specified user has been changed to the superuser or not:

\du;

The output snippet proves that the “joe” has been changed to a superuser successfully.

How to Change an Ordinary User to a Superuser Using ALTER ROLE Statement?

To alter an existing role/user to a superuser, execute the ALTER ROLE statement with the SUPERUSER clause as follows:

ALTER ROLE user_name
WITH SUPERUSER;

In the above syntax, user_name represents a user to be altered, while the “WITH” option is used to specify the SUPERUSER attribute.

Example: How Do I Change an Ordinary User to Superuser in Postgres?

Suppose we need to alter a user named “emp_limit” to the superuser. For this purpose, we will execute the ALTER ROLE command as follows:

ALTER ROLE emp_limit 
WITH SUPERUSER;

The targeted role has been altered successfully. Let’s verify it via the below command:

\du;

The output authenticates the working of ALTER ROLE statement as it succeeded in changing an ordinary user to a superuser.

Conclusion

In PostgreSQL, the ALTER USER and ALTER ROLE statements are used to change an ordinary user to a superuser. For this purpose, execute the ALTER USER or ALTER ROLE statement along with the SUPERUSER attribute. To specify a SUPERUSER attribute, the “WITH” option is used in PostgreSQL. This Postgres blog taught us how to change an ordinary user to a super user using ALTER USER and ALTER ROLE statements.