In PostgreSQL, every account that can connect to the server is represented by a role. A role can act as a single user, a collection of users, or even a template for other roles. Understanding how to create, configure, and secure roles is essential for maintaining a safe and well-organized database environment.

Role Attributes

When is a concise list of the most common attributes you can assign when you define a role:

• LOGIN – permits the role to establish a client connection.
• SUPERUSER – bypasses all permission checks (use sparingly).
• CREATEDB – authorizes the role to issue CREATE DATABASE.
• CREATEROLE – allows the role to create or drop other roles.
• INHERIT – automaticlaly grants the privileges of any role this role belongs to.
• REPLICATION – enables the role to initiate streaming replication.
• PASSWORD – stores an encrypted password for authentication.

Creating a Role

The simplest form creates a role without login rights:

CREATE ROLE app_reader;

To create a role that can log in and has a pasword, add the relevant options:

CREATE ROLE app_writer WITH LOGIN PASSWORD 'Str0ngP@ss!';

Role names follow standard SQL identifier rules: unquoted identifiers must start with a letter and contain only letters, digits, and underscores; otherwise, wrap the name in double quotes.

Group Roles and Membership

PostgreSQL does not distinguish between "users" and "groups"; any role can act as either. To model a group, create a role without LOGIN and then add members to it.

-- create a group role
CREATE ROLE sales_team;

-- add existing roles to the group
GRANT sales_team TO app_writer;
GRANT sales_team TO app_reader;

Because INHERIT is enabled by default, app_writer and app_reader automatically receive any privileges granted to sales_team.

Managing Privileges

Grant or revoke object-level privileges with GRANT and REVOKE.

-- allow read and insert on a table
GRANT SELECT, INSERT ON orders TO sales_team;

-- remove update rights from a single user
REVOKE UPDATE ON orders FROM app_writer;

You can also grant membership in a role:

GRANT sales_team TO new_intern;

Dropping Roles

Remove a role with DROP ROLE. The command fails if the role owns any objects or has active connections.

DROP ROLE old_service;

Inspecting Existing Roles

Query the catalog view pg_roles to list every role:

SELECT rolname
FROM pg_roles
ORDER BY rolname;

Inside psql, the shortcut \du produces a formatted report:

\du

Every fresh PostgreSQL cluster contains one predefined superuser—usually named postgres. Connect as this initial role when ever you need to create additional roles or databases.