PostgreSQL makes life easy for its users which are worried about managing and modifying the roles in databases. It is possible through the “ALTER ROLE” statement in PostgreSQL. Using the ALTER ROLE statement, users can change the name as well as the attributes of roles. Today, we will teach you how to use the “ALTER ROLE” statement in Postgres.

Let's start this journey with prerequisites.

Prerequisites: Check out the Existing Role in PostgreSQL

In the Postgres database, an existing role is already created whose name is “teach_role”. To check out the existing role in the PostgreSQL database, run the “\du” statement as shown below:

\du teach_role;

The above statement shows the details about the “teach_role”:

Now, users can verify that “teach_role” is placed in the column of “Role name”.

Alter the Existing Role With New Role in PostgreSQL

Use the “ALTER ROLE” statement along with the “RENAME TO” clause to modify the name of an existing role:

ALTER ROLE teach_role RENAME TO mentor_role;

In the above statement, “teach_role” represents the name of the existing role, which is altered with the “mentor_role” name in the database:

After executing the statement, “ALTER ROLE” is displayed as output as shown in the above figure.

By executing the “\du teach_role” statement, the user can verify that “teach_role” does not exist in the list of roles:

On the other hand, “mentor_role” can be displayed as the altered role that is located in the column of “Role name”.

Protect Role by ALTER ROLE in PostgreSQL

PostgreSQL provides the facility to protect the specified role in the database. For this, the “ALTER ROLE” statement is utilized with the “WITH PASSWORD” statement as given below.

ALTER ROLE mentor_role WITH PASSWORD 'asdf321';

In the above statement, “mentor_role” is protected with password “asdf321”:

The message “ALTER ROLE” is displayed as an output to confirm the protected role.

Create Roles and Database by ALTER ROLE in PostgreSQL

In PostgreSQL, users can create roles as well as databases through the “ALTER ROLE” statement. The statement is integrated with the “CREATEROLE CREATEDB” for creating role and database:

ALTER ROLE mentor_role CREATEROLE CREATEDB;

The “mentor_role” has the ability to create a role and database in PostgreSQL:

The output “ALTER ROLE” verifies that “mentor_role” is altered with new attributes i.e. Create role, and Create DB:

In the list of existing roles, the user can easily verify that the “mentor_role” has been altered successfully.

Conclusion

PostgreSQL provides the “ALTER ROLE” statement to alter the existing role with the new role. Using the ALTER ROLE statement, users can change the name as well as the attributes of the existing roles. This guide has expressed the practical implementation of the “ALTER ROLE” statement along with examples.