Study notes · 6% of the exam

Security, Integrity and Schema Design

Keep data correct and private: parameterize every value, grant the least privilege, enforce tenant isolation with row-level security, and design schemas so each fact is stored once and constrained by the database.

Key points

1
Bind parameters stop SQL injection for values; identifiers (table and column names) cannot be parameters, so allow-list them or quote them with format('%I') / quote_ident().
2
Privileges are layered: CONNECT on the database, USAGE on the schema, then table or column privileges. Column-level SELECT/UPDATE grants hide or protect individual fields.
3
Row-level security is default-deny once enabled. Superusers, BYPASSRLS roles and the table owner (unless FORCE ROW LEVEL SECURITY) bypass it; a USING clause also checks new rows when there is no WITH CHECK.
4
SECURITY DEFINER functions run with the owner's rights, so set search_path = public, pg_temp and revoke EXECUTE from PUBLIC.
5
Normalize to 3NF/BCNF to avoid update, insert and delete anomalies; denormalize deliberately, and prefer real constraints over EAV or polymorphic designs.
6
Store money as numeric (or integer cents), timestamps as timestamptz, and passwords only as slow salted hashes (Argon2id, scrypt or bcrypt).
7
Soft deletes need a partial unique index such as UNIQUE (email) WHERE deleted_at IS NULL.

Common traps

Revoking a direct grant does not remove access inherited through a role the user belongs to.
A view owned by a superuser bypasses the base table's RLS policies unless it is security_invoker.
Databases restored from PostgreSQL 14 or older still let PUBLIC create objects in public; the PG15 default only applies to new databases.

Read the source

Test yourself on Security, Integrity and Schema Design

Ten questions, with the answer and explanation after each one.