Two questions from Vallmar's council department had been left unanswered since the first day of this module, and neither of them is fixed with an index.

The first was asked by the culture councillor at the follow-up meeting: "who can look up the phone numbers and email addresses of the twelve thousand members?". Nobody knew how to answer, which is the worst possible answer. The second was asked by the finance officer: "what would happen if the server's disk died tonight?". Somebody said "we have backups", and when asked when the last restore had been tested, silence fell.

This lesson answers both, and closes the module and the course's theoretical block.

There is one idea worth putting up front, because it organizes everything that follows: the database is the last frontier. If somebody gets through the firewall, tricks the application or finds a credential in a repository, the only thing left between that person and the data of twelve thousand Vallmar residents are the database's permissions. And if the building burns down, the only thing left between the library and starting from scratch are the backups —tested, not merely existing—.

We will look at authentication and authorization with roles and GRANT; the principle of least privilege applied to three concrete BiblioRed roles; views and row-level security so that each branch sees only its own; SQL injection and why parameterized queries are the only defense that works; encryption in transit and at rest; auditing; the handling of personal data; and the complete backup block, where the WAL from 06-01 reappears turned into the tool that lets you recover the database at the instant before the disaster.

1. Why database security is different from application security
2. Authentication: roles, passwords and pg_hba.conf
3. Authorization: GRANT, REVOKE and the privilege catalog
4. Least privilege: BiblioRed's three roles
5. The danger of connecting as a superuser
6. Views for hiding sensitive columns
7. Row-level security: each branch sees its own
8. SQL injection: how it happens and how it is prevented
9. Encryption in transit, at rest and the special case of passwords
10. Auditing: access logging and a change table
11. Personal data: minimization, pseudonymization and retention
12. Backups: logical and physical
13. Full, differential and incremental
14. WAL archiving and point-in-time recovery
15. The 3-2-1 rule and verifying your backups
16. RPO and RTO applied to BiblioRed
17. BiblioRed's backup plan, annotated
18. The first minutes after an accidental deletion
19. The minimum equivalent in SQLite

It is common to think that security is solved in the application layer: if only authorized staff see the member listing screen, the data is protected. It is a dangerous idea, and listing the paths that reach the database without going through the application is enough to see it:

Six paths, and five of them never see a single line of the application's code. Hence the principle:

Access controls must be where the data is. The application can add convenience and context, but the guarantee has to live in the database.

It is exactly the same argument we used in 04-04 for constraints and in 06-02 for the reading club's capacity: a rule that cannot be broken must live where it cannot be bypassed. Here the rule is "front desk staff cannot export members' email addresses".

A system's security layers, from the outside in:

The two central questions of this lesson are the third and fourth boxes: authentication (who are you?) and authorization (what can you do?). They are constantly confused and they are different things.

PostgreSQL does not distinguish between "user" and "group": it has roles. A role with the LOGIN attribute can connect; a role without it serves as a permission group. CREATE USER is simply shorthand for CREATE ROLE ... LOGIN.

Attributes worth knowing:

Inspecting what is there:

Before checking the password, PostgreSQL consults the pg_hba.conf file (host-based authentication). It is a list of rules evaluated from top to bottom, and the first one that matches is applied. If none matches, the connection is rejected.

The methods, with an assessment of each:

About trust. It literally means "anyone arriving by this route gets in as whichever user they claim to be, with no password". Its only legitimate use is a local development instance on your own machine, with no real data and no port open to the outside. A line host all all 0.0.0.0/0 trust on a server is equivalent to publishing the database on the internet with no door. It shows up more often than anybody would like to admit, almost always because somebody put it there "just for a moment, to test something" and nobody removed it.

Note the hostssl line for management: it forces the connection to be encrypted. And the last line, reject, turns the list into a deny-by-default policy, which is how every access control list should be written.

After editing the file:

Checking which rules are active:

Once the role is authenticated, the next question is what it can do. PostgreSQL's model is hierarchical: to reach a table you have to pass through the database and the schema.

It is the number one mistake when configuring permissions: granting SELECT on the tables and forgetting USAGE on the schema. Without USAGE, the role cannot even see that the table exists.

Two essential advanced forms:

Column-level privileges. You can authorize reading some columns and not others:

That role will be able to read a member's name but not their email. If it tries:

