On this page

Security and Backups

12 min read TextCh. 5 — SQL in Production

Security and Backups

A database is only as good as its security and recovery capabilities. PostgreSQL provides a comprehensive role-based access control system, row-level security for fine-grained data isolation, and several backup strategies for disaster recovery.

This lesson covers everything you need to deploy a PostgreSQL database securely and maintain reliable backups.

The PostgreSQL Role System

PostgreSQL uses roles for both authentication (who can connect) and authorization (what they are allowed to do). A role with the LOGIN attribute is traditionally called a "user," but the underlying system uses a single concept.

Creating Roles

-- Create a login role (user)
CREATE ROLE alice LOGIN PASSWORD 'secure_password_here';

-- Create a group role (no login, used to bundle privileges)
CREATE ROLE developers;

-- Add a login role to a group
GRANT developers TO alice;

-- Create a read-only user
CREATE ROLE readonly_user LOGIN PASSWORD 'another_password'
    NOSUPERUSER NOCREATEDB NOCREATEROLE;

Role Attributes

Attribute Meaning
LOGIN Can establish a database connection
SUPERUSER Bypasses all permission checks
CREATEDB Can create new databases
CREATEROLE Can create and manage roles
REPLICATION Can initiate streaming replication
BYPASSRLS Bypasses row-level security

Always minimize privileges: give each role only the attributes it needs.

GRANT and REVOKE

GRANT gives privileges to a role. REVOKE removes them.

Database and Schema Privileges

-- Allow a role to connect to a database
GRANT CONNECT ON DATABASE production TO app_readonly;

-- Allow a role to use (see) objects in a schema
GRANT USAGE ON SCHEMA public TO app_readonly;

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

-- Also grant for future tables (default privileges)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO app_readonly;

Table Privileges

The available table privileges are: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER.

-- Application user: read and write orders, read-only products
CREATE ROLE app_writer LOGIN PASSWORD 'strong_pass';
GRANT CONNECT ON DATABASE production TO app_writer;
GRANT USAGE ON SCHEMA public TO app_writer;
GRANT SELECT, INSERT, UPDATE ON orders, order_items, customers TO app_writer;
GRANT SELECT ON products, categories TO app_writer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_writer;

-- Revoke a privilege that was granted in error
REVOKE DELETE ON orders FROM app_writer;

Column-Level Privileges

You can grant privileges on specific columns:

-- Allow the reporting role to see product names and prices, but not cost_price
GRANT SELECT (name, category, price, stock) ON products TO reporting_role;

-- The reporting role cannot SELECT * — only the allowed columns

Row Level Security (RLS)

Row Level Security is a powerful PostgreSQL feature that filters which rows a role can see or modify, enforced transparently at the database level.

Enabling RLS

-- Enable RLS on a table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- By default, after enabling RLS, the table owner can still see all rows
-- Other roles see nothing until a policy is created

Creating Policies

-- Policy: each user can only see their own orders
CREATE POLICY orders_self_access ON orders
    USING (customer_id = (
        SELECT customer_id FROM users WHERE username = current_user
    ));

-- Separate policies for SELECT vs INSERT/UPDATE/DELETE
CREATE POLICY orders_select ON orders
    FOR SELECT
    USING (customer_id = current_setting('app.user_id')::INTEGER);

CREATE POLICY orders_insert ON orders
    FOR INSERT
    WITH CHECK (customer_id = current_setting('app.user_id')::INTEGER);

Multi-Tenant RLS

RLS is the standard approach for multi-tenant SaaS databases where all tenants share the same tables:

-- Every table has a tenant_id
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY projects_tenant ON projects
    USING (tenant_id = current_setting('app.tenant_id', TRUE)::INTEGER)
    WITH CHECK (tenant_id = current_setting('app.tenant_id', TRUE)::INTEGER);

-- Application sets the tenant context at connection start
-- SET LOCAL app.tenant_id = '42';

Superusers and BYPASSRLS

Superusers bypass RLS by default. For administrative roles that should also respect RLS, use:

-- Make a role bypass RLS (use carefully)
ALTER ROLE admin_user BYPASSRLS;

-- Make a role respect RLS even if it's a table owner
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

Backups with pg_dump

pg_dump is the standard tool for logical backups of PostgreSQL databases. It exports the schema and data as SQL statements or a compressed binary archive.

# Plain SQL dump (human-readable, can be piped to psql)
pg_dump -U postgres -d myapp > backup_2026_04_02.sql

# Custom format (compressed, supports parallel restore with pg_restore)
pg_dump -U postgres -d myapp -F custom -f backup_2026_04_02.dump

# Dump with verbose output
pg_dump -U postgres -d myapp -F custom -v -f backup.dump

# Dump only the schema (no data)
pg_dump -U postgres -d myapp --schema-only -f schema_only.sql

