LEARN. PRACTICE. GROW.Bind defaults to creator and time
ALTER DEFAULT PRIVILEGES prepares permissions for future objects. In the experiment, configuring SELECT for app_b on objects created by deploy_owner allows querying future_data, while old_data remains inaccessible. Another table created by other_owner does not receive that grant either. A pipeline that changes identity between migrations can therefore produce a schema with inconsistent access. Record current_user when creating an object and check the resulting owner. Correction may require grants on existing objects and defaults for future creations, each explicitly scoped. Do not assume that one change retroactively repairs the complete migration history.
Separate grants from row policies
Account app_a receives SELECT, INSERT and UPDATE on positions. When RLS is enabled without policies, reading returns zero rows despite the grant. After creating tenant_scope with tenant=current_user, app_a sees id 1 and app_b sees id 2. The policy restricts the row set within existing SQL authorization; it does not replace grants required by the operation. When investigating zero results, first confirm identity and policy instead of concluding that data was deleted. In the experiment, the administrator confirms that both rows remain present. That privileged observation is diagnostic evidence, not proof of permitted client access.
Distinguish invisible rows from forbidden new rows
The write test uses three operations. Inserting an app_b row through app_a fails with 42501. Changing the tenant of its own row to app_b also fails. Updating id 2, which app_a cannot see, returns zero rows through RETURNING and leaves units unchanged. These outcomes distinguish filtering an existing row from WITH CHECK on the proposed row. Do not interpret zero updates as functional success when the request required changing exactly one position. Define how the application checks cardinality, communicates unavailable authorization or object existence, and avoids disclosing information about other clients.
Choose the correct test identity
The owner initially reads both rows even with RLS enabled. After FORCE ROW LEVEL SECURITY, that owner sees no rows because none has its name as tenant. Returning to the superuser makes both rows visible. This contrast demonstrates that FORCE does not turn a superuser into a runtime account subject to the policy. For release acceptance, use identities without ownership or BYPASSRLS and check whether they can switch to privileged roles. The experiment includes distinct LOGIN connections for app_a and app_b but uses local trust authentication; it does not validate whether a password, certificate or identity service authorizes the correct client.
Review policy composition
A review adds broad_read, a permissive SELECT policy for app_a with USING(true). The tenant_scope policy remains present, but app_a can now see both clients. Applicable permissive policies combine through OR. Adding tenant_guard as restrictive limits reading to app_a again, even while broad_read remains. Review must consider the command, roles and all applicable policies, not just the new policy text. A SELECT restriction does not demonstrate INSERT or UPDATE behavior. Repeat the matrix per operation and verify the final combination, including changes that broaden access without removing the previous policy.
Recognize operations outside the filter
In the final contrast, app_a receives TRUNCATE and can empty the entire table despite RLS. The experiment wraps the operation in a transaction and performs ROLLBACK; the administrator confirms that both rows are restored. Row protection does not restrict TRUNCATE to one tenant. This permission requires a separate decision and normally does not belong to runtime in this example. The observed reversal is transactional and controlled, without crash or backup recovery. During acceptance, connect each requirement to concrete evidence: cross-client reading, forbidden writing, effective identity, future creation and global operations. Do not use this lab as general security certification for the service.
-- Only in the disposable lab; owner prepares policy and grants separately.
ALTER TABLE service.positions ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_scope ON service.positions
USING (tenant = current_user) WITH CHECK (tenant = current_user);
-- Connect as app_a, then compare allowed and forbidden operations.
SELECT id FROM service.positions;
UPDATE service.positions SET units=99 WHERE id=2 RETURNING idA tenant=current_user policy shows one row per account; a second permissive USING(true) policy broadens reading.
Common pitfalls
Assuming retroactive defaults; using the owner as isolation evidence; combining permissive policies as if AND; granting TRUNCATE.
Related topics: Effective identity and least privilege · Change acceptance and recovery
Test the policy with a representative identity, the exact statement and negative operations, while retaining experiment boundaries.
Reference: Row security and bypass boundaries · PostgreSQL 18 reference semantics;18.6 current stable at review