Default privileges for future objects. A GRANT ON ALL TABLES affects the tables that exist today. The table somebody creates tomorrow will not be included, and that is a classic silent failure:

PostgreSQL has a pseudo-role called PUBLIC that everybody belongs to. Historically, PUBLIC had CREATE on the public schema, which let any user create tables there. Since PostgreSQL 15 that is no longer the case, but on older installations —or migrated ones— it is worth checking and fixing:

Inspecting the privileges granted:

The letters: r = SELECT, a = INSERT, w = UPDATE, d = DELETE, x = REFERENCES, U = USAGE.

Principle of least privilege. Each role receives exactly the permissions it needs for its function, not one more, and only while it needs them.

BiblioRed needs three profiles. We define them first in plain language, because a permission you cannot explain in one sentence is usually badly thought out:

A permission that has not been tested is an assumption. The check is done by impersonating the role:

Two expected errors and one correct query: the permissions do what the document says.

There is also a function to check it without running anything:

It is the most widespread bad practice and the easiest to fix.

When an application connects with a superuser role —postgres in most installations—, all the work of the previous sections is nullified. A superuser bypasses every permission check, including the row-level security of section 7.

What an attacker who gets hold of the application's connection can do:

That last row turns a medium-severity SQL injection into a total compromise of the server.

The checklist:

• The application's connection string never uses postgres or any role with SUPERUSER.
• The tables' owner is an administration role different from the one the application uses.
• Passwords live in a secrets manager or in the service's environment variables, never in the code repository.
• Every person has their own login role. A shared account makes the auditing of section 10 impossible.

Verifying there are no extra superusers:

One. If three or four show up, there is work to do.

The column privileges of section 3 work well, but they have a practical drawback: SELECT * fails, and many reporting tools use it. The elegant alternative is a view.

A view runs with the permissions of whoever created it, not of whoever queries it. That makes it possible to give access to a subset of the data without giving access to the underlying table.

Check:

SELECT * works, and the emails and phone numbers are out of reach.

Other useful views of the same kind in BiblioRed:

A warning about masking: it exists so that support staff can verify an email address a member reads out over the phone, not to anonymize. Masking is not anonymization, and in section 11 we will see the difference.

Technical note: by default views are SECURITY INVOKER as far as the row-level security of the next section is concerned, but they run with the owner's permissions with respect to the tables. If you need the view to apply the querying user's row policies, declare it with WITH (security_invoker = true), available since PostgreSQL 15.

Views hide columns. To hide rows —so that North branch staff do not see South branch loans— PostgreSQL offers row-level security (RLS).

With RLS, each table can carry policies that act as an implicit and mandatory WHERE, applied by the management system to every query from the affected roles.

Careful: with RLS enabled and no policy at all, nobody sees anything. The default behavior is to deny, which is the correct one.

The policy needs to know which branch the user is at. The application communicates it with a session parameter when opening the connection:

A policy's clauses:

Without WITH CHECK, a role could insert rows it then cannot see, which is usually a bug. The complete policy for members:

The same query, the same role, different results: the management system is applying the filter of its own accord. And it is impossible to bypass it from SQL:

1. Superusers and table owners bypass RLS. If the application connects as the tables' owner, the policies are not applied. You have to force it with ALTER TABLE members FORCE ROW LEVEL SECURITY;.
2. It has a performance cost. The policy becomes an extra condition on every query, and policies with subqueries like the one on loans can change the execution plan. Check it with EXPLAIN ANALYZE (06-03).
3. The session parameter must be set reliably by the application and must not be alterable by the end user. If the user can influence app.current_branch, the policy protects nothing.

It is the oldest and best-known vulnerability of database-backed applications, and it still shows up every year in incident lists. Its cause is always the same: mixing code and data in the same string of text.

BiblioRed's catalog search builds the query by concatenating what the user types:

With a normal search, q = map, the resulting query is correct:

Now a visitor types into the search box:

The query that reaches the server is:

The twelve thousand email addresses of Vallmar's members, in the catalog's public search box. With no password, no tools and leaving no more trace than a line in the query log.

And that is just reading. What an injection allows, in general:

That last row explains why sections 4 and 5 are part of the defense against injection: with bibliored_app properly restricted, the same injection cannot read fines, cannot delete anything, and cannot touch the operating system. The damage of an injection is exactly equal to the connection's permissions.

