Multi-tenant row ownership — Examples

Multi-tenant row ownership

Give each row an owner and let PostgreSQL enforce that users only see and write their own rows. The authenticated id flows in through a global, and a policy compares it to .owner:

global current_user: uuid;

type Article extends Base {
  required title: str;
  required owner: uuid;

  policy owner_writes for all to app_user
    using ( .owner = global current_user )
    with check ( .owner = global current_user );
}

This lowers to a CREATE POLICY that reads the current user from a session setting:

CREATE POLICY "owner_writes" ON "article" FOR ALL TO app_user
  USING (owner = current_setting('app.current_user', true)::UUID)
  WITH CHECK (owner = current_setting('app.current_user', true)::UUID);

• using filters which existing rows are visible to SELECT/UPDATE/DELETE.
• with check rejects inserts/updates that would set owner to anyone else.
• Your app must connect as a non-owner role (here app_user) for the policy to apply — the table owner bypasses RLS.

Setting the current user from the client

axel codegen emits helpers that push the id into the session before running your queries (inside a transaction, so it’s safe under connection pooling):

TypeScript

// scope a block of queries through the Runner
await runner.withCurrentUser(userId, async (q) => {
  return q.listArticles();
});

// or a single standalone call
const article = await createArticle(db, params, { currentUser: userId });

Go

// scope a block of queries through the Runner
err := runner.WithCurrentUser(ctx, userID, func(q *gen.Queries) error {
    _, err := q.ListArticles(ctx)
    return err
})

// or a single standalone call
article, err := gen.CreateArticle(ctx, db, params, gen.WithCurrentUser(userID))

With the setting in place, list Articles returns only the caller’s rows, and an insert whose owner isn’t the current user is rejected. See Globals for optional vs required semantics.