Build a TypeScript workflow that shards tenant tables on Neki by tenant_id, applies Postgres row-level security and column-level grants, then proves that one tenant's role cannot read or write another tenant's rows. PlanetScale's shard-patient-records reference example walks the same idea for a healthcare schema; this walkthrough takes the generic tenant case and adds a scripted isolation proof.
What you will build
A self-contained TypeScript script that provisions a Neki branch, shards tenant tables, sets RLS policies, and runs isolation proofs against the live database.
Create demo branch -> add shards -> create admin role -> create tables -> update data topology -> load sample tenants/records/events -> create reporting and front_desk roles -> enable RLS and policies -> verify counts, routing, denied column, and cross-tenant write rejection -> delete branch.
AI prompt
Build a TypeScript workflow on Neki that creates an isolated demo branch, shards tenant tables by tenant_id, configures Postgres RLS policies and least-privilege roles, and verifies that a front-desk role can only see its own tenant's rows while a reporting role can see all rows.
Prerequisites
- Node.js 22 and npm
- PlanetScale CLI (pscale) 0.348.0 or later, placed in ./bin or /workspace/bin
- A PlanetScale organization with Neki access
- A PlanetScale service token that can create branches, shards, and roles
- An existing Neki database and branch to use as the demo parent branch
- npm install pg@8.14.1 ts-node@10.9.2 typescript@5.8.2 @types/pg@8.11.13
The role and connection mechanics used below are described in the Neki connecting/roles guide: Postgres role names are the prefix of the pscale username before the dot, and connections require the full username including the branch suffix.
Environment variables
PLANETSCALE_SERVICE_TOKEN_ID: PlanetScale service token ID for CLI authenticationPLANETSCALE_SERVICE_TOKEN: PlanetScale service token secret for CLI authenticationPLANETSCALE_ORG: PlanetScale organization namePLANETSCALE_DATABASE: Name of the Neki database to usePLANETSCALE_BRANCH: Existing branch to use as the parent for the temporary demo branch
1. Set up the project and install dependencies
Create the project directory, install the Postgres client and TypeScript tooling, and download the PlanetScale CLI into ./bin so the script can call it.
mkdir -p neki-rls-demo/bin && cd neki-rls-demo
npm init -y
npm install pg@8.14.1 ts-node@10.9.2 typescript@5.8.2 @types/pg@8.11.13
# Linux amd64 example; pick the matching asset for your OS from the CLI release page
curl -fsSL -o bin/pscale.tar.gz https://github.com/planetscale/cli/releases/download/v0.348.0/pscale_0.348.0_linux_amd64.tar.gz
tar -xzf bin/pscale.tar.gz -C bin && chmod +x bin/pscale
export PATH="$PWD/bin:$PATH"
pscale version
2. Configure TypeScript
Add a tsconfig.json so ts-node compiles the script as CommonJS.
{
"compilerOptions": {
"target": "ES2022",
"module": "CommonJS",
"moduleResolution": "node",
"esModuleInterop": true,
"strict": true,
"skipLibCheck": true,
"outDir": "./dist"
},
"include": ["index.ts"]
}
3. Implement the verification script
Create index.ts. It reads all credentials from the environment, creates an isolated Neki branch, shards tenant tables by tenant_id, configures RLS, and prints the isolation proof.
import { Client } from "pg";
import { spawnSync } from "child_process";
import { randomUUID } from "crypto";
import { resolve } from "path";
// ---------------------------------------------------------------------------
// Environment
// ---------------------------------------------------------------------------
const serviceTokenId = requireEnv("PLANETSCALE_SERVICE_TOKEN_ID");
const serviceToken = requireEnv("PLANETSCALE_SERVICE_TOKEN");
const org = requireEnv("PLANETSCALE_ORG");
const database = requireEnv("PLANETSCALE_DATABASE");
const parentBranch = requireEnv("PLANETSCALE_BRANCH");
const PATH = [process.env.PATH || "", "/workspace/bin", resolve(process.cwd(), "bin")]
.filter(Boolean)
.join(":");
function requireEnv(name: string): string {
const v = process.env[name];
if (!v) throw new Error(`Missing environment variable ${name}`);
return v;
}
// ---------------------------------------------------------------------------
// pscale CLI helpers
// ---------------------------------------------------------------------------
interface RoleCredential {
id: string;
name: string;
username: string;
password: string;
access_host_url: string;
database_url: string;
ready: boolean;
}
function pscale(args: string[], input?: string): string {
const result = spawnSync("pscale", args, {
env: {
...process.env,
PLANETSCALE_SERVICE_TOKEN_ID: serviceTokenId,
PLANETSCALE_SERVICE_TOKEN: serviceToken,
PATH,
},
input,
encoding: "utf8",
stdio: ["pipe", "pipe", "pipe"],
});
if (result.status !== 0) {
throw new Error(
`pscale ${args.join(" ")} failed: ${result.stderr || result.stdout}`,
);
}
return result.stdout;
}
function pscaleJson<T>(args: string[], input?: string): T {
return JSON.parse(pscale(args, input)) as T;
}
function roleNameFromUsername(username: string): string {
// pscale usernames look like pscale_api_xxx.<branch-id>;
// the Postgres role name is the prefix.
return username.split(".")[0];
}
function connectionString(cred: RoleCredential): string {
// Neki requires the full username (including the branch suffix) to route.
return `postgresql://${cred.username}:${cred.password}@${cred.access_host_url}:5432/postgres?sslmode=verify-full`;
}
async function createRole(
name: string,
branch: string,
inherited?: string,
ttl?: string,
): Promise<RoleCredential> {
const args = [
"role",
"create",
database,
branch,
name,
"--org",
org,
"--format",
"json",
];
if (inherited) args.push("--inherited-roles", inherited);
if (ttl) args.push("--ttl", ttl);
const cred = pscaleJson<RoleCredential>(args);
for (let i = 0; i < 60; i++) {
const client = new Client({
connectionString: connectionString(cred),
ssl: true,
});
try {
await client.connect();
await client.query("SELECT 1");
await client.end();
return cred;
} catch (e: any) {
if (i >= 5) {
console.log(` role ${name} not ready yet (${i + 1}/60): ${e.message}`);
}
await new Promise((r) => setTimeout(r, 1000));
}
}
throw new Error(`Role ${name} did not become ready in time`);
}
// ---------------------------------------------------------------------------
// Postgres helpers
// ---------------------------------------------------------------------------
async function withClient<T>(
connectionString: string,
fn: (c: Client) => Promise<T>,
): Promise<T> {
const client = new Client({ connectionString, ssl: true });
await client.connect();
try {
return await fn(client);
} finally {
await client.end();
}
}
async function queryRows<T = any>(
client: Client,
sql: string,
params?: any[],
): Promise<T[]> {
const result = await client.query(sql, params);
return result.rows;
}
async function queryOne<T = any>(
client: Client,
sql: string,
params?: any[],
): Promise<T> {
const rows = await queryRows<T>(client, sql, params);
if (rows.length !== 1) {
throw new Error(`Expected 1 row for ${sql}, got ${rows.length}`);
}
return rows[0];
}
// ---------------------------------------------------------------------------
// Main
// ---------------------------------------------------------------------------
const tables = {
tenants: "tenant_rls_tenants",
records: "tenant_rls_records",
events: "tenant_rls_events",
};
async function main() {
const runId = randomUUID().slice(0, 8);
const demoBranch = `rls-demo-${runId}`;
console.log(`Creating demo branch ${demoBranch} from ${parentBranch}...`);
pscaleJson<{ name: string }>([
"branch",
"create",
database,
demoBranch,
"--from",
parentBranch,
"--org",
org,
"--wait",
"--format",
"json",
]);
console.log("Adding two shards to the demo branch...");
pscaleJson<{ created: number }>([
"branch",
"shard",
"create",
database,
demoBranch,
"--config-profile",
"default",
"--count",
"2",
"--org",
org,
"--format",
"json",
]);
// Wait until all shards report ready and the control plane knows about them.
for (let i = 0; i < 60; i++) {
const shards = pscaleJson<
Array<{ ready: boolean; name: string }>
>([
"branch",
"shard",
"list",
database,
demoBranch,
"--org",
org,
"--format",
"json",
]);
if (shards.every((s) => s.ready) && shards.length >= 3) {
console.log(`All ${shards.length} shards ready`);
break;
}
await new Promise((r) => setTimeout(r, 2000));
}
for (let i = 0; i < 60; i++) {
const topo = pscaleJson<{
data_topology?: { shard_groups?: Array<{ uid: string }> };
}>([
"branch",
"data-topology",
"get",
database,
demoBranch,
"--org",
org,
"--format",
"json",
]);
const groups = topo.data_topology?.shard_groups || [];
if (groups.length >= 3) {
console.log("Control plane has registered all shards");
break;
}
await new Promise((r) => setTimeout(r, 2000));
}
const adminRoleName = `rls_admin_${runId}`;
const reportingRoleName = `rls_reporting_${runId}`;
const frontDeskRoleName = `rls_front_desk_${runId}`;
console.log("Creating admin role...");
const adminCred = await createRole(adminRoleName, demoBranch, "postgres");
const adminConn = connectionString(adminCred);
await withClient(adminConn, async (admin) => {
console.log("Creating tables...");
await admin.query(`DROP TABLE IF EXISTS ${tables.events} CASCADE`);
await admin.query(`DROP TABLE IF EXISTS ${tables.records} CASCADE`);
await admin.query(`DROP TABLE IF EXISTS ${tables.tenants} CASCADE`);
await admin.query(`
CREATE TABLE ${tables.tenants} (
tenant_id int PRIMARY KEY,
name text NOT NULL
)
`);
await admin.query(`
CREATE TABLE ${tables.records} (
tenant_id int NOT NULL,
record_id bigint NOT NULL,
content text NOT NULL,
secret text,
PRIMARY KEY (tenant_id, record_id)
)
`);
await admin.query(`
CREATE TABLE ${tables.events} (
tenant_id int NOT NULL,
record_id bigint NOT NULL,
event_id bigint NOT NULL,
note text NOT NULL,
PRIMARY KEY (tenant_id, record_id, event_id)
)
`);
console.log("Fetching shard topology...");
const shards = pscaleJson<
Array<{
id: string;
name: string;
display_name: string;
authoritative: boolean;
}>
>([
"branch",
"shard",
"list",
database,
demoBranch,
"--org",
org,
"--format",
"json",
]);
const authoritative = shards.find((s) => s.authoritative)!;
const others = shards.filter((s) => !s.authoritative);
if (others.length < 2) {
throw new Error("Expected at least two non-authoritative shards");
}
const shardA = others[0];
const shardB = others[1];
// Preserve any existing topology entries and add our shard group and tables.
const existingRaw = pscaleJson<any>([
"branch",
"data-topology",
"get",
database,
demoBranch,
"--org",
org,
"--format",
"json",
]);
const existing = existingRaw.data_topology || existingRaw;
const databases: any = {};
for (const [dbName, dbVal] of Object.entries(existing.databases || {})) {
databases[dbName] = dbVal;
}
if (!databases.postgres) {
databases.postgres = { schemas: { public: { tables: {} } } };
}
if (!databases.postgres.schemas) {
databases.postgres.schemas = {};
}
if (!databases.postgres.schemas.public) {
databases.postgres.schemas.public = { tables: {} };
}
databases.postgres.schemas.public.tables = {
...databases.postgres.schemas.public.tables,
[tables.tenants]: { shard_group: authoritative.name },
[tables.records]: { shard_group: "tenant_rls_group" },
[tables.events]: { shard_group: "tenant_rls_group" },
};
const topology = {
shard_indexes: {
...(existing.shard_indexes || {}),
tenant_rls_idx: { type: "xxhash", columns: ["tenant_id"] },
},
shard_groups: [
...(existing.shard_groups || []),
{
uid: "tenant_rls_group",
default_shard_index: "tenant_rls_idx",
key_ranges: [
{ end: "80", shard_uid: shardA.name },
{ start: "80", shard_uid: shardB.name },
],
},
],
databases,
default_shard_group: authoritative.name,
authoritative_shard_group: authoritative.name,
};
console.log("Updating data topology...");
const topoResult = pscaleJson<{ status?: string }>(
[
"branch",
"data-topology",
"update",
database,
demoBranch,
"--org",
org,
"--format",
"json",
],
JSON.stringify(topology),
);
console.log("Topology update:", topoResult.status || "applied");
// Give the control plane a moment to propagate the new topology.
await new Promise((r) => setTimeout(r, 10000));
console.log("Loading sample tenants, records, and events...");
await admin.query(`
INSERT INTO ${tables.tenants} (tenant_id, name)
VALUES (10, 'Acme'), (20, 'Globex'), (30, 'Initech')
`);
await admin.query(`
INSERT INTO ${tables.records} (tenant_id, record_id, content, secret)
SELECT t, g,
'record ' || g || ' for tenant ' || t,
lpad((t * g * 7919 % 10000)::text, 4, '0')
FROM generate_series(10, 30, 10) AS t,
generate_series(1, 10) AS g
`);
await admin.query(`
INSERT INTO ${tables.events} (tenant_id, record_id, event_id, note)
SELECT tenant_id, record_id, e, 'event ' || e
FROM ${tables.records}, generate_series(1, 3) AS e
`);
const counts = await queryRows<{ tenant_id: number; count: string }>(
admin,
`SELECT tenant_id, count(*)::text AS count
FROM ${tables.records}
GROUP BY tenant_id
ORDER BY tenant_id`,
);
console.log("Records per tenant:", counts);
console.log("Creating restricted roles...");
const reportingCred = await createRole(
reportingRoleName,
demoBranch,
"pg_read_all_data",
);
const frontDeskCred = await createRole(frontDeskRoleName, demoBranch);
const frontDeskRole = roleNameFromUsername(frontDeskCred.username);
const reportingRole = roleNameFromUsername(reportingCred.username);
await admin.query(
`GRANT SELECT (tenant_id, record_id, content) ON ${tables.records} TO ${frontDeskRole}`,
);
await admin.query(
`GRANT SELECT, INSERT ON ${tables.events} TO ${frontDeskRole}`,
);
console.log("Enabling row-level security...");
await admin.query(`ALTER TABLE ${tables.records} ENABLE ROW LEVEL SECURITY`);
await admin.query(`ALTER TABLE ${tables.events} ENABLE ROW LEVEL SECURITY`);
await admin.query(`
CREATE POLICY tenant_records_front_desk ON ${tables.records}
FOR SELECT TO ${frontDeskRole}
USING (tenant_id = nullif(current_setting('app.tenant_id', true), '')::int)
`);
await admin.query(`
CREATE POLICY tenant_events_front_desk ON ${tables.events}
FOR ALL TO ${frontDeskRole}
USING (tenant_id = nullif(current_setting('app.tenant_id', true), '')::int)
WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), '')::int)
`);
await admin.query(`
CREATE POLICY tenant_records_reporting ON ${tables.records}
FOR SELECT TO ${reportingRole} USING (true)
`);
await admin.query(`
CREATE POLICY tenant_events_reporting ON ${tables.events}
FOR SELECT TO ${reportingRole} USING (true)
`);
console.log("Verifying tenant isolation...\n");
// Admin sees everything.
const adminCount = (
await queryOne<{ count: string }>(
admin,
`SELECT count(*)::text AS count FROM ${tables.records}`,
)
).count;
console.log(`admin records visible: ${adminCount}`);
const routing = await queryRows<{ "QUERY PLAN": string }>(
admin,
`EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT * FROM ${tables.records} WHERE tenant_id = 20`,
);
const routeLine = routing
.map((r) => r["QUERY PLAN"])
.find((line) => line.includes("Route"));
console.log(
`routing for tenant_id = 20: ${
routeLine || routing.map((r) => r["QUERY PLAN"]).join(" | ")
}`,
);
await withClient(connectionString(reportingCred), async (reporting) => {
const reportingCount = (
await queryOne<{ count: string }>(
reporting,
`SELECT count(*)::text AS count FROM ${tables.records}`,
)
).count;
const secret = await queryOne<{ secret: string }>(
reporting,
`SELECT secret FROM ${tables.records} WHERE tenant_id = 20 LIMIT 1`,
);
console.log(
`reporting records visible: ${reportingCount}, can read secret: ${secret.secret ? "yes" : "no"}`,
);
});
await withClient(connectionString(frontDeskCred), async (frontDesk) => {
// No tenant set -> zero rows.
const noTenant = (
await queryOne<{ count: string }>(
frontDesk,
`SELECT count(*)::text AS count FROM ${tables.records}`,
)
).count;
console.log(`front_desk without app.tenant_id: ${noTenant}`);
// Tenant 20 -> only its rows.
await frontDesk.query("SET app.tenant_id = '20'");
const tenant20 = (
await queryOne<{ count: string }>(
frontDesk,
`SELECT count(*)::text AS count FROM ${tables.records}`,
)
).count;
console.log(`front_desk tenant 20 records: ${tenant20}`);
// Cross-tenant read by primary key returns nothing.
await frontDesk.query("SET app.tenant_id = '20'");
const tenant10Row = await queryRows(
frontDesk,
`SELECT record_id FROM ${tables.records} WHERE tenant_id = 10 AND record_id = 1`,
);
console.log(`front_desk tenant 20 reading tenant 10 row: ${tenant10Row.length}`);
// Denied column.
let deniedMessage = "n/a";
try {
await frontDesk.query("SET app.tenant_id = '20'");
await frontDesk.query(
`SELECT secret FROM ${tables.records} WHERE tenant_id = 20 LIMIT 1`,
);
} catch (e: any) {
deniedMessage = e.message.split("\n")[0];
}
console.log(`front_desk denied column: ${deniedMessage}`);
// Cross-tenant write rejected by WITH CHECK.
let crossWriteMessage = "n/a";
try {
await frontDesk.query("SET app.tenant_id = '20'");
await frontDesk.query(
`INSERT INTO ${tables.events} (tenant_id, record_id, event_id, note)
VALUES (10, 1, 99, 'cross-tenant event')`,
);
} catch (e: any) {
crossWriteMessage = e.message.split("\n")[0];
}
console.log(`front_desk cross-tenant insert: ${crossWriteMessage}`);
});
});
console.log("\nDeleting demo branch...");
pscale(["branch", "delete", database, demoBranch, "--org", org, "--force"]);
console.log("Cleanup complete.");
}
main().catch((err) => {
console.error(err);
process.exit(1);
});
4. Run the verified workflow
Set the five required environment variables in your shell (the two service-token variables plus organization, database, and parent branch names), then run the script. It creates a temporary branch, proves isolation, and deletes the branch automatically.
npm install && npx ts-node index.ts
Verify the result
Run against pscale 0.348.0, Neki PostgreSQL 18.6 on 2026-11-04.
npm install && npx ts-node index.ts
Observed output:
up to date, audited 43 packages in 382ms
found 0 vulnerabilities
Creating demo branch rls-demo-31e2640f from [REDACTED]...
Adding two shards to the demo branch...
All 3 shards ready
Creating admin role...
Creating tables...
Fetching shard topology...
Updating data topology...
Topology update: applied
Loading sample tenants, records, and events...
Records per tenant: [
{ tenant_id: 10, count: '10' },
{ tenant_id: 20, count: '10' },
{ tenant_id: 30, count: '10' }
]
Creating restricted roles...
Enabling row-level security...
Verifying tenant isolation...
admin records visible: 30
routing for tenant_id = 20: Route [EqualUnique]
reporting records visible: 30, can read secret: yes
front_desk without app.tenant_id: 0
front_desk tenant 20 records: 10
front_desk tenant 20 reading tenant 10 row: 0
front_desk denied column: permission denied for table tenant_rls_records
front_desk cross-tenant insert: new row violates row-level security policy for table "tenant_rls_events"
Deleting demo branch...
Cleanup complete.
Expected: Topology applied, 30 total records spread across three tenants, tenant equality queries route to a single shard, reporting sees all rows including secrets, front_desk sees zero rows without a tenant context, exactly 10 rows for tenant 20, zero cross-tenant reads, a permission-denied error for the secret column, and a row-level security violation for a cross-tenant insert.
How it works
Neki exposes standard Postgres to the application while routing queries to real Postgres shards. The script creates a shard group keyed by xxhash(tenant_id), so all rows for one tenant land on the same shard and equality lookups route to a single shard. It then uses ordinary Postgres features on every shard: ALTER TABLE ... ENABLE ROW LEVEL SECURITY, column-level GRANTs, and policies that compare tenant_id with the session variable app.tenant_id. Because the policies live in the database, even a query that forgets a WHERE clause is filtered before rows leave the shard. The reporting role gets a USING (true) policy so pg_read_all_data does not become invisible after RLS is enabled.
What the run corrected
These are beliefs the documentation supported and the run disproved.
Assumed: The Postgres role name (username before the dot) is what you connect with. Observed: Neki requires the full username including the branch suffix for connections; the prefix is only the Postgres role name for grants and policies. Evidence: Connecting with only the prefix returned "User parameter must include branch (e.g., user.branch)"; using the full username succeeded.
Assumed: The shard id field from pscale branch shard list is the value to use in topology key_ranges shard_uid. Observed: The topology uses the shard name field, not id, as shard_uid and group uid. Evidence: Topology updates with id values were rejected; updates with name values succeeded and appeared in pscale branch data-topology get.
Assumed: A role that inherits pg_read_all_data can read rows after RLS is enabled. Observed: After ENABLE ROW LEVEL SECURITY, pg_read_all_data alone returns zero rows until a policy is added for that role. Evidence: The live reporting role returned zero rows before CREATE POLICY ... USING (true) and 30 rows after.
Assumed: The existing shared database branch could be used directly for the demo topology. Observed: Shared branches can be in the middle of MoveTables workflows with unexpected database group names (movetest_src/movetest_dst). Evidence: pscale branch data-topology get on the shared branch showed movetest_src/movetest_dst groups instead of postgres; creating an isolated demo branch produced a clean postgres topology.
Common issues
pscale data-topology update fails with "shard_uid ... is not a shard the cluster has created"
Cause: The control plane knows a shard exists before the data topology service does.
Fix: Poll pscale branch data-topology get until the new shards appear in shard_groups before updating the topology.
Role authentication fails immediately after pscale role create
Cause: Neki connection strings require the full pscale username including the branch suffix, while GRANT/POLICY statements use only the prefix before the dot.
Fix: Connect with the full username from the role JSON; use roleNameFromUsername only in SQL identifiers.
Reporting role sees zero rows after ALTER TABLE ... ENABLE ROW LEVEL SECURITY
Cause: RLS hides every row from roles that are not covered by a policy, even ones that inherit pg_read_all_data.
Fix: Add a USING (true) policy for each role that needs to read all rows.
SELECT * or specific columns fail with "permission denied for table"
Cause: Column-level GRANTs deny any column that was not granted, including wildcard selects.
Fix: List only the granted columns in application queries or grant the columns the query needs.
Next steps
- Replace the synthetic tenant data with your real tables and shard group, keeping tenant_id as the leading primary-key column so equality lookups stay single-shard.
- Move the tenant context out of the connection string by setting app.tenant_id in a transaction or connection middleware rather than letting end users choose it.
- Split hot tenants onto dedicated shards with a range topology once one tenant outgrows its hash bucket, then rerun the same RLS proof to confirm isolation still holds.
verification:
status: verified
tested_at: "2026-11-04"
product_version: "pscale 0.348.0, Neki PostgreSQL 18.6"
command: "provision Neki branch with two shards, shard tenant_rls_records/events by tenant_id, create admin/reporting/front_desk roles, enable RLS with per-role policies, run isolation proofs"
expected_result: "topology applied; 30 records across tenants 10/20/30; tenant_id=20 query routes as Route [EqualUnique]; reporting role sees 30 rows and reads secret column; front_desk sees 0 without app.tenant_id, 10 for tenant 20, 0 cross-tenant reads, permission denied on secret column, row-level security policy violation on cross-tenant insert"