Access Control and SQL Injection

A database holds the most valuable thing your system owns, and it is usually protected by one password shared with the whole application. Two ideas fix most of that: give each part of the system only the access it needs, and never let user input become SQL.

Roles#

PostgreSQL has one concept for users and groups: the role. A role can log in, own objects, and be a member of other roles.

-- A group-style role: cannot log in, just collects privileges
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_rw       NOLOGIN;

-- Roles that people or services use to connect
CREATE ROLE report_user LOGIN PASSWORD 'change-me-please';
CREATE ROLE web_app     LOGIN PASSWORD 'change-me-too';

GRANT app_readonly TO report_user;
GRANT app_rw       TO web_app;

Membership inherits privileges. Add a new analyst by granting app_readonly, and you never repeat a list of table grants.

GRANT and REVOKE#

GRANT USAGE ON SCHEMA public TO app_readonly, app_rw;

GRANT SELECT                          ON ALL TABLES IN SCHEMA public TO app_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE  ON ALL TABLES IN SCHEMA public TO app_rw;

REVOKE DELETE ON orders FROM app_rw;       -- keep order history permanent

USAGE on a schema lets a role look inside it. Without it, table grants do nothing. Privileges are specific: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER on tables, EXECUTE on functions, USAGE on schemas and sequences.

GRANT ... ON ALL TABLES only covers tables that exist now. For tables created later, set default privileges:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO app_readonly;

Inspect what is granted with \dp in psql, or query information_schema.role_table_grants.

Least privilege#

Give each role the minimum it needs.

• The reporting tool reads. It does not need INSERT.
• The web application reads and writes the tables it uses. It does not need DROP, and should never be a superuser.
• The migration tool owns the schema and runs only during deploys, with its own credentials.
• Nobody uses postgres for daily work.

This is damage control. When the web application has a bug, or a key leaks, the blast radius is whatever that one role can do. An application connecting as a superuser means one injection flaw can read every database and run commands on the server.

PostgreSQL also has a pseudo-role called PUBLIC, meaning everyone. Older versions let PUBLIC create objects in the public schema. PostgreSQL 15 closed that default. On older clusters, run REVOKE CREATE ON SCHEMA public FROM PUBLIC;.

Column-level and row-level restrictions#

Privileges can target specific columns:

GRANT SELECT (customer_id, full_name, city, country) ON customers TO analyst;

The analyst can query those four columns and gets permission denied for email or phone. A view (as in Module 4) is the other common way to do this, and is easier to evolve.

Row-level security restricts which rows a role can see:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY customer_sees_own_orders ON orders
    FOR SELECT
    USING (customer_id = current_setting('app.customer_id')::int);

Now a session that sets app.customer_id sees only that customer's orders. This is the foundation of multi-tenant designs. Table owners bypass policies unless you also FORCE ROW LEVEL SECURITY, which is a frequent surprise during testing.

SQL injection#

SQL injection happens when user input is pasted into SQL text and the database cannot tell the difference between the query you wrote and the data the user supplied.

A login lookup, built the wrong way:

email = request.form["email"]
query = f"SELECT customer_id, full_name FROM customers WHERE email = '{email}'"
cur.execute(query)

For a normal user the string becomes:

SELECT customer_id, full_name FROM customers WHERE email = 'aisha@example.com'

Now the attacker types ' OR '1'='1 as the email:

SELECT customer_id, full_name FROM customers WHERE email = '' OR '1'='1'