# Dump only specific tables
pg_dump -U postgres -d myapp -t orders -t order_items -F custom -f orders_backup.dump

Restoring from pg_dump

# Restore a plain SQL dump
psql -U postgres -d newdb < backup_2026_04_02.sql

# Restore a custom format dump (supports parallelism)
pg_restore -U postgres -d newdb -j 4 backup_2026_04_02.dump

# Restore with verbose output
pg_restore -U postgres -d newdb -v backup.dump

Automating Backups

#!/bin/bash
# Scheduled backup script (run via cron)
BACKUP_DIR="/var/backups/postgres"
DB_NAME="myapp"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump"

pg_dump -U postgres -F custom -f "$BACKUP_FILE" "$DB_NAME"

# Remove backups older than 30 days
find "$BACKUP_DIR" -name "*.dump" -mtime +30 -delete

echo "Backup completed: $BACKUP_FILE"

pg_dumpall — Cluster-Level Backups

pg_dumpall backs up all databases in a PostgreSQL cluster, including roles and tablespace definitions — information that pg_dump (which is per-database) does not include:

# Full cluster backup including roles and all databases
pg_dumpall -U postgres > full_cluster_backup.sql

# Backup only global objects (roles, tablespaces)
pg_dumpall -U postgres --globals-only > globals.sql

Physical Backups and Point-in-Time Recovery

For large databases where pg_dump takes too long, or when you need Point-in-Time Recovery (PITR), use base backups with WAL archiving:

# Create a base backup with pg_basebackup
pg_basebackup -U replication_user -D /var/backups/pg_base -Ft -z -P

# WAL archiving is configured in postgresql.conf:
# archive_mode = on
# archive_command = 'cp %p /var/archive/%f'
# wal_level = replica

With WAL archiving, you can restore the database to any point in time — not just the last backup. This is essential for production systems where the last 30 seconds of transactions before a failure must be recoverable.

Security Checklist for Production

Before deploying PostgreSQL to production, verify:

  • The postgres superuser has a strong password
  • Application connects as a role with minimal privileges
  • No role has SUPERUSER except the DBA role
  • pg_hba.conf restricts connections to known IPs only
  • RLS is enabled on tables containing sensitive user data
  • SSL is enabled (ssl = on in postgresql.conf)
  • Automated backups run and are tested weekly
  • Backup files are stored offsite (different cloud region or provider)
  • pg_hba.conf uses scram-sha-256 authentication, not md5 or trust
  • All passwords are randomly generated and stored in a secrets manager

Security and backups are the unglamorous but critical part of database operations. Get them right before your first user signs up, not after your first incident. The final lesson puts everything together in a complete e-commerce database design project.

Never Use the postgres Superuser in Applications
The postgres superuser can do anything: drop tables, bypass RLS, alter any role, and delete the entire database. Application code should connect as a role with only the specific privileges it needs. If an application is compromised, the damage is limited to what that role is allowed to do.
Use pg_dump for Logical Backups
pg_dump exports a database as SQL statements or a custom binary format that can be restored with pg_restore. Run it regularly and test your restores — a backup you have never restored is a backup you cannot trust. Store backups offsite in a separate cloud region or provider.
Row Level Security for Multi-Tenancy
RLS policies are attached to the table and enforced by the database engine, not the application. Even if your application has a bug that forgets to filter by tenant_id, RLS ensures users can only see their own data. Set the current user/tenant ID with SET LOCAL app.current_user_id = '123' at the start of each connection.
sql
-- Create a dedicated application role with minimal privileges
CREATE ROLE app_user LOGIN PASSWORD 'strong_random_password_here';

-- Grant connect to the database
GRANT CONNECT ON DATABASE myapp TO app_user;

-- Grant usage on the schema
GRANT USAGE ON SCHEMA public TO app_user;

-- Grant only the needed privileges on specific tables
GRANT SELECT, INSERT, UPDATE ON TABLE orders    TO app_user;
GRANT SELECT, INSERT, UPDATE ON TABLE customers TO app_user;
GRANT SELECT                 ON TABLE products  TO app_user;

-- Grant on sequences (needed for SERIAL/IDENTITY columns)
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Read-only role for analytics and reporting
CREATE ROLE read_only LOGIN PASSWORD 'another_strong_password';
GRANT CONNECT ON DATABASE myapp TO read_only;
GRANT USAGE ON SCHEMA public TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- Row Level Security: users can only see their own data
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY orders_user_isolation ON orders
    USING (customer_id = current_setting('app.current_user_id')::INTEGER);

-- RLS for a multi-tenant SaaS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY documents_tenant_isolation ON documents
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::INTEGER)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::INTEGER);

-- Revoke a privilege
REVOKE UPDATE ON TABLE products FROM app_user;

-- Drop a policy
DROP POLICY orders_user_isolation ON orders;

-- View current role permissions
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'app_user'
ORDER BY table_name, privilege_type;