Skip to main content

Row-Level Security (RLS)

Row-level security policies filter query results so users only see rows their role set authorizes them to read. Policies are compiled into implicit WHERE clauses during planning — users cannot bypass them, and the optimizer pushes the filter down through joins and into the scan.

RLS and Data Masking are the two policy surfaces that consult the RBAC effective-roles closure. Policies match on role names; any name in the caller's effective set (direct grants + hierarchy, filtered by USE ROLE / USE SECONDARY ROLES) satisfies a TO <role> clause.

Syntax at a Glance​

-- Create
CREATE POLICY <name> ON <catalog.schema.table>
    [ AS (PERMISSIVE | RESTRICTIVE) ]
    [ FOR (ALL | SELECT | INSERT | UPDATE | DELETE) ]
    [ TO <role> [, <role> ...] ]
    USING (<expr>)
    [ WITH CHECK (<expr>) ];

-- Drop
DROP POLICY [IF EXISTS] <name> ON <catalog.schema.table>;

-- Enable / disable RLS enforcement on a table
ALTER TABLE <table> ENABLE ROW LEVEL SECURITY;
ALTER TABLE <table> DISABLE ROW LEVEL SECURITY;

Defaults when clauses are omitted: AS PERMISSIVE, FOR ALL, unrestricted (applies to every role).

Policy Types​

Permissive (default)​

PERMISSIVE policies combine with OR: if any permissive policy's USING predicate evaluates true for a row, the row is visible. This is the common case — each policy grants access to a slice of the data.

CREATE POLICY region_us ON orders
    AS PERMISSIVE FOR SELECT
    TO analyst_us
    USING (region = 'US');

CREATE POLICY region_eu ON orders
    AS PERMISSIVE FOR SELECT
    TO analyst_eu
    USING (region = 'EU');

A user in both analyst_us and analyst_eu sees rows where region IN ('US','EU').

Restrictive​

RESTRICTIVE policies combine with AND: every applicable restrictive policy must match, on top of whatever permissive policies admitted the row.

-- Even admins never see archived orders
CREATE POLICY hide_archived ON orders
    AS RESTRICTIVE FOR SELECT
    USING (status <> 'archived');

-- Analysts only see rows above the visibility threshold
CREATE POLICY hide_low_value ON orders
    AS RESTRICTIVE FOR SELECT
    TO analyst
    USING (amount >= 100);

Command scope​

FOR SELECT | INSERT | UPDATE | DELETE | ALL controls which statements trigger the policy. FOR ALL (default) applies to every data-touching statement.

-- Read policy: what the role sees on SELECT/UPDATE/DELETE (predicate checked against existing rows)
CREATE POLICY region_read ON orders
    FOR SELECT TO analyst
    USING (region = 'US');

-- Write policy: what rows the role can INSERT (WITH CHECK runs against proposed rows)
CREATE POLICY region_write ON orders
    FOR INSERT TO analyst
    WITH CHECK (region = 'US');

USING filters rows already in the table (read path); WITH CHECK validates rows the statement is trying to write (INSERT / UPDATE new image). Both can appear on the same policy.

Targeting Roles​

TO <role> [, <role> ...] restricts a policy to callers whose effective-role set contains at least one of the listed names. Omitting TO makes the policy apply to every caller of the table.

Role matching respects the full RBAC hierarchy:

  • A policy TO analyst fires for a caller with analyst directly, and for one who holds senior_analyst with senior_analyst → analyst in the hierarchy.
  • USE SECONDARY ROLES NONE drops non-primary roles from the session set — TO analyst stops firing even though the user still holds analyst, because only the primary is active.
  • Catalog roles participate the same way, but only after being pulled in by a tenant role (GRANT ROLE <cat_role> IN CATALOG c TO ROLE <tenant_role>), since users never hold catalog roles directly.

Combination Rules​

For a given row and statement, the engine evaluates:

visible
  = (OR of all applicable PERMISSIVE policies' USING predicates)
  AND (AND of all applicable RESTRICTIVE policies' USING predicates)

Where "applicable" means the policy's command-scope matches the statement and the caller's effective roles match the policy's TO set (or the policy has no TO).

If a table has RLS enabled and no permissive policy applies to the caller, the row set is empty by default — restrictive-only policies still leave the permissive side as "no one matches", so the AND degenerates to false. Grant at least one permissive policy per role that needs access.

Canonical Example​

From the RBAC v2 test suite, demonstrating role-hierarchy pass-through:

CREATE POLICY analyst_only ON bb.bb.salaries
    TO analyst
    USING (dept = 'engineering');

-- analyst: holds `analyst` directly       → sees engineering rows
-- senior_analyst: holds `senior_analyst → analyst` → sees engineering rows
-- compliance: no matching role            → policy doesn't apply

Pairing with session controls:

-- Caller holds both primary (compliance) and secondary (analyst)
USE ROLE compliance;
USE SECONDARY ROLES ALL;
SELECT * FROM bb.bb.salaries;   -- sees engineering rows (analyst active)

USE SECONDARY ROLES NONE;
SELECT * FROM bb.bb.salaries;   -- sees nothing (only compliance active, no matching policy)

Planner Behavior​

RLS filters are injected before cost-based optimization, so they participate in:

  • Partition pruning — a policy USING (region = 'US') prunes non-US partitions before reading a single Parquet file.
  • Dynamic filtering — the predicate is published to hash-join probes and can be pushed as a runtime filter.
  • Masking composition — RLS runs before data masking, so masked output columns are still computed on the filtered row set.
-- Original query
SELECT order_id, amount FROM orders WHERE amount > 100;

-- After RLS injection for a user with region='US' (permissive policy TO analyst_us):
SELECT order_id, amount FROM orders
WHERE amount > 100
  AND region = 'US';

Enable / Disable on a Table​

RLS enforcement is table-scoped. A table with ROW LEVEL SECURITY disabled ignores every attached policy.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders DISABLE ROW LEVEL SECURITY;

Disabling is the correct way to temporarily exempt a table (e.g. during bulk load), rather than creating a permissive wildcard policy.

Bypassing RLS​

There is no BYPASS_RLS privilege. Two supported patterns:

  • Permissive wildcard policy for a trusted role: 
    CREATE POLICY admin_override ON orders
        AS PERMISSIVE FOR SELECT
        TO tenant_admin
        USING (true);
  • tenant_admin escape hatch — the tenant_admin role is also a standard role name, so granting it a permissive-true policy is the canonical way to ensure compliance/incident response teams can read every row. See RBAC.

Dropping Policies​

DROP POLICY region_filter ON orders;
DROP POLICY IF EXISTS region_filter ON orders;

DROP POLICY takes effect atomically; the next query picks up the change, the same way role grants do.

Listing Policies​

For interactive inspection of policy bodies, enabled flags, and role targeting:

SHOW ROW POLICIES;
SHOW ROW POLICIES ON orders;

For joinable / filterable introspection — studio badges, BI-tool discovery, reverse lookup ("where is pci_mask applied?") — query INFORMATION_SCHEMA.POLICY_REFERENCES directly:

-- Does this table have a row-access policy?
SELECT COUNT(*) > 0 AS has_rls
FROM my_catalog.information_schema.policy_references
WHERE ref_database_name = 'my_catalog'
  AND ref_schema_name   = 'sales'
  AND ref_entity_name   = 'orders'
  AND policy_kind       = 'ROW_ACCESS_POLICY';

The view covers both row-access and column-masking bindings; see the INFORMATION_SCHEMA reference for the full column semantics and known gaps.

Reference​

Last updated on