Postgresql

PostgreSQL Administration

Installation & Setup

# On Linux (Ubuntu/Debian)
sudo apt-get install postgresql postgresql-contrib

# On macOS
brew install postgresql@15

# Docker installation
docker run --name postgres -e POSTGRES_PASSWORD=password -p 5432:5432 -d postgres:15

# Start and enable PostgreSQL
sudo systemctl start postgresql
sudo systemctl enable postgresql

Connection Basics

# Connect to default database
psql -U postgres

# Connect to specific database
psql -U postgres -d mydb -h localhost -p 5432

# List databases
\l

# List tables in current database
\dt

# Get table info
\d table_name

# Quit psql
\q

User & Role Management

-- Create a new role
CREATE ROLE developer WITH LOGIN PASSWORD 'secure_password';

-- Create superuser role
CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'admin_password';

-- Grant privileges on database
GRANT CONNECT ON DATABASE mydb TO developer;

-- Grant privileges on schema
GRANT USAGE ON SCHEMA public TO developer;

-- Grant privileges on tables
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO developer;

-- Grant privileges on specific table
GRANT SELECT ON employees TO developer;

-- Make role a database owner
ALTER DATABASE mydb OWNER TO developer;

-- Revoke privileges
REVOKE INSERT, UPDATE ON employees FROM developer;

-- Drop role
DROP ROLE developer;

Configuration & Tuning

# PostgreSQL configuration file
sudo nano /etc/postgresql/15/main/postgresql.conf

# Key configuration parameters:

# Memory
shared_buffers = 256MB          # 25% of RAM for dedicated server
effective_cache_size = 1GB      # 50-75% of RAM
work_mem = 64MB                 # RAM per operation

# Connections
max_connections = 200
superuser_reserved_connections = 3

# Write-Ahead Log
wal_level = replica
max_wal_senders = 3
wal_keep_segments = 64

# Query planning
random_page_cost = 1.1          # For SSD
log_min_duration_statement = 1000  # Log slow queries

Backup & Recovery

# Full database backup (text format)
pg_dump -U postgres -d mydb -f mydb_backup.sql

# Binary backup (faster, compressed)
pg_dump -U postgres -d mydb -Fc -f mydb_backup.dump

# Backup specific table
pg_dump -U postgres -d mydb -t employees -f employees_backup.sql

# Backup all databases
pg_dumpall -U postgres -f all_databases.sql

# Restore from backup
psql -U postgres -d mydb -f mydb_backup.sql

# Restore from binary dump
pg_restore -U postgres -d mydb mydb_backup.dump

Maintenance Operations

-- VACUUM (reclaim space)
VACUUM;                    -- Full vacuum

-- VACUUM ANALYZE (reclaim space & update stats)
VACUUM ANALYZE;

-- ANALYZE (update table statistics)
ANALYZE;

-- Check database integrity
REINDEX DATABASE mydb;

-- Show database size
SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname))
FROM pg_database;

-- Show table sizes
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
FROM pg_tables
WHERE schemaname != 'pg_catalog'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Monitoring

-- Active connections
SELECT * FROM pg_stat_activity WHERE state != 'idle';

-- Database statistics
SELECT * FROM pg_stat_database WHERE datname = 'mydb';

-- Table statistics
SELECT * FROM pg_stat_user_tables;

-- Index statistics
SELECT * FROM pg_stat_user_indexes;

-- Cache hit ratio
SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit) as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;

Performance Tuning

-- Check slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

-- Find unused indexes
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

-- Find missing indexes
SELECT * FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
AND n_live_tup > 1000;

-- Analyze query plan
EXPLAIN ANALYZE
SELECT * FROM employees WHERE salary > 50000;

Replication Setup

# On primary server - enable replication in postgresql.conf
wal_level = replica
max_wal_senders = 3
wal_keep_segments = 64

# Create replication user
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'rep_password';

# On standby - base backup from primary
pg_basebackup -h primary_host -D /var/lib/postgresql/15/main -U replicator -v -P -W

# Create recovery.conf on standby
standby_mode = 'on'
primary_conninfo = 'host=primary_host port=5432 user=replicator password=password'

High Availability with pgBouncer

# Install pgBouncer
sudo apt-get install pgbouncer

# Configuration - /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=primary_host port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5

Next Steps

Learn advanced security features including row-level security and SSL/TLS configuration in the postgresql-security skill.

postgresql-dba Guide

PostgreSQL Security

Authentication Configuration

# Edit pg_hba.conf (/etc/postgresql/15/main/pg_hba.conf)

# IPv4 local connections
host    all             all             127.0.0.1/32            scram-sha-256

# IPv6 local connections
host    all             all             ::1/128                 scram-sha-256

