This lesson keeps the promises the course has been making since module 5. It deals with two things that look different and are the same: who decides what gets executed and who can see what. The first half is SQL injection, the oldest and most expensive security flaw in database-backed applications, explained from the side that matters: how it's prevented. The second is PostgreSQL's permission and role system, including row-level security, with a concrete design for GreenStore.

A warning from the outset: this is a defensive lesson. The attack examples you'll see are minimal and harmless —a condition that returns too much— and they're here only so you understand why the vulnerable approach fails. The weight of the lesson is on the defences. And a warning that's meant seriously: before exposing a system with real data to the Internet, commission a review by a security professional and check the handling of personal data with your organization's legal or data protection officer. What follows is the minimum you should know, not a substitute for that review.

1. What an SQL injection is
2. Why it happens: code and data in the same string
3. The defence: parameterized queries
4. What is not a sufficient defence
5. The special case: dynamic identifiers
6. Defence in depth
7. Permissions and roles in PostgreSQL
8. A role design for GreenStore
9. Row Level Security
10. SECURITY DEFINER and the search_path
11. Personal data
12. Security checklist
13. Common Mistakes and Tips
14. Exercises
15. Conclusion

The shop's search box takes a piece of text and looks for products. Written the worst possible way —by concatenating— it looks like this:

With the normal input oil, what reaches the server is SELECT id, name, price FROM products WHERE active AND name ILIKE '%oil%';, which is correct and returns what you'd expect:

Two products. Now the input ' OR '1'='1 — a piece of text that's perfectly typeable in any search box. Concatenated in, the query that runs is:

It returns 19 rows: the entire active catalogue. The user's quote closed the string the programmer had opened, and what came after stopped being search text and became query structure: an OR nobody wrote. With the variant ' OR '1'='1' --, the double dash comments out the rest of the line, including the dangling %':

20 rows. One more: the spirulina capsules show up too, the discontinued product (active = FALSE) the shop should never display. It's a tiny, harmless example, and that's exactly why it's useful: the attacker hasn't done anything strange; they've typed text into a text box, and they've made the business filter stop applying. If instead of a public catalogue the query had been the one for "my orders" or the one for "employees", the result would have been the same mechanism over data that does matter.

And a less obvious but equally serious consequence: the input O'Connor —a perfectly ordinary surname— breaks the query with a syntax error. The same crack that allows the attack makes the program fail on legitimate data. Code that's vulnerable to injection is also code that doesn't work properly.

The cause fits in one sentence, and it's worth learning by heart:

SQL injection happens because code and data travel mixed together in the same string of text, and the engine has no way of knowing which part you wrote and which part the user wrote.

The server receives a piece of text and parses all of it. To it, OR '1'='1' is SQL just as much as the SELECT in front of it: there's no marker saying "this came from a form". Everything that follows from there —adding conditions, commenting out the rest, chaining statements if the driver allows it— is a consequence of that mixing. And out of it comes the only defence that really works: separate the code from the data, and let the engine put them together knowing which is which.

A parameterized query (or prepared statement) sends the server two separate things: the text with placeholders, and the values. The server parses and plans before it sees the values; by the time they arrive there's nothing left to parse and a value cannot turn into code, whatever it contains. You don't even need to escape quotes: the value is never inserted into the text. The same search box, written properly, in four environments:

With the malicious input ' OR '1'='1, all four versions return 0 rows: they look, quite literally, for products whose name contains the text ' OR '1'='1. That none exists is exactly the right answer. The attack stops being an attack and becomes a search with no results.

And the mechanism also exists in plain SQL, useful for seeing it without a language in the way:

Two practical points. The % wildcard goes inside the value, never in the SQL text: ILIKE '%' || $1 || '%' works too, but then you should escape any % and _ the user brings, or a search for 100% will turn into a wildcard. And if you're going to filter by a list of values, don't build the list by concatenating: use WHERE id = ANY($1) passing an array, which every modern driver supports.

Dialect note. The placeholder changes with the driver: $1, $2 in native PostgreSQL and node-postgres, %s in psycopg, ? in JDBC, PDO and many others, :name for named parameters in PDO, SQLAlchemy or JDBI. What doesn't change is the guarantee: as long as the value travels through the parameter channel, it can't become code. Careful with PDO: by default it emulates prepared statements by escaping on the client; turn that off with ATTR_EMULATE_PREPARES => false to use real ones.

The list leaves one conclusion: there are no degrees. Either the value travels through the parameter channel, or it isn't protected.

