Part 5 — Building it properly › Chapter 35

Users, roles, least privilege and GDPR

GRANT and REVOKE, row-level security, encryption at rest vs in transit, and what GDPR asks of a schema.

From the advert

“Conoscenza dei principi di sicurezza dei dati e GDPR” — a table of Italian customers is a legal object as well as a technical one, and the schema is where several of the obligations actually land.

Least privilege, concretely

🧒 Simple

The person who waters the plants gets a key to the front door, not to the safe. Not because you distrust them — because if they lose the key, you have only lost the plants.

Least privilege, written as roles — and what is deliberately absent from them.

-- Grant to ROLES, assign roles to users. Never grant to a person: people leave,
-- roles do not, and a role is a sentence about a job rather than about a name.
CREATE ROLE app_readwrite;                                    -- what the API may do
GRANT SELECT, INSERT, UPDATE ON orders, order_lines TO app_readwrite;   -- write the workflow
GRANT SELECT                  ON products, customers TO app_readwrite;  -- read the reference data
-- Note what is ABSENT: DELETE, any DDL, and any access to the audit tables.

CREATE ROLE reporting_readonly;                               -- what an analyst may do
GRANT SELECT ON v_orders_geo, v_order_totals TO reporting_readonly;     -- VIEWS only
-- The analyst never sees a column the view does not expose. That is the point.

GRANT app_readwrite TO svc_orders_api;                        -- the service account gets the role

Reading a grant list as a design document

The missing DELETE is a decision: this application cancels orders by changing their status, so nothing needs to remove rows. A privilege you never grant is an incident you never have.
Granting on views rather than tables is column-level security done with the tools every engine has. The view is the contract.
Roles survive people. An access review against roles is a page; an access review against individual grants is an archaeology project.
The application's own login should not own the schema. If it can DROP TABLE, then so can an injection you have not found yet.

⚠ Trap — the application logs in as the owner

The commonest real-world finding. The connection string uses sa, postgres, or the schema owner, so an injection flaw anywhere becomes total compromise, a bug can drop a table, and every audit trail says the same username. Separate accounts for migrations (DDL), the application (DML on its own tables) and reporting (SELECT on views) costs one afternoon and changes the blast radius of every other mistake.

Row-level security

Multi-tenant systems usually filter by tenant in application code — which works until one query forgets. RLS moves the rule into the database, where it cannot be forgotten:

Row-level security: the tenant filter that cannot be forgotten, because it is not in the query.

-- PostgreSQL
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;         -- from now on, policies apply
CREATE POLICY tenant_isolation ON orders              -- name it: errors will quote the name
  USING (tenant_id = current_setting('app.tenant_id')::int);  -- the predicate, cast to int
-- Every query against orders now silently carries that predicate.
-- The application sets it per transaction:  SET LOCAL app.tenant_id = '42';

-- SQL Server: an inline table-valued function plus a security policy.
CREATE SECURITY POLICY TenantFilter                            -- the policy object…
  ADD FILTER PREDICATE dbo.fn_tenant_predicate(tenant_id)      -- …binds a function…
  ON dbo.orders;                                               -- …to a table

Why this beats “remember the WHERE clause”

A tenant filter in application code works until one query forgets — a report, a background job, a migration script, a developer in a console. Row-level security moves the rule into the engine, where it cannot be forgotten.
SET LOCAL scopes the setting to the transaction, so a pooled connection cannot leak one tenant's context into the next request. Using SET without LOCAL is exactly that bug.
::int matters: current_setting returns text, and comparing text to an integer column would either fail or force a per-row conversion that defeats the index.
The policy predicate is evaluated per row, so it must be cheap and it must be indexable. A function that queries another table here is a per-row query.
Table owners and superusers bypass RLS unless you say FORCE ROW LEVEL SECURITY. Check which account the application connects as.

Two things to know about it

The predicate is added to every query, so it must be indexable — a policy calling a slow function turns every query into that function's cost.
It does not apply to the table owner or to superusers unless you say so (FORCE ROW LEVEL SECURITY), which is exactly the case where people believe they have tested it and have not.

Encryption: three different things

Kind	Protects against	Does NOT protect against
In transit (TLS)	Network interception	Anything at either end
At rest (TDE, filesystem)	A stolen disk or backup file	Any authenticated query. The engine decrypts transparently
Column-level / application-level	A DBA or an attacker reading the table	Being searchable, sortable or joinable — you gave that up

⚠ Trap — "the database is encrypted" answers almost nothing

Transparent Data Encryption protects the files. It does not protect against a compromised application account, an injection flaw, or a curious DBA — because for anyone holding a valid connection, the data is simply there. It is genuinely valuable for lost backups and decommissioned disks, and it is not a defence against the threat most people are imagining when they say it. Being precise about that separates a real answer from a compliance checkbox.

🎓 The obligations that land on the database designer