A parameterized query sends the SQL text and the values along separate paths. The management system receives the query's structure first and the data afterwards, so it is impossible for a piece of data to be interpreted as code. There is nothing to escape because there is nothing to mix.

With the same attack, the result is:

The database has searched, literally, for materials whose title contains the string ' UNION SELECT member_id, email FROM members --. There are none. The attack has turned into a search with no results, which is exactly what it should be.

In direct SQL, inside psql or a function, the equivalent is PREPARE:

It is tempting to think it is enough to "strip the quotes". It is not, for five reasons:

A parameterized query has none of these problems because it escapes nothing: it separates the paths.

Sometimes the variable part is the name of a sort column, and that cannot be parameterized. The solution is not to escape, it is to validate against an allowlist:

The user's input never reaches the SQL: it is only used as a key for choosing among fixed values written by you.

None of them replaces parameterization; all of them reduce the damage:

• Least privilege (section 4): make sure the connection cannot read what it has no business reading.
• Input validation: check that an identifier is an integer, that a date is a date.
• Never show the database's error message to the end user. An ERROR: column "members.email" does not exist is a free map of the schema.
• Log SQL errors: a burst of syntax errors from the same address is an attack in progress.

Without encryption, queries and their results travel across the network in the clear. Anybody with access to the link can read the members' email addresses as they are transmitted.

And in pg_hba.conf, hostssl instead of host to force the connection to be encrypted. On the client side:

The sslmode modes, which matter more than they seem:

require encrypts but does not check who it is talking to, so it does not protect against a man in the middle. The correct configuration in production is verify-full.

Checking a connection's status:

Two levels, with different purposes:

Disk encryption is cheap, transparent and should always be on. Column encryption is a surgical tool: in BiblioRed there is nothing that justifies it —no card numbers or health data are stored—, and applying it to email would make searching by email impossible and would require guarding a key whose loss would be equivalent to losing the data.

Rule: always encrypt the disk; encrypt columns only when you can name the exact piece of data, the exact risk and who holds the key.

Members' passwords for the web portal deserve a section of their own because the mistake here is serious and frequent.

A password is not encrypted: it is transformed with a one-way function, deliberately slow and salted. You do not need to be able to recover it; you only need to be able to verify it. That is why serious systems offer "reset your password" and never "remind me of my password".

If the password is wrong, the query returns zero rows. The 12 parameter in gen_salt('bf', 12) is the cost: each unit doubles the computation time, which slows down brute-force attacks without bothering the legitimate user.

An important note: the above applies to the passwords of members in the application. The passwords of PostgreSQL roles are managed by the server itself with SCRAM-SHA-256, and nothing special needs doing beyond using that method in pg_hba.conf.

Authentication, authorization and encryption answer "who can?". Auditing answers "who has done what, and when?", which is the question asked after an incident, and also the one any serious review of personal-data processing demands.

That last point deserves attention: turning on log_statement = 'all' on a database with personal data means members' emails and phone numbers end up written into log files, which often have less protection and more copies than the database itself. It is a clear case of a security measure that creates a privacy problem.

To know who modified which row and when, the standard pattern is an audit table fed by a trigger —the TRIGGERs we introduced in 05-04—.

Let us try it out:

Four design decisions worth understanding:

• SECURITY DEFINER makes the function run with the permissions of whoever created it, so the front desk role can generate audit records without having write permission on the audit table. That is exactly what you want: to be able to write to the log but not to erase it.
• current_user identifies the database role. That is why it matters that each person has their own login role (section 5): with a shared account, this column says nothing useful.
• JSONB for the before and after avoids having to rebuild the audit table every time the members schema changes. It is a legitimate use of jsonb of the kind 04-04 called "controlled escape hatches".
• The cost: auditing doubles the writes and grows a table nobody queries daily. It is applied to the tables with sensitive data —members, fines, payments—, not to all of them.

And a final warning: the audit table also contains personal data, including data that has been deleted from the original table. It needs the same restrictive permissions and the same retention policy as the data it audits.

