This guide shards patient records by patient_id on Neki so a patient's chart is one shard, then limits what each database role can see: a read-only reporting role, a front-desk role restricted to some columns and to one clinic's rows with row-level security, and a role that expires on its own. It covers database access scoping only. It does not make a workload compliant with HIPAA or any other regulation, which also depends on agreements, encryption, auditing and application controls outside the schema.
What you will build
patients (patient_id, full_name, birth_date, ssn_last4, clinic_id) --+-- shard group by_patient
encounters (patient_id, encounter_id, clinic_id, diagnosis_code, ...) --+ xxhash(patient_id)
reporting role: pg_read_all_data -> read every column, write nothing
front_desk role: column grants + RLS policy -> no ssn_last4, only the clinic in app.clinic_id
temp_reader role: --ttl 1m -> stops authenticating when it expires
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, shard patient records on Neki by patient id and scope database access by role.
Requirements:
- Create the Neki database with --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 2, add two shards, and wait until every shard answers a direct SELECT 1 with SET __neki.shard. Use one admin role for DDL and grants.
- Shard patients and encounters by patient_id with {"type":"xxhash","columns":["patient_id"]}, in one shard group, with patient_id first in every primary key. Store clinic_id on both tables for row-level security.
- Create a read-only role with pscale role create --inherited-roles pg_read_all_data. Create a narrower role with no inherited roles and give it GRANT SELECT on named columns and GRANT SELECT, INSERT on encounters. The Postgres role name is the username without the part after the first dot.
- Enable row-level security on both tables with policies that compare clinic_id with current_setting('app.clinic_id', true). With RLS enabled, a role that no policy covers sees zero rows, so add a policy for every role that needs rows.
- Create a temporary role with --ttl and confirm it cannot connect after it expires.
- Run the verification step below before finishing.
Prerequisites
A PlanetScale organization with access to the Neki Platform Preview. Create an account if you do not have one, and see Neki availability and access for how an organization administrator joins the preview.
pscale0.344.0 with a service token that can create databases,psql18.6,jq, andpython3:
export PLANETSCALE_SERVICE_TOKEN_ID="<token id>"
export PLANETSCALE_SERVICE_TOKEN="<token>"
export PLANETSCALE_ORG="<organization name>"
pscale version
- A Neki database with three shards. Create it with these commands:
pscale database create clinicdb --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create clinicdb main admin --inherited-roles postgres --org "$PLANETSCALE_ORG" --format json > role.json
export PGHOST=$(jq -r .access_host_url role.json) PGUSER=$(jq -r .username role.json) \
PGPASSWORD=$(jq -r .password role.json) PGPORT=5432 PGDATABASE=postgres PGSSLMODE=require
rm role.json
until psql -At -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done
pscale branch shard create clinicdb main --config-profile default --count 2 --org "$PLANETSCALE_ORG"
until [ "$(psql -At -c 'SELECT count(*) FROM __neki.list_shards()')" = "3" ]; do sleep 10; done
for u in $(psql -At -c "SELECT uid FROM __neki.list_shards()"); do
until psql -At -c "SET __neki.shard='$u';" -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done
done
SHARD_1=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh1'")
SHARD_2=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh2'")
SHARD_3=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh3'")
Note: Output samples are trimmed to the relevant lines. Each shard bills as a three-node cluster.
PS-10-AWS-ARM-NEKIlists at $30 per month per shard, prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish withpscale database delete clinicdb --force --org "$PLANETSCALE_ORG".
1. Create the tables, shard them by patient and load synthetic records
psql -q -v ON_ERROR_STOP=1 \
-c "CREATE TABLE patients (patient_id bigint PRIMARY KEY, full_name text NOT NULL, birth_date date NOT NULL, ssn_last4 text, clinic_id int NOT NULL)" \
-c "CREATE TABLE encounters (patient_id bigint NOT NULL, encounter_id bigint NOT NULL, clinic_id int NOT NULL, visited_at timestamptz NOT NULL, diagnosis_code text NOT NULL, notes text, PRIMARY KEY (patient_id, encounter_id))" \
2>&1 | grep -v NOTICE
cat > data-topology.json <<JSON
{
"shard_indexes": { "patient": { "type": "xxhash", "columns": ["patient_id"] } },
"shard_groups": [
{ "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
{ "uid": "by_patient", "default_shard_index": "patient",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
],
"databases": { "postgres": { "schemas": { "public": { "tables": {
"patients": { "shard_group": "by_patient" },
"encounters": { "shard_group": "by_patient" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update clinicdb main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10
psql -q -c "INSERT INTO patients SELECT p, 'Patient ' || p, date '1960-01-01' + p * 37, lpad((p * 7919 % 10000)::text, 4, '0'), 10 + (p % 3) * 10 FROM generate_series(1, 200) p"
psql -q -c "INSERT INTO encounters SELECT p, e, 10 + (p % 3) * 10, timestamptz '2026-10-01 09:00+00' + e * interval '1 day', 'Z00.0', 'synthetic note' FROM generate_series(1, 200) p, generate_series(1, 5) e"
psql -At -c "SELECT count(*) || ' patients' FROM patients" -c "SELECT count(*) || ' encounters' FROM encounters"
psql -At -c "SELECT 'clinic ' || clinic_id || ': ' || count(*) || ' patients' FROM patients GROUP BY clinic_id ORDER BY clinic_id"
Real output:
applied
200 patients
1000 encounters
clinic 10: 66 patients
clinic 20: 67 patients
clinic 30: 67 patients
The records are synthetic: generated names, dates and four-digit numbers. Use test data like this when you try these steps.
2. Check how the router sends chart queries
route() { psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) $1" | grep -oE 'Route \[[A-Za-z]+\]' | tr '\n' ' '; }
echo "one patient's chart: $(route "SELECT p.full_name, e.visited_at FROM patients p JOIN encounters e USING (patient_id) WHERE p.patient_id = 17")"
echo "one clinic's patients: $(route "SELECT patient_id FROM patients WHERE clinic_id = 20")"
Real output:
one patient's chart: Route [EqualUnique]
one clinic's patients: Route [Scatter]
A chart read by patient_id is one shard. A filter on clinic_id is not the shard key, so it runs on every shard. Row-level security by clinic limits which rows a role sees, and it does not change where the rows live.
3. Create a read-only role and a front-desk role
make_role() { # make_role <name> [inherited roles] [ttl]: write <name>.env with that role's connection settings
pscale role create clinicdb main "$1" ${2:+--inherited-roles "$2"} ${3:+--ttl "$3"} --org "$PLANETSCALE_ORG" --format json > "$1.json"
echo "export PGUSER=$(jq -r .username "$1.json") PGPASSWORD=$(jq -r .password "$1.json")" > "$1.env"
chmod 600 "$1.env"; rm "$1.json"
}
role_of() { ( . "./$1.env"; echo "${PGUSER%%.*}" ); }
as() { r=$1; shift; ( . "./$r.env"; psql -At "$@" 2>&1 | grep -v '^CONTEXT' ); }
make_role reporting pg_read_all_data
make_role front_desk
sleep 10
psql -At -c "GRANT SELECT (patient_id, full_name, clinic_id) ON patients TO $(role_of front_desk)" \
-c "GRANT SELECT, INSERT ON encounters TO $(role_of front_desk)" 2>&1 | grep -v NOTICE
sleep 8
pscale role list clinicdb main --org "$PLANETSCALE_ORG" --format json | jq -r '.[] | .name' | sort
Real output:
GRANT
GRANT
admin
front_desk
reporting
reporting inherits pg_read_all_data, and front_desk has no inherited roles, so it has only the privileges granted to it. Each role has its own username and password, which make_role stores in a file that only the current user can read. Delete those files when you finish.
4. Test the reporting role
as reporting -c "SELECT count(*) FROM patients"
as reporting -c "SELECT patient_id, full_name, ssn_last4 FROM patients WHERE patient_id = 17"
as reporting -c "UPDATE patients SET full_name = 'x' WHERE patient_id = 17"
Real output:
200
17|Patient 17|4623
ERROR: permission denied for table patients
The reporting role can read every column and cannot write.
5. Test the front-desk role
echo "-- granted columns"
as front_desk -c "SELECT patient_id, full_name, clinic_id FROM patients WHERE patient_id = 17"
echo "-- ssn_last4 and select *"
as front_desk -c "SELECT ssn_last4 FROM patients WHERE patient_id = 17"
as front_desk -c "SELECT * FROM patients WHERE patient_id = 17"
echo "-- encounters: read, add, change"
as front_desk -c "SELECT count(*) FROM encounters WHERE patient_id = 17"
as front_desk -c "INSERT INTO encounters VALUES (17, 6, 20, now(), 'Z00.1', 'follow-up')"
as front_desk -c "UPDATE encounters SET notes = 'x' WHERE patient_id = 17 AND encounter_id = 1"
Real output:
-- granted columns
17|Patient 17|30
-- ssn_last4 and select *
ERROR: permission denied for table patients
ERROR: permission denied for table patients
-- encounters: read, add, change
5
INSERT 0 1
ERROR: permission denied for table encounters
The front-desk role reads the three granted columns and cannot read ssn_last4, either by name or through SELECT *. It can read and add encounters and cannot change them, because only SELECT and INSERT were granted.
6. Limit each clinic's rows with row-level security
FD=$(role_of front_desk); RP=$(role_of reporting)
psql -q -v ON_ERROR_STOP=1 \
-c "ALTER TABLE patients ENABLE ROW LEVEL SECURITY" \
-c "ALTER TABLE encounters ENABLE ROW LEVEL SECURITY" \
-c "CREATE POLICY clinic_patients ON patients FOR SELECT TO $FD USING (clinic_id = nullif(current_setting('app.clinic_id', true), '')::int)" \
-c "CREATE POLICY clinic_encounters ON encounters FOR ALL TO $FD USING (clinic_id = nullif(current_setting('app.clinic_id', true), '')::int) WITH CHECK (clinic_id = nullif(current_setting('app.clinic_id', true), '')::int)" \
2>&1 | grep -v NOTICE
sleep 8
echo "-- no clinic set"
as front_desk -c "SELECT count(*) FROM patients"
echo "-- clinic 20"
as front_desk -c "SET app.clinic_id = '20'" -c "SELECT count(*) FROM patients" -c "SELECT count(*) FROM patients WHERE patient_id = 3" -c "SELECT count(*) FROM patients WHERE patient_id = 4" | grep -v '^SET$'
as front_desk -c "SET app.clinic_id = '20'" -c "SELECT count(*) FROM encounters WHERE patient_id = 3" -c "SELECT count(*) FROM encounters WHERE patient_id = 4" | grep -v '^SET$'
echo "-- clinic 20 adds an encounter for a clinic 10 patient"
as front_desk -c "SET app.clinic_id = '20'" -c "INSERT INTO encounters VALUES (3, 6, 10, now(), 'Z00.1', 'wrong clinic')" | grep -v '^SET$'
echo "-- admin and reporting"
psql -At -c "SELECT count(*) FROM patients"
as reporting -c "SELECT count(*) FROM patients"
Real output:
-- no clinic set
0
-- clinic 20
67
0
1
0
5
-- clinic 20 adds an encounter for a clinic 10 patient
ERROR: new row violates row-level security policy for table "encounters"
-- admin and reporting
200
0
With no app.clinic_id set, the front-desk role sees no rows. With app.clinic_id set to 20, it sees only clinic 20's patients and encounters, a chart outside that clinic returns nothing even by patient_id, and the WITH CHECK clause rejects an insert for another clinic's patient. The admin role owns the tables, so the policies do not apply to it. The reporting role, which no policy covers, now also sees zero rows.
7. Give the reporting role a policy, and add a temporary role
psql -q -c "CREATE POLICY reporting_all ON patients FOR SELECT TO $RP USING (true)" 2>&1 | grep -v NOTICE
as reporting -c "SELECT count(*) FROM patients"
make_role temp_reader pg_read_all_data 1m
psql -q -c "CREATE POLICY temp_reader_all ON patients FOR SELECT TO $(role_of temp_reader) USING (true)" 2>&1 | grep -v NOTICE
sleep 8
echo "before expiry: $(as temp_reader -c 'SELECT count(*) FROM patients')"
sleep 70
echo "after expiry: $(as temp_reader -c 'SELECT count(*) FROM patients' | grep -oE 'authentication failed')"
Real output:
200
before expiry: 200
after expiry: authentication failed
The reporting role sees all 200 patients once it has its own policy. The --ttl 1m role could read before its expiry time and failed with authentication failed after it. Use a longer value, such as --ttl 8h, for real temporary access.
Verify the result
as front_desk -c "SET app.clinic_id = '20'" -c "SELECT count(*) FROM patients" | grep -v '^SET$'
as front_desk -c "SELECT ssn_last4 FROM patients WHERE patient_id = 17" | head -1
as reporting -c "UPDATE patients SET full_name = 'x' WHERE patient_id = 17" | head -1
Expected: 67 (the clinic 20 patients), then ERROR: permission denied for table patients twice.
How it works
xxhash on patient_id keeps a patient's patients row and all of their encounters rows on one shard, so a chart read is one single-shard query. Access control is ordinary Postgres, applied on every shard: the Neki router passed the GRANT statements, the column-level privileges and the row-level security policies to the data shards, and the policies were enforced by the shards in testing. app.clinic_id is a session setting. The front-desk role set it itself in these tests, so any session that holds that role's password can choose the clinic. Set it from a trusted service rather than from the end user's input, or give each clinic its own role and a policy with a fixed clinic_id. The Neki data topology guide describes shard groups and index types.
Common issues
Enabling row-level security hides every row from other roles
After ENABLE ROW LEVEL SECURITY, the reporting role returned zero rows until it had a policy of its own, even though it inherits pg_read_all_data. Add a policy for each role that needs rows, and check each one after the change.
A denied column reports the table
Selecting ssn_last4 as the front-desk role returned permission denied for table patients, and so did SELECT *. List the granted columns explicitly in application queries.
Grants need the Postgres role name
pscale role create prints a username such as pscale_api_xxxx.<suffix>. The GRANT and CREATE POLICY statements must use only the part before the first dot, which is what role_of returns here. Using the full username failed with role ... does not exist.
Credential files
make_role writes each role's password to a local file. Delete the .env files when you finish, and delete each role with pscale role delete <db> main <role id> --force --successor postgres. The command takes the role id from pscale role list --format json, not the name, and a role that has been granted privileges needs --successor.
Next steps
- Delete the database with
pscale database delete clinicdb --force --org "$PLANETSCALE_ORG". - Read the guide on keeping a multi-tenant audit log sharded by tenant for the same one-shard-per-key layout with a retention job.
- Read the Neki data topology guide for placing related tables in one shard group.
verification:
status: verified
tested_at: "2026-10-29"
product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
command: "front_desk count with app.clinic_id = 20; front_desk read of ssn_last4; reporting update"
expected_result: "67, then permission denied for table patients twice"