fix: use the sequence ACL default for sequences in the fingerprint
WHY THIS MATTERS
The schema-drift fingerprint explodes each relation's ACL into one line
per (object, grantee, privilege) so CI can diff a fresh install against
an upgraded deployment. When a relation has never had an explicit GRANT
or REVOKE, its pg_class.relacl is NULL - PostgreSQL stores nothing and
applies built-in defaults - so the script synthesizes the effective ACL
with acldefault(). The relation query admits sequences (relkind 'S'),
but it called acldefault('r', ...) - the TABLE default - for every row.
A sequence with a NULL relacl would therefore be fingerprinted as
holding INSERT, DELETE, TRUNCATE, REFERENCES and TRIGGER: privileges a
sequence cannot hold at all. Today no sequence in the schema has a NULL
relacl, so the check passes either way - but the first time one does,
the failure diff would show phantom table privileges on a sequence,
sending whoever debugs the drift down a false trail.
WHAT IS acldefault()
acldefault(objtype, ownerid) answers "what ACL does PostgreSQL treat a
NULL acl column as meaning for this kind of object?". The first argument
is a one-character object-type code, and each code maps to a different
default privilege set:
acldefault('r', owner) -- 'r'elation: SELECT, INSERT, UPDATE,
-- DELETE, TRUNCATE, REFERENCES, TRIGGER
acldefault('s', owner) -- 's'equence: USAGE, SELECT, UPDATE
acldefault('f', owner) -- 'f'unction: EXECUTE (incl. PUBLIC)
Note the code is about the object KIND, not pg_class.relkind: relkind
'S' (a sequence row in pg_class) corresponds to acldefault kind 's'.
Passing 'r' for a sequence does not error - it just fabricates a
table-shaped ACL the sequence could never actually have.
HOW THE FIX WORKS
The lateral aclexplode now picks the acldefault kind per row:
aclexplode(coalesce(c.relacl,
acldefault((case when c.relkind = 'S' then 's' else 'r' end)::"char",
c.relowner)))
Sequences get the sequence default (USAGE, SELECT, UPDATE); tables,
partitioned tables, views and materialized views - everything else the
WHERE clause admits - keep the relation default. The explicit ::"char"
cast matches acldefault's parameter type (the internal one-byte "char",
not char(1)). Verified against a live Postgres 15: a fresh sequence with
NULL relacl now fingerprints as exactly SELECT|UPDATE|USAGE, and the
full drift check (baseline schema + migrations vs. current schema.sql)
still reports identical fingerprints.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
| Repository | open-legal-products/mike |
|---|---|
| Author | Amal <mamalanand3@gmail.com> |
| Authored | |
| Committed | |
| Parents | 317c5559 |
| Stats | 1 file changed , +9 , -1 |
| Part of | Add schema drift checks for database upgrades |
Capture this commit into my fork
Download a Markdown prompt that tells Claude how to port this
exact commit into your working tree. Run it via
claude -p < capture-commit-66877127.md
from inside the repo you want the change in.