⚠️ Important warning. What follows describes technical mechanisms for handling personal data in a database. It is not legal advice. Compliance with the General Data Protection Regulation (GDPR) and with the applicable legislation in each jurisdiction —including the specific obligations of a public administration such as Vallmar town council— must be reviewed by a compliance professional or the organization's data protection officer. Decisions about what data may be collected, on what legal basis, for how long and with what measures, are legal and organizational decisions, not technical ones. This lesson teaches how to implement what is decided, not what should be decided.

That said, there are four techniques every database professional should know.

The most effective measure is not having the data.

Going over BiblioRed's schema with that question in mind:

Every piece of data that is not there cannot leak, does not have to be encrypted, does not have to be audited and does not have to be deleted.

Pseudonymizing means replacing the direct identifiers with a reference that does not identify anybody on its own, while keeping the mapping somewhere else and with different protections. It is reversible with the additional information.

In BiblioRed, the usage statistics table does not need to know who each member is:

The key is the salt (app.stats_salt): without it, a sha256 of a numeric identifier is inverted by trying the 12,000 possible values in less than a second. A pseudonym without a salt is not a pseudonym.

Anonymizing means transforming the data so that it is no longer possible to identify the person, not even with additional information. Unlike pseudonymization, it is irreversible.

The practical case is the development environment. Copying the production database onto a developer's laptop —which is what half of all organizations do— means distributing the data of twelve thousand residents across uncontrolled machines, with no guaranteed disk encryption and no auditing.

An anonymization script for BiblioRed's test environment:

Three rules for an anonymization to be worth anything:

1. It runs as part of the restore process, automatically, never by hand. A manual step will be forgotten.
2. The shape of the data is preserved —lengths, distributions, volume— or the test environment will stop resembling production and the execution plans of 06-03 will not be comparable.
3. You check that no identifiable data is left in secondary tables: auditing, logs, event_reports, free-text fields where somebody noted "call the son's number 6XX".

That last point is the one that fails most often. Free-text comment fields are a well of personal data that no column-based anonymization detects.

Data kept forever is a risk kept forever.

Notice that the loan is not deleted: it is unlinked from the member. The library keeps its circulation statistics —how many times each material was lent, at which branch, in which month—, which is what it needs for its management, and stops keeping who read what. It is the kind of solution that technology can indeed contribute to a retention decision.

This does require, of course, that loans.member_id accept nulls and that the foreign keys allow it, which is a design decision worth taking beforehand, in module 4, and not when the retention policy arrives.

We move on to the council department's second question. Let us start with the statement that organizes the whole block:

An untested backup is not a backup: it is a hope.

PostgreSQL offers two families of backup, and they do not compete: they complement each other.

It generates a file with the statements needed to rebuild the data: CREATE TABLE, COPY, CREATE INDEX.

On success, pg_dump prints nothing: it only speaks up if there are problems. To see the progress on large databases use the -v option.

Restoring:

Important: pg_dump does not copy the roles or the passwords, which are global to the server. They are needed separately:

Forgetting this file is a classic: the data is restored and nobody can get in because no role exists.

It copies the files of the data directory as they are, at block level.

The correct answer for BiblioRed is "both", and section 17 spells out the plan.

The classic classification, applicable to any backup system:

The trade-off is always the same: space and backup time against restore time and complexity. And there is a factor that decides more than the numbers:

The more files a restore needs, the more likely it is that one will fail. An incremental chain of thirty links breaks if number seventeen is missing.

In PostgreSQL, the practical translation of this scheme is:

• The full one is the pg_basebackup.
• The role of the incrementals is played by WAL archiving, which is continuous rather than periodic. And it is a better solution than classic incrementals, because it allows you to restore not just to the moment of a backup, but to any instant.

(PostgreSQL 17 also added native incremental backups with pg_basebackup --incremental, useful on very large databases; WAL archiving is still the basis of the scheme.)

Here the write-ahead log from lesson 06-01 reappears, and its second life is as important as the first.

You will remember the mechanism: before modifying a data page, PostgreSQL writes an entry into the WAL describing the change. That log therefore contains the complete history of every modification since the moment the base backup was taken.

WAL archiving. If we keep a base backup and all the WAL segments generated since then, we can rebuild the database at any instant after the base backup, by applying the log up to the desired point. This is point-in-time recovery (PITR).

That archive_timeout deserves attention because it is what sets the worst case: without it, a half-filled 16 MB segment might not be archived for hours, and that work would be lost. With 300 seconds, the maximum loss is bounded to five minutes.

