SQL Security

Security in PostgreSQL operates at several layers: who can connect, what objects they can access, which rows they can see, and how queries are constructed in application code. Each layer provides independent protection — if one layer fails (a credential is leaked, a bug exists in application logic), the others limit the damage. Getting each layer right means a compromised application credential causes minimal harm.

Roles and Users

PostgreSQL uses a unified concept called a role. A role with the LOGIN attribute is what most people call a “user”. This unified model lets you create group roles that hold permissions, then grant those roles to login roles — so you manage permissions in one place and users inherit them.

-- Create a role that cannot log in (a group role for permission management)
CREATE ROLE readonly;

-- Create a role that can log in (a user)
CREATE ROLE app_user WITH LOGIN PASSWORD 'str0ng-passw0rd';

-- Create a superuser — avoid using this for application connections
CREATE ROLE admin_user WITH LOGIN SUPERUSER PASSWORD 'very-str0ng';

-- Grant one role to another: app_user inherits all of readonly's permissions
GRANT readonly TO app_user;

-- Inspect all roles
SELECT rolname, rolsuper, rollogin, rolcreatedb FROM pg_roles;

Superusers bypass all permission checks. Never use a superuser role for application connections — only for administrative tasks.

GRANT and REVOKE

By default, a new role has no access to any objects. You grant access explicitly. This principle of default-deny means new tables and schemas are inaccessible until you deliberately open them up, which is the safe baseline.

-- Grant SELECT on a single table
GRANT SELECT ON orders TO readonly;

-- Grant SELECT on all current tables in a schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Grant SELECT on future tables too — otherwise each new table needs individual grants
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

-- Grant full DML to the application role
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;

-- Grant usage on sequences — required for INSERT with SERIAL/BIGSERIAL
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Revoke a specific permission
REVOKE DELETE ON orders FROM app_user;

Least-Privilege Principle

Create one role per responsibility. A read-only reporting user should never be able to write data. An application user should never be able to drop tables. Restricting each role to exactly what it needs limits the blast radius if a credential is compromised.

-- Three distinct roles for a typical web app
CREATE ROLE web_readonly WITH LOGIN PASSWORD 'read-pass';
CREATE ROLE web_app      WITH LOGIN PASSWORD 'app-pass';
CREATE ROLE migrations   WITH LOGIN PASSWORD 'mig-pass' CREATEROLE;

-- Revoke the default PUBLIC access to the schema
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO web_readonly, web_app, migrations;

-- web_readonly: SELECT only — safe for reporting and analytics connections
GRANT SELECT ON ALL TABLES IN SCHEMA public TO web_readonly;

-- web_app: DML but not DDL — can read and write data but cannot alter structure
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO web_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO web_app;

-- migrations role: full DDL — only used during deploy, not for ongoing traffic
GRANT ALL ON SCHEMA public TO migrations;

Row-Level Security (RLS)

RLS lets you attach filter policies directly to a table. Even if the application role has SELECT on the table, queries only return rows allowed by the active policy. This is the right tool for multi-tenant applications where different users must see only their own data — it enforces isolation at the database level regardless of application code.

-- Enable RLS on a table — by default, no rows are visible once enabled
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see documents they own
-- The USING clause filters rows for SELECT, UPDATE, and DELETE
CREATE POLICY user_isolation ON documents
  FOR ALL
  TO app_user
  USING (owner_id = current_setting('app.current_user_id')::INT);

Separate read and write policies for finer control:

CREATE POLICY select_own ON documents
  FOR SELECT TO app_user
  USING (owner_id = current_setting('app.current_user_id')::INT);

-- WITH CHECK validates the row being written — prevents inserting rows for another user
CREATE POLICY insert_own ON documents
  FOR INSERT TO app_user
  WITH CHECK (owner_id = current_setting('app.current_user_id')::INT);

-- Set the session variable from application code before running queries
SET app.current_user_id = 17;
SELECT * FROM documents;  -- returns only rows where owner_id = 17

Roles with BYPASSRLS skip all policies — useful for admin roles and migration scripts:

ALTER ROLE migrations BYPASSRLS;

SQL Injection

SQL injection happens when user-supplied input is concatenated directly into a query string. The attacker closes your string and appends arbitrary SQL — they can read, modify, or delete any data the application role can access. It remains one of the most common and damaging vulnerabilities in web applications.

-- Vulnerable pattern (never do this)
-- Input: ' OR '1'='1
-- Result: SELECT * FROM users WHERE username = '' OR '1'='1'
-- This returns every row in the table
query = "SELECT * FROM users WHERE username = '" + user_input + "'"

The fix is always parameterized queries. The driver sends the query and parameters separately; the database never parses user input as SQL. No amount of sanitization is as reliable as never interpolating user data into query strings in the first place.

# Python with psycopg2 — the %s placeholder is replaced safely by the driver
import psycopg2

conn = psycopg2.connect(dsn)
cur = conn.cursor()

# Parameters are passed separately — never concatenated into the query string
cur.execute(
    "SELECT id, email FROM users WHERE username = %s",
    (user_input,)
)
rows = cur.fetchall()

// Node.js with node-postgres (pg) — $1 is a positional placeholder
const { Pool } = require('pg');
const pool = new Pool();

async function getUser(username) {
  // The username value is passed in the array, never interpolated into the string
  const result = await pool.query(
    'SELECT id, email FROM users WHERE username = $1',
    [username]
  );
  return result.rows[0];
}

// Go with pgx — $1 placeholder, value passed as a separate argument
row := conn.QueryRow(ctx,
    "SELECT id, email FROM users WHERE username = $1",
    username,
)

For dynamic identifiers (table names, column names) that cannot be parameterized, use your driver’s identifier quoting function — never string formatting:

# psycopg2 — safe dynamic identifier using the sql module
from psycopg2 import sql

cur.execute(
    sql.SQL("SELECT * FROM {}").format(sql.Identifier(table_name))
)

pg_audit Extension

For compliance-heavy environments, pg_audit logs every SQL statement executed, including which rows were affected. This provides the complete audit trail required by regulations like PCI-DSS, HIPAA, and SOC 2.

-- Enable in postgresql.conf (requires restart):
-- shared_preload_libraries = 'pgaudit'
-- pgaudit.log = 'write, ddl'

-- Enable per-role audit logging for more granular control
ALTER ROLE app_user SET pgaudit.log = 'write';

Audit logs should be shipped to an external system (not stored only in the database) so they cannot be tampered with by a compromised database user.

Security Checklist

• Never use the postgres superuser in application connection strings
• Revoke default PUBLIC privileges on your schema
• Use RLS for multi-tenant data isolation at the database level
• Always use parameterized queries — no exceptions
• Rotate passwords using ALTER ROLE app_user PASSWORD 'new-pass'
• Enable SSL (sslmode=require) for all connections
• Restrict pg_hba.conf to known IP ranges
• Audit GRANT history regularly with \dp (psql) or information_schema.role_table_grants