This guide gives a nightly batch job its own database role on Neki. The role has no inherited roles, so it starts with no access. The script grants it only what the job needs, gives it a time to live, and deletes it when the job ends, whether the job succeeds or fails. The guide tests what the role can and cannot do, and what happens to a session when the role is deleted.
What you will build
orders (order_id, customer_id, total_cents, placed_at) sharded by order_id
customers (customer_id, email) sharded by customer_id
daily_totals (day, orders, revenue_cents) unsharded
run_job.sh: pscale role create ... --ttl 30m (no inherited roles)
GRANT SELECT (placed_at, total_cents) ON orders; GRANT SELECT, INSERT ON daily_totals
run the job as that role
pscale role delete <id> --force --successor postgres (trap: runs on success and on failure)
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, run a nightly batch job on Neki with a short-lived role that can do only what the job needs.
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.
- Create the job role with pscale role create <db> main <name> --ttl 30m and no --inherited-roles. The Postgres role name is the username before the first dot. Keep the role id from the JSON output, because pscale role delete takes the id and not the name.
- Grant the role SELECT on only the columns the job reads and SELECT, INSERT on the table the job writes. Wait about 8 seconds for the grants to reach every shard.
- The job reads an aggregate from orders and writes one row per day with psql \gexec, because a cross-shard INSERT ... SELECT into an unsharded table fails with [117].
- Delete the role with pscale role delete <id> --force --successor postgres from a shell trap, so the role is removed even when the job fails. A role that has been granted privileges cannot be deleted without --successor.
- Show what the role cannot do, show that deleting a role ends a session that is open, and show that a role created with --ttl stops authenticating 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 batchjob --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create batchjob 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 batchjob 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 batchjob --force --org "$PLANETSCALE_ORG".
1. Create the tables, shard them and load orders
psql -q -v ON_ERROR_STOP=1 \
-c "CREATE TABLE orders (order_id bigint PRIMARY KEY, customer_id bigint NOT NULL, total_cents int NOT NULL, placed_at timestamptz NOT NULL)" \
-c "CREATE TABLE customers (customer_id bigint PRIMARY KEY, email text NOT NULL)" \
-c "CREATE TABLE daily_totals (day date PRIMARY KEY, orders int NOT NULL, revenue_cents bigint NOT NULL)" \
2>&1 | grep -v NOTICE
cat > data-topology.json <<JSON
{
"shard_indexes": {
"order": { "type": "xxhash", "columns": ["order_id"] },
"customer": { "type": "xxhash", "columns": ["customer_id"] }
},
"shard_groups": [
{ "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
{ "uid": "by_order", "default_shard_index": "order",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] },
{ "uid": "by_customer", "default_shard_index": "customer",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
],
"databases": { "postgres": { "schemas": { "public": { "tables": {
"orders": { "shard_group": "by_order" },
"customers": { "shard_group": "by_customer" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update batchjob main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10
psql -q -c "INSERT INTO orders SELECT i, i % 50, 1000 + i, timestamptz '2026-10-01' + i * interval '1 minute' FROM generate_series(1, 5000) i"
psql -q -c "INSERT INTO customers SELECT c, 'customer' || c || '@example.com' FROM generate_series(0, 49) c"
psql -At -c "SELECT count(*) || ' orders, ' || (SELECT count(*) FROM customers) || ' customers' FROM orders"
Real output:
applied
5000 orders, 50 customers
customers holds email addresses, which the batch job does not need.
2. Create the job role and grant it only what the job needs
make_job_role() { # make_job_role <name> <ttl>: create the role, write <name>.env and <name>.id
pscale role create batchjob main "$1" --ttl "$2" --org "$PLANETSCALE_ORG" --format json > "$1.json"
echo "export PGUSER=$(jq -r .username "$1.json") PGPASSWORD=$(jq -r .password "$1.json")" > "$1.env"
jq -r .id "$1.json" > "$1.id"
chmod 600 "$1.env"; rm "$1.json"
}
pg_role() { ( . "./$1.env"; echo "${PGUSER%%.*}" ); }
as_job() { r=$1; shift; ( . "./$r.env"; psql -X -At "$@" 2>&1 | grep -v '^CONTEXT' ); }
make_job_role job-nightly 30m
sleep 10
R=$(pg_role job-nightly)
psql -q -v ON_ERROR_STOP=1 \
-c "GRANT SELECT (placed_at, total_cents) ON orders TO $R" \
-c "GRANT SELECT, INSERT ON daily_totals TO $R" 2>&1 | grep -v NOTICE
sleep 8
pscale role get batchjob main "$(cat job-nightly.id)" --org "$PLANETSCALE_ORG" --format json | jq -r '"\(.name) has an expiry time: \(.expires_at != null)"'
Real output:
job-nightly has an expiry time: true
The role has an expiry time, set by --ttl 30m. It inherits nothing, so it has only the two grants.
3. Run the job as the role
as_job job-nightly <<'SQL'
SELECT format('INSERT INTO daily_totals VALUES (%L, %s, %s)', day, n, r)
FROM (SELECT placed_at::date AS day, count(*) AS n, sum(total_cents) AS r FROM orders GROUP BY 1) t
\gexec
SQL
psql -At -c "SELECT count(*) || ' days, ' || sum(orders) || ' orders' FROM daily_totals"
Real output:
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
4 days, 5000 orders
The job read the two granted columns of orders and wrote four daily rows. The totals add up to the 5,000 orders.
4. What the role cannot do
as_job job-nightly -c "SELECT customer_id FROM orders WHERE order_id = 1"
as_job job-nightly -c "SELECT email FROM customers LIMIT 1"
as_job job-nightly -c "UPDATE daily_totals SET orders = 0"
as_job job-nightly -c "DELETE FROM orders WHERE order_id = 1"
as_job job-nightly -c "CREATE TABLE scratch (a int)"
Real output:
ERROR: permission denied for table orders
ERROR: permission denied for table customers
ERROR: permission denied for table daily_totals
ERROR: permission denied for table orders
ERROR: permission denied for schema public
LINE 1: CREATE TABLE scratch (a int)
^
The job role cannot read the customer_id column or the customers table, cannot update or delete, and cannot create tables. Each statement was refused with a permission error.
5. Delete the role when the job ends, even when it fails
cat > run_job.sh <<'EOF'
#!/bin/bash
# run_job.sh <role name> <job command...>: create a job role, run the command with its settings, always delete the role
name=$1; shift
. ./functions.sh
make_job_role "$name" 30m
trap 'pscale role delete batchjob main "$(cat "$name.id")" --force --successor postgres --org "$PLANETSCALE_ORG" > /dev/null 2>&1; rm -f "$name.env" "$name.id"' EXIT
sleep 10
psql -q -c "GRANT SELECT (total_cents) ON orders TO $(pg_role "$name")" 2>&1 | grep -v NOTICE
sleep 8
( . "./$name.env"; "$@" )
EOF
chmod +x run_job.sh
declare -f make_job_role pg_role > functions.sh
./run_job.sh job-failing bash -c 'psql -At -c "SELECT sum(total_cents) FROM orders"; exit 7'; echo "job exit=$?"
echo "roles left: $(pscale role list batchjob main --org "$PLANETSCALE_ORG" --format json | jq -r '.[].name' | sort | tr '\n' ' ')"
Real output:
17502500
job exit=7
roles left: admin job-nightly
The job ran as the temporary role and read the sum, then exited with status 7. The EXIT trap still ran and deleted the role, so the role list has no job-failing. A role is deleted by its id, which the script reads from the file that make_job_role wrote.
6. Delete a role while a session is open
( . ./job-nightly.env; psql -X -At -c "SELECT pg_sleep(1)" -c "SELECT 'still connected'" -c "SELECT pg_sleep(30)" -c "SELECT 'survived the delete'" 2>&1 | grep -v '^CONTEXT' ) &
sleep 12
pscale role delete batchjob main "$(cat job-nightly.id)" --force --successor postgres --org "$PLANETSCALE_ORG" > /dev/null 2>&1
wait
Real output:
still connected
ERROR: role "pscale_api_5nhvgbzw8ouh" does not exist
The session was connected before the delete. Deleting the role ended it before the long statement could finish.
7. A role stops working when its time to live ends
make_job_role job-short 1m
sleep 10
as_job job-short -c "SELECT 'connected'"
sleep 70
as_job job-short -c "SELECT 'connected'" | grep -oE 'authentication failed|password authentication failed'
pscale role delete batchjob main "$(cat job-short.id)" --force --successor postgres --org "$PLANETSCALE_ORG" > /dev/null 2>&1
rm -f job-short.env job-short.id
Real output:
connected
authentication failed
A role that nobody deleted stops authenticating after its expiry. Use the time to live as a backstop and delete the role yourself as soon as the job ends.
Verify the result
pscale role list batchjob main --org "$PLANETSCALE_ORG" --format json | jq -r '.[].name' | sort | tr '\n' ' '; echo
psql -At -c "SELECT count(*) || ' daily rows, ' || sum(orders) || ' orders' FROM daily_totals"
Expected: admin is the only role left, and 4 daily rows, 5000 orders.
How it works
pscale role create makes a Postgres role with only the privileges you give it through --inherited-roles. With none, the role can connect and nothing else, so each GRANT is an explicit decision. Grants and policies are ordinary Postgres and apply on every shard. The role's expiry is set by --ttl, and a role that has passed it stops authenticating, but it still exists in the role list until it is deleted. Delete by id so that a script cannot delete the wrong role by a similar name. The Neki overview describes the routers and shards that apply these grants.
Common issues
Delete takes the role id, not the name
pscale role delete <db> main <name> failed with role ... does not exist, because the argument is the role's id, which pscale role create --format json returns as .id. Keep it in a file as the scripts here do.
A role with grants needs a successor
pscale role delete on a role that had been granted privileges failed with Role is still referenced and cannot be dropped. Pass --successor postgres, which names the role that takes over the objects and privileges the role had. A role with no grants was deleted without it. The scripts here always pass it, and a delete that is silenced with > /dev/null hides this error, so check the role list as step 5 does.
Grants take a few seconds
A grant is applied on every shard, and a statement that ran right after it was still refused in earlier testing. The scripts wait about 8 seconds after a GRANT.
The Postgres role name is not the username
pscale role create prints a username such as pscale_api_xxxx.<suffix>. GRANT needs the part before the first dot, which pg_role returns.
INSERT ... SELECT into an unsharded table
INSERT INTO daily_totals SELECT ... FROM orders GROUP BY ... failed with [117] cross-shard INSERT from SELECT into unsharded table is not yet supported. The job reads the aggregate and writes the rows with \gexec. For a large result, write the rows with COPY ... FROM STDIN instead.
Credential files
make_job_role writes the password to a file only your user can read, and run_job.sh removes it with the role. Keep these files out of the repository.
Next steps
- Delete the database with
pscale database delete batchjob --force --org "$PLANETSCALE_ORG". - Read the guide on sharding patient records by patient id and scoping access for column grants and row-level security on the same kind of role.
- Read the Neki data topology guide for placing tables in a shard group.
verification:
status: verified
tested_at: "2026-10-29"
product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
command: "roles left after the job runs and the rows the job wrote"
expected_result: "admin only; 4 daily rows, 5000 orders"