Database RLS Policies

Originally published on Medium on June 4, 2026.

What is row-level security?

Normally, a database role with SELECT access to a table can read all of its rows. Row-level security (RLS) adds policies that determine which rows that role may access.

For a role subject to RLS, even a query requesting the whole table returns only the rows allowed by the applicable policies. Enforcement lives inside PostgreSQL, rather than depending entirely on filters in application code.

Think of a filing cabinet where you can open a drawer, but only the folders you are allowed to access are visible.

Why use RLS?

Application-level authorization can be undermined by a missing filter, a misconfigured route, or another path to the database. RLS adds a second layer of enforcement that also applies to direct database queries made by roles subject to its policies.

Instead of relying solely on developers to write queries that respect data boundaries, the database enforces those boundaries too. This is particularly useful in applications where users or tenants share tables.

How it works

1. Enable RLS on a table.
2. Create policies containing Boolean expressions that describe permitted access.
3. PostgreSQL applies the relevant policies when processing queries.

Policies can refer to columns in the row, the PostgreSQL role executing the query through current_user, or application context stored in a setting such as app.current_user_id.

When RLS is enabled and no applicable policy allows access, the default is to deny access for roles subject to RLS. Ordinary table privileges are still required.

Policies for SELECT, INSERT, UPDATE, and DELETE

First, enable RLS:

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

The following examples assume that users.id is a UUID and that the application sets app.current_user_id for the authenticated request.

Read your own data

CREATE POLICY "Users can only access their own data"
ON users
FOR SELECT
USING (
  id = current_setting('app.current_user_id', true)::uuid
);

Insert your own data

CREATE POLICY "Users can only create their own data"
ON users
FOR INSERT
WITH CHECK (
  id = current_setting('app.current_user_id', true)::uuid
);

Update your own data

CREATE POLICY "Users can only modify their own data"
ON users
FOR UPDATE
USING (
  id = current_setting('app.current_user_id', true)::uuid
) WITH CHECK (
  id = current_setting('app.current_user_id', true)::uuid
);

Delete your own data

CREATE POLICY "Users can only delete their own data"
ON users
FOR DELETE
USING (
  id = current_setting('app.current_user_id', true)::uuid
);

The distinction is:

• USING determines which existing rows may be read or targeted.
• WITH CHECK validates the new row produced by an insert or update.

Rows excluded by USING are filtered out. A write that violates WITH CHECK raises an error rather than silently discarding the new row.

Read application context with current_setting

current_setting(name, missing_ok) reads a configuration parameter previously set using SET or set_config().

current_setting('app.current_user_id')          -- missing_ok = false (default)
current_setting('app.current_user_id', true)    -- missing_ok = true

Without the second argument, PostgreSQL raises an error if the setting does not exist:

ERROR: unrecognized configuration parameter "app.current_user_id"

With missing_ok = true, it returns NULL for a missing setting. A comparison between a row’s ID and that NULL value does not evaluate to true, so the example policy does not allow access.

This avoids the missing-setting error, but does not authorize an unauthenticated request. A SELECT will not return a matching row under this policy; an INSERT can still fail its WITH CHECK condition. An empty or malformed setting also is not a valid UUID simply because missing_ok is enabled.

I prefer the default error behavior when diagnosing missing context or configuration issues. The choice should be deliberate and consistent with the application’s authentication flow.

Handle login and registration separately

Login and registration happen before an authenticated user context is available. They therefore need a separate, carefully controlled access path.

The approach described in the original article uses a dedicated service_role for those operations, while ordinary authenticated requests use app_user.

CREATE ROLE service_role LOGIN;
GRANT SELECT, INSERT ON TABLE users TO service_role;
ALTER ROLE service_role BYPASSRLS;