How to create a New Role in PostgreSQL?

Create a New Role

Write a PostgreSQL query to create a new role named data_analyst with login privileges.

Solution:

-- Create a new role with login privileges
CREATE ROLE data_analyst WITH LOGIN;

Explanation:

• Purpose of the Query:
• This query creates a new role (data_analyst) that can log in to the database.
• PostgreSQL roles can act as both users and groups, depending on their assigned privileges.
• Key Components:
• CREATE ROLE data_analyst: Defines a new role.
• WITH LOGIN: Allows the role to log in as a user.
• Real-World Application:
• This is useful when setting up different user accounts with controlled access to a database.

Notes:

• A role without LOGIN cannot authenticate as a user.
• Use CREATE USER as an alternative (internally, it's the same as CREATE ROLE WITH LOGIN).

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a new role named guest_user without login privileges.
• Write a PostgreSQL query to create a role named readonly_user with the ability to log in but without the ability to modify data.
• Write a PostgreSQL query to create a role named temp_admin that has superuser privileges.
• Write a PostgreSQL query to create a role named limited_role that can only connect to the database between 9 AM and 6 PM.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Creating and managing roles and users Home.