Databases · Lesson 4.1
Letting each app do only its job
So when server A opens a connection to the database server, it logs in, with a username and a password.
Postgres checks them, and then runs the queries server A sends over that connection.
Say Simplified's team also has an analytics app, showing how learners do on quizzes.
It logs in to the same database, to read the attempts table: every answer every learner gives.
It never needs to change a single row.
But say a mistake in its code sent DELETE FROM attempts, with no WHERE.
Or say an attacker slipped that same DELETE in, by SQL injection.
Either way, all 400,000,000 answers would be gone.
But how can Postgres stop the analytics app from doing more than its job needs?
Each program logs in with a username of its own.
Simplified's servers, A and B, log in as app.
The team's analytics app logs in as reporting.
That username is called a role.
A role is a username a program logs in as, with its own list of what it may do.
app may read and change rows, because it saves learners' XP and answers.
reporting may only read attempts, because that's all the analytics app needs.
So what happens when the analytics app sends that DELETE, as reporting?
Postgres checks every query against what its role may do.
A SELECT on attempts only reads, so Postgres runs it, and sends back the rows.
But a DELETE changes rows, and reporting may only read.
So Postgres refuses it, with an error: permission denied.
Not one row of attempts is touched.
It's like a Google Doc someone shared with you as "View only".
You can read every word, but typing changes nothing.
And it doesn't matter how the DELETE got sent, by a mistake or by an injection.
Postgres only checks what the role it came from may do.
So give each program a role with only what it needs, and nothing more.
Postgres refuses any query its role may not run, however that query got sent.
Learn more: Postgres's guide to roles.
Simplified's analytics app logs in to Postgres as reporting, a role that may only read the attempts table. Say a mistake in its code sends DELETE FROM attempts WHERE user_id = 2;, to remove Ben's answers.
What does Postgres do with that DELETE?
Explanation
First, Postgres checks the DELETE against what reporting may do: only read attempts.
Then, a DELETE changes rows, so reporting may not run it.
Therefore, Postgres refuses it, with permission denied, and Ben's answers stay.
Simplified's analytics app logs in as reporting, which may only read the attempts table. Say a bug makes it send three queries: SELECT * FROM attempts;, then DELETE FROM attempts;, then UPDATE attempts SET correct = true;.
How many of the three does Postgres refuse?
Databases · Lesson 4.2
Why a replica isn't a backup
So reporting can't delete a single answer: its role may only read.
But Simplified's server logs in as app, and app may change users, to save learners' XP.
Now say a mistake in its code sends DELETE FROM users, with no WHERE.
With no WHERE, every row passes, so all 2,000,000 learners' rows are deleted.
Postgres doesn't refuse it, because app may delete from users.
And Simplified also runs a replica, with a copy of every row.
So are the learners' rows still safe on the replica?
The primary deletes every row, and sends that change on to the replica, like every other change.
The replica can't tell a mistake from any other change.
So it deletes every row of users too.
DROP TABLE deletes a whole table: every row, and the table itself.
So a DROP TABLE on users goes on to the replica the same way, and users is gone there too.
How long does the replica keep the rows, then?
Say the DELETE commits on the primary at 0 milliseconds.
Its entry reaches the replica after the replication lag, about 50 milliseconds.
So at 50 milliseconds, the replica deletes every row too.
A read from the replica at 100 milliseconds finds 0 rows.
So what does a replica protect Simplified from?
If the primary dies, it sends nothing more, so the replica keeps its copy of every row.
But a bad write isn't a dead machine: the primary sends it on, like any change.
So the replica makes the same mistake, about 50 milliseconds later.
A replica saves the rows from a dead machine, but never from a bad write.
Simplified's primary and its replica both hold all 2,000,000 rows of users, and the replication lag is about 50 milliseconds. At 0 milliseconds, DELETE FROM users;, with no WHERE, commits on the primary.
How many rows of users does the replica hold at 100 milliseconds?
Explanation
First, the primary sends the DELETE's entry on to the replica, like every other change.
Then, it arrives after about 50 milliseconds, before 100, so the replica deletes every row too.
Therefore, the replica holds 0 rows of users.
Simplified's primary has a replica, and the replication lag is about 50 milliseconds. At 0 milliseconds, DROP TABLE users; commits on the primary. DROP TABLE deletes a whole table: every row, and the table itself.
What does the replica have at 1 second?
Say Simplified runs a primary and 3 replicas, each holding all 400,000,000 rows of attempts, with a replication lag of about 50 milliseconds. At 0 milliseconds, DELETE FROM attempts;, with no WHERE, commits on the primary.
At 1 second, how many of the 3 replicas still hold any rows of attempts?
Simplified keeps learners' rows in users, on a primary with one replica.
Which of these does the replica keep those rows safe from?
Say Simplified's server sends DELETE FROM users;, with no WHERE, to its primary, and the primary deletes every row. About 50 milliseconds later, the primary's replica has deleted every row of users too.
Why?
Simplified's primary and its replica both have Ada at 120 XP, and the replication lag is about 50 milliseconds. At 0 milliseconds, UPDATE users SET xp = 0;, with no WHERE, commits on the primary. At 20 milliseconds, a read from the replica asks for Ada's XP.
What XP does it get?
Simplified's primary has one replica. Say Simplified wants learners' rows to survive a DELETE FROM users; with no WHERE.
Would adding a second replica do that?
Databases · Lesson 4.3
Getting yesterday's rows back
So when a DELETE FROM users with no WHERE commits, the primary sends it on.
About 50 milliseconds later, the replica has deleted every row too.
So where could Simplified get its learners' rows back from?
It needs a copy that no later change can reach.
Say the company that runs Simplified's database, its host, copies all of it every night at 02:00.
It keeps that copy apart from the database, so nothing sent to the database reaches it.
That copy is called a backup.
A backup is a copy of the whole database, taken at one moment and kept apart from it.
The company that hosts your database usually takes one for you, every day.
Supabase, for one, does on its paid plans.
So what happens to the backup if that DELETE comes at 14:00?
The DELETE reaches the primary, and the primary sends it on to the replica.
But the backup is kept apart, so nothing is sent to it.
So it still holds all 2,000,000 learners' rows, as they were at 02:00.
But how does Simplified get those rows back?
It restores the backup.
Restoring a backup brings the database back to the moment that backup was taken.
So every learner's row comes back, as it was at 02:00.
But everything written after 02:00 is lost.
Say Ada finished a lesson at 10:00, and earned 10 XP.
That came after the 02:00 backup, so the backup doesn't have it.
So after the restore, Ada's 10 XP from that lesson is gone too.
From 02:00 to 14:00 is 12 hours, so every write in those 12 hours is lost.
Now what does restoring an iCloud backup bring back on a new iPhone?
Say your iPhone backed itself up to iCloud last night, while it charged.
This morning, you changed your wallpaper.
Then you drop your iPhone in the pool, and on a new one you pick "From iCloud Backup" in setup.
The new iPhone comes back as your old one was last night, with last night's wallpaper.
This morning's wallpaper came after the backup, so it isn't there.
Restoring a backup brings back one moment, and loses every change made since.
Learn more: how a host like Supabase handles backups.
Simplified's host backs up its database every day at 02:00. Say at 09:00, a mistake runs DROP TABLE attempts;, and Simplified restores that morning's 02:00 backup.
How many hours of writes are lost?
Explanation
First, restoring brings the database back to the moment of the backup, 02:00.
Then, every write from 02:00 to 09:00 is lost: 9 − 2 = 7 hours.
Therefore, 7 hours of writes are lost.
Simplified's host backs up its database every day at 02:00. Say at 01:00, a mistake sends DELETE FROM users; with no WHERE, and Simplified restores its latest backup, the one from 02:00 the day before.
How many hours of writes are lost?
Simplified's host backed up its database at 02:00. At 11:00, a new learner, Gus, signs up from the USA, and Simplified adds his row to users. At 14:00, a mistake sends DELETE FROM users; with no WHERE, and Simplified restores the 02:00 backup.
How many rows does Gus have in users after the restore?
Simplified's server logs in as app, which may change users, and its primary has one replica. Simplified wants a copy of its learners' rows that a DELETE FROM users; with no WHERE can't reach.
Which of these gives it that?
Simplified's host backs up its database every day at 02:00. Ben finishes one lesson at 01:00, another at 06:00 and a third at 12:00. At 14:00, a mistake sends DELETE FROM users; with no WHERE, and Simplified restores that day's 02:00 backup.
Which of Ben's lessons from that day are still saved afterwards?
Say a company hosts your app's Postgres database for you, and its plan includes daily backups.
Who takes your database's daily backup?
Simplified's host took a backup of its database at 02:00. At 14:00, DELETE FROM users; with no WHERE commits on the primary, and its replica deletes every row too.
Why does the 02:00 backup still hold every learner's row?