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.
