How Row-Level Security (RLS) Isolates Users' Durable Function Instances and Variables in pg_durable

pg-durable enforces per-user isolation by defining all durable functions as SECURITY INVOKER and applying PostgreSQL Row-Level Security (RLS) policies that restrict every df.instances, df.nodes, and df.vars row to the calling PostgreSQL role.

The Microsoft pg_durable extension orchestrates durable workflows directly inside PostgreSQL. To isolate users' durable function instances and variables, the extension relies on PostgreSQL's built-in Row-Level Security (RLS) rather than manual access checks in application code.

Why pg-durable Requires Row-Level Security

Without RLS, the extension would need to grant DML rights on its internal tables to every application role so that internal SPI calls succeed. Those broad table-level grants would allow any user to read, update, or delete any other user's instances, nodes, and variables. RLS keeps the necessary grants while restricting each role to only its own rows.

Isolation Columns in the pg-durable Schema

Three internal tables store workflow state, and each carries a REGROLE column that ties every row to a specific PostgreSQL role:

• df.instances.submitted_by — The outer (calling) role that started the instance. The extension populates this via GetOuterUserId() in src/lib.rs.
• df.nodes.submitted_by — The same role that owns the parent instance.
• df.vars.owner — The role that created the key-value pair. This defaults to current_user::regrole.

These columns are populated automatically by the extension code. End users cannot modify them, which prevents a malicious caller from forging ownership.

RLS Policy Definitions in pg-durable

The extension enables and configures RLS through its SQL installation and upgrade scripts. In sql/pg_durable--0.1.1.sql, RLS is enabled on the core tables:

ALTER TABLE df.instances ENABLE ROW LEVEL SECURITY;
ALTER TABLE df.nodes ENABLE ROW LEVEL SECURITY;

The policies enforce the following rules for all operations (FOR ALL):

• df.instances — submitted_by = current_user::regrole, with a matching WITH CHECK clause on INSERT.
• df.nodes — Identical policy using submitted_by = current_user::regrole.
• df.vars — owner = current_user::regrole for all CRUD operations. This policy was added in the upgrade script sql/pg_durable--0.1.1--0.2.0.sql.

Because the policies are declared FOR ALL, they automatically cover SELECT, INSERT, UPDATE, and DELETE. The WITH CHECK clause guarantees that a user cannot INSERT a row pretending to belong to another role.

Worker Role Bypass

The background worker that executes workflow steps runs as a superuser configured through the pg_durable.worker_role GUC. Superusers bypass RLS by design, so the worker can read and update any row regardless of submitted_by or owner. This is intentional; the worker must manage all users' workflows globally.

How RLS Affects Each pg-durable Function

Because all functions are defined as SECURITY INVOKER, every SQL statement inside a df.* function executes with the privileges of the calling role. RLS silently filters every table access accordingly:

• df.start() — Inserts into df.instances and df.nodes under the caller's role, and reads only the caller's rows from df.vars.
• df.status(), df.result(), df.wait_for_completion(), df.explain(), df.list_instances(), df.instance_info(), df.instance_nodes() — SELECT on df.instances and df.nodes is automatically restricted to the caller's rows.
• df.cancel() — The internal SELECT on df.instances runs through RLS. If the instance is not owned by the caller, the subsequent UPDATE affects zero rows, and the durable-client call aborts.
• df.setvar(), df.getvar(), df.unsetvar(), df.clearvars() — All DML on df.vars is scoped by the owner column and its matching RLS policy.
• df.signal() — Calls the duroxide client without touching internal tables. The client runs under the worker role, so there is no RLS impact at the SQL layer.

Practical Isolation Example

The following example demonstrates how Alice and Bob are isolated from each other, and how the worker role bypasses RLS:

-- Alice creates a workflow instance and a variable
SET SESSION AUTHORIZATION alice;
SELECT df.start(df.sql('SELECT 42'), 'alice-job');   -- creates rows with submitted_by = 'alice'
SELECT df.setvar('my_key', 'alice_value');           -- inserts into df.vars with owner = 'alice'
RESET SESSION AUTHORIZATION;

-- Bob tries to see Alice's data – all queries are filtered by RLS
SET SESSION AUTHORIZATION bob;
SELECT count(*) FROM df.instances;                   -- 0  (Alice's rows are hidden)
SELECT df.getvar('my_key');                         -- NULL (Bob sees only his own vars)
SELECT df.cancel('<alice-instance-id>');            -- No effect / error, because RLS blocks the SELECT check
RESET SESSION AUTHORIZATION;

-- The worker role (superuser) can see everything
SET SESSION AUTHORIZATION postgres;   -- assuming the default worker role
SELECT count(*) FROM df.instances;    -- shows rows from all users
SELECT * FROM df.vars;                -- shows all variables
RESET SESSION AUTHORIZATION;

Security Model Overview

The RLS implementation in pg_durable delivers two core guarantees:

• Confidentiality — A user can only SELECT rows where the isolation column matches their role, so they never see another user's instances, nodes, or variables.
• Integrity — INSERT, UPDATE, and DELETE are rejected unless the isolation column matches the caller, preventing forged ownership.

Trusted superusers—and specifically the designated worker role—intentionally bypass RLS so that background execution can proceed across all user workflows. The full design rationale is documented in docs/rls.md, which supersedes the earlier notes in docs/user-isolation.md.

Summary

• pg-durable isolates users by combining SECURITY INVOKER functions with PostgreSQL RLS policies on df.instances, df.nodes, and df.vars.
• Isolation columns (submitted_by and owner) are populated automatically by extension code in src/lib.rs and cannot be tampered with by callers.
• Policies in sql/pg_durable--0.1.1.sql and sql/pg_durable--0.1.1--0.2.0.sql enforce current_user::regrole matching for all CRUD operations.
• The background worker configured via pg_durable.worker_role runs as a superuser, bypassing RLS to manage workflows globally.
• All design rationale is maintained in docs/rls.md inside the Microsoft pg_durable repository.

Frequently Asked Questions

How does pg-durable prevent a user from forging another user's instance?

The extension populates the submitted_by and owner columns internally via GetOuterUserId() and DEFAULT current_user::regrole. Additionally, the RLS policies include a WITH CHECK clause that rejects any INSERT where the isolation column does not match current_user::regrole.

Can an ordinary PostgreSQL role disable RLS on pg-durable tables?

No. Only the table owner or a superuser can disable Row-Level Security. Because the pg_durable extension owns the df.* tables, application roles cannot alter the RLS policies or bypass them.

Why does the background worker need to bypass RLS?

The background worker must manage, execute, and clean up workflow instances for all users. It runs as the superuser specified by the pg_durable.worker_role GUC. PostgreSQL superusers bypass RLS by design, which lets the worker operate on every row while keeping end users isolated.

What happens if a user tries to cancel another user's durable instance?

When a user calls df.cancel() on an instance they do not own, the internal SELECT on df.instances returns zero rows because of the RLS policy on submitted_by. The subsequent UPDATE affects no rows, and the durable-client call is aborted. The caller receives an error rather than affecting another user's workflow.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}