Access control

Why it matters

Most data breaches do not need a clever exploit: they use credentials that had far more access than they needed. A reporting tool that can DELETE, an application account that owns the schema, a former contractor who still has a login. Least privilege means every account gets exactly the rights its job requires and nothing more, so that a bug, a leaked password or a compromised server can do limited damage. Access control is also what auditors check first.

The building blocks (PostgreSQL)

• Roles are users (with LOGIN) and groups (without). Grant privileges to group roles, then grant group membership to people and services. When someone leaves, you drop one membership, not fifty grants.
• Privileges are granted on objects: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER on tables; USAGE and CREATE on schemas; CONNECT on databases; EXECUTE on functions.
• Owners can do anything to their objects, including dropping them. Applications should not own the tables they use.
• PUBLIC is a pseudo-role meaning "everyone". Check what it has; for example, CONNECT on a database is granted to PUBLIC by default.

-- Group roles
CREATE ROLE app_rw NOLOGIN;
CREATE ROLE reporting_ro NOLOGIN;

GRANT CONNECT ON DATABASE shop TO app_rw, reporting_ro;
GRANT USAGE ON SCHEMA app TO app_rw, reporting_ro;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA app TO app_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO reporting_ro;

-- Tables created later get the same rights automatically
ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO reporting_ro;

-- Members
GRANT app_rw TO shop_app;
GRANT reporting_ro TO analyst_ana;

-- Take a right away
REVOKE DELETE ON app.payments FROM app_rw;
REVOKE CONNECT ON DATABASE shop FROM PUBLIC;

GRANT ... ON ALL TABLES only affects tables that exist now. ALTER DEFAULT PRIVILEGES covers future ones, which is the step people most often forget. Column-level grants (GRANT SELECT (id, email) ON app.users TO support) and row-level security (ALTER TABLE ... ENABLE ROW LEVEL SECURITY plus CREATE POLICY) restrict access further when needed.

MySQL and SQL Server use the same GRANT/REVOKE vocabulary with different details (MySQL grants to 'user'@'host'; SQL Server separates logins, users and schemas, and adds DENY). SQLite has no users at all: access is whoever can read the file, which is why it is not used as a shared multi-user server.

Beyond grants

• Authentication: scram-sha-256 passwords or certificates in pg_hba.conf, and limit which addresses may connect. Single sign-on through your identity provider is common for people.
• Encryption in transit: require TLS (hostssl lines, sslmode=verify-full on clients).
• Secrets: application passwords live in a secrets manager or the platform's environment settings, never in source code. Rotate them.
• Separation of duties: DBAs use personal admin accounts, not a shared postgres login, so actions are attributable. Break-glass superuser access is logged.
• Auditing: log connections and DDL (log_connections, log_statement = 'ddl'), or use the pgAudit extension for detailed logs.

Worked example: auditing grants

In PostgreSQL you can list actual table privileges from the catalog:

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'app'
ORDER BY grantee, table_name, privilege_type;

In psql, \dp app.* shows the same as access-control lists. An audit compares this list with an approved policy: anything granted but not approved is a candidate to revoke. That comparison is a plain SQL anti-join, which you can practise in any database:

SELECT g.role_name, g.table_name, g.privilege
FROM grants g
LEFT JOIN approved_policy p
  ON p.role_name = g.role_name
 AND p.table_name = g.table_name
 AND p.privilege = g.privilege
WHERE p.role_name IS NULL;

You can even generate the fix: 'REVOKE ' || privilege || ' ON ' || table_name || ' FROM ' || role_name || ';'. Review generated statements before running them, and run them in a change window.

Common mistakes

• Applications connecting as the schema owner or a superuser.
• Granting to individual users instead of group roles.
• Forgetting ALTER DEFAULT PRIVILEGES, so new tables are unreadable (or someone "fixes" it with a superuser).
• Leaving PUBLIC privileges and stale accounts in place.
• Shared admin passwords with no audit trail.

Quick recap

• Least privilege: group roles with only the needed privileges, membership for people and services.
• GRANT/REVOKE, ALTER DEFAULT PRIVILEGES, column grants and row-level security shape access.
• Authenticate strongly, encrypt connections, keep secrets out of code, and audit grants against a policy.

Try it

In the Do tab, audit a grants table against an approved policy and generate the REVOKE statements for every excess privilege.