Authorization (RBAC and RLS)

Goal

Set up role-based access control and row-level security policies so that users see and modify only the data they are permitted to.

Prerequisites

SQL session with a user that has SUPERUSER or SECURITY_ADMIN privileges.
A test table (e.g. public.users) with representative data.

RBAC — users, roles, privileges

Create a user and grant a role

CREATE USER app_reader WITH PASSWORD 'change-me';
GRANT reader TO app_reader;
GRANT SELECT ON public.users TO reader;

Object-level grants

GRANT SELECT ON TABLE public.orders TO analyst;
GRANT INSERT, UPDATE ON TABLE public.orders TO writer_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

Default policy is deny-by-default: a user without an explicit GRANT cannot access another user’s tables.

Built-in role hierarchy

SUPERUSER
├── SECURITY_ADMIN — policy, audit, key, and break-glass management
├── DBA — ops (shutdown, backup, restore, settings, diagnostics)
├── CREATEROLE — CREATE/ALTER/DROP USER and ROLE
└── CREATEDB — CREATE DATABASE

SUPERUSER does not bypass RLS — only BREAK_GLASS can (see break-glass.md).

RLS — row-level security

Enable RLS on a table

ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;

After this, the table follows deny-by-default: no rows are visible to anyone (including the owner) until at least one policy is created.

Create a security policy

CREATE SECURITY POLICY p_users_tenant ON public.users
 USING (tenant_id = current_setting('app.tenant_id')::int);

The USING predicate is injected as an automatic WHERE clause on SELECT, INSERT, UPDATE, and DELETE.

RLS enforcement rules

Operation	Behaviour
`SELECT`	Rows that fail the predicate are silently excluded.
`INSERT`	The new row must satisfy the predicate; otherwise an error is raised.
`UPDATE`	The old row must be visible (silent skip if not); the new row must satisfy the predicate (error if not).
`DELETE`	The row must be visible (silent skip if not; returns 0 affected rows).

Multiple policies on one table use AND semantics — all must pass.

RLS v1 masking metadata

Policies can include a MASK clause for user-facing field masking:

ALTER SECURITY POLICY p_users_tenant ON public.users
 USING (tenant_id = current_setting('app.tenant_id')::int)
 MASK (email USING 'partial');

Supported mask types in v1:

Mask	Effect
`partial`	Returns a stable masked shape of the original value.
`nullify`	Returns `NULL` in place of the real value.

Unsupported mask expressions return 0A000 feature_not_supported.

Unsupported predicates — fail-closed

In the IR/planner mode, complex predicates that cannot be safely rewritten (subqueries, arbitrary function calls, JOINs) are rejected:

ERROR: 0A000 feature_not_supported
 unsupported RLS predicate form

This is a bounded contract, not a bug. Use the supported predicate language (column references, current_setting(), current_user, literals, comparisons, AND/OR/NOT, IN, IS [NOT] NULL, type casts).

Introspection

SELECT * FROM angara_table_policies('public.users');
SELECT * FROM angara_effective_rls_predicate('public.users');
SELECT * FROM sys.users;
SELECT * FROM sys.roles;
SELECT * FROM sys.user_roles;
SELECT * FROM sys.role_privileges;
SELECT * FROM sys.object_grants;
SELECT * FROM sys.my_privileges;
SELECT * FROM sys.security_policies;
SELECT * FROM angara_user_roles('alice');
SELECT * FROM angara_user_privileges('alice');
SELECT angara_has_privilege('alice', 'SELECT', 'TABLE', 'public.orders');
SELECT angara_is_rls_active('public.users');

angara_effective_rls_predicate() shows the combined predicate, provenance, and mask metadata for a table — useful for explaining row-visibility behaviour.

Expected result

RBAC grants control object-level access; users without grants are denied.
RLS predicates filter rows transparently on SELECT/INSERT/UPDATE/DELETE.
angara_table_policies reflects active policies, mask metadata, and provenance.
Unsupported paths return a deterministic SQLSTATE, never a silent bypass.

Troubleshooting

42501 insufficient_privilege The current user lacks the required role or grant. Check sys.my_privileges and angara_user_roles().
0A000 feature_not_supported on RLS policy or mask The predicate or mask expression is outside the supported v1 syntax. Simplify the expression.
Rows unexpectedly invisible after enabling RLS Deny-by-default is working correctly. Create a SECURITY POLICY with a USING predicate to allow the intended rows.
Need a bug-report artifact? See ../reference/support.md.

AngaraBook