'1'='1' is always true, so the WHERE matches every row. The same trick bypasses login checks (admin'-- comments out the password test), reads other tables with UNION SELECT, and, if the driver allows multiple statements, runs '; DROP TABLE orders; --.

The root cause is not clever attackers. It is that the program built code from data.

The fix: parameterized queries#

Send the SQL text and the values separately.

cur.execute(
    "SELECT customer_id, full_name FROM customers WHERE email = %s",
    (email,),
)

await client.query(
  'SELECT customer_id, full_name FROM customers WHERE email = $1',
  [email]
);

Notice that %s here is a placeholder handled by the driver, not Python string formatting. Never combine the two ("... %s" % email is the vulnerable version again).

With a parameterized query the value ' OR '1'='1 is treated as a literal string to compare against. The database searches for a customer whose email is literally that text and finds none.

Why it works: in the PostgreSQL protocol, the statement text is parsed and planned first, with $1 marking where a value will go. Values arrive afterwards, in a separate message, and are bound to the plan. By then the structure is fixed and the value can no longer change it.

 vulnerable:   text = "... email = '" + input + "'"   ->  parser sees attacker's characters
 safe:         text = "... email = $1"   ->  parse once
               values = [input]          ->  bind later; never parsed as SQL

Some drivers (psycopg2) interpolate with careful escaping on the client instead. That is also safe when you use their placeholder API, and the rule for you is the same.

What parameters cannot do#

Parameters stand in for values. They cannot stand in for table names, column names or keywords (ORDER BY ?, FROM ?). People then fall back to string concatenation for sorting and dynamic columns, and the injection returns through a side door.

Fix it with an allowlist: map what the user chose to a value you control.

SORTABLE = {"name": "name", "price": "unit_price", "stock": "stock_qty"}

sort_key = request.args.get("sort", "name")
column = SORTABLE.get(sort_key, "name")          # unknown input falls back to a safe default
direction = "DESC" if request.args.get("dir") == "desc" else "ASC"

cur.execute(f"SELECT name, unit_price FROM products ORDER BY {column} {direction}")

The f-string is acceptable here only because column and direction can only ever be strings you wrote.

In PL/pgSQL, dynamic SQL uses format with %I for identifiers and USING for values:

EXECUTE format('SELECT COUNT(*) FROM %I WHERE status = $1', p_table)
INTO v_count
USING p_status;

%I quotes an identifier safely. USING passes the value as a real parameter. Stored procedures are not injection-proof by default: if they build SQL from their arguments with ||, they carry the same flaw.

Other places injection hides#

• ORMs with raw fragments. .filter(text(f"name = '{name}'")) and its relatives are string concatenation. Use bound parameters in raw fragments too.
• LIKE searches. Parameterize the whole pattern: pass '%' + term + '%' as the parameter. If users should not control wildcards, escape % and _ in the term first.
• Second-order injection. A malicious value is stored safely, then later read back and concatenated into a query by a different piece of code. Data from your own database is not automatically trusted.
• Stored identifiers. A report builder that lets users type column names is an injection surface by design.

Defense in depth#

Parameterization is the primary defense. Add the others anyway.

• Least privilege. Even a successful injection can only do what the connection's role can.
• Do not show raw database errors to users. Error text leaks table and column names.
• Validate input by type and format at the edge (an ID is an integer). Treat it as a second layer, never the first.
• Separate roles per service, so one leaked credential does not open everything.
• Audit logging for sensitive tables.

A web application firewall is not a fix. It filters known attack shapes and misses new ones.

Passwords and connections#

Store credentials in environment variables or a secrets manager, never in code. PostgreSQL's default password method is SCRAM-SHA-256, so keep password_encryption at its modern default. Require TLS (sslmode=require or stricter) for connections that leave your private network, and restrict who can reach the database at all through pg_hba.conf and firewalls.

Common mistakes#

Building SQL with f-strings or + from user input. The root cause of injection.

Connecting as a superuser. Every flaw becomes a total compromise.

Trusting that "internal" data is safe. Second-order injection exists.

Escaping by hand. Replacing quotes yourself fails against edge cases (encodings, backslashes). Use the driver.

Granting ALL PRIVILEGES to make an error go away. You have removed the control you were trying to configure.

Practice#

1. Create the app_readonly and report_user roles, grant read access, connect as report_user and confirm that INSERT fails with permission denied.
2. Grant a column-level SELECT on customers that excludes email and phone. Connect as that role and try SELECT *. What happens, and what does SELECT full_name, city return?
3. Write a vulnerable query in a script using string formatting, demonstrate ' OR '1'='1 against it, then rewrite it with a parameter and rerun the attack.
4. Implement an allowlist for a sortable column in a small function, and test it with an input like unit_price; DROP TABLE products.
5. Write a PL/pgSQL function that counts rows in a table chosen by name, using format('%I'). Try to pass a table name containing a quote and a semicolon, and explain why it fails safely.