TL;DR
Postgres ships reasonably safe and then waits for you to configure the rest. Five things carry most of the weight: SSL on every connection, strong passwords, a pg_hba.conf that actually restricts who can connect, roles with the minimum privileges they need, and parameterized queries everywhere. Multi-tenant app? Turn on Row Level Security too, so one user's bug can't read another user's rows.
Connection Security
Start with how clients reach the database at all.
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 decides who gets in.
- 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, and give each one the least it can get away with.
- 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)
If you're building anything multi-tenant, this one isn't optional.
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
It's still the most common database vulnerability, and it still turns up in scans of apps written last month.
- 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 keep your code and your data in separate lanes. That separation is the entire defence.
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: SSL/TLS on connections, and disk encryption on the server. For genuinely sensitive columns, look at pgcrypto. And encrypt the backups, which is the step people skip.
Using PostgreSQL?
Scan your application for SQL injection and other database vulnerabilities.