# Remote TCP connections with password
host    mydb            developer       192.168.1.0/24          scram-sha-256

# Unix socket (local only, most secure)
local   all             all                                      trust

# Require password for local connections
local   all             all                                      scram-sha-256

SSL/TLS Configuration

# Generate self-signed certificate
sudo openssl req -new -x509 -days 365 -nodes \
  -out /etc/postgresql/15/main/server.crt \
  -keyout /etc/postgresql/15/main/server.key

# Set proper permissions
sudo chmod 600 /etc/postgresql/15/main/server.key
sudo chown postgres:postgres /etc/postgresql/15/main/server.*

# Enable SSL in postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/15/main/server.crt'
ssl_key_file = '/etc/postgresql/15/main/server.key'

User & Role Security

-- Create user with restricted permissions
CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password';

-- Revoke default public privileges
REVOKE ALL ON DATABASE mydb FROM PUBLIC;

-- Grant only necessary privileges
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT ON schema_table TO app_user;

-- Create read-only role
CREATE ROLE read_only WITH NOLOGIN;
GRANT CONNECT ON DATABASE mydb TO read_only;
GRANT USAGE ON SCHEMA public TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- Add read-only user with login
CREATE ROLE report_user WITH LOGIN PASSWORD 'password' IN ROLE read_only;

-- Enforce password requirements
ALTER ROLE app_user VALID UNTIL '2025-12-31';
ALTER ROLE app_user WITH CONNECTION LIMIT 5;

Row-Level Security (RLS)

-- Create table with RLS
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2),
  department_id INT,
  created_by VARCHAR(100)
);

-- Enable RLS
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

-- Create policy - users see only their department
CREATE POLICY dept_isolation ON employees
  FOR SELECT
  USING (department_id = (
    SELECT department_id FROM employees
    WHERE created_by = current_user LIMIT 1
  ));

-- Users can only update their own records
CREATE POLICY user_update ON employees
  FOR UPDATE
  USING (created_by = current_user);

-- View policies
\d+ employees

Audit Logging

# Enable query logging in postgresql.conf
log_statement = 'all'           # Log all statements
log_min_duration_statement = 1000  # Log queries over 1 second
log_duration = on               # Log duration
log_connections = on            # Log connections
log_disconnections = on         # Log disconnections
log_lock_waits = on            # Log lock wait times

Privilege Audit

-- Check object privileges
SELECT * FROM information_schema.table_privileges
WHERE table_schema = 'public';

-- Check role membership
SELECT role_name, member_name, admin_option
FROM information_schema.role_table_grants;

-- Find users with superuser privileges
SELECT usename FROM pg_user WHERE usesuper;

-- Check default privileges
SELECT * FROM information_schema.default_privileges;

-- Set default privileges for new tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO read_only;

Extension Security

-- Install security-related extensions

-- Install pgcrypto for encryption
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Encrypt sensitive data
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255),
  password_hash TEXT,
  encrypted_ssn bytea
);

-- Insert encrypted data
INSERT INTO users VALUES (
  1,
  'user@example.com',
  crypt('password', gen_salt('bf')),
  pgp_sym_encrypt('123-45-6789', 'encryption_key')
);

-- Verify password
SELECT email FROM users
WHERE password_hash = crypt('password', password_hash);

-- Install pg_stat_statements for query monitoring
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Compliance & Audit Trail

-- Create audit log table
CREATE TABLE audit_log (
  id SERIAL PRIMARY KEY,
  table_name TEXT,
  operation TEXT,
  username TEXT,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  old_data JSONB,
  new_data JSONB
);

-- Create trigger function for audit
CREATE OR REPLACE FUNCTION audit_function() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, username, old_data, new_data)
  VALUES (TG_TABLE_NAME, TG_OP, current_user,
          row_to_json(OLD), row_to_json(NEW));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger on table
CREATE TRIGGER employees_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION audit_function();

Network Security

# Configure firewall (UFW on Linux)
sudo ufw allow from 192.168.1.0/24 to any port 5432

# Only listen on specific interface
echo "listen_addresses = '192.168.1.10'" >> postgresql.conf

# Disable superuser remote access
# In pg_hba.conf
host    all             postgres        0.0.0.0/0              reject

Best Practices Checklist

✅ Use strong passwords with character requirements ✅ Enable SSL/TLS for remote connections ✅ Implement least privilege principle ✅ Use connection limits to prevent resource exhaustion ✅ Enable audit logging for compliance ✅ Regular backups with encryption ✅ Monitor and alert on failed logins ✅ Use row-level security for multi-tenant apps ✅ Keep PostgreSQL updated ✅ Regular security audits and penetration testing