Database Security Best Practices Cheat Sheet
Covers access control, encryption at rest and in transit, SQL injection prevention, and auditing practices for securing production databases.
3 PagesIntermediateMar 10, 2026
Access Control Fundamentals
Core principles for limiting who and what can reach your data.
- Principle of least privilege- Grant each user or service account only the minimum permissions needed for its job; avoid blanket SUPERUSER/root-equivalent access.
- Role-based access control (RBAC)- Define roles (readonly, app_writer, dba) with specific grants, then assign users to roles instead of granting permissions directly.
- Separate app and admin credentials- Application connection strings should use a low-privilege service account distinct from the credentials DBAs use for schema changes.
- Rotate credentials- Rotate database passwords and API keys on a schedule, and immediately after any suspected exposure such as a key committed to a repo.
- Multi-factor authentication- Enforce MFA for human access to the database console or admin panel (e.g. cloud provider IAM), not just the database password.
- Network isolation- Place databases in private subnets/VPCs with no public internet exposure; only allow inbound access from application servers via security groups or firewall rules.
Least-Privilege Roles (PostgreSQL)
Create a read-only role and lock down default public schema access.
sql
-- Create a read-only role for reportingCREATE ROLE readonly NOLOGIN;GRANT CONNECT ON DATABASE shop TO readonly;GRANT USAGE ON SCHEMA public TO readonly;GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;-- Create an application user and attach the roleCREATE USER app_reporting WITH PASSWORD 'use-a-secrets-manager' LOGIN;GRANT readonly TO app_reporting;-- Harden the public schema (older PostgreSQL versions grant CREATE on it by default)REVOKE ALL ON SCHEMA public FROM PUBLIC;
Encryption
Protecting data at rest, in transit, and in backups.
- Encryption at rest- Enable disk/volume-level encryption (AWS RDS storage encryption, LUKS) so data files are unreadable if the underlying disk is stolen.
- Encryption in transit- Require TLS/SSL for all client connections (sslmode=require or verify-full in Postgres) to prevent credentials and data from being sniffed on the network.
- Column-level encryption- Encrypt highly sensitive fields (SSNs, card numbers) at the application layer or with extensions like pgcrypto, so even DBAs with table access can't read plaintext.
- Transparent Data Encryption (TDE)- Available in SQL Server, Oracle, and some managed MySQL/Postgres offerings; encrypts data files and backups automatically with no application changes.
- Encrypted backups- Ensure backups and snapshots inherit encryption — an unencrypted backup of an encrypted database defeats the purpose.
- Secrets management- Never hardcode database credentials in code or config files; use a secrets manager (Vault, AWS Secrets Manager) with automatic rotation.
Preventing SQL Injection
Always use parameterized queries; never build SQL with string concatenation.
python
import psycopg2conn = psycopg2.connect(dbname="shop", user="app_reporting")cur = conn.cursor()# SAFE: parameterized query — the driver escapes the valuecur.execute("SELECT * FROM orders WHERE customer_id = %s", (customer_id,))# UNSAFE: never build SQL via string formatting/concatenation# cur.execute(f"SELECT * FROM orders WHERE customer_id = {customer_id}")# Named parameters work the same waycur.execute( "SELECT * FROM orders WHERE status = %(status)s AND amount > %(min)s", {"status": "paid", "min": 100},)
Auditing & Monitoring
Detecting misuse and verifying your safeguards actually work.
- Enable audit logging- Turn on native audit logs (pgaudit for Postgres, MySQL Enterprise Audit, SQL Server Audit) to record who ran what query and when.
- Log failed logins- Alert on repeated authentication failures, which can indicate brute-force or credential-stuffing attempts.
- Monitor privilege escalation- Alert whenever a role is granted SUPERUSER/DBA-equivalent privileges outside of a change-controlled process.
- Patch promptly- Apply database engine security patches quickly; unpatched CVEs in the DB engine itself are a common breach vector.
- Test backup integrity- Periodically restore backups to verify they're valid and untampered, not just that the backup job reported success.
Column-Level Dynamic Masking
Mask sensitive columns for non-privileged roles using PostgreSQL security-barrier views instead of exposing raw data.
sql
-- Security-barrier view masks card numbers and emails for the support roleCREATE VIEW customers_masked WITH (security_barrier) ASSELECT id, left(email, 2) || '***@' || split_part(email, '@', 2) AS email, 'xxxx-xxxx-xxxx-' || right(card_number, 4) AS card_number, created_atFROM customers;REVOKE SELECT ON customers FROM support_role;GRANT SELECT ON customers_masked TO support_role;-- pgcrypto for column-level encryption of true secretsCREATE EXTENSION IF NOT EXISTS pgcrypto;UPDATE customers SET ssn_encrypted = pgp_sym_encrypt(ssn, current_setting('app.enc_key'));
Enforcing Full TLS Verification
sslmode=require alone does not verify the server certificate; use verify-full to also stop MITM attacks.
bash
# WEAK: encrypts the connection but does not verify server identitypsql "host=db.internal dbname=shop user=app sslmode=require"# STRONG: verifies the cert chain AND that the hostname matches the certpsql "host=db.internal dbname=shop user=app sslmode=verify-full sslrootcert=ca.pem"# Reject non-TLS connections entirely server-side (pg_hba.conf)# hostssl shop app 10.0.0.0/8 scram-sha-256# host all all all reject
pgaudit Statement-Level Auditing
Configure pgaudit to log DDL and role/permission changes for compliance-grade audit trails.
sql
-- postgresql.conf-- shared_preload_libraries = 'pgaudit'CREATE EXTENSION pgaudit;-- Log all DDL, role changes, and writes; leave reads to session logging if neededALTER SYSTEM SET pgaudit.log = 'ddl, role, write';ALTER SYSTEM SET pgaudit.log_catalog = off;ALTER SYSTEM SET pgaudit.log_parameter = on;-- Fine-grained per-role auditing for a sensitive tableGRANT SELECT ON payments TO auditor_watched;ALTER ROLE auditor_watched SET pgaudit.log = 'read';
Dynamic Short-Lived Credentials with Vault
Issue per-request database credentials that auto-expire instead of long-lived shared passwords.
bash
# Configure Vault's database secrets engine against PostgreSQLvault secrets enable databasevault write database/config/shop-db \ plugin_name=postgresql-database-plugin \ connection_url="postgresql://{{username}}:{{password}}@db:5432/shop" \ allowed_roles="app-readwrite" \ username="vault_admin" password="..."vault write database/roles/app-readwrite \ db_name=shop-db \ creation_statements="CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; GRANT app_writer TO \"{{name}}\";" \ default_ttl="1h" max_ttl="24h"# Application requests fresh, auto-revoking credentials at connect timevault read database/creds/app-readwrite
Advanced Threat Vectors
Attack patterns beyond textbook string-concatenation SQL injection that harden systems still miss.
- Second-order SQL injection- Malicious input is safely stored (e.g. via parameterized insert) but later concatenated unsafely into a different query — auditing must cover every read path, not just the write path.
- Blind / time-based injection- Attackers infer data via boolean or timing side-channels (e.g. `pg_sleep()`) when the app suppresses error output; rate-limit and monitor for abnormal query latency patterns.
- NoSQL / operator injection- Passing raw JSON request bodies as MongoDB query operators (e.g. `{"$gt": ""}`) bypasses auth checks; always validate/sanitize types before building queries, even on 'schemaless' stores.
- Privilege escalation via views/functions- SECURITY DEFINER functions or views owned by a high-privilege role can leak elevated access to lower-privilege callers if not carefully scoped with `SET search_path` and minimal grants.
- Connection string leakage- Credentials embedded in ORM connection strings often end up in crash logs or APM traces; scrub secrets from logging pipelines and use env-injected credentials instead.
- Backup exfiltration- Backups are a common blind spot for access control — apply the same least-privilege and encryption rules to snapshot storage (S3 buckets, backup servers) as to the live database.
Pro Tip
Enable row-level security (RLS) in PostgreSQL for multi-tenant applications — it enforces tenant isolation inside the database itself, so a bug in an application-layer WHERE clause can't leak another tenant's rows.
Was this cheat sheet helpful?
Explore Topics
#DatabaseSecurityBestPractices#DatabaseSecurityBestPracticesCheatSheet#Database#Intermediate#AccessControlFundamentals#Least#Privilege#Roles#Databases#Security#APIs#CheatSheet#SkillVeris
