SecurityNo. 05
Six layers in production, each built for the day the one above it fails: forced row-level security, logins that cannot bypass it, sealed containers, phone certificates, weekly restores.
Did this article help you?
A heart tells me what to write more of.
Architecture
How I keep a 322,000-line accounting system correct on my own: one save gate, append-only money, a hash-chained audit trail, real-database tests, and the audit that caught what they missed.
Arabic interfaces
Why ESC/POS printers mangle Arabic, how to print it as a 1-bit image at the head's exact width, and the geometry bug that turned a 12 cm receipt into a roll on the floor.
Architecture
Offline-first billing for electricity stations: one id from the phone to the receipt, why bill totals must never be added, an outbox that knows what to retry, and the 500 that looped.
FinyxWatt, my platform for electricity and water stations, keeps the customers, bills and receipts of many stations in one PostgreSQL database. Every query its API sends is one missing WHERE tenant_id = ? away from showing one station's customers to another. I write those queries. One day I will forget the filter.
So nothing depends on me remembering it.
This is the field manual for how FinyxWatt and the server behind this site are protected: six layers, each built on the assumption that the layer above it has already failed. Every config comes from the real files, trimmed, without secrets or hostnames. By the end you can make PostgreSQL refuse a read your code forgot to filter, run containers that can barely do anything, give a phone an identity it cannot fake, and prove every week that your backup restores.
Three traps wait along the way: a PostgreSQL setting that comes back as an empty string, a header anyone could forge if one container joined the wrong network, and a retry default that lets a broken backup hold its own test hostage for hours.
The usual tenant boundary is a WHERE clause repeated in every query. It holds until one query, written in a hurry, leaves it out, and the bug is silent: the page works, it just shows too much.
FinyxWatt puts the boundary inside the database. Row-level security is enabled and forced on 88 of the 94 tables in its migrations. The six without it hold the permission catalogue, schema metadata and the bookkeeping of a one-time import. Every policy checks the row's station and that the user is an active member of it:
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_scope_api ON invoices FOR ALL TO platform_api
USING (
tenant_id = app_private.current_tenant_id()
AND EXISTS (
SELECT 1 FROM tenant_memberships m
WHERE m.tenant_id = app_private.current_tenant_id()
AND m.identity_id = app_private.current_identity_id()
AND m.status = 'active'
)
)
WITH CHECK ( /* the same condition */ );The migration that added the membership check opens with its reason: "a future coding error must not turn a verified identity from tenant A into tenant B merely by setting a wrong transaction setting." A wrong station id finds no active membership, and the query returns nothing.
The API sets both values at the start of every transaction, from the verified token:
async withTenant<T>(tenantId: string, identityId: string, callback: (trx: Transaction<Database>) => Promise<T>) {
return this.db.transaction().execute(async (trx) => {
await sql`select set_config('app.tenant_id', ${tenantId}, true)`.execute(trx)
await sql`select set_config('app.identity_id', ${identityId}, true)`.execute(trx)
return callback(trx)
})
}Three details decide whether this works.
The final true makes the setting local to the transaction. A plain SET stays on the connection, and the pool hands it, still pointing at station A, to the next request.
The empty string is the first trap. On a new connection, a setting that was never defined reads as NULL. On a pooled connection that has already served a request, it reads as '', and ''::uuid throws. The helper every policy calls handles both:
CREATE FUNCTION app_private.current_tenant_id() RETURNS uuid
LANGUAGE sql STABLE PARALLEL SAFE AS $function$
SELECT nullif(current_setting('app.tenant_id', true), '')::uuid;
$function$;NULL equals nothing, so a request without context sees zero rows. It fails closed.
FORCE matters because a table's owner skips its policies by default. Superusers and BYPASSRLS roles skip them always. That is layer 2's job.
CI applies every migration to a fresh PostgreSQL and runs adversarial SQL tests as the API's own role. The core of one, ready to copy:
SET ROLE platform_api;
SET LOCAL app.tenant_id = '<station A>';
SET LOCAL app.identity_id = '<an active member of station A>';
DO $test$
BEGIN
IF (SELECT count(*) FROM customers) <> 1 THEN
RAISE EXCEPTION 'RLS expected one station-A customer';
END IF;
BEGIN
INSERT INTO customers (tenant_id, subscriber_number, display_name)
VALUES ('<station B>', 'ILLEGAL', 'Cross-tenant write');
RAISE EXCEPTION 'RLS accepted a cross-tenant write';
EXCEPTION WHEN insufficient_privilege THEN NULL;
END;
END $test$;All of this assumes the API connects as a role the policies apply to.
Connect as the owner without FORCE, as a superuser or as a BYPASSRLS role, and every policy above is decoration.
FinyxWatt's database creates its logins on first start, "before the API image is ever allowed to connect", in the words of the script. Trimmed; the real one takes names from the environment and passwords from secret files:
CREATE ROLE migrator LOGIN NOSUPERUSER NOCREATEDB CREATEROLE NOREPLICATION NOBYPASSRLS;
CREATE ROLE api_login LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;
ALTER DATABASE app OWNER TO migrator;
REVOKE CONNECT ON DATABASE app FROM PUBLIC;
GRANT CONNECT ON DATABASE app TO migrator, api_login;
GRANT platform_api TO api_login; -- the role every policy is written forOnly the migration login owns the schema. No runtime login holds a migration password, and the API runs with RUN_MIGRATIONS: "false". Five runtime roles split the work:
| Role | May | Cannot |
|---|---|---|
platform_api |
station data through the policies; append to ledger and audit log | edit or delete a ledger entry or audit event |
platform_worker |
deliver queued notifications and SMS jobs | share a login or a password with the API |
platform_control |
the platform's control plane | act without a platform identity in the transaction |
platform_auditor |
read the audit log and change log | write anything |
platform_sms_enrollment |
one-time codes and pending phone requests | see SMS jobs, devices, audit records or customers |
The ledger and the audit log refuse changes twice:
GRANT SELECT, INSERT ON ledger_entries TO platform_api; -- no UPDATE, no DELETE
CREATE FUNCTION app_private.prevent_ledger_mutation() RETURNS trigger
LANGUAGE plpgsql SECURITY INVOKER SET search_path = pg_catalog, public AS $function$
BEGIN
RAISE EXCEPTION 'ledger entries are append-only; post a reversal entry instead'
USING ERRCODE = 'integrity_constraint_violation';
END;
$function$;
CREATE TRIGGER ledger_entries_append_only
BEFORE UPDATE OR DELETE ON ledger_entries
FOR EACH ROW EXECUTE FUNCTION app_private.prevent_ledger_mutation();A later migration can widen a grant by accident. The trigger still refuses, and its message tells the next developer what to do instead.
-- Logins that skip row-level security
SELECT rolname FROM pg_roles
WHERE rolcanlogin AND (rolsuper OR rolbypassrls);
-- Tables without enabled AND forced row-level security
SELECT relname FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace
AND NOT (relrowsecurity AND relforcerowsecurity);Roles decide what a login may do. Which station it acts for comes from a token.
FinyxWatt signs people in with Keycloak. The API checks every token's signature, issuer, audience and expiry against Keycloak's published keys:
this.jwks ??= createRemoteJWKSet(new URL(this.config.keycloakJwksUri))
const { payload } = await jwtVerify(token, this.jwks, {
issuer: this.config.keycloakIssuer,
audience: this.config.keycloakAudience,
})Then it reads the verified azp claim to learn which client obtained the token. The comment above that check is the whole lesson of this layer: "Never accept a client-type header: a caller could forge one to route an Android bearer around a cookie-only endpoint."
The station follows the same rule. The API accepts only memberships of the verified identity, then reads that membership again under row-level security. Sessions are short and single-use:
"accessTokenLifespan": 300,
"revokeRefreshToken": true,
"refreshTokenMaxReuse": 0,
"bruteForceProtected": true,
"failureFactor": 5Access tokens live five minutes. A refresh token works once, so a copied one is spent the moment either side uses it. Five wrong passwords make the account wait.
So far the attacker has been a bug in my code. The next layers assume the attacker is already inside a container.
This site runs in one Caddy container, defined like this:
web:
image: caddy:2.10.2-alpine
ports: ['80:80', '443:443', '443:443/udp']
volumes:
- ./Caddyfile:/etc/caddy/Caddyfile:ro
- /srv/moghalis:/srv/moghalis:ro
- caddy_data:/data
- caddy_config:/config
read_only: true
tmpfs: [/tmp]
cap_drop: [ALL]
cap_add: [NET_BIND_SERVICE]
security_opt: [no-new-privileges:true]
mem_limit: 128mNothing can be written outside /tmp and the certificate volumes, every Linux capability is gone except binding low ports, no process can gain new privileges, and memory stops at 128 MB.
FinyxWatt runs twenty services, each with no-new-privileges; its Caddy is read-only with every capability dropped. The stronger protection is the network map:
networks:
edge:
application: { internal: true }
database: { internal: true }
observability: { internal: true }
sms_gateway: { internal: true }
egress:An internal network has no route out, and each service joins only what it needs:
| Service | Networks |
|---|---|
| Caddy, the front door | edge, application, sms_gateway, observability |
| API | application, database, egress, observability |
| SMS gateway | sms_gateway, database |
| Phone enrolment | application, database |
| Web front end, file storage | application |
The front door has no route to the database, and the services in the last three rows have no route to the internet.
FinyxWatt's production stack needs 28 secrets. Each is a separate file with its own random value, mounted as a Docker secret only into the containers that need it:
api:
environment:
DB_USER: ${PLATFORM_API_USER}
DB_PASSWORD_FILE: /run/secrets/platform_api_password
secrets:
- platform_api_passwordThe secrets guide puts the rule in one sentence: "Do not put secrets in command-line arguments, .env files, shell history, container images, or source control." An environment variable shows up in docker inspect; a file under /run/secrets exists only where it was mounted.
Caddy on this site's server renews HTTPS certificates and adds these headers to every response:
header {
Strict-Transport-Security "max-age=31536000; includeSubDomains"
X-Content-Type-Options "nosniff"
X-Frame-Options "SAMEORIGIN"
Referrer-Policy "strict-origin-when-cross-origin"
Permissions-Policy "camera=(), microphone=(), geolocation=(), payment=()"
-Server
}FinyxWatt's front door also closes what it does not serve:
{
admin off
}
(restricted_console) {
@outside_trusted_networks not remote_ip {$ADMIN_TRUSTED_CIDRS}
respond @outside_trusted_networks 403
basic_auth {
{$OBSERVABILITY_USER} {$OBSERVABILITY_PASSWORD_HASH}
}
}admin off turns off Caddy's own admin API. Monitoring consoles answer only from trusted networks, then ask for a password the server holds only as a bcrypt hash. /metrics returns 404 from the internet, and the public phone-enrolment route accepts 32 KB at most, because it never receives a file. Each security header comes from one place: the API sets X-Frame-Options itself, so Caddy's block for the API leaves it out.
The front door's most interesting job is checking a passport for every phone that sends bills.
FinyxWatt sends each customer's bill by SMS from dedicated Android phones at the station. A phone that sends messages in a station's name needs more than a password, so it gets a client certificate.
The database keeps only an HMAC of the code, made with a secret pepper, and the schema enforces the limits itself:
-- HMAC-SHA-256(code-id || secret), never the raw enrollment code.
code_digest bytea NOT NULL CHECK (octet_length(code_digest) = 32),
expires_at timestamptz NOT NULL,
max_attempts smallint NOT NULL DEFAULT 5 CHECK (max_attempts BETWEEN 1 AND 10),
attempts smallint NOT NULL DEFAULT 0 CHECK (attempts BETWEEN 0 AND 10),
CONSTRAINT sms_device_enrollment_codes_attempts_bound CHECK (attempts <= max_attempts),
CONSTRAINT sms_device_enrollment_codes_expiry CHECK (expires_at > created_at)Approval goes through the main API, which refuses the code's creator with SMS_ENROLLMENT_SELF_APPROVAL_FORBIDDEN and binds a certificate from a private authority whose public key matches the key the phone proved it holds. From then on:
tls {
client_auth {
mode require_and_verify
trust_pool file /run/secrets/sms_device_ca_pem
}
}
reverse_proxy sms-gateway:8081 {
header_up X-Verified-Client-Certificate-Fingerprint {tls_client_fingerprint}
}Now the second trap. The gateway believes that fingerprint header without question, and that is safe for one reason: it sits on an internal network that only Caddy joins, and Caddy overwrites the header after verifying the certificate. The gateway's own code spells out how to break it: "Do not expose the gateway container port or add arbitrary containers to that network; doing either would make this header forgeable." One ports: line added for a quick debug, and anyone could send the header.
The proxy hides one more trap. Every request reaches the gateway with Caddy's address, so a per-IP rate limit would give all phones one shared budget. The gateway keys its limit on the verified certificate:
await app.register(fastifyRateLimit, {
max: 120,
timeWindow: '1 minute',
keyGenerator: (request) => {
const fingerprint = request.headers['x-verified-client-certificate-fingerprint']
const normalized = typeof fingerprint === 'string' ? fingerprint.trim().toLowerCase().replaceAll(':', '') : ''
return /^[0-9a-f]{64}$/.test(normalized) ? `device:${normalized}` : `addr:${request.ip}`
},
})The authority's private key stays offline, and each phone's certificate is revoked when the device is.
This site's server was set up by one script, and its SSH part is careful about order:
if [ -s "/home/$USER_NAME/.ssh/authorized_keys" ]; then
cat >/etc/ssh/sshd_config.d/10-moghalis.conf <<'EOF'
PermitRootLogin no
PasswordAuthentication no
KbdInteractiveAuthentication no
EOF
sshd -t && systemctl reload ssh
else
echo "!! No SSH key for $USER_NAME yet: password login left ON."
fi
ufw default deny incoming
ufw allow OpenSSH
ufw allow 80/tcp
ufw allow 443/tcp
ufw allow 443/udp
ufw --force enablePassword login goes off only when a key is in place, SSH reloads only when sshd -t accepts the config, and the firewall allows SSH before it goes up. Get any of the three out of order on a fresh VPS and your next stop is the provider's rescue console.
Six layers, each assuming the one above it fails. The backup assumes they all did.
FinyxWatt's PostgreSQL archives its write-ahead log at least every five minutes and takes a full base backup every night with WAL-G, whose binary is pinned and checksum-verified. Every file is compressed and encrypted before it is written. The owner's office computer pulls new files every four hours and keeps 45 days of them.
The encryption key is the backup: lose it and every file is noise. It is sealed with the other server secrets in a password-protected file, on a USB drive and in a second place, with the password kept apart from both.
None of that proves anything until a restore works. Every Friday a script restores the newest base backup and every archived log after it into a throwaway volume. Trimmed:
timeout --kill-after=60 2700 docker run --rm --network none --name "$name" \
-e PITR_STORAGE_MODE=local -e WALG_DOWNLOAD_FILE_RETRIES=2 \
-v "$name:/restore" \
-v "$repository:/local-pitr:ro" \
-v "$key:/run/secrets/pitr_encryption_key:ro" \
--entrypoint bash "$image" /restore-check.shInside, the copy's config gets these lines before PostgreSQL starts:
cat >> /restore/data/postgresql.auto.conf <<'CONF'
restore_command = '/usr/local/bin/pitr-walg wal-fetch %f %p'
archive_mode = off
listen_addresses = ''
CONF
touch /restore/data/recovery.signalEach line is a guard. No network, so the copy cannot touch the live database. A read-only repository and key. No archiving, no listeners. And $image is read from the live database container, so the check uses the exact image production runs. When recovery finishes, it counts what came back:
SELECT json_build_object(
'migrations', (SELECT count(*) FROM schema_migrations),
'stations', (SELECT count(*) FROM tenants),
'customers', (SELECT count(*) FROM customers),
'bills', (SELECT count(*) FROM invoices),
'receipts', (SELECT count(*) FROM payments),
'newestChange', (SELECT max(changed_at) FROM change_log));The counts say the backup holds what it should; the newest change says how fresh it is. Then everything is deleted, whatever happened. A failed check, or no check for nine days, raises an alert in my developer app.
Now the third trap, and the reason for WALG_DOWNLOAD_FILE_RETRIES=2. WAL-G retries a file it cannot read 15 times, minutes apart. Point it at a damaged or undecryptable backup and the check does not fail. It waits, for hours. Two retries and a 45-minute ceiling turn that wait into a clear failure with a reason.
A watchdog runs every ten minutes and reports what quietly breaks backups: a disk at 85%, a stopped service, a full backup older than 26 hours, stuck WAL archiving, an office computer that has not pulled for three days, a certificate close to expiry.
The layers protect the running system. The gate protects it from the next release.
A repository check fails the build on private keys, tokens, JWTs, database URLs with a password, real emails and phone numbers, and any tracked .env or secrets/ file:
['private key material', /-----BEGIN(?: [A-Z]+)? PRIVATE KEY-----/],
['GitHub access token', /\bgh[pousr]_[A-Za-z0-9_]{30,}\b/],
['database URI with embedded password', /\bpostgres(?:ql)?:\/\/[^\s:@/${]+:[^\s@/${]{8,}@/i],It lists files with git ls-files --cached --others --exclude-standard, so run locally it catches a file before you add it.
Then CI runs every migration on a fresh PostgreSQL with the tenant-isolation tests, the backend tests, integration tests under the restricted database role, and npm audit --omit=dev --audit-level=high. Automatic deployment waits for all of it:
on:
workflow_run:
workflows: [Independent Platform CI]
types: [completed]
branches: [main]
concurrency:
group: production-deploy
cancel-in-progress: false
jobs:
deploy:
if: github.event.workflow_run.conclusion == 'success'
steps:
- uses: actions/checkout@v4
with:
ref: ${{ github.event.workflow_run.head_sha }}Three traps live in those lines. workflow_run fires when CI completes, pass or fail, so without the if a red build deploys. Checking out main instead of head_sha can ship a commit CI never tested. And a deployment cancelled halfway leaves a half-migrated server. The job also pins the server's host-key fingerprint before its first SSH command.
A written review attacked FinyxWatt through its code and with passive requests to the live servers, recording each result with the file that proves it:
| Attempt | What stops it |
|---|---|
A forged token: alg: none, or another issuer |
jose, the published keys, issuer, audience, azp |
| Acting for another station through a membership header | memberships of the verified identity only, re-read under RLS |
| Reading another station's change feed | row-level security on the change log |
| A cross-site POST against the web session | an origin check and SameSite=Strict cookies |
CORS reflection with a hostile Origin |
no Access-Control-Allow-Origin in the reply, checked live |
| A forged certificate fingerprint header | unpublished port; Caddy overwrites the header |
| Guessing a phone's pairing code | five tries, HMAC with a pepper, an expiry, a row lock |
Reaching /metrics or Keycloak's console from outside |
404 and 403, checked live |
set_config(..., true), from the token, read through nullif.BYPASSRLS; one login per job.UPDATE or DELETE grants, plus a trigger that refuses both..env files.None of these layers is clever on its own. The strength is the assumption under all of them: the layer above has already failed, and this one has to hold anyway. Write that assumption into your configs, and the day one of them fails becomes an ordinary day.
If you would rather have someone run this for your system, that is what hosting and support covers.
Comments
Join the conversation
Sign in with Google to like articles and comment. It takes one tap.