How to Create Role in PostgreSQL: Syntax and Examples

To create a role in PostgreSQL, use the CREATE ROLE command followed by the role name and optional attributes like login permission. For example, CREATE ROLE myuser WITH LOGIN PASSWORD 'mypassword'; creates a role that can log in with a password.

Syntax

The basic syntax to create a role in PostgreSQL is:

• CREATE ROLE role_name; creates a role without login permission.
• LOGIN allows the role to log in like a user.
• PASSWORD 'password' sets a password for login roles.
• SUPERUSER, CREATEDB, CREATEROLE are optional attributes to grant special privileges.

sql

CREATE ROLE role_name [WITH option ...];

-- Options include:
-- LOGIN
-- PASSWORD 'password'
-- SUPERUSER
-- CREATEDB
-- CREATEROLE
-- NOINHERIT
-- NOCREATEROLE
-- NOSUPERUSER
-- NOLOGIN

Example

This example creates a role named report_user that can log in with a password and can create databases.

sql

CREATE ROLE report_user WITH LOGIN PASSWORD 'securepass123' CREATEDB;

Output

CREATE ROLE

Common Pitfalls

Common mistakes when creating roles include:

• Forgetting the LOGIN attribute if you want the role to connect to the database.
• Not setting a password for login roles, which can cause authentication failures.
• Using uppercase letters in role names without quoting, which converts them to lowercase.

sql

/* Wrong: role cannot login without LOGIN */
CREATE ROLE user1 PASSWORD 'pass';

/* Right: add LOGIN to allow login */
CREATE ROLE user1 WITH LOGIN PASSWORD 'pass';

Output

ERROR:  role "user1" cannot login
CREATE ROLE

Quick Reference

Option	Description
LOGIN	Allows the role to log in to the database
PASSWORD 'password'	Sets the password for the role
SUPERUSER	Grants all privileges
CREATEDB	Allows creating new databases
CREATEROLE	Allows creating and managing roles
NOINHERIT	Prevents role from inheriting privileges
NOLOGIN	Prevents role from logging in

Key Takeaways

Use CREATE ROLE with LOGIN to create a user that can connect to the database.

Always set a password for roles that need to log in.

Role names are case-insensitive unless quoted.

Use role attributes like SUPERUSER and CREATEDB to grant specific privileges.

Check for missing LOGIN attribute if authentication fails.