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:
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:
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:
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:
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:
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:
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.
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.