Learn advanced PostgreSQL security hardening techniques, including access control, encryption, auditing, and secure configuration to protect your database from common threats.
Learn advanced PostgreSQL security hardening techniques, including access control, encryption, auditing, and secure configuration to protect your database from common threats.
Security is a critical aspect of database administration. A properly secured PostgreSQL database
protects sensitive data, prevents unauthorized access, and maintains the integrity and availability
of your systems. In this lesson, we'll explore comprehensive security hardening techniques for
PostgreSQL databases that go beyond the basics.
We'll cover advanced access control, encryption options, auditing mechanisms, and security
best practices that help protect your database from both internal and external threats.
These techniques are essential for any production PostgreSQL deployment, especially those
handling sensitive or regulated data.
Defense in Depth
Implement multiple layers of security controls so that if one fails,
others will still protect your data. This includes network, OS, and
database-level security measures.
Principle of Least Privilege
Grant only the minimum privileges necessary for users and applications
to perform their required functions, limiting the potential impact of
security breaches.
Regular Auditing and Monitoring
Continuously track database activity to detect suspicious behavior,
policy violations, and security incidents as they occur.
Secure Configuration
Start with a secure baseline configuration and maintain it through
proper change management, regular updates, and security patches.
Access Control
Encryption
Effective access control is the foundation of database security. PostgreSQL provides robust
mechanisms for controlling who can access your database and what actions they can perform.
Let's explore advanced access control techniques beyond basic user and role management.
Role-Based Access Control (RBAC)
PostgreSQL's role system is the foundation for implementing Role-Based Access Control (RBAC),
which assigns permissions to roles rather than individual users, making access management
more scalable and maintainable.
RBAC Implementation Best Practices
Use role hierarchies
— Create a structured system of roles with inheritance
Separate duties
— Assign different responsibilities to different roles
Application-specific roles
— Create dedicated roles for each application
Avoid superuser access
— Limit superuser privileges to essential operations
Document role structure
— Maintain clear documentation of your role hierarchy
Here's an example of implementing a sophisticated RBAC model in PostgreSQL:
This RBAC model creates a three-tier hierarchy of roles:
Base permission roles
(readonly, readwrite, admin) that define privilege levels
Functional roles
(app_*) that define permissions for specific applications or functions
Team roles
(finance_team, etc.) that group related functional roles
User roles
(alice, bob, carol) that inherit permissions through membership in team roles
Row-Level Security (RLS)
Row-Level Security (RLS) allows you to restrict access to specific rows in a table based on
the user executing the query. This enables fine-grained access control beyond the traditional
table-level permissions.
RLS Use Cases
Multi-tenant applications
— Ensure each tenant sees only their data
Data privacy
— Limit access to sensitive personal information
Departmental isolation
— Restrict department data to respective teams
Hierarchical data access
— Managers see their reports' data
Time-based restrictions
— Limit access to data based on time periods
Here's how to implement Row-Level Security in PostgreSQL:
RLS Performance Considerations
Row-Level Security adds overhead to queries, especially with complex policies. To optimize performance:
Create appropriate indexes on columns used in policy conditions
Use simple policy expressions when possible
Consider materialized views for complex policy queries
Monitor query performance after implementing RLS
Use RLS bypass roles for batch operations that need to process all rows
Advanced Authentication Methods
PostgreSQL supports several advanced authentication methods beyond basic username/password
authentication. These methods can significantly enhance your database security posture.
Authentication Method
Description
Security Level
SCRAM-SHA-256
Password-based challenge-response mechanism with salting and secure hashing
Default authentication for new PostgreSQL installations
SSL Certificate
Authentication using client certificates over SSL
Server-to-server connections, high-security environments
Authentication against LDAP/Active Directory server
Enterprise environments with centralized identity management
Medium-High
GSSAPI/Kerberos
Single sign-on using Kerberos infrastructure
Enterprise environments with existing Kerberos
Pluggable Authentication Modules for system-level authentication
Linux systems with existing PAM configuration
Medium-High
Authentication against RADIUS server
Organizations with existing RADIUS infrastructure
Medium-High
Authentication based on operating system user
Local connections on Unix-like systems
Authentication based on client's operating system user via ident server
Local connections within trusted network
Configuring SSL Certificate Authentication
SSL certificate authentication is one of the strongest authentication methods available.
Here's how to configure it:
Configure PostgreSQL to use these certificates by modifying postgresql.conf:
Update the pg_hba.conf file to require SSL certificate authentication:
For client connection, use the following connection string:
Setting Up LDAP Authentication
LDAP authentication allows you to centralize user management:
First, install the required packages:
Configure pg_hba.conf to use LDAP authentication:
For secure LDAP (LDAPS), use:
Remember that LDAP users must still have corresponding roles in the PostgreSQL database.
You can create these roles with no password (since authentication is done via LDAP):
1-- Create base roles for different functional areas
2CREATE ROLE readonly;
3CREATE ROLE readwrite;
4CREATE ROLE admin;
5
6-- Create application-specific roles
7CREATE ROLE app_analytics;
8CREATE ROLE app_reporting;
9CREATE ROLE app_transaction;
10
11-- Create team/department roles
12CREATE ROLE finance_team;
13CREATE ROLE marketing_team;
14CREATE ROLE engineering_team;
15
16-- Create individual user roles
17CREATE ROLE alice WITH LOGIN PASSWORD 'secure_password';
18CREATE ROLE bob WITH LOGIN PASSWORD 'secure_password';
19CREATE ROLE carol WITH LOGIN PASSWORD 'secure_password';
20
21-- Set up role hierarchy with GRANT
22GRANT readonly TO readwrite; -- readwrite inherits readonly privileges
23GRANT readwrite TO admin; -- admin inherits readwrite privileges
24
25GRANT app_analytics TO finance_team, marketing_team;
26GRANT app_reporting TO finance_team;
27GRANT app_transaction TO finance_team, engineering_team;
28
29-- Assign users to appropriate team roles
30GRANT finance_team TO alice;
31GRANT marketing_team TO bob;
32GRANT engineering_team TO carol;
33GRANT admin TO carol; -- Carol is also an admin
34
35-- Set up schema-level permissions
36GRANT USAGE ON SCHEMA public TO readonly;
37GRANT USAGE ON SCHEMA analytics TO app_analytics;
38GRANT USAGE ON SCHEMA reporting TO app_reporting;
39
40-- Set up table-level permissions for readonly role
41GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
42GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO app_analytics;
43GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO app_reporting;
44
45-- Set up table-level permissions for readwrite role
46GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
47
48-- Set up function-level permissions
49GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;
50
51-- Automatically apply permissions to new objects
52ALTER DEFAULT PRIVILEGES IN SCHEMA public
53 GRANT SELECT ON TABLES TO readonly;
54
55ALTER DEFAULT PRIVILEGES IN SCHEMA public
56 GRANT INSERT, UPDATE, DELETE ON TABLES TO readwrite;
57
58ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
59 GRANT SELECT ON TABLES TO app_analytics;1-- Create a table with sensitive customer data
2CREATE TABLE customer_data (
3 id SERIAL PRIMARY KEY,
4 customer_id INTEGER NOT NULL,
5 name TEXT NOT NULL,
6 email TEXT,
7 phone TEXT,
8 credit_card TEXT,
9 account_manager TEXT,
10 region TEXT,
11 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
12);
13
14-- Create an application security context function
15-- This function will determine the current tenant context
16CREATE OR REPLACE FUNCTION current_tenant_id()
17RETURNS INTEGER AS $$
18DECLARE
19 tenant_id INTEGER;
20BEGIN
21 -- Get tenant_id from session variable, if set
22 tenant_id := current_setting('app.tenant_id', TRUE);
23
24 -- If not set, try to get it from the current user
25 IF tenant_id IS NULL THEN
26 -- Extract tenant ID from username (if using format tenant_123)
27 tenant_id := substring(current_user from 'tenant_([0-9]+)');
28 END IF;
29
30 RETURN tenant_id;
31END;
32$$ LANGUAGE plpgsql SECURITY DEFINER;
33
34-- Enable Row-Level Security on the table
35ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY;
36
37-- Create policies for different access patterns
38-- 1. Multi-tenant policy: Users can only view their own tenant's data
39CREATE POLICY tenant_isolation ON customer_data
40 USING (customer_id = current_tenant_id());
41
42-- 2. Account manager policy: Account managers can see their assigned customers
43CREATE POLICY account_manager_access ON customer_data
44 TO account_managers
45 USING (account_manager = current_user);
46
47-- 3. Regional access policy: Regional managers see all customers in their region
48CREATE POLICY regional_access ON customer_data
49 TO regional_managers
50 USING (region IN (SELECT region FROM user_regions WHERE username = current_user));
51
52-- 4. Mask sensitive data based on role
53-- First, create a view with column-level security
54CREATE VIEW customer_data_masked AS
55 SELECT
56 id,
57 customer_id,
58 name,
59 CASE WHEN current_user = account_manager THEN email
60 ELSE 'REDACTED' END AS email,
61 CASE WHEN pg_has_role(current_user, 'support_level_2', 'MEMBER') THEN phone
62 ELSE 'REDACTED' END AS phone,
63 CASE WHEN pg_has_role(current_user, 'finance', 'MEMBER') THEN credit_card
64 ELSE 'REDACTED' END AS credit_card,
65 account_manager,
66 region,
67 created_at
68 FROM customer_data;
69
70-- Grant access to the view instead of the base table for most users
71GRANT SELECT ON customer_data_masked TO readonly;
72REVOKE SELECT ON customer_data FROM readonly;
73
74-- 5. Bypass policy for superusers and auditors
75CREATE POLICY superuser_bypass ON customer_data
76 TO postgres, auditor
77 USING (TRUE);1# Step 1: Create a Certificate Authority (CA)
2openssl req -new -x509 -days 365 -nodes -out root.crt -keyout root.key -subj "/CN=PostgreSQL-CA"
3
4# Step 2: Create server certificate and key
5openssl req -new -nodes -out server.csr -keyout server.key -subj "/CN=postgresql.example.com"
6openssl x509 -req -in server.csr -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt
7
8# Step 3: Create client certificate and key
9openssl req -new -nodes -out client.csr -keyout client.key -subj "/CN=dbuser"
10openssl x509 -req -in client.csr -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out client.crt
11
12# Step 4: Set proper permissions
13chmod 600 server.key root.key client.key
14chmod 644 server.crt root.crt client.crt1# Enable SSL
2ssl = on
3ssl_ca_file = 'root.crt'
4ssl_cert_file = 'server.crt'
5ssl_key_file = 'server.key'
6ssl_ciphers = 'HIGH:!aNULL:!MD5'1# TYPE DATABASE USER ADDRESS METHOD
2hostssl all all all cert clientcert=11psql "host=postgresql.example.com dbname=mydb user=dbuser sslmode=require sslcert=client.crt sslkey=client.key"1# For Debian/Ubuntu
2apt-get install libpq-dev libldap2-dev
3
4# For RHEL/CentOS
5yum install postgresql-devel openldap-devel1# Basic LDAP authentication
2host all all 0.0.0.0/0 ldap ldapserver=ldap.example.com ldapprefix="uid=" ldapsuffix=",ou=People,dc=example,dc=com"
3
4# LDAP with search
5host all all 0.0.0.0/0 ldap ldapserver=ldap.example.com ldapbasedn="ou=People,dc=example,dc=com" ldapsearchfilter="(uid=$username)"1host all all 0.0.0.0/0 ldap ldapserver=ldaps://ldap.example.com:636 ldaptls=1 ldapbasedn="ou=People,dc=example,dc=com" ldapsearchfilter="(uid=$username)"1CREATE ROLE ldapuser WITH LOGIN;Encryption is a critical component of a defense-in-depth security strategy. PostgreSQL
offers several options for encrypting data both at rest and in transit. This section
covers the various encryption approaches and their implementation.
Encryption in Transit (TLS/SSL)
Encrypting data in transit protects information as it travels between the PostgreSQL server
and clients. This prevents eavesdropping and man-in-the-middle attacks on your database
connections.
SSL/TLS Benefits
Confidentiality
— Encrypts all data transmitted between client and server
— Ensures data isn't modified in transit
Authentication
— Can verify the identity of both client and server
Compliance
— Helps meet regulatory requirements (PCI-DSS, HIPAA, etc.)
Configuring SSL/TLS in PostgreSQL involves several steps:
Then update pg_hba.conf to require SSL for connections:
SSL Configuration Best Practices
Use TLSv1.2 or newer; disable older protocols (TLSv1.0, TLSv1.1)
Regularly rotate certificates (recommend yearly rotations)
Use strong cipher suites and disable weak ciphers
Protect private keys with appropriate file permissions (600)
Implement certificate revocation checking with CRL
Consider using managed PKI services for certificate management
Test SSL configuration with tools like sslyze or testssl.sh
Encryption at Rest
Encryption at rest protects data stored on disk, preventing unauthorized access if
someone gains physical access to your database files. PostgreSQL offers several
approaches for encryption at rest.
Encryption Method
Description
Full-disk encryption
Entire disk or filesystem is encrypted using OS-level encryption (LUKS, BitLocker, etc.)
Transparent to PostgreSQL
No application changes needed
Low performance overhead
Data decrypted when system is running
No column-level granularity
Key management tied to OS
Tablespace encryption
Encryption at tablespace level using pgcrypto or filesystem-level encryption
More granular than full-disk
Can encrypt specific tables
Relatively transparent
More complex management
Limited to filesystem boundaries
Some performance impact
Column-level encryption
Encrypt specific columns using pgcrypto extension functions
Highest granularity
Only sensitive data encrypted
Key management flexibility
Application changes required
Significant performance impact
Limited indexing capabilities
WAL and backup encryption
Encrypt Write-Ahead Log and backup files
Protects data outside main DB
Safeguards recovery files
Critical for cloud backups
Requires additional tools
Complex key management
Can impact backup processes
Implementing Column-Level Encryption with pgcrypto
Column-level encryption allows you to selectively encrypt sensitive data. Here's how to
implement it using the pgcrypto extension:
Key Management for Column Encryption
The biggest challenge with column-level encryption is secure key management.
Options include:
Environment variables
— Store keys as environment variables
accessed by the database
External key management
— Use services like AWS KMS, HashiCorp Vault,
or other dedicated key management systems
Application-side keys
— Store keys in the application and provide
them at query time
Hardware security modules (HSMs)
— Use dedicated hardware for
key storage and cryptographic operations
Never store encryption keys in database tables or configuration files.
Setting Up Full-Disk Encryption (Linux Example)
Full-disk encryption provides a transparent layer of protection for all data. Here's
how to set it up on Linux using LUKS:
Important Considerations
When using full-disk encryption:
Plan for how to provide the passphrase during system boots
Consider using a key file on a removable device for additional security
Test the boot process thoroughly before deploying to production
Ensure you have secure backup procedures for the encryption keys
Remember that data is only protected when the system is powered off
Encryption Strategy Recommendation
For most production environments, a layered encryption approach provides the best protection:
Full-disk encryption
for the base layer of protection
for all client connections
Column-level encryption
for the most sensitive data fields
Encrypted backups
for off-site storage
This defense-in-depth strategy provides multiple layers of protection against different threat vectors.
1# In postgresql.conf
2ssl = on # Enable SSL
3ssl_cert_file = 'server.crt' # Server certificate
4ssl_key_file = 'server.key' # Server private key
5ssl_ca_file = 'root.crt' # Certificate authority for client verification
6ssl_crl_file = '' # Certificate revocation list
7ssl_prefer_server_ciphers = on # Server chooses the cipher
8ssl_min_protocol_version = 'TLSv1.2' # Minimum TLS protocol version
9ssl_max_protocol_version = '' # Maximum TLS protocol version
10ssl_ciphers = 'HIGH:!aNULL:!eNULL:!LOW:!EXP:!MD5:!RC4' # Allowed cipher suites1# Force SSL for all non-local connections
2hostssl all all 0.0.0.0/0 scram-sha-256
3host all all 0.0.0.0/0 reject
4
5# Allow non-SSL connections only from specific trusted networks
6host all all 10.0.0.0/8 scram-sha-256
7host all all 192.168.0.0/16 scram-sha-2561-- Install the pgcrypto extension
2CREATE EXTENSION pgcrypto;
3
4-- Create a table with encrypted columns
5CREATE TABLE sensitive_data (
6 id SERIAL PRIMARY KEY,
7 username VARCHAR(100) NOT NULL,
8 email VARCHAR(100) NOT NULL,
9 -- Encrypted columns
10 ssn TEXT,
11 credit_card TEXT,
12 medical_notes TEXT
13);
14
15-- Create a function to encrypt data
16CREATE OR REPLACE FUNCTION encrypt_data(data TEXT, key TEXT)
17RETURNS TEXT AS $$
18BEGIN
19 RETURN pgp_sym_encrypt(data, key);
20END;
21$$ LANGUAGE plpgsql SECURITY DEFINER;
22
23-- Create a function to decrypt data
24CREATE OR REPLACE FUNCTION decrypt_data(encrypted_data TEXT, key TEXT)
25RETURNS TEXT AS $$
26DECLARE
27 decrypted_text TEXT;
28BEGIN
29 BEGIN
30 decrypted_text := pgp_sym_decrypt(encrypted_data, key);
31 RETURN decrypted_text;
32 EXCEPTION WHEN OTHERS THEN
33 RETURN NULL; -- Return NULL on decryption failure
34 END;
35END;
36$$ LANGUAGE plpgsql SECURITY DEFINER;
37
38-- Insert with encryption
39INSERT INTO sensitive_data (username, email, ssn, credit_card, medical_notes)
40VALUES (
41 'johndoe',
42 '[email protected]',
43 encrypt_data('123-45-6789', 'your-encryption-key'),
44 encrypt_data('4111-1111-1111-1111', 'your-encryption-key'),
45 encrypt_data('Patient has a history of...', 'your-encryption-key')
46);
47
48-- Query with decryption
49SELECT
50 id,
51 username,
52 email,
53 decrypt_data(ssn, 'your-encryption-key') AS ssn,
54 decrypt_data(credit_card, 'your-encryption-key') AS credit_card,
55 decrypt_data(medical_notes, 'your-encryption-key') AS medical_notes
56FROM sensitive_data;
57
58-- Create a view for authorized users
59CREATE VIEW sensitive_data_decrypted AS
60SELECT
61 id,
62 username,
63 email,
64 decrypt_data(ssn, 'your-encryption-key') AS ssn,
65 decrypt_data(credit_card, 'your-encryption-key') AS credit_card,
66 decrypt_data(medical_notes, 'your-encryption-key') AS medical_notes
67FROM sensitive_data;
68
69-- Restrict access to the view
70GRANT SELECT ON sensitive_data_decrypted TO authorized_users;1# Install required packages
2sudo apt-get install cryptsetup lvm2
3
4# Create an encrypted volume for PostgreSQL data
5# (Use a partition or dedicated disk)
6sudo cryptsetup luksFormat /dev/sdb1
7
8# Open the encrypted volume
9sudo cryptsetup luksOpen /dev/sdb1 pg_encrypted
10
11# Create a filesystem
12sudo mkfs.ext4 /dev/mapper/pg_encrypted
13
14# Create a mount point and mount the filesystem
15sudo mkdir /var/lib/postgresql/encrypted-data
16sudo mount /dev/mapper/pg_encrypted /var/lib/postgresql/encrypted-data
17
18# Set ownership
19sudo chown -R postgres:postgres /var/lib/postgresql/encrypted-data
20
21# Add to fstab for automatic mounting at boot
22echo "/dev/mapper/pg_encrypted /var/lib/postgresql/encrypted-data ext4 defaults 0 2" | sudo tee -a /etc/fstab
23
24# Add to crypttab for automatic opening at boot
25echo "pg_encrypted /dev/sdb1 none luks,discard" | sudo tee -a /etc/crypttab
26
27# Update PostgreSQL configuration to use the new data directory
28sudo -u postgres psql -c "ALTER SYSTEM SET data_directory = '/var/lib/postgresql/encrypted-data';"
29
30# Initialize the PostgreSQL data directory
31sudo -u postgres initdb -D /var/lib/postgresql/encrypted-data
32
33# Start PostgreSQL
34sudo systemctl restart postgresqlComprehensive auditing is essential for security monitoring, compliance, and forensic
analysis. PostgreSQL offers several mechanisms for tracking database activity and
changes, allowing you to detect suspicious behavior and maintain detailed logs for
compliance requirements.
Audit Logging Options
PostgreSQL provides several built-in options for audit logging, each with different
capabilities and performance characteristics.
Audit Method
Capabilities
Performance Impact
PostgreSQL Logs
Query logging
Connection tracking
Error logging
Low to Moderate
Basic auditing, performance monitoring
Trigger-Based Auditing
DML change tracking
Before/after values
User attribution
Detailed change tracking, compliance
pgAudit Extension
Session logging
Object logging
Customizable detail levels
Comprehensive auditing, compliance
Logical Decoding
Transaction-level changes
Replication-based
External processing
Low to Moderate
Real-time audit systems, CDC
Event Triggers
DDL change tracking
Schema modifications
System-level changes
Schema change tracking, configuration management
Implementing pgAudit for Comprehensive Auditing
pgAudit is a PostgreSQL extension that provides detailed session and object-level auditing
capabilities. It's the recommended solution for environments with strict compliance requirements.
First, install the pgAudit extension:
Configure PostgreSQL to load pgAudit by adding it to shared_preload_libraries:
Create the extension in your database:
Configure pgAudit settings in postgresql.conf:
pgAudit Logging Classes
— SELECT and COPY statements
— INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM
— Function and stored procedure calls
— All DDL statements not included in other classes
— GRANT, REVOKE, CREATE/ALTER/DROP ROLE
— Various commands like DISCARD, VACUUM, etc.
— SET and RESET configuration parameters
— All of the above
To enable object-level logging for specific tables:
Performance Considerations
pgAudit can generate substantial log volume, so consider:
Log rotation and archiving for log management
Separate disk for audit logs to avoid I/O contention
Monitoring log space usage to prevent disk filling
Using pgaudit.log_statement_once to reduce duplicate entries
Being selective about which operations to log
Trigger-Based Audit Trail Implementation
Trigger-based auditing captures detailed before/after values for table changes. This approach
is particularly useful when you need to track specific data changes.
Query the audit trail for a specific table and user:
Trigger-Based Auditing Best Practices
Consider auditing only sensitive tables to minimize performance impact
Use partitioning on the audit_trail table for better performance with large volumes
Create indexes on frequently queried audit_trail columns (timestamp, user_id, table_name)
Implement a retention policy to archive or purge old audit data
Consider using column-level triggers if you only need to audit specific columns
Use SECURITY DEFINER for audit functions to ensure they cannot be bypassed
Real-time Activity Monitoring
Beyond historical audit logs, real-time monitoring of database activity is crucial for
detecting and responding to security incidents as they occur.
Key Activity Metrics to Monitor
Failed login attempts
— Multiple failures may indicate brute force attacks
Privilege escalation
— Changes to role memberships or permissions
Schema changes
— Unexpected DDL operations
Access patterns
— Unusual query patterns or data access
High-privilege operations
— Actions performed by superusers
Unusual query volume
— Spikes in query activity
Sensitive data access
— Queries against tables with sensitive information
Here's how to implement real-time activity monitoring using PostgreSQL system views:
Integrate with external monitoring solutions using a simple exporter script:
Security Information and Event Management (SIEM) Integration
For comprehensive security monitoring, integrate PostgreSQL audit logs with your SIEM system:
Use Filebeat, Fluentd, or similar tools to ship PostgreSQL logs to your SIEM
Create correlation rules to detect complex attack patterns
Set up alerts for security-relevant events
Combine database events with network and system logs for context
Implement automatic response workflows for critical security events
Ensure your SIEM retains logs for compliance requirements
1# For Debian/Ubuntu
2sudo apt-get install postgresql-14-pgaudit
3
4# For RHEL/CentOS
5sudo yum install postgresql14-pgaudit
6
7# For source installation
8git clone https://github.com/pgaudit/pgaudit.git
9cd pgaudit
10make install1# In postgresql.conf
2shared_preload_libraries = 'pgaudit'
3
4# Restart PostgreSQL after changing shared_preload_libraries
5# sudo systemctl restart postgresql1CREATE EXTENSION pgaudit;1# Session audit logging - logs statements based on session
2pgaudit.log = 'write, ddl, role, misc, misc_set'
3
4# Object audit logging - logs statements affecting specific objects
5pgaudit.log_catalog = on
6pgaudit.log_parameter = on
7pgaudit.log_relation = on
8pgaudit.log_statement_once = off
9
10# Log level settings
11pgaudit.log_level = log
12logging_collector = on
13log_destination = 'csvlog'
14log_directory = 'pg_log'
15log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
16
17# Reload PostgreSQL configuration
18# SELECT pg_reload_conf();1-- Enable object-level audit logging for a specific role
2ALTER ROLE auditor SET pgaudit.role TO 'auditor';
3
4-- Set up a role for which objects will be audited
5CREATE ROLE auditor;
6
7-- Grant AUDIT privileges on objects to be audited
8GRANT AUDIT ON TABLE sensitive_data TO auditor;1-- Create audit trail table
2CREATE TABLE audit_trail (
3 audit_id SERIAL PRIMARY KEY,
4 table_name TEXT NOT NULL,
5 operation TEXT NOT NULL,
6 user_id TEXT NOT NULL,
7 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
8 old_data JSONB,
9 new_data JSONB,
10 query_txt TEXT
11);
12
13-- Create generic audit function
14CREATE OR REPLACE FUNCTION audit_trigger_func()
15RETURNS TRIGGER AS $$
16DECLARE
17 old_data JSONB = NULL;
18 new_data JSONB = NULL;
19BEGIN
20 IF (TG_OP = 'UPDATE') THEN
21 old_data = to_jsonb(OLD);
22 new_data = to_jsonb(NEW);
23 INSERT INTO audit_trail (table_name, operation, user_id, old_data, new_data, query_txt)
24 VALUES (TG_TABLE_NAME, TG_OP, current_user, old_data, new_data, current_query());
25 RETURN NEW;
26 ELSIF (TG_OP = 'DELETE') THEN
27 old_data = to_jsonb(OLD);
28 INSERT INTO audit_trail (table_name, operation, user_id, old_data, query_txt)
29 VALUES (TG_TABLE_NAME, TG_OP, current_user, old_data, current_query());
30 RETURN OLD;
31 ELSIF (TG_OP = 'INSERT') THEN
32 new_data = to_jsonb(NEW);
33 INSERT INTO audit_trail (table_name, operation, user_id, new_data, query_txt)
34 VALUES (TG_TABLE_NAME, TG_OP, current_user, new_data, current_query());
35 RETURN NEW;
36 END IF;
37 RETURN NULL;
38END;
39$$ LANGUAGE plpgsql SECURITY DEFINER;
40
41-- Apply audit trigger to a table
42CREATE TRIGGER audit_sensitive_data
43AFTER INSERT OR UPDATE OR DELETE ON sensitive_data
44FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();
45
46-- Function to add audit trigger to multiple tables
47CREATE OR REPLACE FUNCTION add_audit_trigger(target_table text)
48RETURNS VOID AS $$
49BEGIN
50 EXECUTE format('
51 CREATE TRIGGER audit_%s
52 AFTER INSERT OR UPDATE OR DELETE ON %s
53 FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();
54 ', target_table, target_table);
55END;
56$$ LANGUAGE plpgsql;
57
58-- Apply audit triggers to multiple tables
59SELECT add_audit_trigger('users');
60SELECT add_audit_trigger('accounts');
61SELECT add_audit_trigger('transactions');1-- Query the audit trail
2SELECT
3 audit_id,
4 timestamp,
5 operation,
6 user_id,
7 old_data,
8 new_data
9FROM audit_trail
10WHERE table_name = 'sensitive_data'
11 AND user_id = 'alice'
12 AND timestamp > '2023-01-01'
13ORDER BY timestamp DESC;
14
15-- Check for specific field changes
16SELECT
17 audit_id,
18 timestamp,
19 operation,
20 user_id,
21 old_data->>'email' AS old_email,
22 new_data->>'email' AS new_email
23FROM audit_trail
24WHERE table_name = 'sensitive_data'
25 AND operation = 'UPDATE'
26 AND old_data->>'email' <> new_data->>'email'
27ORDER BY timestamp DESC;1-- View current activity
2CREATE OR REPLACE VIEW v_database_activity AS
3SELECT
4 pid,
5 usename,
6 application_name,
7 client_addr,
8 backend_start,
9 xact_start,
10 query_start,
11 state,
12 wait_event_type,
13 wait_event,
14 query
15FROM pg_stat_activity
16WHERE state != 'idle';
17
18-- Track failed login attempts
19CREATE TABLE login_failures (
20 id SERIAL PRIMARY KEY,
21 attempt_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
22 username TEXT,
23 ip_address TEXT,
24 application_name TEXT
25);
26
27-- Function to log failed authentication attempts
28CREATE OR REPLACE FUNCTION log_failed_login()
29RETURNS event_trigger AS $$
30BEGIN
31 IF current_query() LIKE '%authentication failed for%' THEN
32 INSERT INTO login_failures (username, ip_address, application_name)
33 VALUES (
34 substring(current_query() from 'authentication failed for "(.+?)"'),
35 client_addr,
36 application_name
37 );
38 END IF;
39END;
40$$ LANGUAGE plpgsql;
41
42-- Monitor for suspicious patterns (example: detect multiple failed logins)
43CREATE OR REPLACE FUNCTION check_login_attacks()
44RETURNS TABLE (
45 username TEXT,
46 ip_address TEXT,
47 attempt_count BIGINT,
48 first_attempt TIMESTAMP,
49 last_attempt TIMESTAMP
50) AS $$
51BEGIN
52 RETURN QUERY
53 SELECT
54 lf.username,
55 lf.ip_address,
56 COUNT(*) AS attempt_count,
57 MIN(lf.attempt_time) AS first_attempt,
58 MAX(lf.attempt_time) AS last_attempt
59 FROM login_failures lf
60 WHERE lf.attempt_time > NOW() - INTERVAL '1 hour'
61 GROUP BY lf.username, lf.ip_address
62 HAVING COUNT(*) > 5;
63END;
64$$ LANGUAGE plpgsql;
65
66-- Create a function to detect suspicious schema changes
67CREATE OR REPLACE FUNCTION detect_suspicious_schema_changes()
68RETURNS TRIGGER AS $$
69BEGIN
70 IF TG_OP = 'DROP' AND current_user != 'db_admin' THEN
71 -- Alert about non-standard user dropping objects
72 INSERT INTO security_alerts (alert_type, description, user_id, object_name)
73 VALUES ('SUSPICIOUS_DROP', 'Unauthorized drop operation', current_user, TG_TABLE_NAME);
74 END IF;
75 RETURN NULL;
76END;
77$$ LANGUAGE plpgsql;
78
79-- Apply event trigger for schema changes
80CREATE EVENT TRIGGER schema_change_monitor ON ddl_command_end
81EXECUTE FUNCTION detect_suspicious_schema_changes();
82
83-- Function to check for sensitive data access
84CREATE OR REPLACE FUNCTION log_sensitive_data_access()
85RETURNS TRIGGER AS $$
86BEGIN
87 INSERT INTO data_access_log (
88 table_name,
89 user_id,
90 query,
91 access_time,
92 ip_address
93 ) VALUES (
94 TG_TABLE_NAME,
95 current_user,
96 current_query(),
97 CURRENT_TIMESTAMP,
98 (SELECT client_addr FROM pg_stat_activity WHERE pid = pg_backend_pid())
99 );
100 RETURN NULL;
101END;
102$$ LANGUAGE plpgsql;
103
104-- Apply trigger to sensitive tables
105CREATE TRIGGER log_pii_access
106AFTER SELECT ON customer_pii
107FOR EACH STATEMENT EXECUTE FUNCTION log_sensitive_data_access();1#!/usr/bin/env python
2import psycopg2
3import json
4import time
5import os
6import requests
7
8# Database connection parameters
9DB_PARAMS = {
10 "dbname": "your_database",
11 "user": "monitor_user",
12 "password": "secure_password",
13 "host": "localhost"
14}
15
16# SIEM or monitoring system API endpoint
17MONITORING_ENDPOINT = "https://your-monitoring-system.com/api/events"
18API_KEY = "your_api_key"
19
20# Queries to monitor database activity
21MONITOR_QUERIES = {
22 "failed_logins": """
23 SELECT username, ip_address, COUNT(*) as attempt_count
24 FROM login_failures
25 WHERE attempt_time > NOW() - INTERVAL '15 minutes'
26 GROUP BY username, ip_address
27 HAVING COUNT(*) > 3
28 """,
29 "privileged_actions": """
30 SELECT pid, usename, query, query_start
31 FROM pg_stat_activity
32 WHERE usename IN ('postgres', 'admin_role')
33 AND state = 'active'
34 """,
35 "sensitive_data_access": """
36 SELECT * FROM data_access_log
37 WHERE access_time > NOW() - INTERVAL '15 minutes'
38 """
39}
40
41def send_to_monitoring(event_type, data):
42 headers = {
43 "Content-Type": "application/json",
44 "Authorization": f"Bearer {API_KEY}"
45 }
46 payload = {
47 "event_type": event_type,
48 "source": "postgresql",
49 "timestamp": time.time(),
50 "data": data
51 }
52 try:
53 response = requests.post(MONITORING_ENDPOINT, headers=headers, json=payload)
54 return response.status_code == 200
55 except Exception as e:
56 print(f"Error sending to monitoring: {e}")
57 return False
58
59def monitor_database():
60 try:
61 conn = psycopg2.connect(**DB_PARAMS)
62 cursor = conn.cursor()
63
64 for event_type, query in MONITOR_QUERIES.items():
65 cursor.execute(query)
66 results = cursor.fetchall()
67
68 if results:
69 column_names = [desc[0] for desc in cursor.description]
70 formatted_results = []
71
72 for row in results:
73 formatted_results.append(dict(zip(column_names, row)))
74
75 send_to_monitoring(event_type, formatted_results)
76
77 conn.close()
78 except Exception as e:
79 print(f"Error monitoring database: {e}")
80
81if __name__ == "__main__":
82 monitor_database()Database security hardening involves a comprehensive set of measures to minimize your
attack surface and protect against common threats. This section provides a detailed
checklist of security hardening steps for PostgreSQL databases.
Network Security
Securing network access to your PostgreSQL instance is the first line of defense
against unauthorized access.
Network Security Hardening Measures
Implementation
Restrict listening addresses
Implement firewall rules
Use connection limits
Implement connection pooling
Use PgBouncer or Pgpool-II to manage connections and prevent DoS attacks
Set up VPN for remote access
Use OpenVPN, WireGuard, or other VPN solutions for secure remote database access
Implement network segmentation
Place database servers in separate network segments with restricted access
Example host-based access control configuration (pg_hba.conf) with restrictive rules:
Review pg_hba.conf Regularly
The pg_hba.conf file is your primary access control mechanism. Review it regularly to:
Remove temporary access rules that are no longer needed
Ensure the most restrictive rules are applied
Verify that rules are ordered correctly (first match applies)
Remove any overly broad network masks (like 0.0.0.0/0)
Ensure strong authentication methods are used
Database Configuration Hardening
Properly configuring PostgreSQL parameters is essential for security. Here's a comprehensive
list of security-related configuration settings.
Recommended Setting
Description
Risk Level
password_encryption
scram-sha-256
Use strongest password hashing available
Enable SSL for all connections
ssl_min_protocol_version
Disable older, vulnerable SSL/TLS versions
ssl_prefer_server_ciphers
Server specifies which ciphers to use
ssl_ciphers
HIGH:!aNULL:!MD5:!RC4
Use strong cipher suites only
log_connections
Log all connection attempts
log_disconnections
Log all session disconnections
log_min_error_statement
Log all SQL that causes errors
log_line_prefix
"%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h "
Detailed log line prefix
log_statement
Log all DDL statements
log_duration
Log the duration of statements
log_min_duration_statement
Log statements running > 1s
track_activities
Enable tracking of user activity
local_preload_libraries
Disable user-controlled libraries
log_error_verbosity
Don't expose context to untrusted users
tcp_keepalives_idle
Detect and close dead connections
Sample hardened postgresql.conf file:
Configuration Verification
After updating your PostgreSQL configuration, verify that your changes are effective:
Operating System Security
The security of the operating system hosting PostgreSQL is crucial for overall database security.
Here are key OS-level hardening measures:
Recommendation
File Permissions
Set PostgreSQL data directory permissions to 700 (only postgres user access)
Set configuration file permissions to 600
Restrict log file permissions to postgres user and DBA group
Ensure WAL files have restricted permissions
User Management
Run PostgreSQL as a dedicated non-root user
Restrict sudo access for the postgres user
Apply principle of least privilege for OS users
Use separate user accounts for different database roles
System Hardening
Keep the OS and all packages up-to-date with security patches
Remove unnecessary services and packages
Enable and configure host-based firewall
Implement SELinux or AppArmor profiles for PostgreSQL
Set up fail2ban or similar for brute force protection
Monitoring & Logging
Enable auditd for system call monitoring
Configure syslog for comprehensive system logging
Set up log rotation to prevent disk filling
Implement file integrity monitoring
Enable process accounting
Filesystem Security
Mount database volumes with noexec, nosuid options where appropriate
Use separate filesystems for data, WAL, and backups
Set up filesystem encryption where required
Implement disk quotas to prevent DoS via disk filling
Example script to verify and fix PostgreSQL file permissions:
Security Benchmark Compliance
Consider implementing security standards and benchmarks for PostgreSQL:
CIS PostgreSQL Benchmark for comprehensive security guidelines
DISA STIG for PostgreSQL if in government or military environments
NIST Database Security Guidelines (SP 800-123)
PCI-DSS requirements if storing payment information
HIPAA requirements if storing healthcare information
These standards provide detailed checklists for securing PostgreSQL deployments.
Application Security Best Practices
Many database security issues originate in application code. Here are best practices for
securing database access from applications:
Application Security Guidelines
Use parameterized queries
— Always use prepared statements to prevent SQL injection
Implement connection pooling
— Use pgBouncer or similar to manage database connections
Apply least privilege
— Use specific database roles for each application function
Secure connection strings
— Never hardcode database credentials in application code
Use SSL for all connections
— Configure applications to require SSL for database connections
Implement query timeouts
— Prevent long-running queries from impacting performance
Audit application-level access
— Log all significant database operations with user context
Implement data validation
— Validate all input before sending to the database
Use database encryption
— Encrypt sensitive data stored in the database
Implement application-level security controls
— Don't rely solely on database security
Example of parameterized query implementation in various languages:
Parameterized Queries Examples
Node.js with pg module:
Python with psycopg2:
Java with JDBC:
SQL Injection Prevention Tips
NEVER concatenate user input directly into SQL strings
Use parameterized queries or prepared statements for ALL user-provided data
If dynamic SQL is absolutely necessary, use input validation and whitelisting
For dynamic table or column names (which can't be parameterized), use whitelists and validation
Consider using an ORM with built-in SQL injection protection
Apply principle of least privilege to minimize damage if SQL injection occurs
Use database input validation with CHECK constraints and triggers
Common Database Security Vulnerabilities in Applications
Vulnerability
Mitigation
SQL Injection
Use parameterized queries, prepared statements, and ORM frameworks
Excessive Privileges
Implement least privilege access for application database users
Hard-coded Credentials
Use environment variables, secret management systems, or secure vaults
Unencrypted Data
Encrypt sensitive data in the database at the column level
Connection String Exposure
Secure connection strings in environment variables or secret stores
Insufficient Logging
Implement comprehensive application-level audit logging
1# In postgresql.conf
2listen_addresses = 'localhost,192.168.1.5' # Specific IPs only1# Using iptables (Linux)
2iptables -A INPUT -p tcp --dport 5432 -s 192.168.1.0/24 -j ACCEPT
3iptables -A INPUT -p tcp --dport 5432 -j DROP1# In postgresql.conf
2max_connections = 100
3superuser_reserved_connections = 31# TYPE DATABASE USER ADDRESS METHOD
2
3# Local connections using Unix socket with peer authentication
4local all postgres peer
5local all all peer
6
7# Allow replication connections from localhost with peer authentication
8local replication all peer
9
10# Allow local IPv4 connections with SCRAM authentication
11host all all 127.0.0.1/32 scram-sha-256
12
13# Allow connections from internal network with client certificates
14hostssl all all 10.0.0.0/8 cert clientcert=1
15
16# Allow application servers with specific users and databases
17hostssl appdb app_user 10.10.0.0/24 scram-sha-256
18
19# Allow monitoring from monitoring server
20host all monitor_user 10.20.30.40/32 scram-sha-256
21
22# Allow replication from replica servers
23hostssl replication replication 10.0.0.5/32 scram-sha-256
24
25# Allow admin connections only via GSSAPI
26hostssl all +admin_role 10.0.5.0/24 gss
27
28# Reject all other connections
29host all all all reject1# Security-Hardened PostgreSQL Configuration
2
3# Connection Settings
4listen_addresses = 'localhost,192.168.1.5'
5port = 5432
6max_connections = 100
7superuser_reserved_connections = 3
8unix_socket_directories = '/var/run/postgresql'
9unix_socket_permissions = 0700
10unix_socket_group = ''
11
12# Authentication
13authentication_timeout = 60
14password_encryption = scram-sha-256
15db_user_namespace = off
16
17# SSL Settings
18ssl = on
19ssl_cert_file = 'server.crt'
20ssl_key_file = 'server.key'
21ssl_ca_file = 'root.crt'
22ssl_min_protocol_version = 'TLSv1.2'
23ssl_prefer_server_ciphers = on
24ssl_ciphers = 'HIGH:!aNULL:!MD5:!RC4'
25
26# Network Security
27tcp_keepalives_idle = 300
28tcp_keepalives_interval = 60
29tcp_keepalives_count = 10
30
31# Process Security
32track_activities = on
33track_counts = on
34track_io_timing = on
35track_functions = all
36track_activity_query_size = 2048
37
38# Logging for Auditing
39log_destination = 'csvlog'
40logging_collector = on
41log_directory = 'pg_log'
42log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
43log_file_mode = 0600
44log_truncate_on_rotation = off
45log_rotation_age = 1d
46log_rotation_size = 100MB
47
48log_statement = 'ddl'
49log_min_error_statement = 'error'
50log_min_duration_statement = 1000
51log_connections = on
52log_disconnections = on
53log_duration = on
54log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
55log_lock_waits = on
56log_checkpoints = on
57log_autovacuum_min_duration = 0
58log_error_verbosity = default
59log_timezone = 'UTC'
60
61# Restrict Features
62local_preload_libraries = ''1-- Check current parameter settings
2SELECT name, setting, unit, context, short_desc
3FROM pg_settings
4WHERE name IN (
5 'password_encryption', 'ssl', 'ssl_min_protocol_version',
6 'log_connections', 'log_disconnections', 'log_statement'
7);
8
9-- Check if SSL is being used for current connections
10SELECT datname, usename, ssl, client_addr, application_name
11FROM pg_stat_ssl
12JOIN pg_stat_activity USING (pid)
13WHERE pid <> pg_backend_pid();1#!/bin/bash
2# PostgreSQL security hardening script
3
4# Get PostgreSQL data directory
5PG_DATA_DIR=$(psql -t -c "SHOW data_directory;" | xargs)
6PG_CONFIG_FILE=$(psql -t -c "SHOW config_file;" | xargs)
7PG_HBA_FILE=$(psql -t -c "SHOW hba_file;" | xargs)
8
9echo "Checking permissions on PostgreSQL directories and files..."
10
11# Fix data directory permissions
12echo "Setting permissions on data directory: $PG_DATA_DIR"
13chmod 700 "$PG_DATA_DIR"
14chown -R postgres:postgres "$PG_DATA_DIR"
15
16# Fix configuration file permissions
17echo "Setting permissions on configuration files"
18chmod 600 "$PG_CONFIG_FILE"
19chmod 600 "$PG_HBA_FILE"
20chown postgres:postgres "$PG_CONFIG_FILE" "$PG_HBA_FILE"
21
22# Set correct permissions on log directory (assuming default location)
23PG_LOG_DIR="/var/log/postgresql"
24if [ -d "$PG_LOG_DIR" ]; then
25 echo "Setting permissions on log directory: $PG_LOG_DIR"
26 chmod 750 "$PG_LOG_DIR"
27 chown postgres:postgres "$PG_LOG_DIR"
28 chmod 640 "$PG_LOG_DIR"/*
29 chown postgres:postgres "$PG_LOG_DIR"/*
30fi
31
32# Check for world-writable directories in PostgreSQL paths
33echo "Checking for world-writable directories in path to PostgreSQL files..."
34find "$(dirname $PG_DATA_DIR)" -type d -perm -o=w -print
35
36# Verify no unauthorized users in postgres group
37echo "Users in postgres group:"
38grep -Po '^postgres:.*:\K([^:]+)' /etc/group
39
40echo "Security check complete."1const { Pool } = require('pg')
2const pool = new Pool()
3
4async function getUserData(userId) {
5 try {
6 // Parameterized query - SAFE
7 const query = 'SELECT name, email FROM users WHERE user_id = $1'
8 const result = await pool.query(query, [userId])
9 return result.rows[0]
10 } catch (err) {
11 console.error('Database error:', err)
12 throw err
13 }
14}
15
16// DANGEROUS - NEVER DO THIS
17function unsafeGetUserData(userId) {
18 const query = \`SELECT name, email FROM users WHERE user_id = \${userId}\` // SQL Injection risk
19 return pool.query(query)
20}1import psycopg2
2from psycopg2.extras import RealDictCursor
3
4def get_user_data(user_id):
5 try:
6 conn = psycopg2.connect("dbname=app user=app_user")
7 cur = conn.cursor(cursor_factory=RealDictCursor)
8
9 # Parameterized query - SAFE
10 query = "SELECT name, email FROM users WHERE user_id = %s"
11 cur.execute(query, (user_id,))
12
13 result = cur.fetchone()
14 cur.close()
15 conn.close()
16 return result
17 except Exception as e:
18 print(f"Database error: {e}")
19 raise
20
21# DANGEROUS - NEVER DO THIS
22def unsafe_get_user_data(user_id):
23 conn = psycopg2.connect("dbname=app user=app_user")
24 cur = conn.cursor(cursor_factory=RealDictCursor)
25
26 # SQL Injection risk
27 query = f"SELECT name, email FROM users WHERE user_id = {user_id}"
28 cur.execute(query)
29
30 result = cur.fetchone()
31 cur.close()
32 conn.close()
33 return result1import java.sql.Connection;
2import java.sql.PreparedStatement;
3import java.sql.ResultSet;
4import java.sql.SQLException;
5import javax.sql.DataSource;
6
7public class UserRepository {
8 private final DataSource dataSource;
9
10 public UserRepository(DataSource dataSource) {
11 this.dataSource = dataSource;
12 }
13
14 public User getUserData(Long userId) throws SQLException {
15 // Parameterized query - SAFE
16 String query = "SELECT name, email FROM users WHERE user_id = ?";
17
18 try (Connection conn = dataSource.getConnection();
19 PreparedStatement stmt = conn.prepareStatement(query)) {
20
21 stmt.setLong(1, userId);
22 ResultSet rs = stmt.executeQuery();
23
24 if (rs.next()) {
25 User user = new User();
26 user.setName(rs.getString("name"));
27 user.setEmail(rs.getString("email"));
28 return user;
29 }
30 return null;
31 }
32 }
33
34 // DANGEROUS - NEVER DO THIS
35 public User unsafeGetUserData(String userId) throws SQLException {
36 // SQL Injection risk
37 String query = "SELECT name, email FROM users WHERE user_id = " + userId;
38
39 try (Connection conn = dataSource.getConnection();
40 Statement stmt = conn.createStatement();
41 ResultSet rs = stmt.executeQuery(query)) {
42
43 if (rs.next()) {
44 User user = new User();
45 user.setName(rs.getString("name"));
46 user.setEmail(rs.getString("email"));
47 return user;
48 }
49 return null;
50 }
51 }
52}Learn about Key Takeaways.
Implement comprehensive Role-Based Access Control (RBAC)
Use Row-Level Security (RLS) for fine-grained access control
Utilize strong authentication methods like SCRAM-SHA-256 or certificate-based auth
Restrict database access with restrictive pg_hba.conf rules
Apply the principle of least privilege across all access levels
Encrypt data in transit using SSL/TLS (minimum TLSv1.2)
Apply layered encryption at rest using file system and column-level encryption
Secure sensitive data using pgcrypto for column-level encryption
Implement secure key management practices
Encrypt backups and WAL files to protect data outside the database
Implement comprehensive audit logging with pgAudit
Use trigger-based auditing for detailed change tracking
Monitor database activity in real-time
Integrate with SIEM systems for centralized security monitoring
Maintain audit logs for compliance and forensic purposes
Secure PostgreSQL configuration with hardened settings
Implement network security controls and firewall rules
Apply operating system security best practices
Enforce application security with parameterized queries
Conduct regular security assessments and updates
PostgreSQL Documentation: Security
The official PostgreSQL documentation on security features and best practices.
OWASP Database Security Cheat Sheet
Comprehensive guidelines for securing database systems.
CIS PostgreSQL Benchmark
Detailed security configuration guidelines for PostgreSQL.
pgAudit Documentation
In-depth information on implementing PostgreSQL auditing.
PostgreSQL Security Best Practices Guide
Comprehensive security recommendations for PostgreSQL deployments.
Securing PostgreSQL
Detailed guide on PostgreSQL security hardening techniques.
Your progress is saved in this browser, on this device only.