8. The security review - pgroles docs

An auditor asks a harder question than “what grants are in our YAML?”: can this role actually perform the operation? Acme’s role graph is tidy, but PostgreSQL has access paths outside ordinary named ACLs.

Chapter 8 · Security review

PostgreSQL 18.3 · PGlite ↗

The auditor asks: “Who can really do this?”

Effective access hides outside ordinary direct ACLs. Investigate three surprises: PUBLIC, SECURITY DEFINER, and delegated grant options.

Chapter 8 · Security review

PG 18.3

Step 1 of 4

Every step seeds its own database — jump anywhere

Step 1

Surprise 1: PUBLIC can execute

PostgreSQL grants EXECUTE on new functions to PUBLIC by default. Every role is part of PUBLIC, even when no named function grant exists.

Call the function as contractor, who has no declared access.

Run as

SELECT billing_api.order_total() AS total_cents;

Editable SQL · disposable browser database · changes are discarded after the run

PostgreSQL output

PostgreSQL’s rows, command tags, or exact error will appear here.

How the browser lab models roles

The selector sets session authorization inside an isolated PGlite database. Statements run one at a time and autocommit, like psql: execution stops at the first error, and earlier statements keep their effect. PostgreSQL performs ordinary role, ownership, schema, and object checks; the diagram comes from catalog privilege queries after your SQL. This is not a password, CONNECT, pg_hba.conf, or concurrent-session test.

Close the PUBLIC path explicitly

PostgreSQL gives PUBLIC—every role—EXECUTE on new functions by default. State both desired absences so existing and future functions converge:

pgroles.yamlpolicy schema

SectionFieldRole / profile / settingObjectPrivilege / typeUnknown

grants:
  - role: PUBLIC
    ensure: absent
    privileges: [EXECUTE]
    object: { type: function, schema: billing_api, name: "*" }

default_privileges:
  - owner: app_owner
    scope: { type: global }
    grant:
      - role: PUBLIC
        ensure: absent
        privileges: [EXECUTE]
        on_type: function

The global default matters because PostgreSQL’s built-in function default is global. A schema-scoped revoke cannot subtract a global grant.

Keep the other two boundaries visible

A SECURITY DEFINER function is an intentional privilege boundary. Review its owner, body, fixed search_path, callable surface, and PUBLIC exposure. pgroles manages who may execute the function; it does not prove the function body is safe.

WITH GRANT OPTION lets an application grantee delegate an object privilege. pgroles checks whether its executor can grant wildcard privileges safely, but it does not model or converge grant options held by application roles. Audit and manage that boundary separately.

One more finding costs nothing to write down: Acme’s application still connects with the founder-era admin credentials, and a superuser bypasses every check in this chapter. No grant, policy, or RLS rule constrains that connection—pgroles cannot manage it away. Moving the application onto a scoped login, the way reporting_app was built in chapter 2, is the remediation an auditor will ask for first.

Desired ACLs are necessary; effective-access tests tell you whether every other path agrees with them.

Guide

Open the Acme playground

Investigate the finished database with any role and any SQL.

Open guide

Guide

Limits and boundaries

Review unmanaged column grants, grant options, and effective access.

Open guide