There's one case parameterized queries don't solve, and it's the way injection sneaks into otherwise correct applications: a parameter can't be a table name, a column name, or the direction of an ORDER BY.

The key idea: the user doesn't supply text, they pick an option from a set you control. What gets concatenated never comes from the input; it comes from your dictionary. And as a side effect, the default value turns a junk parameter into a reasonable ordering instead of an error.

When the dynamic SQL is built inside PostgreSQL —in a PL/pgSQL function (10-04)— the tool is format() with its type markers:

%I applies quote_ident: it quotes the identifier and neutralizes anything odd it carries. Never use %s with user input; %s is concatenation under another name. And even with %I, the right thing is to validate first against the list of allowed tables or against information_schema: %I prevents injection, but it doesn't prevent somebody counting the rows of a table that's none of their business.

No single layer is enough on its own; the good news is that they add up.

On error messages: in production, PostgreSQL's full error tells the user the names of the tables, the columns and the constraints. Log the detail on the server, return a generic message and an id that lets support find the trace; in development, the other way round.

This is where the promises from 05-04, 09-05, 10-01 and 10-04 get kept. The model is simpler than it looks, with one central idea:

In PostgreSQL there are no "users" and "groups": there are only roles. A role with LOGIN behaves like a user; a role without LOGIN that's granted to other roles behaves like a group. It's the same object.

Privileges are granted with GRANT, withdrawn with REVOKE and applied at different levels:

Two traps that catch everybody:

• USAGE on the schema is essential. Without it, GRANT SELECT ON orders is useless: the role has permission on a table it can't reach. The error is permission denied for schema public and it's baffling because the table's GRANT is right there.
• ALTER DEFAULT PRIVILEGES, or new objects inherit nothing. A GRANT SELECT ON ALL TABLES IN SCHEMA public affects the tables that exist at that moment. The table you create tomorrow won't be included, and the failure will show up in production after the deploy:

Careful: ALTER DEFAULT PRIVILEGES applies to objects created by the role that runs the command. If migrations are run by gs_admin, you have to run it as gs_admin (or with FOR ROLE gs_admin).

They're two different things with the same name, and confusing them is the lesson's classic. PUBLIC is a pseudo-role meaning "every role that exists and ever will". public is the default schema where GreenStore's nine tables live.

Historically, PUBLIC had CREATE on the public schema, so any user could create objects there. PostgreSQL 15 fixed that, but in databases created earlier, or migrated, it's worth checking and hardening:

And remember from module 10 that a view only protects if you revoke the permission on the table: GRANT SELECT ON v_customers_public is worthless while the role keeps SELECT ON customers.

Three group roles, three real profiles, and nobody connecting as the owner of the tables:

Checking what each role has is as important as granting it:

In psql, \dp orders gives the same information more compactly. The golden rule: the application never connects as a superuser or as the owner of the tables. If web_prod is the owner, every REVOKE in the world is decorative: the owner can grant everything back to itself.

The permissions above reach as far as the table and the column. Row Level Security (RLS) reaches the row: it lets two users run SELECT * FROM orders and get different results. The classic case: each sales rep sees only their own orders.

With SET app.employee_id = '4' at the start of the session, the sales rep Óscar (employee 4) sees 4 orders —numbers 2, 6, 10 and 16— and no others, even if they write SELECT * FROM orders with no WHERE. The ten web orders, with employee_id NULL, aren't visible to anyone under this policy, because NULL = 4 isn't true: if everyone should see them, the condition would be employee_id = ... OR employee_id IS NULL, and that decision has to be made explicitly.

Three warnings. The table's owner and superusers bypass RLS by default (you need FORCE ROW LEVEL SECURITY). Policies are written per operation —FOR SELECT, FOR INSERT with WITH CHECK, FOR UPDATE, FOR ALL— and if you don't define the INSERT one, nothing can be inserted. And the performance cost is real: the condition goes into every query, so the policy's column has to be indexed. RLS shines in multi-tenant setups (company_id); for three screens and one role, a view is usually enough.

This closes 10-04. By default a function runs with the permissions of whoever calls it (SECURITY INVOKER). With SECURITY DEFINER it runs with those of whoever created it, which lets you give controlled access to data the user can't read: for example, letting an analyst get the average salary without being able to read employees.

The course's canonical average salary, €35,037.50, served without granting access to the table. And now the risk, which is serious: a SECURITY DEFINER function runs with somebody else's privileges, so if the attacker controls which objects it resolves inside it, they execute code with those privileges. The vector is the search_path: if the function says FROM employees unqualified and the caller has put a schema of their own in front containing their own employees table, the function will read the attacker's.

