Row-level security

How it works

Privileges decide which tables a role can touch. Row-level security decides which rows. You enable it per table with ALTER TABLE … ENABLE ROW LEVEL SECURITY, then add policies. Each policy has an expression that must be true for a row: USING decides which existing rows a command can see, update or delete, and WITH CHECK decides which new or changed rows it may write.¹

Two defaults matter:

• No policy means no rows. Once row security is enabled on a table, access with no matching policy is denied.¹
• Policies add up with OR. Several permissive policies, which is the default kind, are combined with OR; restrictive policies are combined with AND.¹ One broad permissive policy can undo a careful narrow one.

Who skips it

• Superusers and roles with BYPASSRLS always bypass row security.¹
• The table’s owner, normally. Owners bypass row security unless the table has FORCE ROW LEVEL SECURITY set.¹ An agent that connects as the role that owns the tables, as many setups do, isn’t filtered at all.
• Whole-table operations. TRUNCATE and REFERENCES aren’t subject to row security,¹ so a role allowed to truncate a table empties all of it.
• A disabled table. Policies can exist while row security is disabled on the table, and then they’re ignored.²

The catch with agents: who the session says it is

Most multi-tenant policies compare a row with a session setting, such as current_setting('app.customer_id', true). The true makes it return NULL instead of an error when the setting doesn’t exist,³ so a session that hasn’t said who it’s working for sees nothing, as in the example’s first query.

The setting belongs to the session, though. A setting attached to a role with ALTER ROLE … SET is only that role’s default at login: in our test, a session of that role set its own app.customer_id, and the role’s row-level security policy then trusted it.⁴ The example’s second query is the problem: the session chose customer 7 itself. An agent that can send raw SQL can send SET app.customer_id = '8' just as easily and read another customer’s tickets. Nothing fails; the policy does what it says.

Three ways to close that:

• Don’t give the agent raw SQL. Give it tools with fixed queries, and have the tool server set the customer from the conversation it serves, not from the model.
• Key policies to something the session can’t change. SET ROLE changes current_user but leaves session_user alone,⁵ so a login role per tenant with a policy on session_user holds against raw SQL.
• Put a rule in front of the SQL that refuses SET, set_config and multi-statement calls before they reach the database.

The safe Postgres access post runs each of these on PostgreSQL 18.6 with the output.