PostgreSQL / Intermediate: concurrency and maintainable SQL / Lesson 10

Roles, authentication and schema privileges

Separate login identity, database access and object privileges. Design a constrained report role without weakening PostgreSQL authentication or trusting writable schemas.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Explain PostgreSQL roles and first-match HBA rules.
Distinguish CONNECT, USAGE and table grants.

Roles unify users and groups

PostgreSQL roles can own objects, receive privileges and belong to other roles. LOGIN distinguishes a role that can initiate a session; there is no MySQL-style user@host account identity. Network admission and authentication are instead governed by connection settings and pg_hba.conf rules, while object authorization is checked afterward. A successful login does not grant access to every schema or table. Keep service, migration and monitoring responsibilities separate rather than using a superuser for ordinary application requests.

Inspect effective identity and capabilities

These read-only functions check the current identity and selected privileges in an authorized training session. Schema USAGE permits name resolution within a schema; it does not grant SELECT on its tables. CONNECT permits database connection subject to other admission checks. The table privilege function requires the named fixture object to exist, so a missing-object error is not a negative privilege answer. Actual booleans depend on grants, ownership and role membership; no observed values are fabricated here.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT session_user, current_user;
SELECT has_database_privilege(current_user, current_database(), 'CONNECT');
SELECT has_schema_privilege(current_user, 'quay_lab', 'USAGE');
SELECT has_table_privilege(current_user, 'quay_lab.bookings', 'SELECT');

Inspect each layer independently. These functions do not grant anything.

The first matching HBA rule decides authentication

pg_hba.conf distinguishes local Unix-socket connections from host TCP connections and matches database, role and client address. The first matching rule is used; failed authentication does not fall through to later matching rules. peer can authenticate local OS identity, while scram-sha-256 verifies an appropriate password exchange. TLS policy and client support also matter. Do not fix a rejected service login by adding broad trust rules. PostgreSQL 18 deprecates MD5 password support; choose supported SCRAM-capable clients and follow the exact release guidance.

Give a report role only its intended scope

The administrative illustration assumes the role quay_reporter and training schema already exist and that an authorized owner applies the change only in a disposable lab. Granting schema USAGE and SELECT on one table is narrower than SELECT on every present and future table. Membership and inheritance policies determine how a service obtains that capability. Object ownership is another powerful source of control, so runtime roles should not automatically own all application tables. The course does not create real accounts or embed passwords.

sql Illustrative SQL; expected results are reasoned, not measured.

GRANT USAGE ON SCHEMA quay_lab TO quay_reporter;
GRANT SELECT ON TABLE quay_lab.bookings TO quay_reporter;
SELECT has_table_privilege('quay_reporter', 'quay_lab.bookings', 'SELECT');

Expected privilege after successful grants: SELECT is true for the named table. Other capabilities are not implied.

Future objects and search paths need policy

ALTER DEFAULT PRIVILEGES affects future objects created by the relevant creating role, not existing objects or every creator automatically. Review that ownership context before expecting a migration-created table to be readable. Schema defaults also differ by PostgreSQL version and upgrade history; do not assume the public schema is equally writable on every installation. SECURITY DEFINER functions need carefully controlled search_path and grants because callers should not redirect privileged name resolution. Verify denied operations as well as permitted reads using the intended service identity.

Common errors and fixes

permission denied for schema (42501)

Why it happens: The role lacks the schema-level capability needed to resolve objects.
How to fix it: Review and grant narrowly scoped USAGE through authorized provisioning.
Prevent it next time: Test schema and table privileges separately.

password authentication failed for user (28P01)

Why it happens: The selected HBA method, credential or client capability does not match.
How to fix it: Inspect the intended rule and credentials without broadening access.
Prevent it next time: Document socket/TCP, role, database, TLS and SCRAM requirements.

A new migration table is unreadable

Why it happens: Default privileges were configured for a different creating role or only existing tables were granted.
How to fix it: Inspect the actual owner and apply the reviewed existing/future-object policy.
Prevent it next time: Include the migration owner in privilege acceptance checks.

Check your understanding

1. A role has SELECT on quay_lab.bookings but cannot resolve the table. Which additional scope should be inspected?

Show worked solution

Inspect USAGE on quay_lab and confirm the connection targets the intended database. Table access and schema name lookup are separate checks. Grant only the required schema capability through the normal owner process, then verify the role can read the specific table while unrelated tables and writes remain denied. Do not use superuser as a diagnostic workaround.

2. Why can app_owner default privileges fail to cover a table created by deploy_owner?