Postgres row-level security for mobile app backends

Row-level security (RLS) is a Postgres feature that filters which rows a query may read or change, based on a policy you attach to the table. For a mobile backend, you tie each policy to the authenticated user’s id, so a query for “all notes” returns only that user’s notes even if the application code forgets a where clause. RLS is a strong safety net, but it only works if you enable it correctly, avoid the roles that bypass it, and test it.

This article covers how policies work, the mistakes that most often leave data exposed, how to keep them fast, and how to test them.

What does row-level security do?

Without RLS, access control in Postgres works at the table level: a role may or may not select from notes. With RLS enabled, Postgres adds a second check per row. For each statement, it evaluates the table’s policies and only lets through rows that satisfy them.

Mobile apps are a good fit for this because the client is untrusted. Your API or database layer receives a request from a phone, and a bug in one endpoint, such as a missing ownership filter, can expose another user’s data. RLS moves the ownership rule into the database, where every code path has to pass through it.

Here is the smallest useful example.

create table notes (
  id         uuid primary key default gen_random_uuid(),
  owner_id   uuid not null,
  body       text not null,
  created_at timestamptz not null default now()
);

alter table notes enable row level security;

create policy notes_owner_select on notes
  for select
  using (owner_id = current_setting('app.user_id')::uuid);

After this, a role subject to RLS that runs select * from notes sees only rows whose owner_id equals the value of the app.user_id setting for the session.

How do policies know who the user is?

Postgres does not know about your app’s users. Its roles are database roles. You have to hand the user’s identity to the session, and the policy reads it.

There are two common ways.

Session settings. Your API validates the user’s token, then, inside the transaction, sets a custom setting that the policies read.

begin;
select set_config('app.user_id', '3f1c0d52-8a4e-4a52-9d1f-0a2f6f5b7a10', true);
-- run the user's queries here
commit;

The third argument, true, makes the setting local to the transaction. That matters when you use a connection pool: you do not want one user’s identity to leak to the next request that reuses the connection. Always set it per transaction, and never rely on a value set earlier on a pooled connection.

Claims from a token. Some platforms put the validated token’s claims into a setting for you, and expose helper functions such as one that returns the current user’s id. The idea is the same: the identity comes from a token the server validated, not from a value the client typed.

In both cases the rule is that the identity must come from a verified source. Do not let a client-supplied header become app.user_id without verification.

How do you write policies for each operation?

Policies are per command: select, insert, update, delete, or all. The full syntax is in the CREATE POLICY reference. Two clauses do different jobs.

using filters existing rows. It applies to select, update, and delete, deciding which rows are visible and targetable.
with check validates new or changed rows. It applies to insert and update, deciding whether the row you write is allowed.

A full set for the notes table looks like this.

create policy notes_insert on notes
  for insert
  with check (owner_id = current_setting('app.user_id')::uuid);

create policy notes_update on notes
  for update
  using      (owner_id = current_setting('app.user_id')::uuid)
  with check (owner_id = current_setting('app.user_id')::uuid);

create policy notes_delete on notes
  for delete
  using (owner_id = current_setting('app.user_id')::uuid);

The with check on update is easy to forget. Without it, a user could update their own row and set owner_id to someone else’s id, handing the row over. With it, the new row must still belong to the user.

If you write several permissive policies for the same command, Postgres combines them with OR. A row is allowed if any one policy allows it. That is useful for sharing models, such as “owner or collaborator”, and dangerous if you add a broad policy later and forget it widens everything. Postgres also supports restrictive policies, which are combined with AND, for rules that must always hold.

To make the policy easy to read and reuse, wrap the identity lookup in a function.

create function app_user_id() returns uuid
language sql stable as $$
  select nullif(current_setting('app.user_id', true), '')::uuid
$$;

The true argument makes current_setting return null instead of raising an error when the setting is missing. A null id then matches no rows, which is the safe failure.

What are the common mistakes?

Forgetting to enable RLS, or forgetting FORCE

A policy has no effect until you run alter table ... enable row level security (see ALTER TABLE). Creating policies on a table without enabling RLS silently does nothing.

There is a second trap. By default, the owner of a table is not subject to its RLS policies. If your application connects as the role that owns the tables, which is common when migrations and the app share a role, every policy is bypassed. The fix is either to run the app as a different, non-owner role, or to also run:

alter table notes force row level security;

force makes the policies apply to the table owner too. Use a separate role for migrations and a restricted role for the app, and consider force as a second layer.

Superusers and BYPASSRLS roles

Superusers and roles with the BYPASSRLS attribute skip policies entirely. Check which role your API connects with, and confirm it has neither. A quick way is to query pg_roles for rolsuper and rolbypassrls.

Service-role keys in the wrong place