The four rules, and they aren't optional: (1) always set SET search_path = public, pg_temp on every SECURITY DEFINER function (or qualify every object: public.employees); (2) REVOKE EXECUTE ... FROM PUBLIC and grant it only to those who should have it, because by default EXECUTE is granted to PUBLIC; (3) make the function as small as possible and with no dynamic SQL inside —and if there is any, format('%I'/'%L') and an allowlist—; (4) use it only when you have to: SECURITY INVOKER is the default for a good reason.

GreenStore is fictional, but its schema holds exactly the data that in a real system is regulated:

The measures you need to know, without getting into legal territory:

• Encryption in transit: TLS mandatory on the connection (sslmode=require or better); without it, credentials and data travel in the clear. At rest: disk or volume encryption, and of the backups, which is the one most often forgotten.
• Least privilege, which is section 8's point: most analysts don't need to see email addresses. And access logging for sensitive data, not just change logging.
• Pseudonymization for test environments: replace names and emails, shift dates, alter amounts — with the warning that badly done pseudonymization is reversible (a "hash of the email" is reversed by trying email addresses).
• Retention: personal data isn't kept forever. You have to decide how long and then delete or anonymize it (05-04).

⚠️ Before exposing a real system: commission a security review from a professional (code review, configuration and penetration testing) and check the handling of personal data with your organization's data protection officer or legal counsel. This lesson gives you the vocabulary and the minimum practices; it replaces neither an audit nor a compliance analysis.

Queries. 1. Do all the user's values travel as parameters? Look for concatenation and f-strings with SQL inside. 2. Are there dynamic identifiers (ORDER BY, a table name)? Are they resolved with an allowlist or %I? 3. Are % and _ escaped in LIKE/ILIKE searches?

Permissions. 4. Does the application connect with a role that isn't a superuser or an owner? 5. Does that role have only what it needs, with no gratuitous DELETE or TRUNCATE? 6. Is ALTER DEFAULT PRIVILEGES set up for future tables? 7. Has CREATE on the public schema been revoked from PUBLIC? 8. Do the "security" views have the permission on the base table revoked? 9. Do the SECURITY DEFINER functions set search_path and have REVOKE ... FROM PUBLIC?

Data and operations. 10. Does the connection use TLS, and are the backups encrypted? 11. Do the test environments have fictional or pseudonymized data? 12. Are production error messages generic, with the detail in the log? 13. Are accesses to sensitive data and repeated syntax errors logged and reviewed? 14. Is there a date for the external review before going into production?

