User Management, Roles, and Object Privileges

Why Access Control Matters

A database that stores sensitive data — customer records, financial transactions, personnel files — must restrict who can read, change, or delete that data. PostgreSQL enforces access control through roles and privileges. Getting this right is not optional: unauthorized access or accidental data modification are among the leading causes of data breaches and compliance failures. The principle of least privilege requires that every user or process receives only the permissions needed to do its job and nothing more.

Roles vs. Users in PostgreSQL

In PostgreSQL, roles are the single unified concept for both users and groups. A role that has the LOGIN attribute is a database user. A role without LOGIN is a group role used to bundle privileges. This unified model means you assign permissions to a role once, then grant that role to any number of users — a far cleaner approach than setting permissions individually.

Common attributes you control when creating a role:

• LOGIN / NOLOGIN — whether the role can open a connection
• SUPERUSER — bypasses all permission checks; reserve this strictly for DBAs who need it
• CREATEDB — allows the role to create new databases
• CREATEROLE — allows the role to create other roles
• PASSWORD — sets the login credential

Creating Roles and Users

Create a group role (no login):

```sql

CREATE ROLE readonly_role;

CREATE ROLE data_entry_role;

CREATE ROLE dba_role;

```

Create users (roles with LOGIN):

```sql

CREATE ROLE alice WITH LOGIN PASSWORD 'secure_pass1';

CREATE ROLE bob WITH LOGIN PASSWORD 'secure_pass2';

CREATE ROLE carol WITH LOGIN PASSWORD 'secure_pass3';

CREATE ROLE dan WITH LOGIN PASSWORD 'secure_pass4';

```

Assign users to group roles:

```sql

GRANT readonly_role TO alice;

GRANT data_entry_role TO bob;

GRANT data_entry_role TO carol;

GRANT dba_role TO dan;

```

Granting Object Privileges

Privileges are granted on specific database objects — tables, views, sequences, functions. The core table privileges are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and TRIGGER.

```sql

-- readonly_role may only query

GRANT SELECT ON TABLE orders TO readonly_role;

-- data_entry_role may query and add/edit rows

GRANT SELECT, INSERT, UPDATE ON TABLE orders TO data_entry_role;

-- dba_role gets full access including delete

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders TO dba_role;

```

Because Alice is a member of readonly_role, she inherits SELECT on orders automatically — she does not need a direct grant.

Testing Privileges with SET ROLE

You can impersonate a role without logging out by using `SET ROLE`. This is the standard technique for verifying that a role has exactly the access it should:

```sql

SET ROLE alice;

SELECT * FROM orders; -- should succeed

INSERT INTO orders ...; -- should fail

RESET ROLE; -- return to original role

```

Always `RESET ROLE` after testing so you do not accidentally remain in an impersonated context.

Revoking Privileges

Use REVOKE to remove permissions:

```sql

REVOKE DELETE ON TABLE orders FROM dba_role;

REVOKE data_entry_role FROM carol;

```

Revoking a role membership removes all privileges that role carried for that user.

Auditing Privileges

PostgreSQL provides catalog views and functions to inspect the current state of permissions:

• pg_roles — lists all roles and their attributes (superuser, login, etc.)
• pg_user — a convenient view showing only login-capable roles
• information_schema.role_table_grants — shows which roles hold which privileges on which tables
• has_table_privilege(user, table, privilege) — returns TRUE/FALSE; excellent for scripted audits

Example audit query:

```sql

SELECT grantee, table_name, privilege_type

FROM information_schema.role_table_grants

WHERE table_schema = 'public'

ORDER BY grantee, table_name;

```

Document results in a privilege matrix: rows are users, columns are tables, cells list allowed operations.

Principle of Least Privilege and the Superuser Hazard

Superuser status in PostgreSQL is equivalent to root on a Linux system — it bypasses every privilege check. If a data_entry user is accidentally granted SUPERUSER, they can drop tables, read any schema, and alter roles. The fix:

```sql

ALTER ROLE bob NOSUPERUSER;

```

Verify immediately with:

```sql

SELECT rolname, rolsuper FROM pg_roles WHERE rolname = 'bob';

```

Document the before state (rolsuper = true) and the after state (rolsuper = false) so the incident is traceable. This audit trail practice aligns with DAMA DMBOK2 guidance on data security governance.

Safety and Professional Practice

Database administration does not involve physical hazards like electrical work, but it carries its own serious risks. Always work in a non-production environment when learning or testing privilege changes. Never share superuser credentials. Use strong, unique passwords and store them in a secrets manager rather than in scripts. Before revoking any privilege in a live system, confirm with a supervisor that downstream applications will not break. Maintain a change log of every GRANT and REVOKE for compliance and forensic purposes.