CREATE ROLE in PostgreSQL: Users, Group Roles and Inheritance

PostgreSQL has no separate "user" and "group" entities: there is one universal role. The CREATE ROLE statement creates both the person who logs in and the group that holds privileges for others. The only difference is which attributes you attach.

A login role and a group role

A role with the LOGIN attribute can connect to the server, which is what we usually call a "user". A role without LOGIN cannot connect and typically serves as a container of privileges, that is, a "group".

-- a login role: an actual person or service
CREATE ROLE analyst LOGIN PASSWORD 'secret';

-- a group role: no login, just holds privileges
CREATE ROLE app_readonly NOLOGIN;

CREATE USER is merely a synonym for CREATE ROLE ... LOGIN. So CREATE USER analyst and CREATE ROLE analyst LOGIN do exactly the same thing; there is no separate "user type" under the hood.

Let us give the group read access to our schema:

GRANT SELECT ON users, orders, employees TO app_readonly;

Role membership and inheritance

A role becomes a member of a group with GRANT role TO member. From there the INHERIT attribute decides everything: if it is set (the default), the member automatically uses the group's privileges.

-- analyst now inherits everything app_readonly can do
GRANT app_readonly TO analyst;

-- as analyst, this just works thanks to inheritance:
SELECT country, count(*) FROM users GROUP BY country;

If the role is created with NOINHERIT, the group's privileges do not apply automatically; you have to "put them on" explicitly with SET ROLE:

CREATE ROLE deploy_bot LOGIN NOINHERIT PASSWORD 'secret';
GRANT app_readonly TO deploy_bot;

-- without SET ROLE the SELECT below is denied
SET ROLE app_readonly;
SELECT amount FROM orders WHERE status = 'paid';
RESET ROLE;

Gotcha: attributes such as SUPERUSER, CREATEDB and LOGIN are NEVER inherited through membership. A member of a superuser group does not become a superuser. Only ordinary privileges (SELECT, INSERT and so on) are inherited, never role attributes.

Role attributes

Attributes define what a role may do at the cluster level. The most common ones:

• LOGIN / NOLOGIN controls whether the role can connect to the server.
• SUPERUSER / NOSUPERUSER decides whether the role bypasses all permission checks. Hand it out very sparingly.
• CREATEDB lets the role create databases.
• CREATEROLE lets the role create and alter other roles.
• PASSWORD and VALID UNTIL set the password and its expiry date.

CREATE ROLE etl_owner LOGIN
    CREATEDB
    PASSWORD 'secret'
    VALID UNTIL '2026-12-31';

You can change attributes later with ALTER ROLE, which is also the handy way to revoke dangerous powers:

ALTER ROLE analyst CONNECTION LIMIT 5;
ALTER ROLE etl_owner NOCREATEDB;

The "users and groups" model

A practical pattern: create a few group roles for sets of privileges, create people as login roles, and connect them through membership. Changing access later is one GRANT / REVOKE, not editing per-table permissions for every individual.

-- groups by responsibility
CREATE ROLE readers NOLOGIN;
CREATE ROLE writers NOLOGIN;

GRANT SELECT ON users, orders TO readers;
GRANT SELECT, INSERT, UPDATE ON orders TO writers;

-- writers should also be able to read
GRANT readers TO writers;

-- people
CREATE ROLE maria LOGIN PASSWORD 'secret';
CREATE ROLE pavel LOGIN PASSWORD 'secret';

GRANT readers TO maria;
GRANT writers TO pavel;

When Pavel leaves the write team, you run REVOKE writers FROM pavel and access drops across every table at once.

Engine differences:

• MySQL had no roles at all before 8.0; it now has CREATE ROLE and CREATE USER, but they are distinct commands, and a role must still be activated with SET ROLE or SET DEFAULT ROLE.
• ClickHouse supports CREATE ROLE, CREATE USER and GRANT, but the privilege model is flatter, without deep inheritance through chains of roles.

The baseline rule: attach privileges to groups, make people members of groups, and keep SUPERUSER only on platform administrators.