How to Change a User to Superuser in PostgreSQL?

Changing a user to a superuser in PostgreSQL is a straightforward process that involves using commands to alter the user’s role. PostgreSQL, as a robust open-source relational database management system, allows administrators to manage user roles efficiently. PostgreSQL provides the flexibility to adjust user roles dynamically, ensuring that the right users have the necessary permissions to perform specific tasks.

In this article, we are going to learn how we can change a USER to a Superuser. Postgresql is one of the famous open-source RDBMS used by many companies, and startups and is growing day by day. So, learning Postgresql will add a new skill to your inventory.

How to Change a User to Superuser in PostgreSQL?

The process of changing a user to a superuser involves two primary steps the first one is to create an ordinary user and then alter the user’s role to superuser status. To create a user the CREATE USER command is used to specify the username and any additional parameters as needed. Once the user is created the ALTER USER or ALTER ROLE command is used to modify the user’s role to superuser. The following methods are used to Change a User to Superuser in PostgreSQL are as follow:

1. Change an Ordinary User to a Superuser Using ALTER USER Statement
2. Change an Ordinary User to a Superuser Using ALTER ROLE Statement

Let’s set up an Environment for performing operations

Before changing the role of a user, we have to create an ordinary user with no role. We will use the query CREATE USER to create a new user with no superuser role. The user created with this query will be ordinary with a specific role.

Command:

CREATE USER <user_name>;

Output:

User Created

Explanation:

• CREATE USER <user_name>: This command will create an ordinary user with name. Here we have created and user with name “ujjwal”.
• CREATE ROLE: This shows that command is executed successfully and the user is created successfully without any error.

Let’s List the Users with their Roles

Now as we have created an ordinary user with no specific role, but we might want to see the users and their respective roles. So, to perform that operation we have to use the respective command.

Command:

\du

Output:

user with their roles

Explanation:

• /du: This command is used to list all the users and with their specific roles.
• Here “d” means describe and “u” means users.
• Use this command whenever you want list or describe users with their specific roles.
• Here “ujjwal” user is just an ordinary user and doesn’t have any attributes with it.

1. Change an Ordinary User to a Superuser Using ALTER USER Statement

Now we have already created an ordinary user, its time to change that USER to Superuser using ALTER USER statement. The statement syntax looks like this:

Syntax:

ALTER USER user-name WITH SUPERUSER;

Explanation:

• ALTER: is used to alter or a make changes to something , it might a table, user etc.
• USER: it is used to define that we want to alter or make changes in USER.
• WITH: it is used to interchange the attribute.
• SUPERUSER: Attribute we are assigning to user.

Example: Changing Ordinary User to Superuser Using ALTER USER Statement

Now we are going to alter role of ordinary user to superuser in Postgres using ALTER USER statement.

Command:

ALTER USER ujjwal WITH SUPERUSER;

Output:

Role altered using ALTER command

Now check the final result, with the below query:

Command:

\du

Output:

Role changed

Explanation: As we can see we have successfully change an ordinary user to superuser using ALTER statement.

2. Change an Ordinary User to a Superuser Using ALTER ROLE Statement

We have so far covered all the related topics, like creating an ordinary user and then changing an ordinary USER to Superuser using ALTER USER statement.

Now, we are going to change ordinary user to superuser using ALTER ROLE Statement, the statement is similar to ALTER USER statement and does the same work. Lets look at the syntax:

Syntax:

ALTER ROLE user_name WITH SUPERUSER;

Explanation:

• ALTER: To make changer or alter attributes in user, table etc.
• ROLE: To define that we want to alter role of the user.
• WITH: To specify the role to change with.
• SUPERUSER: Attribute we are assigning to an ordinary user.

Example: Changing ordinary user to superuser using ALTER ROLE statement in Postgres

Now we are going to change ordinary User to Superuser, but here we are going to use ALTER ROLE Statement in postgres. For that purpose, we have already created a new ordinary user, follow the above explained steps to create a user in postgres. Let’s Creating a new ordinary user to alter its role.

Command:

CREATE USER <username>;

Output:

user-created

Now, we will alter role using ALTER ROLE Statement are defined below.

Command:

ALTER ROLE zoro WITH SUPERUSER;

Output:

Role altered

Explanation:

• We have created a new ordinary user with name “zoro”.
• We used ALTER ROLE command to change ordinary user that is “zoro” to Superuser.
• To see the changes we use “\du” command.

Conclusion

Overall, we have learned about How to Change a User to Superuser in PostgreSQL Using ALTER ROLE and ALTER USER will alter the role of an ordinary user to Superuser, it depends on your choice to use which command among both of these commands. Both statements are used with “WITH” to specify the attribute to change or alter with. Learning how to manage user roles in PostgreSQL is a valuable skill that can enhance your career prospects. Whether you’re a developer, database administrator or IT professional, understanding PostgreSQL’s user management features can help you effectively manage database access and permissions.