Team access with Postgres RLS

Use this when a document belongs to a team and every member of that team can read or write it. A direct owner: column fits one principal per row. ownerVia fits a one-hop parent row. A many-to-many membership table needs a custom Postgres predicate.

Model the membership table#

Start with the join table. Membership is tenant data, not global reference data. Model the row with an explicit owner or custom policy so a user can only read the memberships they are allowed to see:

import { kovo } from '@kovojs/drizzle';
import { pgTable, text } from 'drizzle-orm/pg-core';

export const teamMemberships = pgTable(
  'team_memberships',
  {
    id: text('id').primaryKey(),
    teamId: text('team_id').notNull(),
    userId: text('user_id').notNull(),
  },
  kovo((columns) => ({ domain: 'team-membership', key: columns.id, owner: columns.userId })),
);

Use owner: columns.userId when each user may see and manage their own membership rows. If admins manage membership for other users, put an authzPolicy on this table instead. Do not use reference: true for membership graphs; reference is for immutable global lookup rows with no tenant data.

Add the document policy#

Annotate the document table with kovo(() => ({ authzPolicy: sql.raw(...) })). The predicate should answer: "does the current database principal have a membership row for this document's team?"

import { kovo, sql } from '@kovojs/drizzle';
import { pgTable, text } from 'drizzle-orm/pg-core';

export const teamMemberships = pgTable(
  'team_memberships',
  {
    id: text('id').primaryKey(),
    teamId: text('team_id').notNull(),
    userId: text('user_id').notNull(),
  },
  kovo((columns) => ({
    domain: 'team-membership',
    key: columns.id,
    owner: columns.userId,
  })),
);

export const teamDocuments = pgTable(
  'team_documents',
  {
    id: text('id').primaryKey(),
    teamId: text('team_id').notNull(),
    title: text('title').notNull(),
    body: text('body').notNull(),
  },
  kovo((columns) => ({
    domain: 'team-document',
    key: columns.id,
    authzPolicy: sql.raw(`EXISTS (
      SELECT 1 FROM "team_memberships"
      WHERE "team_memberships"."team_id" = "team_documents"."team_id"
        AND "team_memberships"."user_id" = current_setting('kovo.principal', true)
    )`),
  })),
);

Keep the predicate literal. Kovo binds these exact SQL bytes into the generated table-security manifest and installs that snapshot as the RLS policy. An interpolation such as ${teamMemberships} fails during the build. A table referenced by the predicate needs its own readable, protected posture; owner: 'userId' supplies that posture for the membership table above.

Provision and check it#

Run provision with an admin connection and the ordinary runtime login. Give check the runtime and system URLs so it can bind the runtime witness to the isolated posture audit:

KOVO_ADMIN_DATABASE_URL=postgres://admin@db:5432/app?sslmode=verify-full KOVO_DATABASE_URL=postgres://app@db:5432/app?sslmode=verify-full \
  kovo db provision
KOVO_DB_SYSTEM_URL=postgres://kovo_system@db:5432/app?sslmode=verify-full KOVO_RUNTIME_DATABASE_URL=postgres://app@db:5432/app?sslmode=verify-full \
  kovo db check

The command derives the table posture from src/schema.ts. For the document table, the important shape is:

ALTER TABLE "team_documents" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "team_documents" FORCE ROW LEVEL SECURITY;

CREATE POLICY kovo_authz_policy ON "team_documents"
  USING (EXISTS (...))
  WITH CHECK (EXISTS (...));

You do not write that policy by hand. kovo db provision creates or reasserts it, and kovo db check fails non-zero when the table is missing forced RLS, the policy, or a reachable object falls outside the safe closure audit.

Check member and non-member behavior#

With this seed data:

INSERT INTO team_memberships (id, team_id, user_id)
VALUES ('m1', 'team-a', 'user-a');

INSERT INTO team_documents (id, team_id, title, body)
VALUES ('d1', 'team-a', 'Alpha plan', '...');

user-a is a member of team-a, so a scoped read sees the row:

as user-a:
id  title
d1  Alpha plan

user-b has no matching membership row, so the same read returns nothing:

as user-b:
(0 rows)

A cross-team write fails at the database boundary too:

as user-b inserting team_id = 'team-a':
ERROR: new row violates row-level security policy for table "team_documents"

That failure is the point of the custom predicate path. The app can still run guards and typed mutation errors for a better user experience, but the database is the last line of defense.

Run it#

Provision once, then check with the app credential and a member/non-member seed:

KOVO_ADMIN_DATABASE_URL=postgres://admin@db:5432/app?sslmode=verify-full KOVO_DATABASE_URL=postgres://app@db:5432/app?sslmode=verify-full kovo db provision
KOVO_DB_SYSTEM_URL=postgres://kovo_system@db:5432/app?sslmode=verify-full KOVO_RUNTIME_DATABASE_URL=postgres://app@db:5432/app?sslmode=verify-full kovo db check

Then run the two reads above. The member sees the row. The non-member gets zero rows, and a cross-team write fails at the RLS boundary.

Know the boundary#

Kovo guarantees this table is enrolled in the authorization census, provisioned with forced RLS, and covered by a present kovo_authz_policy. It also checks that posture before the app serves.

Kovo does not prove that your predicate expresses the right business rule. If the predicate says "team members may edit archived documents," Kovo enforces that rule. If archived documents need a second condition, put that condition in the predicate and test it with member and non-member cases.

Handle failure#

The failure mode to show explicitly is the provision-time unsupported posture:

KV433_AUTHZ_POLICY_UNSUPPORTED team_documents authzPolicy must stay inside the supported SQL subset.

If you hit that, simplify the predicate to the supported shape or move the business rule into a reviewed database object the policy can reference directly.

Next#

• Security & authorization - owner annotations, guards, and security review.
• kovo db in the CLI guide - provision and posture-check command options.
• Testing with @kovojs/test - exercise database behavior before deploy.