And an important operational warning: if archive_command fails, PostgreSQL keeps the segments and the disk fills up. An archive_command pointing at an unreachable destination is a sure way to bring the server down within hours. Keep an eye on it:

failed_count = 0 and a recent last_archived_time: archiving is working. These two columns should be on the monitoring dashboard.

A real scenario: at 11:47 somebody runs the following in production, believing they are in the test environment:

At 11:52 it is spotted. The steps:

In the server log:

Notice the decisive line: recovery stopping before commit of transaction 90412. That transaction 90412 is the DELETE. Recovery stops just before committing it.

The twelve thousand members are back. Only the operations of those five minutes between 11:47 and 11:52 have been lost, and they are rebuilt by hand from the directory set aside in step 2 and from the front desk's paper receipts.

Other possible recovery targets, besides recovery_target_time:

That last one is pure gold before a schema migration:

Doing all this by hand is feasible, but in production people use tools that manage retention, verification and a backup catalog: pgBackRest, Barman and WAL-G are the three usual ones. They all implement the same underlying mechanism we have just seen. Knowing the mechanism is what lets you understand the tool, and not the other way round.

3 copies of the data · on 2 different types of media · with 1 copy off site.

Applied to BiblioRed:

Modern additions to the rule, which experience with ransomware has made essential:

• 1 immutable copy: storage that does not allow modification or deletion for a set period. Without this, an attacker with access to the server encrypts or deletes the backups too, which is exactly what happens in ransomware incidents.
• 0 errors in verification: no backup counts until it has been restored successfully.

This is the point where most organizations fail. The backup process is configured, it is seen to produce files, and nobody ever restores until the day of the disaster, which is the worst moment to discover that the file was truncated, that the roles were missing, or that the process had been backing up an empty database for seven months.

An automatic verification script, run weekly:

The volume and date checks are the heart of the script. A backup that restores without errors but contains an empty database restores perfectly, and is worth nothing. The classic mistake —backing up the wrong database, or one that stopped being used— is only caught by counting rows.

And once a year, a full drill: restore on a different server, start the application against the restored copy, and time how long it takes from the phone call to the service being up. That stopwatch is the real RTO, and it is almost always three times the one that had been estimated.

Two acronyms that organize any conversation about continuity, because they turn "we want to be safe" into two numbers that can be designed and budgeted for.

Applied to BiblioRed's operations:

The technical decisions come straight out of the table:

Notice the logic: first you decide how much can be lost and how long you can wait; then you choose the technology. Doing it the other way round —building the infrastructure and seeing what RPO comes out— is like designing the schema without knowing the domain.

With everything above, this is the concrete plan:

And the decisions behind it, one by one:

Why both logical and physical backups. The physical one gives the low RTO (restoring is copying files) and is the only possible basis for PITR. The logical one allows restoring a single table —which is what is needed 90% of the time, because the typical disaster is not a dead disk, it is a badly written DELETE— and is the only one that works for migrating to a different major version.

Why the WAL is retained for 35 days and the weekly base for 8 weeks. The WAL is only useful if the corresponding base backup exists. Retaining 35 days of WAL with no bases older than 35 days would be useless; conversely, retaining bases without their WAL prevents PITR. WAL retention must cover, with a margin, that of the oldest base backup you want to use for PITR.

Why an immutable copy. Because an attacker who compromises the server and has the backup credentials will delete or encrypt the backups too. The monthly immutable copy is the last resort, and its one-month RPO is terrible but infinitely better than nothing.

Why verification is weekly and not monthly. Because the typical failure of a backup process is silent —a permission that changed, a full disk, a path that stopped existing— and one week is the maximum acceptable time to detect it.

Why an annual drill, if there is already weekly verification. Because they are different things. Verification checks that the backup file is good; the drill checks that the procedure works with people involved: that somebody knows where the document is, that the external storage credentials are still valid, that the person who wrote it is not the only one who knows how to do it, and how long it really takes.

What is missing from this plan. It is worth saying too: there is no standby replica. With the 2-hour RTO of section 16 it is not essential, but it is the first investment that would be needed if the library decided to lower that objective. The primary-standby replication of 03-01 is exactly that piece.

