In PostgreSQL, a role is an important entity that has different privileges to perform operations in a database. For instance, you can create multiple roles to perform multiple operations. Each role has its own rights according to its specifications.

Today, we will show you how to use the “CREATE ROLE” statement along with practical examples in PostgreSQL.

• Method 1: Create a Simple User Role in PostgreSQL
• Method 2: Create a Role With Login Attribute in PostgreSQL
• Method 3: Create a Superuser Role in PostgreSQL
• Method 4: Create a Role With Validity in PostgreSQL
• Method 5: Create a Role That can Create a Database in Postgres
• Method 6: Create a Role for Connection Limit in PostgreSQL

So, let’s begin!

Method 1: Create a Simple User Role in PostgreSQL

In the PostgreSQL database, users can create a new role with the “CREATE ROLE” statement:

CREATE ROLE std_role;

The output shows that a role named “std_role” has been created successfully. Execute the “\du” command to see the available roles:

\du;

The output shows that the std_role has been created successfully; however, from the output, it is clear that the “std_role” can’t log in.

Method 2: Create a Role With Login Attribute in PostgreSQL

To assign the login privileges to the std_role, you must use the LOGIN attribute:

CREATE ROLE std_role LOGIN PASSWORD 'ABC123';

Let’s run the \du command to verify the role’s creation:

\du;

This way, users can create a role with a login attribute.

Method 3: Create a Superuser Role in PostgreSQL

To create a superuser role in PostgreSQL, write down the below statement:

CREATE ROLE teacher_role SUPERUSER LOGIN PASSWORD 'xyz321';

Let’s verify the role’s creation using “\du” command:

\du;

The output clarifies that a new role named “teacher_role” has been created as a superuser.

Method 4: Create Role With Validity Duration in PostgreSQL

Do you wanna create a role whose password should expire after a specified period of time? If yes! Then you can utilize the “VALID UNTIL” attribute along with the CREATE ROLE statement as follows:

CREATE ROLE submit_assignment
LOGIN PASSWORD 'pas321'
VALID UNTIL '2022-12-01';

Let’s run the “\du” command to verify the role creation:

\du;

The output proves that the role “submit_assigment” has been successfully created with the VALID UNTIL attribute.

Method 5: Creating a Role That can Create a Database in Postgres

PostgreSQL provides a facility to specify the role with database creation privileges. For this purpose, execute the “CREATE ROLE” command as follows:

CREATE ROLE teach_role
CREATEDB
LOGIN PASSWORD 'ABCXYZ';

A new role named “teach_role” has been created successfully. Execute the “\du” command to verify the role creation:

\du

The output clarifies that the teach_role has been created with the create DB privileges.

Method 6: Create a Role for Connection Limit in PostgreSQL

For concurrent connections, use the “CREATE ROLE” statement and specify the number of connections via the “CONNECTION LIMIT” attribute.

CREATE ROLE emp_limit
LOGIN
PASSWORD 'ABCXYZ'
CONNECTION LIMIT 200;

The CREATE ROLE message indicates that a new role has been created successfully. Run the below command to verify the role’s creation:

\du

The output shows that a new role with the specified connection limit has been created successfully.

That’s it! You have experienced all possible use cases to create roles in PostgreSQL via the “CREATE ROLE” statement.

Conclusion

In PostgreSQL, the CREATE ROLE statement is used with different role attributes to create a role with specific privileges such as SUPERUSER, LOGIN, CONNECTION LIMIT, etc. For instance, the SUPERUSER attribute grants all the privileges to a user, the CREATEDB attribute grants the database creation privileges to the user, etc. This write-up explained various use cases of the CREATE ROLE statements via practical examples.