• Believing a numeric value isn't injectable. WHERE id = " + input is injectable without a single quote. Parameterize always, not just the text fields.
• Using an ORM and assuming you're safe. The moment raw(), text() or createNativeQuery() shows up with an f-string, the protection vanishes (11-05). And leaving PDO with prepared-statement emulation on: it escapes on the client instead of using real prepared statements.
• Granting SELECT without USAGE on the schema. permission denied for schema public with the table's GRANT already in place: the GRANT USAGE ON SCHEMA is missing. And forgetting ALTER DEFAULT PRIVILEGES: everything works until a migration adds a table and the application stops seeing it in production.
• Confusing PUBLIC (the "everyone" pseudo-role) with the public schema. A REVOKE ... FROM PUBLIC affects every role, present and future.
• Creating a "security" view and leaving the GRANT on the table. It protects nothing. And SECURITY DEFINER without a fixed search_path: that's a textbook privilege escalation.
• Tip: search the whole repository for execute( followed by an f-string or a +. It's the cheapest security review there is and it finds most cases. And test every form field with ' and with --: if something breaks on a quote, there's concatenation behind it.
• Tip: write your GRANTs in a migration, not by hand. Permissions are part of the schema and must be reconstructible from the repository (05-06).

This endpoint returns a customer's orders:

(1) Point out all three injection paths. (2) Rewrite it correctly. (3) Why isn't the third one fixed the same way as the other two?

Design the permissions for two new GreenStore profiles: gs_warehouse (Irene, warehouse operator) needs to see paid orders and their lines, and update the order's status to shipped; gs_support (Marc, customer support) needs to see everything about a customer, including their email address, and to create returns. (1) Write the GRANTs. (2) How would you stop gs_warehouse changing the status to cancelled? (3) What is gs_support missing in order to insert into returns?

A colleague proposes: "Let's give SELECT on everything to the analysts, they're trustworthy, and then they'll stop bothering us asking for permissions". Rebut the proposal with four concrete arguments referring to GreenStore, and propose an alternative that solves their real problem.

Solution 1

1. All three: customer_id interpolated without quotes —injectable with 1 OR 1=1, and the one most often overlooked precisely because "it's a number"—; status interpolated inside quotes, injectable by closing them; and sort, which is an identifier and can't be a parameter.

3. Because a parameter is always a value, never structure. ORDER BY $1 would sort by the constant $1, not by the column whose name it holds: the engine has already planned the query by the time it receives the value, and the ordering is part of the plan. That's why identifiers are resolved with an allowlist (or with %I if the SQL is built inside the server), which is a different technique with the same goal: the user's input must never supply SQL text.

Solution 2

2. A GRANT UPDATE (status) lets you change the column, but doesn't control which value you set. That's a business rule and it goes in the database: a BEFORE UPDATE trigger (10-05) that rejects disallowed transitions, or an sp_mark_shipped procedure granted EXECUTE while the direct UPDATE is withdrawn — the second option is cleaner, because it exposes the operation and not the column. 3. It's missing the permission on the sequence —returns_id_seq— without which an INSERT that lets the id be generated fails with permission denied for sequence. It's the most common oversight when granting INSERT, and that's why section 8 has a GRANT USAGE ON ALL SEQUENCES.

Solution 3

The four arguments. (a) customers.email and employees.salary are neither trusted nor distrusted: they're personal data whose access must be limited to those who need it for their job, and a sales analyst doesn't. (b) Trust doesn't protect against accidents: a CSV export, a lost laptop or a shared dashboard turn a legitimate SELECT into a leak. (c) Trust doesn't protect against a compromised account: if Daniel's credentials are stolen, the attacker inherits exactly what Daniel had. (d) Without ALTER DEFAULT PRIVILEGES and with "access to everything", every new table —including a future payroll— is exposed by default, which is precisely the opposite of what should happen.

The alternative, which also solves their real problem (no longer being bothered for permissions): give them gs_read on the analysis views, not on the tables. A layer of v_* views excluding the sensitive columns, with ALTER DEFAULT PRIVILEGES so new views are granted automatically, and a SECURITY DEFINER for the few aggregates that need restricted data —like fn_average_salary. The analyst gains autonomy and the exposure is far smaller.

This lesson closes the least visible half of the course and the one that costs most when it's missing:

• SQL injection happens because code and data travel in the same string. With the input ' OR '1'='1, a concatenated search box goes from returning 2 products to returning all 19 active ones; with a -- in there, all 20, including the discontinued one. And the same flaw makes O'Connor break the query. The defence is one thing and it's total: parameterized queries. The server parses and plans before it sees the values, so a value can't turn into code. The same in psycopg, JDBC, PDO and node-postgres, and in plain SQL with PREPARE/EXECUTE.
• These are not defences: escaping by hand, blacklists, hiding errors, validating in the browser or "using an ORM". And identifiers can't be parameterized: they go through an allowlist, or through format('%I') / quote_ident inside the server. Never %s.
• Permissions: in PostgreSQL everything is a role; GRANT/REVOKE on database, schema, table, column, sequence and function; USAGE on the schema is essential and ALTER DEFAULT PRIVILEGES is what makes future objects inherit. PUBLIC (every role) isn't the public schema, and a view only protects if the permission on the table is revoked.
• GreenStore's design: gs_read for analysts without email or employees, gs_app without DELETE or DDL, gs_admin only for migrations. And the golden rule: the application never connects as a superuser or as the owner.
• RLS filters by row —sales rep 4 sees their 4 orders and no others— at the price of a condition on every query and harder debugging; SECURITY DEFINER lends privileges and demands SET search_path and REVOKE EXECUTE FROM PUBLIC. And personal data: identify which columns qualify, TLS in transit, encryption at rest and of the backups, pseudonymization in test environments, retention — with a review by a security professional and by your legal officer before exposing anything real.

With this the system is protected and maintainable. Now to get value out of it. In the next lesson, SQL for data analysis, you'll see the analyst's craft: the flow that runs from the business question to the well-defined metric —and why half of analysis errors are definitional and not about SQL: does "sales" include shipping? what about the cancelled order?—; GreenStore's fundamental metrics computed one by one; time analysis with running totals and moving averages; segmentation and the Pareto of customers and products; cohorts with their honest warning about sample size; presentation with CASE and with crosstab, closing 06-05's promise; and where SQL fits against Python and BI tools.