Security Best Practices

This page is a defensive, educational checklist for running PostgreSQL securely. None of it is exotic — most database breaches trace back to a small set of well-known mistakes, not novel attacks. Getting the basics right closes off the vast majority of real-world risk.

Least privilege for roles

Every application, service, and person that connects to PostgreSQL should use a role with only the permissions it actually needs — see Roles & Permissions for the mechanics of `GRANT`/`REVOKE`. Never point an application at the superuser account: a role that can only `SELECT`, `INSERT`, and `UPDATE` on specific tables limits the damage of a compromised application to those tables, whereas a superuser connection gives an attacker the entire cluster.

Application role with least privilege

SQL

CREATE ROLE app_service LOGIN PASSWORD 'use-a-secrets-manager-not-this';

GRANT CONNECT ON DATABASE storefront TO app_service;
GRANT USAGE ON SCHEMA public TO app_service;
GRANT SELECT, INSERT, UPDATE ON orders, customers TO app_service;
-- No DELETE, no DDL, no superuser.

Restrict network access

pg_hba.conf controls which hosts, users, and databases are allowed to connect and by what authentication method. Keep it as narrow as possible: only the application's known IP ranges, only the databases it needs, and never trust authentication over a network connection.

pg_hba.conf — require SSL and scoped access

Text

# TYPE  DATABASE   USER         ADDRESS          METHOD
hostssl storefront app_service  10.0.4.0/24      scram-sha-256
hostssl all        all          0.0.0.0/0        reject

The hostssl prefix requires the connection to use SSL/TLS — plain host entries permit unencrypted connections. For any connection that travels over a network you don't fully control, require TLS so credentials and data can't be read off the wire.

Never build SQL from user input

SQL injection

Constructing SQL by concatenating strings with user-supplied input is one of the most damaging and common real-world vulnerabilities — it has its own entry in the OWASP Top 10 for a reason, and it remains a leading cause of serious data breaches decades after it was first identified. Always use parameterized queries or prepared statements from application code, and never interpolate raw user input into a SQL string.

Vulnerable vs. safe

SQL

-- NEVER do this — string concatenation with user input:
-- query = "SELECT * FROM users WHERE email = '" + userInput + "'"
-- An input like  ' OR '1'='1  changes the query's meaning entirely.

-- Do this instead — a parameterized query (driver-specific syntax):
-- SELECT * FROM users WHERE email = $1;
-- (the driver sends userInput as a bound parameter, never as SQL text)

Patching and encryption

Keep PostgreSQL updated with minor version security patches — most fixes address specific, disclosed vulnerabilities.
Encrypt data in transit by requiring SSL/TLS for remote connections (see pg_hba.conf above).
Encrypt data at rest using disk/volume-level encryption for the whole cluster, and consider the pgcrypto extension for column-level encryption of especially sensitive individual fields, such as national ID numbers, where even database administrators should not see plaintext.

Row-Level Security (RLS)

Beyond table-level grants, PostgreSQL can restrict access down to individual rows using row-level security policies. This is a genuinely distinctive feature — few databases offer anything this flexible built in — and it's especially valuable for multi-tenant applications, where every tenant's data lives in the same tables but must never be visible to other tenants.

Multi-tenant table restricted with RLS

SQL

CREATE TABLE invoices (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   INT NOT NULL,
  amount      NUMERIC(10, 2) NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Turn on row-level security for the table.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- The application sets this per-connection/session, e.g. right after login.
-- SET app.current_tenant = '42';

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.current_tenant')::INT);

-- A connection with app.current_tenant = '42' can now only see and
-- modify rows where tenant_id = 42 — even a plain "SELECT * FROM invoices"
-- transparently returns only that tenant's rows.

Note

RLS policies apply even to ad-hoc queries and to most tools, which makes them a strong defense-in-depth layer against application bugs that might otherwise leak one tenant's data into another's response. They do not apply to the table owner or superusers unless the table is explicitly configured with `FORCE ROW LEVEL SECURITY`.

Tip

Treat security as layered, not binary: least-privilege roles, network restrictions, parameterized queries, encryption, and RLS each close a different class of mistake. No single control is a substitute for the others.