Part V — Transactions, Concurrency, and Performance

Chapter 20. Database Security and Integrity

Chapter 1 introduced security as one of the seven file-processing problems databases were invented to fix, and every chapter since has quietly built toward this one: constraints enforce integrity, views hide columns, least privilege has been practiced since Chapter 14's first account. This chapter assembles the discipline: authentication (who are you?), authorization (what may you do?), and the surrounding practice — SQL injection, password and connection security, auditing — that separates a database deployment from a database liability.

Security is also where the application-tier lessons of Chapters 23–24 get their policy: everything granted or withheld here is what attackers and buggy programs actually hit.

After studying this chapter you will be able to:

• Distinguish authentication from authorization and trace each to its mechanisms.
• Manage users and roles on both platforms, including group modeling.
• Grant and revoke privileges at object, database, and column levels.
• Apply role-based access control, including MySQL 8.0's roles.
• Use schema permissions and row-level security.
• Enforce integrity as defense in depth, in the database.
• Explain and prevent SQL injection, with parameterization as the standing fix.
• Manage passwords and secure connections (TLS).
• Audit and log for accountability.
• Apply the platform security checklist end to end.

20.1 Database authentication and authorization

Authentication proves who is connecting; authorization decides what they may do. The split is the whole architecture: authentication happens once, at connect time (Chapter 14's pg_hba.conf and user@host discussion); authorization happens on every statement — the privilege check that answers GRANT questions before execution.

The mechanisms by platform. PostgreSQL: the server consults pg_hba.conf per connection — local peer (OS identity), password methods (scram-sha-256, the modern salted challenge), certificates, and rejects methods it does not trust (the ancient md5 survives only for legacy); then the role's privileges govern every action. MySQL: the account is a user@host pair — the server resolves the best-matching account from its grant tables (more specific host patterns win), authenticates via the account's plugin (caching_sha2_password default), and then its privileges — stored per level: global, database, table, column, and routine — govern statements.

Two rules cut through all the mechanism. First, least privilege (Chapter 1): every account gets exactly the access its function needs and nothing more — the portal account reads sections and inserts enrollments, and it never, at any point, needs to read instructor.salary. Second, the application is not a security boundary: Chapter 4's three-tier architecture means attackers can talk SQL to the database through any compromised path; the database's own grants are the layer they cannot talk their way past.

20.2 Users, roles, and privileges

PostgreSQL unifies users and groups as roles. A role with LOGIN is a user; a role without is a group. Role attributes carry administrative powers (SUPERUSER, CREATEDB, CREATEROLE, REPLICATION), and roles may be members of roles — the group pattern:

CREATE ROLE portal_app LOGIN PASSWORD 'change-me';
CREATE ROLE grade_reader NOLOGIN;
GRANT grade_reader TO portal_app;         -- membership: portal inherits its grants

MySQL has users only — until 8.0, when roles (groups of privileges) arrived; Section 20.4 covers them. Inspect who exists and what they hold: PostgreSQL's \du and pg_roles; MySQL's SELECT user, host FROM mysql.user; and SHOW GRANTS FOR 'portal'@'localhost'; — the inventory commands every audit begins with.

The privilege inventory is shared in spirit: connection (CONNECT — PG database level), usage of schema/objects, SELECT/INSERT/UPDATE/DELETE on tables, EXECUTE on functions, and administrative powers (ALTER, CREATE, TRUNCATE, and the platform globals). The professional naming discipline from Chapter 9 extends here: one account per function (portal, report, migration, admin), never per person for applications, and every account documented with its reason.

20.3 GRANT and REVOKE

The grant machinery, in its canonical university shapes:

-- PostgreSQL: object-level, explicit
GRANT SELECT, INSERT ON enrollment TO portal_app;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO grade_reader;
REVOKE INSERT ON enrollment FROM portal_app;

-- MySQL: pattern-level privileges
GRANT SELECT, INSERT ON university.enrollment TO 'portal'@'localhost';
GRANT SELECT ON university.* TO 'report'@'%';
REVOKE INSERT ON university.enrollment FROM 'portal'@'localhost';

The grammar points worth fixing: privileges attach to objects with grantors (the account that ran GRANT — REVOKE removes what a grantor gave); PUBLIC is the everyone-role (grant sparingly, revoke by default); WITH GRANT OPTION lets the grantee grant onward — powerful, audit-sensitive, rarely right for application accounts; and column-level grants exist for the salary-column case study: GRANT SELECT (dept_id, dept_name, building) ON department TO report_user; — the instructor table's salary column simply never appears in this account's world. MySQL's SHOW GRANTS and PostgreSQL's \dp (table privileges) display the effective grants — the audit trail of who may do what.

20.4 Role-based access control

Role-based access control (RBAC) names the pattern every serious deployment converges on: people and programs hold roles, roles hold privileges, and changes to either stay independent. The university, modeled:

-- PostgreSQL: group roles, inherited at login
CREATE ROLE faculty NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO faculty;
CREATE ROLE prof_kabir LOGIN PASSWORD 'change-me';
GRANT faculty TO prof_kabir;         -- membership ⇒ inherited grants

-- MySQL 8.0: roles, activated per session
CREATE ROLE 'faculty';
GRANT SELECT ON university.* TO 'faculty';
CREATE USER 'prof_kabir'@'localhost' IDENTIFIED BY 'change-me';
GRANT 'faculty' TO 'prof_kabir'@'localhost';
SET DEFAULT ROLE 'faculty' TO 'prof_kabir'@'localhost';

The platforms' dialect difference inside one idea: PostgreSQL inherits role grants automatically; MySQL roles are granted but activated per session (SET DEFAULT ROLE makes them automatic at login — the line worth remembering, because MySQL roles silently do nothing until activated). Both models deliver the same administrative payoff: the dean asks for "faculty can now see the grade distribution," and the DBA changes the role's grants — one statement, zero per-user edits, and the audit question "who can read grades" answers with a role list.

20.5 Schema and object permissions

PostgreSQL adds the layer MySQL lacks: schema permissions — GRANT USAGE ON SCHEMA reporting TO report_user (the right to see the schema's objects) plus CREATE (the right to add objects) — the namespace controls of Chapter 15 turned into security. The practical pairing is default privileges: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO grade_reader; — objects created later automatically carry the grant, closing the "new table invisible to the report account" ticket before it exists.

The deepest control in either platform is PostgreSQL's row-level security (RLS) — policies that filter rows per role, enforced in the engine no matter which query asks:

ALTER TABLE enrollment ENABLE ROW LEVEL SECURITY;

CREATE POLICY own_enrollments ON enrollment
    FOR SELECT
    TO student_role
    USING (student_id = current_setting('app.current_student_id')::integer);

A student-role session sees only its own enrollments — every query, every tool, every application, with no WHERE clause to forget. (The application sets app.current_student_id per transaction — Chapter 23's connection discipline.) MySQL has no native RLS; the standard emulation is a security-view per role (a view over the user's rows, granted instead of the table) or triggers — the Chapter 16 pattern, applied to security.

20.6 Data integrity enforcement

Chapter 9 declared the rule — every rule the DBMS can enforce, the DBMS should enforce — and security gives it a second life: constraints are defense in depth. The application that forgets the grade scale is also the attacker's SQL injection attempting to write grade = 'Z': the CHECK constraint rejects both, identically, because the constraint lives below every client. The integrity layers and their security contributions:

• Constraints (keys, FKs, CHECKs, UNIQUE): the always-on floor under every writer, legitimate or not.
• Triggers (Chapter 22): cross-table rules the constraint language cannot state — audit trails, derived invariants, the temporal correctness of Chapter 6's order-line prices.
• Views with CHECK OPTION (Chapter 13): scope enforcement — writes that leave the role's world are refused, not merely hidden.
• RLS (Section 20.5): row-scope enforcement — the deepest of the four, and the newest to most teams.

The design habit that follows: when a new integrity rule appears, ask in order — constraint? trigger? view? RLS? — and only then "application code," because application enforcement is the one layer an attacker or a bug routes around.

20.7 SQL injection and parameterized queries

SQL injection is the classic database attack: input treated as code. The shape — a query built by string concatenation:

query = "SELECT full_name, gpa FROM student
         WHERE full_name = '" + userInput + "'"
userInput = "' OR '1'='1"
query becomes:  ... WHERE full_name = '' OR '1'='1'     → every row returned

Worse spellings append '; DROP TABLE student; -- (older clients allowed stacked statements) or UNION SELECT ... to read other tables — the point is always the same: the attacker's text becomes part of the SQL grammar. SQL injection is perennially in the top of web vulnerability lists precisely because concatenation is so convenient and the fix is so simple.

The fix — parameterized (prepared) statements, Chapter 23's code and this chapter's law:

-- The query structure and the data travel separately:
PREPARE find_student (text) AS
    SELECT full_name, gpa FROM student WHERE full_name = $1;
EXECUTE find_student('Arif Mahmud');

$1 is a parameter slot — the driver sends the structure and the value separately, the value is never parsed as SQL, and ' OR '1'='1' harmlessly is a string no student matches. The three habits that end injection: never concatenate input into SQL (including ORDER BY column names and identifiers — those use allowlists, not parameters); parameterize everything through the drivers/ORMs of Chapter 23; and grant minimally so the injection that slips through a forgotten query meets an account that cannot DROP anything. Injection prevention is layers 1 (parameterize) and 2 (least privilege) — both from this chapter, applied in the next.

20.8 Password management and connection security

Account credentials, done properly. Storage: passwords are stored hashed and salted — PostgreSQL's scram-sha-256 (salted, iterated challenge — set it as the pg_hba method and the default password_encryption); MySQL's caching_sha2_password (salted SHA-256, cached for re-authentication speed). Rotation: ALTER ROLE portal_app PASSWORD '...' / ALTER USER 'portal'@'localhost' IDENTIFIED BY '...'; MySQL adds expiration policies (PASSWORD EXPIRE INTERVAL 90 DAY, FAILED_LOGIN_ATTEMPTS lockout — 8.0's small but real account-hardening layer). Secrets management: passwords live in a vault or secret manager (Chapter 23's configuration section), never in code, never in git, not in ENVIRONMENT.md — Chapter 14's promise, kept here.

Connection security: unencrypted traffic is readable on any network segment it crosses — credentials first (the handshake sends the proof), data always. Both platforms do TLS: PostgreSQL clients declare sslmode (from prefer up to verify-full — certificate-verified, the production setting; the server's hostssl lines in pg_hba.conf require TLS per rule); MySQL's server takes require_secure_transport = ON and accounts can demand it (CREATE USER ... REQUIRE SSL). Production defaults: TLS to the database from everywhere, certificates verified, and the database port exposed on private networks only — the firewall is a layer, not the layer.

20.9 Auditing and logging

Security's third question — what happened? — is answered by logs. Both platforms ship a layered story:

• Connection logging: PostgreSQL's log_connections/log_disconnections (with log_line_prefix carrying user, database, address); MySQL's general log (off by default — expensive; enable surgically) and the error log.
• Statement logging: PostgreSQL's log_statement (none/ddl/mod/all — ddl is the sane default) and the pgAudit extension for full compliance-grade audit (who ran what, on which object); MySQL's audit plugin family (enterprise) or the general/slow logs as the open-source floor.
• What to log for a compliance question — "who read the Fall 2026 grades?" — needs object-level audit: pgaudit's pgaudit.log = 'read' scoped to the role, or MySQL's audit plugin filters. The design decision is upstream: decide the questions first (who saw grades, who changed budgets), then configure the logging that answers them.
• Protecting the logs: append-only destinations, shipping off-host (syslog for PostgreSQL; MySQL 8's log sinks), restricted read access — an audit log the administrator can edit is testimony from the defendant.

20.10 Security practices for PostgreSQL and MySQL

The consolidated checklist — the chapter as an audit pass over any deployment:

1. Accounts: one per function; no shared accounts; applications on their own least-privilege accounts; postgres/root for administration only, never in application config (Chapter 14's rule, now audited).
2. Privileges: the grant inventory reviewed (SHOW GRANTS / \dp); PUBLIC revoked by default; no WITH GRANT OPTION on application accounts; column-level grants hiding salary-class columns; RLS or security-views where row scope is the requirement.
3. Authentication: scram-sha-256 / caching_sha2 everywhere; pg_hba's method column up to date (no md5/trust — trust means no authentication, and its every appearance is a finding); MySQL accounts existing only for the hosts that need them, localhost-scoped where possible.
4. Network: TLS with verification (verify-full / REQUIRE SSL); the port unreachable from the public internet; per-application firewall rules.
5. Code: parameterized queries only (Chapter 23's drivers enforce it); identifiers through allowlists; error messages that never echo SQL or stack traces to clients.
6. Integrity: constraints, triggers, CHECK OPTION views as the always-on floor (Section 20.6).
7. Audit: the questions chosen, the logging configured to answer them, the logs shipped and protected.
8. Recovery: backups exist, are tested, and their restoration is itself access-controlled (Chapter 21 — an unprotected backup is the database wearing no armor at all).

The mindset is Chapter 1's, grown up: centralize the value, enforce at every layer, assume some other layer will fail — because the security posture that matters is the one that holds when it does.

Chapter Summary

• Authentication proves identity at connect time (pg_hba methods, user@host + plugins); authorization checks privileges on every statement.
• PostgreSQL roles unify users and groups with inheritance; MySQL has user@host accounts plus 8.0's activated roles; inventories are \du / SHOW GRANTS.
• GRANT/REVOKE attach privileges to objects (PG) and object patterns (MySQL), with PUBLIC, WITH GRANT OPTION, and column-level grants as the sharp tools.
• RBAC: group roles hold privileges, people/programs hold roles; PG inherits, MySQL activates (SET DEFAULT ROLE) — the dialect difference inside one pattern.
• PG adds schema permissions, default privileges, and row-level security (policies enforced under every query); MySQL emulates row scope with security views and triggers.
• Integrity is defense in depth: constraints, triggers, CHECK OPTION views, RLS — each a layer attackers cannot talk their way past.
• SQL injection is input-as-code; parameterized statements separate structure from data and are the standing fix, with least privilege as the blast-radius limiter.
• Passwords hashed (scram/caching_sha2), rotated, vault-stored; connections TLS-verified; ports private.
• Auditing answers "what happened" — pgaudit and MySQL audit plugins for object-level questions, logs shipped and protected.
• The checklist audits accounts, grants, methods, network, code, integrity, audit, and recovery together — layers, because some layer will fail.

Key Terms

Laboratory Exercises

1. Account inventory: list every role/user on both platforms (\du, SELECT user, host FROM mysql.user), map each to a function, and flag any shared or superuser-in-application accounts. Expected result: an inventory table with one finding per anomaly — ideally none after Chapter 14's discipline.
2. Grant matrix drill: create grade_reader, grant SELECT on all public tables, then narrow it to column-level on instructor (no salary); verify with a session as that role: SELECT * FROM instructor fails on permissions; the named-column SELECT succeeds. Expected results: the role sees the four permitted instructor columns; salary access refused by the column grant.
3. RBAC: build the faculty/student/app role structure of Section 20.4 on both platforms (MySQL with SET DEFAULT ROLE); confirm inheritance (PG) and activation (MySQL) with SHOW GRANTS / \dp before and after. Expected result: one grant change on the role visibly re-scopes every member on both platforms.
4. Row-level security (PostgreSQL): enable and policy enrollment as Section 20.5; connect as a student-role session with app.current_student_id set to 21300003 and list enrollments — then as 21600001. Expected results: Arif sees his three enrollments; Zara sees only section 13 — every query, enforced below the client.
5. Injection, produced and cured (dev only): with a concatenated query in a script or GUI, run the search for ' OR '1'='1 and observe the 12-row leak; then the parameterized form and observe zero rows; write the two-line lesson in your notes. Expected results: 12 rows versus 0 rows — the same input, harmless only under parameterization.
6. TLS and audit: verify your connections' encryption (sslmode in the connection string; \s / SHOW STATUS LIKE 'Ssl%'), enable connection logging on PostgreSQL, connect and disconnect as two different roles, and read the log entries. Expected results: encrypted sessions confirmed; two logged connections with user and database recorded.

Review Questions and Exercises

1. Split "the portal may insert enrollments" into its authentication and authorization halves. Authentication: the portal account proves itself at connect time (password method); authorization: its INSERT privilege on enrollment is checked per statement.
2. What is the difference between a PostgreSQL role and a MySQL role, in one sentence each? PG: one identity type covering users and groups, grants inherited through membership; MySQL: a named privilege bundle granted to users and activated per session (SET DEFAULT ROLE makes it automatic).
3. Why is trust in pg_hba.conf a finding in any audit? It disables authentication — any local (or matching-network) user connects as any role, including postgres.
4. Write the grant that lets the report account read the department table except its budget. *GRANT SELECT (dept_id, dept_name, building) ON department TO report_user; — the column-level grant.*
5. What does WITH GRANT OPTION cost, and when is it justified? The grantee can re-grant, multiplying privilege paths outside your inventory; justified for delegated administrators with their own audit trail — not for applications.
6. How does PostgreSQL's RLS enforce what a view cannot? Policies apply below every query and tool, at the engine — views only filter queries that use the view; RLS covers all access to the table.
7. Name the four integrity-as-security layers in order and one rule each enforces. Constraints — grade scale; triggers — cross-table invariants/audit; CHECK OPTION views — writes stay in scope; RLS — row scope.
8. Explain why ' OR '1'='1 returns every row under concatenation, and what it returns under a parameter. It closes the string literal and adds a tautology to the WHERE clause; as a parameter it is simply a no-match string — zero rows.
9. Why do identifiers (table names, ORDER BY columns) need allowlists rather than parameters? Parameters are values, not grammar — an identifier must be part of the statement structure, so it must come from your own permitted list.
10. Why is verify-full the right sslmode and prefer not? prefer still allows unverified/fallback connections; verify-full requires TLS with a verified certificate — no silent downgrade, no impersonated server.
11. Which audit question requires object-level tooling, and what answers it on each platform? "Who read X?" — value-level logging; pgaudit's read logging scoped to roles; MySQL's audit plugin family.
12. Give three findings a security checklist pass might raise on a default Chapter 14 deployment. Application configured with superuser; PUBLIC retains defaults; connection logging off; sslmode left at prefer; accounts with % hosts — any three, matched to checklist items.

Mini-Project

Conduct and document a security audit of your own laboratory. Deliver SECURITY.md with: (1) the account inventory (every role/user, function, authentication method, host scope — the two platforms' inventories side by side); (2) the privilege matrix — functions × objects, with column-level and RLS row-scope rows included; (3) the hardening pass — every checklist item of Section 20.10 applied or explicitly justified as not applicable, with the commands that applied it; (4) the injection demonstration — the vulnerable script (marked dev-only), its leak produced, and the parameterized cure, in the platform's driver of your choice from Chapter 23; (5) the audit plan — three real questions ("who read Fall 2026 grades", "who changed a budget", "who connected from outside the lab subnet") and the logging configuration that answers each, with a sample log line as evidence; (6) the incident runbook stub — for "suspected compromise," the first five actions in order. This document is Chapter 30's security-testing section, written early, and the habit it builds is the chapter's real deliverable: security as a pass you can run, not a feeling you have.