PostgreSQL Row-Level Security for AI Agents: A Testable Setup

The short answer

PostgreSQL row-level security can keep an AI agent inside one tenant or workflow, but only when the agent connects as a non-owner role without BYPASSRLS, the table has row security enabled, and every allowed command has an explicit policy.

Treat RLS as an additional database boundary, not a replacement for ordinary privileges. Use GRANT to decide which tables and operations the agent may attempt. Use RLS to decide which rows those allowed operations may reach. Then test the setup through the same login and connection path the agent will use.

The most dangerous false-positive test is running a query as the table owner or a superuser. Table owners normally bypass RLS, roles with BYPASSRLS always bypass it, and superusers always bypass it. A policy can be correct while an owner-based integration still sees every row.

This guide builds a reproducible two-tenant example and proves both allowed and denied behavior.

What RLS does—and what it does not do

PostgreSQL applies two independent permission layers to normal table access:

1. Standard privileges such as SELECT, INSERT, UPDATE, and DELETE decide whether the role may perform an operation on the table.
2. Row security policies decide which rows that permitted operation may see or change.

An RLS policy does not grant table access by itself. A role with a matching SELECT policy still needs SELECT on the table. Conversely, a role with table-level SELECT can see every row when RLS is disabled or bypassed.

According to the PostgreSQL row security documentation, enabling RLS without any applicable policy creates a default-deny state for normal row access. That is a useful failure mode, but it is not a complete deployment plan: table ownership, role attributes, command-specific policies, and test identity still matter.

Operations that act on the whole table, including TRUNCATE and REFERENCES, are not governed by row security. Do not grant capabilities that the agent does not need.

Create a minimal multi-tenant fixture

Run the administrative statements as an authorized database administrator in a disposable environment first.

CREATE SCHEMA support;