Platforms often provide a privileged key that bypasses RLS, for admin jobs and server code. That is useful on a server. It is a serious leak if it ships in the mobile app, in the same way an API key for a paid service would be. The client should only ever hold the credential that is subject to RLS. Keep the bypass credential in server-side environment variables, and use it only for jobs that truly need cross-user access, such as a nightly report.

Writing policies that do not cover every command

If you create a select policy and no insert policy, then with RLS enabled inserts are denied by default for roles subject to it. That is safe but confusing. The opposite mistake is for all with only a using clause, which also applies it as the check, and may not say what you meant. Spell out each command.

RLS applies per table, and a policy that refers to another table runs a subquery against it, which is itself subject to that table’s policies.

Take a comments table whose access depends on the parent notes row.

create policy comments_select on comments
  for select
  using (
    exists (
      select 1 from notes
      where notes.id = comments.note_id
        and notes.owner_id = app_user_id()
    )
  );

This works, but remember three things. First, every table in a join is filtered by its own policies, so a join cannot reveal rows you could not select directly. Second, a policy that reads a table whose own policy reads back can cause infinite recursion errors, so avoid cycles. Third, views run with the privileges of the view owner by default, which can bypass RLS on the underlying tables. Recent Postgres versions let you mark a view with security_invoker so it uses the caller’s rights. Check the documentation for your version before relying on it.

Functions that run as their owner

A function declared security definer runs with its owner’s privileges, which often means bypassing RLS. Use it sparingly, set a fixed search_path, and treat every such function as a place where you must reimplement the ownership check by hand.

How do you keep RLS fast?

A policy is an extra predicate added to every query on the table, so it needs index support like any other filter.

Index the columns the policy uses. For the notes example, that means an index on owner_id, ideally combined with the columns you sort by, such as (owner_id, created_at desc).
Avoid per-row function calls that cannot be cached. If the policy calls a function for each row, Postgres may evaluate it repeatedly. Wrapping the lookup in a scalar subquery, such as owner_id = (select app_user_id()), lets the planner evaluate it once per statement in many cases. Confirm with explain analyze on your version.
Keep join-based policies shallow. A policy that walks three tables per row will be slow at scale. Consider storing the owner id on child tables, even though it duplicates data, so the policy is a single column comparison.
Mark helper functions correctly. A function that always returns the same value within a statement should be declared stable, as above. That lets the planner use it in index conditions. Combined with the scalar subquery above, it is usually evaluated once per statement.

Run explain (analyze, buffers) on your hottest queries as the app role, with the setting populated, and look for sequential scans on large tables. Test as the app role, because running as a superuser skips the policy and hides the cost.

How do you test policies?

Policies are security code, so they deserve tests that fail loudly.

Set up two users and some rows for each, then assert the boundaries. In plain SQL, a test runs as the app role.

begin;
set local role app_user;                         -- the restricted role
select set_config('app.user_id', :'alice', true);

-- Alice sees only her rows
select count(*) from notes;                      -- expect Alice's count

-- Alice cannot insert as Bob
insert into notes (owner_id, body) values (:'bob', 'x');   -- expect failure

-- Alice cannot hand a row to Bob
update notes set owner_id = :'bob' where id = :'alice_note'; -- expect failure
rollback;

Cover these cases for every table:

A user sees their rows and no one else’s.
A user cannot insert, update, or delete another user’s rows.
A user cannot change ownership of a row.
With no identity set, the query returns nothing.
The app role is not a superuser and does not have BYPASSRLS.

Run these in CI against a real Postgres, not a mock. A migration that drops a policy or disables RLS should fail the build. You can add a simple check that queries pg_tables and pg_class for any table in your app schema where relrowsecurity is false, and fails if it finds one.

When is RLS not enough?

RLS answers “which rows can this user touch?” It does not answer business rules such as “free users may create five projects” or “only paid users may call this endpoint”. Those belong in server logic or in constraints. It also does not replace input validation, rate limits, or good secrets handling. Think of RLS as the last wall, not the only one.

How does this relate to Edict?

Edict is a mobile backend in development. It is being built on PostgreSQL, and the planned database module is designed to compile declared entities into tables and user-scoped APIs with ownership rules built in. The planned authentication module would supply the verified identity those rules depend on. These capabilities are planned, and the design may change.

The inventory and collections example keeps each user’s items private, and the social and community apps example shows where sharing rules make policies harder. For another place where old data and old clients meet, read mobile API versioning and migrations for old apps.

Key takeaways

RLS adds a per-row check inside Postgres, so a missing where clause in your API cannot leak other users’ data.
Pass the verified user id into each transaction with a local setting or token claims, and never trust a client-supplied value.
Enable RLS on every table, use force or a non-owner app role, and make sure the app role has no BYPASSRLS.
Write using and with check for each command, and watch for permissive policies combining with OR.
Keep service-role credentials on servers, never in the app.
Index the columns your policies use, and measure as the app role.
Test the boundaries with two users in CI, and fail the build if a table loses RLS.