Module 8 · Security

TLS, Authentication, Secrets, and SQL Injection Defense

Lesson 24 of 30 14 min read Updated

PostgreSQL security starts before a query reaches the executor. A client opens a network connection, negotiates optional TLS, passes through host-based authentication rules, authenticates as a role, and then receives only the privileges granted to that role. SQL injection defense completes the chain by ensuring that untrusted values are data, not executable SQL text.

The outcome is practical: require encrypted connections, choose authentication methods deliberately, keep secrets out of SQL and logs, grant narrow privileges, and verify that application queries use parameters instead of string concatenation. Together these controls form the path from a user’s request to PostgreSQL executing under a specific authenticated identity.

How A Connection Is Admitted

A PostgreSQL client begins with a startup exchange. If TLS is requested and the server is configured for SSL, the connection is wrapped before authentication data is sent. The server then evaluates pg_hba.conf from top to bottom. The first matching row decides the authentication method, so order is security-sensitive. A broad host all all 0.0.0.0/0 md5 line placed above a stricter line can silently weaken a deployment.

Each host-based authentication line names a connection type, databases, users, client address range, and method. hostssl matches only TLS connections. hostnossl matches only cleartext TCP. local covers Unix-domain socket connections. Authentication methods include scram-sha-256 passwords, certificate checks, peer authentication for local sockets, and supported external mechanisms. For password logins, prefer SCRAM because the stored verifier is stronger than legacy MD5 password storage.

After authentication, PostgreSQL checks privileges through roles. A login role can connect. A no-login role is useful as a privilege bundle. Membership lets a login role inherit or assume those grants. This separation keeps rotation manageable: rotate the application login secret without rediscovering table grants, and revoke an operator’s login without rewriting the privilege model.

TLS Internals And Certificate Checks

TLS protects confidentiality and integrity on the wire. On the server, ssl = on enables TLS support when the server has a certificate and private key. On the client, sslmode determines what is verified. require encrypts but does not necessarily prove the server’s name. verify-ca verifies that the server certificate chains to a trusted CA. verify-full also checks that the certificate name matches the host the client intended to reach. For applications connecting over a network, verify-full is the setting that protects against accidentally sending credentials to the wrong endpoint.

PostgreSQL exposes TLS evidence in pg_stat_ssl. You can inspect whether the current backend uses SSL and which protocol and cipher were negotiated. This is better than assuming that a connection string is honored by every environment. A pooler, sidecar, or local socket can change where encryption starts and ends, so verify the database boundary you care about.

Secrets And Role Design

A secret is not just a password string. It is operational authority. Store database passwords in a secret manager, injected environment variable, or platform credential store, not in code, migrations, SQL comments, shell history, or logs. PostgreSQL cannot prevent an application from leaking its own connection string, so the database role must still be least privilege. If a web role only needs to read products and insert orders, it should not own schemas, create extensions, or update user password hashes.

Use separate roles for migrations, application traffic, reporting, and emergency administration. A migration role may need DDL. The runtime application role usually should not. Reporting roles may need read access to selected schemas but no writes. This limits damage from injection, stolen credentials, and bugs because a compromised session can only use its role’s grants.

SQL Injection Mechanics

SQL injection happens when untrusted input becomes part of SQL syntax. The classic failure is concatenating a login name into WHERE username = ' plus user input plus '. If the input contains quotes and operators, the parser sees new SQL tokens, not a string value. Parameterized statements change that contract: the SQL text is parsed with placeholders, and values are transmitted separately using the client library’s binding protocol. The value may contain quotes, semicolons, or comment markers, but those bytes remain a value for one placeholder.

Parameters protect values, not identifiers. Table names, column names, sort directions, and SQL keywords cannot be safely supplied as ordinary bind values. For those, use an allowlist and the identifier-quoting facilities in your client library. The safest design is often to avoid dynamic identifiers entirely by exposing explicit functions or explicit query branches for the few operations the application supports.

Configuration Anatomy

A secure PostgreSQL connection policy spans server configuration, host-based authentication, role DDL, and application connection strings. Name their jobs clearly: server TLS settings enable encryption, pg_hba.conf chooses authentication by source, roles define identity and privileges, and the client connection string sets certificate validation.

# postgresql.conf fragment
ssl = on
password_encryption = 'scram-sha-256'

# pg_hba.conf fragment: first matching line wins
hostssl appdb app_login 10.20.0.0/16 scram-sha-256
hostnossl all all 0.0.0.0/0 reject