CREATE TABLE support.tickets (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  subject text NOT NULL,
  status text NOT NULL DEFAULT 'open',
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO support.tickets (tenant_id, subject)
VALUES
  (42, 'Tenant 42: first ticket'),
  (42, 'Tenant 42: second ticket'),
  (84, 'Tenant 84: private ticket');

Create a separate owner and an agent login. The agent role must not own the database, schema, table, sequence, or policies.

CREATE ROLE support_owner
  NOLOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  NOBYPASSRLS;

ALTER TABLE support.tickets OWNER TO support_owner;
ALTER SEQUENCE support.tickets_id_seq OWNER TO support_owner;

CREATE ROLE agent_tenant_42
  LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  NOBYPASSRLS
  CONNECTION LIMIT 5;

Set the login password through your normal secret-management workflow or interactively in psql; do not put a production credential in a shared SQL file.

Grant only the operations required by this example:

GRANT CONNECT ON DATABASE app_production TO agent_tenant_42;
GRANT USAGE ON SCHEMA support TO agent_tenant_42;
GRANT SELECT, INSERT, UPDATE ON support.tickets TO agent_tenant_42;
GRANT USAGE ON SEQUENCE support.tickets_id_seq TO agent_tenant_42;

The example intentionally omits DELETE, TRUNCATE, schema CREATE, and ownership.

Enable and force row-level security

Enable RLS on the table:

ALTER TABLE support.tickets ENABLE ROW LEVEL SECURITY;

That protects ordinary non-owner roles once policies exist. It does not normally constrain the table owner. Apply FORCE ROW LEVEL SECURITY when owner sessions should also be subject to policies:

ALTER TABLE support.tickets FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITY changes owner behavior; it does not constrain superusers or roles with BYPASSRLS. PostgreSQL documents those roles as unconditional RLS bypass paths.

The clean production pattern is still to keep runtime identities separate from ownership. An owner role should administer the object. The AI-facing login should be a narrow, non-owner role.

Add command-specific policies

Create separate policies for reads, inserts, and updates. Explicit policies make reviews easier because USING and WITH CHECK answer different questions.

CREATE POLICY tickets_tenant_42_select
  ON support.tickets
  FOR SELECT
  TO agent_tenant_42
  USING (tenant_id = 42);

CREATE POLICY tickets_tenant_42_insert
  ON support.tickets
  FOR INSERT
  TO agent_tenant_42
  WITH CHECK (tenant_id = 42);

CREATE POLICY tickets_tenant_42_update
  ON support.tickets
  FOR UPDATE
  TO agent_tenant_42
  USING (tenant_id = 42)
  WITH CHECK (tenant_id = 42);

The distinction is important:

For the update policy, USING prevents the role from selecting another tenant's row as an update target. WITH CHECK prevents the role from changing an allowed row so that it escapes into another tenant.

The CREATE POLICY reference defines the command-specific behavior. If a policy that can use both expressions omits WITH CHECK, PostgreSQL can reuse its USING expression. Writing both explicitly is often clearer during a security review.

Test through the real agent role

Do not validate RLS only from an owner or superuser session. Connect directly as agent_tenant_42 using the same host, database, TLS settings, and pool mode that the AI integration will use.

Start by confirming identity and role attributes:

SELECT current_user, session_user;

SELECT
  rolname,
  rolsuper,
  rolbypassrls
FROM pg_roles
WHERE rolname = current_user;

Expected values for the agent login are rolsuper = false and rolbypassrls = false.

Allowed read

SELECT id, tenant_id, subject
FROM support.tickets
ORDER BY id;

Expected result: only the two rows where tenant_id = 42.

Invisible cross-tenant update

UPDATE support.tickets
SET status = 'closed'
WHERE tenant_id = 84;

Expected result:

UPDATE 0

The other tenant's row is not an eligible update target under the USING expression.

Rejected cross-tenant insert

INSERT INTO support.tickets (tenant_id, subject)
VALUES (84, 'Should be rejected');

Expected result: an error indicating that the new row violates the row-level security policy.

Rejected tenant reassignment

UPDATE support.tickets
SET tenant_id = 84
WHERE tenant_id = 42;

Expected result: an error. The existing rows satisfy USING, but the resulting rows fail WITH CHECK.

Rejected ungranted operation

DELETE FROM support.tickets
WHERE tenant_id = 42;

Expected result: permission denied. The role has neither table-level DELETE nor a delete policy.

Keep these checks as a repeatable regression matrix. Run them after role changes, policy changes, ownership transfers, schema migrations, pool reconfiguration, or changes to the AI execution layer.

Prove that owner bypass is not your test path

Before FORCE ROW LEVEL SECURITY, the table owner normally bypasses policies. That means an owner-based test may return all three fixture rows and incorrectly suggest that RLS is broken.

Inspect ownership and RLS flags directly:

SELECT
  n.nspname AS schema_name,
  c.relname AS table_name,
  pg_get_userbyid(c.relowner) AS table_owner,
  c.relrowsecurity AS rls_enabled,
  c.relforcerowsecurity AS rls_forced
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname = 'support'
  AND c.relname = 'tickets';

Expected state:

Even with forced RLS, use the runtime login for acceptance tests. It catches effective grants, inherited roles, connection defaults, and pool behavior that an owner simulation can miss.

Inspect every policy, not just its name

The pg_policies system view exposes the roles, command, policy type, USING expression, and WITH CHECK expression:

SELECT
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies
WHERE schemaname = 'support'
  AND tablename = 'tickets'
ORDER BY policyname;

Store this output with a deployment review or compare it in a migration test. A policy can retain a reassuring name while its expression or target role changes.

Also inspect inherited memberships. A narrow login may inherit broader table privileges or membership in a role that has another applicable policy.

\du agent_tenant_42
\dp support.tickets

RLS evaluates applicable policies using normal role membership rules. Review the effective role graph, not only grants written directly to the login.

Understand permissive and restrictive policy composition

Policies are permissive by default. When multiple permissive policies apply to the same command and role, PostgreSQL combines them with OR. Adding a second permissive policy can widen access.

Restrictive policies are combined with AND and can narrow rows already allowed by a permissive policy. For example, a restrictive policy could require that a ticket is not archived:

CREATE POLICY tickets_not_archived
  ON support.tickets
  AS RESTRICTIVE
  FOR SELECT
  TO agent_tenant_42
  USING (status <> 'archived');

Review the full set together. A restrictive policy is not a standalone grant; at least one applicable permissive policy must allow the row before restrictive policies can narrow it.

Do not trust a tenant value the agent can set

A common multi-tenant pattern stores tenant context in a custom run-time setting and reads it inside a policy with current_setting. PostgreSQL supports transaction-local settings through set_config(..., true) or SET LOCAL.

That pattern is safe only when a trusted application layer derives the tenant from authenticated identity, sets it inside the transaction, and prevents the untrusted SQL caller from choosing or replacing the value.

If an AI agent has a general SQL execution tool and can run this itself:

SET app.tenant_id = '84';

then a policy that blindly trusts current_setting('app.tenant_id') does not establish tenant identity. It only reads a client-controlled variable.

For a direct database connection, prefer a fixed database role with fixed policy scope, as in this guide. For a pooled application architecture, bind tenant context in trusted code, use transaction-local state, reset it reliably, and test reuse of the same physical connection across different tenants. The configuration settings functions document the difference between transaction-local and session-level values.

Account for RLS boundaries outside ordinary queries

PostgreSQL documents several limits that matter in a threat model:

• Superusers and BYPASSRLS roles always bypass row security.
• Table owners normally bypass RLS unless the table uses FORCE ROW LEVEL SECURITY.
• TRUNCATE and REFERENCES are not controlled by RLS.
• Unique, primary-key, and foreign-key checks bypass row security to preserve integrity and can reveal conflicts about rows the caller cannot select.
• Policy expressions that query other tables can create race conditions if authorization data changes concurrently.
• Setting row_security = off does not bypass RLS; it makes queries error when a policy would filter rows, which is useful for backup and integrity workflows that must not silently omit data.

RLS is a strong row filter, but it is not a complete isolation system by itself. Combine it with non-owner roles, explicit grants, safe constraints, trusted identity binding, query limits, and regression tests.

Production checklist

• The AI login is not a superuser and has NOBYPASSRLS
• The AI login does not own the database, schema, tables, views, functions, or sequences it uses
• RLS is enabled on every protected table
• FORCE ROW LEVEL SECURITY is enabled when owner sessions must also be filtered
• Every allowed command has an explicit policy for the intended role
• USING restricts existing target rows
• WITH CHECK restricts inserted and updated row values
• Table grants omit unneeded operations such as DELETE and TRUNCATE
• Role memberships and all applicable policies have been reviewed together
• Tenant identity cannot be selected by the untrusted SQL caller
• Allowed and denied tests run through the actual runtime login and connection path
• The matrix is repeated after migrations, ownership changes, policy changes, and pool changes

Official PostgreSQL references

• Row Security Policies
• CREATE POLICY
• ALTER TABLE
• pg_policies
• ALTER ROLE
• Configuration Settings Functions