In PostgreSQL, a superuser bypasses all the permission checks except for logging in. Managing a system requires a superuser account, which has broad privileges. To create a superuser in Postgres, use the CREATE ROLE or CREATE USER statement with the SUPERUSER attribute.
This Postgres blog will teach you how to create a superuser or change a user to a superuser via practical examples. So, let’s start!
How to Create a Superuser in Postgres?
In Postgres, the CREATE ROLE or CREATE USER statements are used to create a user/role. The CREATE USER statement is the same as CREATE ROLE, with the exception that the CREATE USER statement has LOGIN privileges by default, whereas CREATE ROLE doesn’t.
Syntax
To create a superuser in Postgres, use the CREATE USER statement followed by the user name and finally specify the SUPERUSER attribute:
CREATE USER user_name SUPERUSER;
To create a superuser, you can also use the CREATE ROLE statement as follows:
CREATE ROLE user_name SUPERUSER LOGIN PASSWORD 'password';
Example 1: How Do I Create a Superuser in Postgres Using CREATE USER Statement?
Let’s execute the below statement from the SQL Shell to create a superuser in Postgres:
CREATE USER example_user SUPERUSER;
The “CREATE ROLE” message in the output indicates that the user has been created. Run the “\du” command to verify the user creation:
\du;
From the above snippet, you can authenticate that a superuser named “example_user” has been created successfully.
Example 2: How Do I Create a Superuser in Postgres Using CREATE ROLE Statement?
As mentioned earlier, the CREATE ROLE statement has no login credentials/privileges by default. So you must specify the LOGIN attribute alongside the SUPERUSER attribute to create a superuser using CREATE ROLE statement:
CREATE ROLE user_admin SUPERUSER LOGIN PASSWORD '12345';
Let’s verify the user creation via the below command:
\du;
A superuser named “user_admin” has been created successfully.
How to Change/Alter a Simple User to Superuser in Postgres?
Use the ALTER USER command with the SUPERUSER attribute to modify a user to superuser in Postgres:
ALTER USER user_name WITH SUPERUSER;
In the above snippet:
- User_name represents a user to be altered.
- WITH is an option used to specify the SUPERUSER attribute.
Example: How Do I Change/Modify a Simple User to Superuser in Postgres?
Follow the steps provided below to change a particular user to a superuser in Postgres:
Step 1: List Users
Run the “\du” command to get the list of users:
\du;
Suppose we want to change a user named “hr_manager” to a superuser.
Step 2: Change User to Superuser
Run the below-provided command to change the “hr_manager” from user to superuser:
ALTER USER hr_manager WITH SUPERUSER;
The “ALTER ROLE” message in the output signifies that the given user has been altered to a superuser.
Step 3: Verify User Alteration
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 hr_manager has been changed to a superuser successfully.
That’s it from this Postgres blog!
Conclusion
To create a superuser in Postgres, use the CREATE ROLE or CREATE USER statement with the SUPERUSER attribute. The CREATE USER statement is the same as CREATE ROLE, with the exception that the CREATE USER statement has LOGIN privileges by default, whereas CREATE ROLE doesn’t. Use the ALTER USER command with the SUPERUSER attribute to modify a user to a superuser in Postgres. This blog post has demonstrated how to create a superuser in PostgreSQL using various examples.