This fragment requires TLS for app_login from the application subnet and rejects non-TLS TCP connections. Certificate paths and local administrative rules vary by deployment. The deterministic behavior is the rule order: if a non-TLS TCP client matches the reject line, authentication is refused before any password method succeeds.

Example 1: Verify The Current TLS Session

Start with observation. Connect using the same hostname, port, and connection method that the application will use, then inspect PostgreSQL’s view of the connection.

SELECT s.ssl, s.version, s.cipher, a.usename, a.client_addr
FROM pg_stat_ssl AS s
JOIN pg_stat_activity AS a ON a.pid = s.pid
WHERE s.pid = pg_backend_pid();

An encrypted TCP connection returns one row with ssl true, a TLS version, a cipher, the authenticated user, and the client address. If ssl is false, either the client did not request TLS, the connection used a local socket, the server is not serving TLS, or a pooler terminated TLS before the PostgreSQL backend. The correction depends on where encryption is required: update sslmode, server TLS configuration, pooler configuration, or the connection route.

Example 2: Separate Login From Privileges

The next step is to make the runtime identity narrow. This example creates a login role, a no-login privilege role, and grants only the operations needed by a small application schema.

CREATE ROLE app_runtime NOLOGIN;
CREATE ROLE app_login LOGIN PASSWORD 'replace-with-secret-manager-value';
GRANT app_runtime TO app_login;

GRANT CONNECT ON DATABASE appdb TO app_runtime;
GRANT USAGE ON SCHEMA public TO app_runtime;
GRANT SELECT, INSERT ON TABLE public.orders TO app_runtime;
GRANT SELECT ON TABLE public.products TO app_runtime;

Sessions authenticated as app_login can connect and perform the granted reads and inserts, but cannot drop tables, create extensions, or update ungranted tables. In a real system, the password should be generated and stored outside this SQL text. The SQL shows the role relationship and grant shape; the secret belongs in operational storage.

Example 3: Bind Values Instead Of Building SQL

Application code should send untrusted values through the driver parameter mechanism. This Python example uses psycopg-style placeholders. The query text has one placeholder, and the username is supplied separately.

import os
import psycopg

conninfo = os.environ["DATABASE_URL"]
username = "alice' OR true --"

with psycopg.connect(conninfo) as conn:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, username FROM app_user WHERE username = %s",
            (username,),
        )
        print(cur.fetchall())

If the table contains no literal username equal to alice' OR true --, the deterministic output is an empty list. The injected-looking characters are treated as a string value, not as OR true syntax. If this code were rewritten with string interpolation, the same input could change the predicate and return unrelated rows. The protection comes from the driver and server separating SQL text from data values.

Example 4: Allowlist Dynamic Sorts

Sometimes an application needs controlled dynamic SQL. A parameter cannot stand in for a column name or ASC/DESC, so validate those pieces before composing the statement.

ALLOWED_SORTS = {"created_at": "created_at", "total": "total_cents"}
ALLOWED_DIRECTIONS = {"asc": "ASC", "desc": "DESC"}

def order_query(sort_key: str, direction_key: str) -> str:
    column = ALLOWED_SORTS[sort_key]
    direction = ALLOWED_DIRECTIONS[direction_key]
    return f"SELECT id, total_cents FROM orders ORDER BY {column} {direction} LIMIT %s"

The expected behavior is narrow. order_query("total", "desc") returns a statement ordering by total_cents DESC. order_query("id; drop table orders; --", "desc") raises KeyError before SQL is produced. The values used with LIMIT %s should still be bound as parameters when executing the returned statement.

Design Choices And Trade-Offs

sslmode=require is easy to deploy, but it does not provide the same identity guarantee as verify-full. Use verify-full when clients cross networks where endpoint confusion matters. Certificate management adds rotation work, but it prevents credentials from being exposed to a server that only looks like the intended one.

Password authentication is simple and works with most drivers. Certificate authentication can reduce password handling but increases certificate lifecycle work and may be harder to operate across developer machines, CI, and managed platforms. Role inheritance keeps grants centralized, but overly large privilege roles become another broad authority. Keep runtime roles small and split write-heavy, read-only, migration, and support tasks.

Prepared statements and parameter binding are the normal injection defense for values. Stored procedures do not automatically solve injection; a function that concatenates user input into dynamic SQL is still injectable. Conversely, dynamic SQL can be safe when every identifier comes from a strict allowlist and every value is bound.

Failure Modes And Troubleshooting

Symptom: clients fail with certificate verification errors after enabling verify-full. Cause: the certificate subject or subject alternative name does not match the hostname in the connection string, or the client lacks the issuing CA. Diagnostic steps: compare the application hostname with the certificate names and confirm the client CA file or platform trust store. Correction: issue a certificate for the actual PostgreSQL hostname and deploy the CA bundle used to sign it.

