Logo ← PostgreSQL Blog

PostgreSQL Roles

If you are transitioning to PostgreSQL from another database system, you will eventually hit a wall when trying to figure out the…

PostgreSQL Roles

If you are transitioning to PostgreSQL from another database system, you will eventually hit a wall when trying to figure out the difference between a USER and a ROLE.

Here is the spoiler: In modern PostgreSQL, there is no difference. Before version 8.1, PostgreSQL had separate concepts for users and groups. Today, everything is unified under a single, elegant concept: the ROLE. A role can act as a user (if it can log in), a group (if it holds permissions for others), or both.

In this deep dive, we will unpack the CREATE ROLE command, exploring every single parameter so you can architect a bulletproof Role-Based Access Control (RBAC) system.

The Syntax: More Than Meets the Eye

The base command looks simple:

CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | SYSID uid

But the option list is where the magic happens. Let’s break down every single flag you can use.

1. The Gateway: Authentication & Connection

These options dictate whether and how a role can connect to your database.

  • LOGIN | NOLOGIN
  • This is the core distinction. If you use LOGIN, the role can open a database session (it acts like a User). If you use NOLOGIN (which is the default for CREATE ROLE), the role cannot connect directly; it acts purely as a container for permissions (a Group).
  • Pro Tip: CREATE USER name; is literally just an alias under the hood for CREATE ROLE name WITH LOGIN;.
  • PASSWORD 'password' | PASSWORD NULL
  • Sets the password for the role. By default, passwords are encrypted (using SCRAM-SHA-256 in modern Postgres). If you set PASSWORD NULL, password authentication will fail for this role (useful if you rely strictly on client certificates or peer authentication).
  • VALID UNTIL 'timestamp'
  • Sets an expiration date for the password. Once the timestamp passes, the login will fail. This is an excellent tool for temporary contractors or enforcing strict corporate password rotation policies. (Example: VALID UNTIL '2026-12-31 23:59:59')
  • CONNECTION LIMIT connlimit
  • Restricts how many concurrent connections this specific role can open. If you have a web application role and want to prevent it from exhausting all your database connection pools during a traffic spike, set this to a hard number (e.g., CONNECTION LIMIT 50).

2. The Superpowers: Database Privileges

These flags grant overarching administrative capabilities that bypass standard table-level GRANT statements.

  • SUPERUSER | NOSUPERUSER
  • The God mode. A superuser bypasses all permission checks in the database (except for the right to log in). Never use this for application users. Use it only for top-level database administrators.
  • CREATEDB | NOCREATEDB
  • Allows the role to create new databases. Useful for developer environments where developers need to spin up test databases on the fly without needing full superuser rights.
  • CREATEROLE | NOCREATEROLE
  • Allows the role to create, alter, and drop other roles, as well as grant or revoke membership in them. A role with this privilege can essentially manage the database’s user infrastructure.

3. The Specialists: Advanced Operations

These options are reserved for very specific, advanced use cases.

  • REPLICATION | NOREPLICATION
  • Required if the role is going to be used to initiate streaming replication or put the system in backup mode. It is a highly privileged operation, but much safer than granting full SUPERUSER to your replication tools.
  • BYPASSRLS | NOBYPASSRLS
  • Row-Level Security (RLS) allows you to restrict which rows a user can see (e.g., a tenant can only see their own data). BYPASSRLS allows a role to ignore these policies entirely. This is crucial for backup utilities like pg_dump; they need to read all data to back it up, but shouldn't be superusers.

4. The Hierarchy: Membership & Inheritance

PostgreSQL allows you to define role memberships right at the moment of creation, saving you from writing separate GRANT statements.

  • INHERIT | NOINHERIT
  • If a role is granted membership in a group role, INHERIT dictates whether it automatically gets that group's privileges.
  • If INHERIT is on (the default): The user instantly possesses the group's powers.
  • If NOINHERIT is on: The user must explicitly type SET ROLE group_name; in their SQL session to activate those powers.
  • IN ROLE role_name [, ...]
  • Automatically adds your new role as a member of the existing roles listed here. (e.g., Make the new user an immediate member of the read_only group).
  • ROLE role_name [, ...]
  • The reverse of IN ROLE. It automatically adds the listed existing roles as members of your new role.
  • ADMIN role_name [, ...]
  • Like ROLE, but it adds the existing roles with the WITH ADMIN OPTION. This means those roles can not only use the new role's privileges, but they can also grant membership in this new role to other people.

5. The Dinosaur: Legacy Support

  • SYSID uid
  • You will see this in the documentation, but you can safely ignore it. Prior to PostgreSQL 8.1, users had specific numeric IDs. This parameter is kept purely for backward compatibility with ancient scripts and does absolutely nothing in modern Postgres.

Bringing It All Together: Best Practices

To build a secure and scalable architecture, you should separate humans/applications from permissions.

CommandActs AsTypical Use CaseCREATE ROLE ... NOLOGINGroupDefining a set of privileges (e.g., web_app_read_write).CREATE ROLE ... LOGINUserActual humans or apps connecting to the DB.

The Golden Workflow:

Create the Group: Set up a role without login capabilities to act as a container.

CREATE ROLE data_analyst NOLOGIN;

GRANT SELECT
        ON ALL TABLES IN SCHEMA public TO data_analyst;

Create the User: Set up the actual person with an expiring password and connection limits.

CREATE ROLE oz WITH LOGIN
PASSWORD 'SecurePass!' VALID UNTIL '2026-12-31' CONNECTION LIMIT 10;

Link Them: Grant the group to the user.

GRANT data_analyst TO oz;

Because oz has INHERIT by default, he can instantly SELECT from the tables, but he cannot mess with the schema.

Conclusion

Understanding PostgreSQL roles is the foundation of database security. By moving away from assigning permissions to individual users and instead adopting a NOLOGIN group-based architecture, you ensure your database remains secure, manageable, and highly organized as your team scales.

Stop treating roles just as users and start utilizing them as the versatile security framework they were designed to be!