Data minimisation (Art. 5). Do not store a personal data field you have no purpose for. A column that exists because "we might want it" is a liability with storage costs. This site's own accounts service stores no email address for exactly this reason, and says so.
Erasure (Art. 17). You must be able to delete a person's data on request. That is a schema question: can you find everything about one person? Foreign keys and a documented data map are what make the answer yes.
Portability (Art. 20). Export it in a machine-readable form — which is a query you should have written before somebody asks for it.
Storage limitation (Art. 5). A retention policy, implemented as a scheduled job, not as an intention.
Records and access control (Art. 30, 32). Who accessed personal data, and when. That is an audit trail — and see chapter 33 on why a trigger is the honest place for it.

✔ The tension worth naming out loud

Erasure conflicts with immutable accounting records. You cannot delete an invoice; Italian tax law requires it be kept for ten years, and Article 17 has an explicit exemption for legal obligations. The usual resolution is pseudonymisation: delete or hash the identifying columns, keep the transaction with a non-identifying key. Knowing that the two requirements collide, and that the answer is pseudonymisation rather than "delete everything" or "keep everything", is precisely the kind of thing that distinguishes someone who has done this from someone who has read about it.

Erasure that satisfies GDPR without destroying the accounts: pseudonymisation.

-- The person disappears; the accounting does not.
UPDATE customers                                      -- the person's own row…
SET name       = 'ERASED',                            -- the identifying data goes…
    email      = 'erased+' || id || '@invalid',       -- …but the UNIQUE constraint stays valid
    vat_number = NULL,                                -- nullable, so it can simply go
    erased_at  = now()                                -- record that it happened, and when
WHERE id = :id;                                       -- one person, by key
-- orders, order_lines and payments are untouched: they reference customer_id,
-- which is now a meaningless surrogate. Which is what a surrogate key is FOR.

Why not DELETE

Deleting the customer would either fail on the foreign keys or cascade and destroy the order history — which you are legally required to keep for accounting. The right to erasure is not a right to delete the invoices.
'erased+' || id || '@invalid' keeps every erased row distinct, so a UNIQUE(email) constraint survives the second erasure. Setting them all to 'ERASED' fails on customer number two. .invalid is a reserved TLD that can never route.
Remember the copies: backups, replicas, the data warehouse, the search index, and the log files. Naming those in an interview is what distinguishes somebody who has done this from somebody who has read about it.
erased_at is not decoration — it is how you prove the request was honoured, and how you exclude the row from future exports.

Masking, and what it is worth

Masking: a presentation feature that is often mistaken for a security control.

-- SQL Server dynamic data masking: non-privileged users see a masked value.
ALTER TABLE customers ALTER COLUMN email               -- an existing column…
  ADD MASKED WITH (FUNCTION = 'email()');              -- …gets a masking rule
-- 'mario.rossi@esempio.it'  ->  'mXXX@XXXX.com'

What it is worth, precisely

It masks the value on the way out. The data is unchanged on disk, in backups and in the log. This is not encryption and not anonymisation.
It is not a permission boundary either: a user who can run WHERE email LIKE 'mario%' can still confirm a guess, one character at a time. Filtering happens before masking.
Where it genuinely helps: keeping a support tool's screenshots clean, and reducing what a curious but authorised user sees by default. Treat it as a UI feature implemented in the database.

⚠ Masking is a display feature, not a security boundary

Microsoft's own documentation says so. A user who can query the column can infer it: WHERE email LIKE 'a%' still filters on the real value, so a few dozen queries recover it. It is useful for keeping production data off a support screen. It is not a substitute for not granting access.

Grant to roles, not people. Roles outlive employees and describe a job.
Separate accounts for migrations, the application and reporting. It changes the blast radius of every other mistake.
An application connecting as the owner turns any injection flaw into total compromise.
Row-level security moves a tenant filter into the database, where it cannot be forgotten — and its predicate must be indexable.
Encryption at rest protects stolen disks and backups, not authenticated queries.
Column-level encryption costs you searching, sorting and joining on that column.
Data minimisation is a schema decision: a column with no purpose is a liability.
Erasure requires knowing where a person's data is. That is a data map plus foreign keys.
Erasure and accounting retention conflict; pseudonymisation is the standard resolution.
Dynamic data masking is a display feature and can be inferred around.

Interview questions this chapter answers

How would you secure a database for an application?

Separate accounts by purpose — migrations get DDL, the application gets DML on its own tables and nothing else, reporting gets SELECT on views. Grants go to roles, not users. TLS in transit and encryption at rest for the backups. Then verify what the application account can actually do, because the finding is almost always that it is the owner.

A customer asks to be forgotten, but their invoices must be kept ten years. What do you do?

Pseudonymise rather than delete: clear or hash the identifying columns on the customer record and keep the transactions, which reference a surrogate key that no longer identifies anyone. GDPR Article 17 has an explicit exemption for legal obligations, so the two requirements are reconcilable — but only if the schema separates identity from transaction.

Is encryption at rest enough?

It protects a stolen disk or backup file. It does nothing against a compromised application account or an injection flaw, because the engine decrypts transparently for anyone with a valid connection. It is worth having and it answers a narrower question than people usually mean.