The moment when most damage is done is the one immediately following the mistake, when somebody tries to fix it in a hurry. This is the procedure, and it is worth having printed out.

Minute 0 — Stop. Do not run anything else. Do not try to "insert the data back". Every subsequent write complicates the reconciliation and may overwrite recoverable information.

Minute 1 — Is the transaction still open? If the DELETE was run inside an uncommitted BEGIN, the solution is a ROLLBACK and that is that (06-01). Check who has what open:

There it is: the transaction is still open. A ROLLBACK in that session resolves the whole incident. It is worth checking this before anything else.

Minute 2 — Isolate. If it is already committed, prevent new writes while a decision is made:

Minute 3 — Determine the exact scope. Which table, how many rows, at what time, with which user. The audit table from section 10 answers all of this:

Minute 5 — Decide the recovery route:

The audit-table option, when it applies, is the most surgical:

Minute 30 — Afterwards. Restore the permissions, check consistency with the control queries from 05-03, and write down what happened. The post-incident report is not looking for culprits: it is looking for why it was possible. Almost always the answer is one of these three: the connection had more permissions than it needed, there was no way to tell the production environment from the test one, or there was no procedure for bulk operations.

Three preventive measures come out of that:

To close, the reduced version of the same problem.

SQLite has no users, no roles and no GRANT: the permissions are those of the file in the operating system. Whoever can read the file reads everything; whoever can write it writes everything. The whole first half of this lesson has no equivalent, and that is an important reason not to use SQLite in a multi-user system with personal data.

Backups do have an equivalent, and it is simple:

And the central warning:

Copying the .db file with cp while the database is in use produces a corrupt backup, because it may capture the file halfway through a transaction, and in WAL mode it leaves out the -wal file with the recent changes. Copying the file directly is only safe with the database closed and no connection open.

.backup is safe hot: it uses SQLite's internal backup mechanism, which guarantees a consistent image. It is a one-line difference that separates a valid backup from a useless one.

Connecting the application as a superuser. It nullifies the entire permission setup and turns any injection into a compromise of the server. It is the most serious mistake and the easiest to fix.

Granting SELECT on the tables and forgetting USAGE on the schema. The role will see nothing and you will get a confusing error report. It is the number one failure when configuring permissions for the first time.

Using GRANT ... ON ALL TABLES and believing it covers the future. It only affects existing tables. Without ALTER DEFAULT PRIVILEGES, the table created tomorrow will be inaccessible or —worse, depending on the setup— accessible to the wrong people.

Leaving trust in pg_hba.conf. Even "just for a moment, to test something". That moment lasts three years.

Using sslmode=require and believing it is secure. It encrypts, but it does not verify who it is talking to. Use verify-full.

Escaping quotes by hand instead of parameterizing. One forgotten place out of two hundred is enough. Parameterized queries do not have that failure mode.

Showing the database's error to the end user. It gives away the schema. Log the full error internally and show a generic message.

Copying the production database onto a development laptop without anonymizing. It is the most common data leak and the one least perceived as such. Anonymize as an automatic part of the restore process.

Turning on log_statement = 'all' on a database with personal data. Emails and phone numbers end up in log files with less protection and more copies than the database.

Having backups and never having restored one. It is this lesson's mistake. An untested backup is not a backup. Verify weekly and automatically, and run a full drill once a year.

Not backing up the roles. pg_dump does not include them. Without pg_dumpall --globals-only, you will restore the data and be unable to get in.

Not watching pg_stat_archiver. If archive_command fails, the WAL piles up, the disk fills and the server stops. failed_count belongs on a dashboard.

Confusing masking with anonymizing. m***@example.org is still personal data in a record that contains the name and the branch. Anonymizing means replacing, not covering up.

Final tip: write the recovery procedure down and keep it outside the system it protects. A restore document stored only on the server that has burned down is a joke that is only funny before the fire. On paper, in the external storage, and with the necessary credentials held by at least two people.

Vallmar's culture department hires an external firm to analyze library usage over six months. The firm needs: to read the loans, copies, materials, branches and events; it must not be able to identify any member; it must not be able to write anything; and its access must expire automatically on 31 December 2026.

Write the complete SQL: the role, the permissions, any view you need, and the check that it cannot reach the personal data.

A technician has left this configuration on BiblioRed's production server. Identify all the security problems, ordered by severity, and write the fix for each one.