Symptom: password login fails even though the password is correct. Cause: a preceding pg_hba.conf line matched the connection and selected a different method or rejection. Diagnostic steps: inspect rules from top to bottom, checking connection type, database, user, and client address. Reload configuration after changes. Correction: place the intended narrow hostssl rule before broader rules and remove accidental catch-all allowances.

Symptom: an application user can read or modify unexpected tables. Cause: grants were made directly to the login role, inherited from a broad role, or left available through default public privileges. Diagnostic steps: test with the exact application login and query table privileges from information schema or psql access listings. Correction: revoke broad grants, move intended grants to a no-login role, and set default privileges for future objects deliberately.

Symptom: a security scan still finds SQL injection after parameters were added. Cause: identifiers, sort clauses, or WHERE fragments are still concatenated from request values. Diagnostic steps: search application code for string formatting around SQL and review any dynamic ORDER BY, table selection, or filter builder. Correction: replace value concatenation with parameters and replace dynamic identifiers with allowlists or explicit query branches.

Security And Reliability Implications

TLS protects credentials and query contents in transit, but it does not fix overbroad privileges. Authentication proves who connected, but it does not prove the application will build safe SQL. Least privilege reduces blast radius, but it does not remove the need to patch drivers and rotate secrets. Treat these controls as layered defenses. Each one should still be testable independently.

There is also a performance dimension. TLS adds handshake work and some CPU cost, usually outweighed by security requirements. Connection pooling can amortize handshakes, but it changes where authentication and TLS sessions terminate. Prepared statements can improve repeated query execution, but parameter-sensitive plans and long-lived sessions need normal database monitoring. Security settings should be measured under representative connection counts, not only during a single manual psql session.

Hands-On Lab

Prerequisites: a disposable PostgreSQL instance where you can edit authentication rules or use a managed equivalent, a client with psql or an application driver, and a test database named appdb. Do not run this lab against a shared production cluster.

  1. Enable server TLS using your platform’s certificate mechanism. If you manage files yourself, install a server certificate and private key readable by the PostgreSQL service account, then enable SSL and reload or restart as required by your platform.
  2. Add a narrow hostssl rule for the test database and login role. Add a later hostnossl reject rule for TCP connections that should not use cleartext.
  3. Create the no-login runtime role, login role, and grants from Example 2, replacing the demonstration password with a generated test secret stored outside source control.
  4. Connect with a client connection string that requires certificate verification, preferably sslmode=verify-full when you have a matching hostname and CA.
  5. Run the TLS inspection query from Example 1 and record the row. Verify that ssl is true and that the displayed role is the expected login.
  6. As the application login, attempt the intended operation, such as inserting an order. Then attempt an unrelated operation, such as dropping a table or updating a restricted table. The intended operation should succeed and the unrelated operation should be denied.
  7. Run the parameterized query from Example 3 with a value containing quotes and SQL comment markers. Confirm that it returns only rows whose stored value exactly matches the input.

Verification: successful completion requires encrypted connection evidence from pg_stat_ssl, successful authentication as the intended login, denial of an ungranted privilege, and parameterized query behavior that treats hostile-looking input as a value. Cleanup: drop the test login and runtime roles after revoking dependent grants, remove lab-only authentication rules, reload configuration, and delete any temporary test secrets from your secret store.

Assessment Exercises

  1. A team enables sslmode=require and says server identity is now verified. Explain what is still missing and when verify-full matters.
  2. Given three pg_hba.conf lines, identify which one matches a connection from a specific user, database, address, and TLS state. Explain why rule order changes the result.
  3. Design roles for a service that reads products, inserts orders, runs migrations, and supports analyst reports. Which roles can log in, and which privileges belong to no-login roles?
  4. Review a query builder that parameterizes WHERE values but concatenates an ORDER BY field from an HTTP request. Explain the remaining injection risk and propose a correction.
  5. Describe a verification plan that proves a stolen reporting password cannot modify application tables.

Summary

PostgreSQL admits a client through a concrete sequence: TLS negotiation, host-based rule matching, authentication, role privilege checks, and statement execution. Strong security comes from making every stage explicit. Require encrypted and verified connections where the network boundary demands it, use SCRAM or certificate methods deliberately, store secrets outside code, split login identity from privilege roles, and bind untrusted values as parameters. SQL injection defense is strongest when paired with least privilege, because even a missed dynamic SQL path then runs with restricted authority.

Progress is saved in this browser.