TL;DR
PostgreSQL is secure by default in many ways, but needs proper configuration. Enable SSL for all connections, use strong passwords, configure pg_hba.conf to limit access, create roles with minimal privileges, and always use parameterized queries. For multi-tenant apps, enable Row Level Security (RLS) to isolate user data.
Connection Security
Secure how clients connect to your database:
Enable SSL/TLS
- Require SSL for all connections in production
- Use certificates from a trusted CA
- Verify server certificates in your application
- Consider client certificate authentication for sensitive systems
Configure pg_hba.conf
The pg_hba.conf file controls who can connect:
- Never allow passwordless local connections in production
- Restrict IP ranges that can connect
- Use scram-sha-256 for password authentication
- Disable trust authentication
Important: Never expose PostgreSQL directly to the internet. Use a VPN, SSH tunnel, or place it behind a firewall with restricted access.
Authentication and Roles
Password Security
- Use strong, unique passwords for each role
- Store connection strings securely (environment variables)
- Rotate passwords periodically
- Consider using secrets managers
Role-Based Access Control
Create separate roles with minimal privileges:
- Application role: Only SELECT, INSERT, UPDATE, DELETE on needed tables
- Read-only role: For analytics and reporting
- Admin role: For schema changes, used sparingly
- Don't use the postgres superuser for applications
Principle of least privilege: Your application should have only the permissions it needs. If it never deletes data, don't grant DELETE. If it only reads certain tables, grant SELECT only on those.
Row Level Security (RLS)
RLS is essential for multi-tenant applications:
What RLS Does
- Automatically filters rows based on policies
- Works at the database level, not application level
- Protects against application bugs exposing data
- Each query is filtered based on current user context
When to Use RLS
- Multi-tenant SaaS applications
- User-owned data (profiles, documents)
- Any table where users should only see their own data
Query Security
Preventing SQL Injection
SQL injection is the most common database vulnerability:
- Always use parameterized queries
- Never concatenate user input into SQL
- Use an ORM that handles escaping
- Validate and sanitize input as a defense in depth
Using Prepared Statements
Prepared statements separate code from data:
Safe query pattern: Use SELECT * FROM users WHERE id = $1 with the parameter passed separately, not SELECT * FROM users WHERE id = '${userId}'.
Data Protection
Encryption at Rest
- Enable disk encryption on database servers
- Consider column-level encryption for sensitive data
- Use pgcrypto for application-level encryption
- Encrypt backups
Sensitive Data Handling
- Hash passwords with bcrypt or argon2
- Consider encrypting PII columns
- Implement data masking for non-production environments
- Log access to sensitive data
Monitoring and Auditing
- Enable query logging for security-sensitive operations
- Monitor for unusual query patterns
- Set up alerts for failed authentication attempts
- Use pg_audit extension for detailed audit logs
Updates and Maintenance
- Keep PostgreSQL updated with security patches
- Review and update roles periodically
- Test backups regularly
- Review pg_hba.conf when infrastructure changes
What are the basic PostgreSQL security steps?
Enable SSL/TLS for connections, use strong passwords, configure pg_hba.conf to restrict access, create separate roles with minimal privileges, and keep PostgreSQL updated. Never run the database as root or expose it directly to the internet.
Should I use Row Level Security in PostgreSQL?
Yes, especially for multi-tenant applications. RLS lets you define policies that automatically filter data based on the current user, preventing users from accessing other users' data even if your application has bugs.
How do I prevent SQL injection in PostgreSQL?
Always use parameterized queries or prepared statements. Never concatenate user input into SQL strings. Use an ORM like Prisma or Drizzle that handles parameterization automatically.
Should I encrypt my PostgreSQL data?
At minimum, use SSL/TLS for connections and disk encryption on the server. For highly sensitive data, consider column-level encryption using pgcrypto. Always encrypt backups.
Using PostgreSQL?
Scan your application for SQL injection and other database vulnerabilities.
Start Free Scan