Vallmar's library network adds a musical instrument lending service, with its own instruments database. Its characteristics: 400 instruments, 900 users, around 30 operations a day, and the deposit amounts (up to €300 per instrument) are recorded in a deposits table.

Define the RPO and the RTO of each type of operation, justify them, and write the backup plan that follows from them. Explain how it differs from BiblioRed's plan in section 17 and why.

Solution 1

And in pg_hba.conf, restricting the origin and forcing TLS:

Check:

Key points of the solution:

• loans is revoked and access is given only to the view. Granting SELECT on loans would have handed over member_id in the clear, which combined with the dates allows specific people to be re-identified with little effort.
• VALID UNTIL makes the access expire on its own. Trusting somebody to remember to revoke it in December is trusting too much.
• CONNECTION LIMIT 3 stops a badly configured analytics tool from opening two hundred connections and affecting the front desk service.
• hostssl with the specific address limits access to the consultancy's network and forces encryption.
• Compliance note: the pseudonym makes re-identification harder, but a data set with dates, branch and materials may still be re-identifiable by combination. Whether this treatment is sufficient and on what legal basis it is disclosed to a third party is a question the council's data protection officer must validate, not the technical team.

Solution 2

Problem 1 (critical) — SUPERUSER on the application's connection.

It nullifies every permission and every row policy, and turns any injection into operating system command execution through COPY ... FROM PROGRAM.

Problem 2 (critical) — GRANT ALL ... TO PUBLIC.

PUBLIC includes every present and future role. Anybody who manages to connect has full control over every table, including DELETE and TRUNCATE.

Problem 3 (critical) — SQL injection by concatenation.

Besides, the SELECT * is removed: the query should ask only for the columns it uses, which avoids exposing email by accident and helps the Index Only Scan of 06-03.

Problem 4 (critical) — local all all trust.

Any operating system user on the server gets in as any role, including postgres, with no password.

Problem 5 (serious) — host all all 0.0.0.0/0.

The database accepts connections from anywhere on the internet, to any database and with any user.

Problem 6 (serious) — md5 instead of scram-sha-256.

A method with known weaknesses. The method is changed and the passwords are reset, because changing the method does not convert the existing ones on its own:

Problem 7 (serious) — No TLS.

Every line was host, not hostssl: the traffic travels in the clear. Fixed in problem 5, plus ssl = on in postgresql.conf.

Problem 8 (moderate) — A weak and predictable password.

bibliored2026 is guessed on the first attempt of a targeted attack. Use a long randomly generated password, stored in a secrets manager, never in the code repository, with planned rotation.

Recommended order of intervention: first close down pg_hba.conf (problems 4, 5, 6 and 7), which stops the improper access immediately; then remove SUPERUSER and PUBLIC (1 and 2), which limit the damage; and in parallel fix the code (3), which requires a deployment. And as soon as the urgent part is closed: review the logs to determine whether this configuration was exploited, which is an incident that must be reported through the organization's channels.

Solution 3

RPO and RTO per operation:

Resulting backup plan:

Differences from BiblioRed's plan and why:

1. archive_timeout = 600 instead of 300. With 30 operations a day, one WAL segment every 10 minutes is more than enough and generates far fewer files to archive and retain. The effective 10-minute RPO is still better than the 1-hour requirement for lending.

2. Logical backup retention of 30 days instead of 14. The database is tiny —900 users and 400 instruments—, so 30 compressed daily backups take up a few megabytes. When space is not a constraint, you retain more, because the error that takes longest to detect is silent logical corruption: somebody modified some data badly three weeks ago and nobody noticed.

3. A more relaxed RTO (4-8 hours against 2). The instrument service is not critical to the libraries' daily operation and can run on paper for a working day. That makes any investment in a standby replica unnecessary.

4. The RPO of 0 on deposits does not force a synchronous replica here. Unlike BiblioRed's fine payments —which arrive by card reader and leave no receipt at the library—, an instrument deposit is collected with a paper receipt signed by the user. That receipt is the RPO 0 fallback, and it is far cheaper than the equivalent infrastructure. It is a good example that a continuity requirement is not always solved with technology.

