PostgreSQL provides a RENAME TO clause that is used with the ALTER ROLE or the ALTER USER statements to rename a user. In Postgres, the session user(currently logged in) can't be renamed. You must log out from the current user and log in as another user to rename the current session user.
This write-up will teach you how to rename a user in Postgres via ALTER ROLE and ALTER USER commands.
How Do I Rename a User/Role in Postgres Using ALTER ROLE Command?
The below syntax must be followed to rename a user in Postgres:
ALTER ROLE user_name RENAME TO new_user_name;
Let’s implement it practically!
Example 1: Rename User Using ALTER ROLE Statement
Firstly, execute the “\du” command to see the available users:
\du;
Suppose we want to rename the “sample_user” to “sample_role”. For this purpose, the ALTER ROLE statement will be executed as follows:
ALTER ROLE sample_user RENAME TO sample_role;
Let’s verify the user alteration via the “\du” command:
\du;
The output snippet shows that the “sample_user” has been renamed to “sample_role”.
Example 2: Rename Currently Logged in User
We are logged in as “command_prompt”
Let’s try to rename it using the ALTER ROLE statement:
ALTER ROLE command_prompt RENAME TO cp_user;
An error occurred when we tried to rename the session user. To rectify this error, we must log in from some other user, as shown below:
In the above snippet, we are logged in as “postgres” users. Let’s execute the ALTER ROLE statement to rename the “command_prompt” user to “cp_user”:
ALTER ROLE command_prompt RENAME TO cp_user;
The above snippet verifies that the ALTER ROLE statement was executed successfully. Let’s verify the role’s modified name using the “\du” command:
\du;
The output clarifies that the “command_prompt” user has been renamed to “cp_user” successfully.
How Do I Rename a User/Role in Postgres Using ALTER USER Command?
To rename a user via ALTER USER statement, specify the ALTER USER statement followed by the user name. Next, specify the RENAME TO clause and the new username:
ALTER USER user_name RENAME TO new_user_name;
Let’s understand this concept practically.
Example: Rename User Using ALTER USER Statement
Let’s check the available users using the “\du” command:
\du;
Suppose we need to rename the “example_user” to “user_1”. For this purpose, we will execute the “ALTER USER” statement as follows:
ALTER USER example_user RENAME TO user_1;
You can verify the user’s modified name using the “\du” command:
\du;
The output snippet verifies that the “example_user” has been renamed to “user_1” successfully.
Conclusion
In PostgreSQL, the RENAME TO clause is used with the ALTER USER or ALTER ROLE statement to rename a user. In Postgres, the session user(currently logged in) can't be renamed. To do that, log out from the current user, log in as another user, and execute the ALTER USER or ALTER ROLE statement with the RENAME TO clause to rename the current session user. This write-up explained how to rename a user/role in Postgres via the ALTER ROLE and ALTER USER statements.