5. The same verification and drill regime. This point is not relaxed because of the database's size. The probability of a backup process failing silently does not depend on how many rows there are, and a small database usually has less supervision, not more. It is exactly where automatic verification is most needed.

The two questions from Vallmar's council department now have answers, and both are concrete answers.

Who can look up members' phone numbers and email addresses? The bibliored_app role, because the web application needs to send the due-date notices, and nobody else. Front desk staff can modify an email address but not read it in a listing. Management accesses v_members_public, where those columns do not exist. The external consultancy works on v_loans_analytics with salted pseudonyms. And every change to members is recorded in members_audit with the role, the originating address and the previous value. The answer is no longer "I don't know": it is a GRANT script that can be read, checked with has_column_privilege and audited.

What would happen if the disk died tonight? The latest pg_basebackup would be restored, the archived WAL would be applied up to the last available segment, and the service would come back with a maximum loss of five minutes and in under two hours. And that statement is not a hope: it is a written procedure, verified automatically every Monday with a script that counts rows and checks dates, and rehearsed in full once a year with a stopwatch.

Along the way we have gone through the complete layers. Authentication, with roles that are both users and groups, scram-sha-256 as the method and that pg_hba.conf that is read from top to bottom and must end in reject. Authorization, with its hierarchy of database, schema and table —and the USAGE everybody forgets—, column-level privileges, ALTER DEFAULT PRIVILEGES for objects that do not exist yet, and the principle of least privilege turned into three concrete, testable roles. Views for hiding columns and row-level security for hiding rows, with the warning that neither protects against a superuser. SQL injection, with the catalog search returning twelve thousand email addresses and the parameterized query turning the same attack into a search with no results, because it escapes nothing: it separates the paths. Encryption in transit with verify-full and not with require, at rest at the disk level, and passwords that are not encrypted but derived with a salt and a cost. Auditing with SECURITY DEFINER, which allows writing to the log without being able to erase it. And personal data, with minimization being the only measure that cannot fail, pseudonymization that without a salt is not pseudonymization, anonymization that must be an automatic step of the restore process, and the reminder —worth repeating— that these are technical mechanisms and that regulatory compliance is decided by a compliance professional, not by the development team.

And the backup block, where the WAL from 06-01 has had its second life: what in that lesson was the mechanism guaranteeing a COMMIT's durability against a power cut has here become the complete history of modifications that lets you stop recovery just before transaction 90412, the one that deleted twelve thousand members at 11:47:03. We have seen logical and physical backups with their different and complementary roles, the 3-2-1 rule with its modern additions of immutability, RPO and RTO as the two numbers that turn "we want to be safe" into engineering decisions, a backup plan annotated decision by decision, and the first-minutes procedure, which starts with the most counterintuitive and most effective thing of all: stop and check whether the transaction is still open.

With this, module 6 closes and, with it, the course's theoretical block. It is worth looking back at the whole journey. We started in module 1 with an overflowing spreadsheet and opened up the DBMS to see its pieces from the inside. In module 2 we learned the relational model and SQL as far as aggregates and referential integrity. In module 3 we left the relational world to understand NoSQL, the CAP theorem and eventual consistency, and came back knowing what is gained and what is paid on each side. In module 4 we designed BiblioRed's schema from the entity-relationship diagram to the last CHECK. In module 5 we put it through a formal examination, found a real flaw in fines and justified every denormalization in writing. And in module 6 we stopped looking at the schema and started looking at the system in operation: the transactions that happen in full or do not happen at all, the concurrency that breaks correct code as soon as there are two people, the indexes that turned fourteen seconds into forty milliseconds, and the security and backups that separate a database from an accident waiting to happen.

What is left of the course is no longer new theory: it is doing it with your hands. Module 7, Practical Exercises, is where all of the above turns into skill, and it is organized along the same journey: SQL exercises over the BiblioRed schema you now know inside out (07-01); schema design exercises, with new domains you will have to model from scratch (07-02); normalization exercises, with tables hiding functional dependencies you now know how to spot (07-03); and advanced query and transaction exercises, where the two parallel psql sessions, the isolation levels and the execution plans of this module will come back (07-04). After that, module 8 will go through three complete case studies —relational, non-relational and polyglot persistence— and module 9 will gather the books, courses and tools to carry on with on your own. Open the two terminals, keep the BiblioRed schema to hand, and see you in the first exercise.