PostgreSQL is a powerful and reliable database, but a secure database doesn’t happen automatically.
In a production application, PostgreSQL may contain sensitive information such as:
- User accounts
- Customer information
- Orders
- Payments
- Business data
- Application credentials
- Internal configuration
- Audit records
A database breach can expose far more than just individual records. Attackers may gain access to an application’s entire data layer if database credentials, permissions, or network security are poorly configured.
That’s why PostgreSQL security should be considered at multiple levels:
Application
↓
Authentication
↓
Network
↓
PostgreSQL
↓
Roles & Permissions
↓
Data
↓
Backups
In this guide, we’ll cover practical PostgreSQL security best practices for production applications, including authentication, roles, least privilege, SSL/TLS, network restrictions, Row-Level Security, password management, SQL injection prevention, backups, logging, monitoring, and common security mistakes.
1. Use the Principle of Least Privilege
One of the most important PostgreSQL security principles is:
Give each user or application only the permissions it actually needs.
Avoid using a superuser account for your application.
For example, don’t configure your production application to connect as:
postgres
Instead, create a dedicated application role:
CREATE ROLE app_user
LOGIN
PASSWORD 'strong-password';
Then grant only the required permissions.
For example:
GRANT CONNECT ON DATABASE myapp TO app_user;
And:
GRANT USAGE ON SCHEMA public TO app_user;
The application should not automatically have unrestricted access to the entire database.
2. Avoid Using the PostgreSQL Superuser
The PostgreSQL superuser has extremely powerful privileges.
A superuser can bypass many normal permission checks.
If an application is compromised and its database account is a superuser, the attacker may gain extensive control over the database.
Instead:
Application
↓
Limited Role
↓
Only required permissions
Keep administrative accounts separate from application accounts.
3. Create Separate Roles for Different Applications
If multiple services use the same PostgreSQL database, avoid giving them all the same credentials.
For example:
api_service
reporting_service
migration_service
admin_service
Each role can have different permissions.
Conceptually:
API
↓
api_user
Reporting
↓
reporting_user
Migration
↓
migration_user
This limits the impact if one service is compromised.
4. Use Strong Passwords
Database passwords should be:
- Long
- Random
- Unique
- Not reused elsewhere
- Stored securely
Avoid passwords such as:
postgres123
password
admin123
myapp2026
Use a secrets manager or secure environment configuration instead.
For example:
DATABASE_PASSWORD=<secret>
Do not commit database passwords to Git repositories.
5. Never Hardcode Database Credentials
Avoid:
const password = "myProductionPassword";
Instead, load credentials securely:
const password = process.env.DB_PASSWORD;
A better production setup can use a dedicated secret-management system.
Conceptually:
Application
↓
Secret Manager
↓
Database Credentials
This makes credential rotation and access management easier.
6. Rotate Database Credentials
Passwords shouldn’t necessarily remain unchanged forever.
Have a process for rotating:
Database passwords
API credentials
Certificates
Access tokens
Secrets
When rotating credentials, make sure applications can transition without unnecessary downtime.
For larger deployments, automated secret rotation can reduce operational risk.
7. Encrypt Connections With SSL/TLS
Database traffic should be protected when it travels across networks.
Without encryption:
Application ─────── PostgreSQL
With TLS:
Application
↓
Encrypted TLS Connection
↓
PostgreSQL
PostgreSQL supports SSL/TLS connections.
For production systems, especially when application servers and databases communicate across networks, encryption in transit is an important security measure.
8. Don’t Trust Any Network by Default
PostgreSQL’s pg_hba.conf controls client authentication and access rules.
For example:
host all all 0.0.0.0/0 scram-sha-256
may be far broader than necessary.
A better approach is to restrict access to known application networks.
Conceptually:
Application Network
↓
PostgreSQL
rather than:
Internet
↓
PostgreSQL
9. Never Expose PostgreSQL Directly to the Public Internet Without a Strong Reason
A production database generally shouldn’t be directly accessible from anywhere on the internet.
Prefer:
Internet
↓
Application
↓
Private Network
↓
PostgreSQL
instead of:
Internet
↓
PostgreSQL
Use private networking, firewalls, security groups, VPNs, or equivalent network controls where appropriate.
10. Restrict pg_hba.conf
The PostgreSQL Host-Based Authentication file, commonly known as:
pg_hba.conf
controls which clients can connect and how they authenticate.
Rules can restrict:
Database
User
Client address
Authentication method
For example:
hostssl myapp app_user 10.0.0.0/24 scram-sha-256
This can require SSL connections from a specific network range using SCRAM authentication.
Always test authentication rules carefully because an overly restrictive configuration can block legitimate application access.
11. Prefer SCRAM Authentication
Modern PostgreSQL installations support SCRAM-SHA-256 authentication.
For example:
scram-sha-256
is generally preferable to older password authentication mechanisms when supported by your PostgreSQL version and clients.
You can configure:
password_encryption = scram-sha-256
for password storage behavior when creating or changing passwords.
Make sure your drivers and clients support the authentication method you choose.
12. Keep PostgreSQL Updated
Security vulnerabilities can affect database software and its dependencies.
Regularly apply appropriate:
PostgreSQL updates
Operating system security updates
Database driver updates
Connection library updates
Infrastructure patches
A secure configuration is not enough if the underlying software is severely outdated.
Have a process for evaluating PostgreSQL security releases and applying relevant updates.
13. Use a Supported PostgreSQL Version
PostgreSQL major versions have defined support lifecycles.
Avoid running an old, unsupported PostgreSQL version in production simply because the application still works.
A production database should have a planned upgrade strategy:
Current Version
↓
Test Upgrade
↓
Staging
↓
Production Upgrade
↓
Verification
Test extensions, drivers, queries, and application compatibility before upgrading.
14. Prevent SQL Injection
SQL injection is primarily an application-level security problem, but PostgreSQL applications must protect against it.
Never construct SQL using raw user input.
Bad:
const query =
"SELECT * FROM users WHERE email = '" +
email +
"'";
If the input is malicious, it can alter the SQL statement.
Use parameterized queries instead:
const result = await pool.query(
'SELECT * FROM users WHERE email = $1',[email]
);
Now the user input is treated as data rather than SQL syntax.
15. Use Parameterized Queries Everywhere
Parameterized queries should be your default approach.
For example:
SELECT *
FROM users
WHERE id = $1;
Then provide:
[userId]
as the parameter.
This is safer than dynamically concatenating user input into SQL statements.
16. Be Careful With Dynamic SQL
Sometimes applications need dynamic SQL.
For example:
Dynamic column
Dynamic table
Dynamic sorting
Dynamic filters
Parameterization doesn’t work for every SQL identifier.
For identifiers, use a trusted allowlist.
For example:
Allowed sorting fields:
- created_at
- name
- price
Reject anything outside the approved list.
Don’t blindly insert user-controlled identifiers into SQL.
17. Use Database Constraints
Security isn’t only about authentication.
Data integrity also matters.
Use constraints such as:
PRIMARY KEY
FOREIGN KEY
UNIQUE
NOT NULL
CHECK
For example:
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
age INTEGER CHECK (age >= 18)
);
Constraints prevent invalid data from entering the database.
18. Use Row-Level Security When Appropriate
PostgreSQL supports Row-Level Security (RLS).
RLS can restrict which rows a particular role can access.
For example, suppose multiple organizations share the same table:
orders
----------------
id
organization_id
amount
Without row-level controls, application bugs could potentially expose another organization’s data.
RLS can enforce policies at the database level.
Conceptually:
Organization A
↓
Only Organization A rows
Organization B
↓
Only Organization B rows
19. Example Row-Level Security
Enable RLS:
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
Then create a policy:
CREATE POLICY organization_orders
ON orders
USING (
organization_id = current_setting(
'app.organization_id'
)::bigint
);
The exact design should match your application’s authentication and transaction model.
RLS is powerful, but it should be thoroughly tested before production use.
20. Understand RLS Bypass Behavior
Not every PostgreSQL role is affected by RLS in exactly the same way.
Superusers and roles with appropriate bypass privileges can bypass row-level security.
Therefore:
RLS
+
Least privilege
should be designed together.
Do not assume RLS alone protects data if the application connects using an overly privileged account.
21. Separate Application and Migration Permissions
Database migrations often require more privileges than normal application queries.
For example:
Application
→ SELECT / INSERT / UPDATE
Migration process
→ CREATE / ALTER / DROP
Don’t automatically give the production application role permission to alter the entire schema.
A safer architecture can separate:
Runtime Role
Migration Role
Administrative Role
22. Limit Schema Permissions
Applications don’t always need full schema privileges.
For example:
GRANT USAGE ON SCHEMA app TO app_user;
Then grant only the required table permissions:
GRANT SELECT, INSERT, UPDATE
ON ALL TABLES IN SCHEMA app
TO app_user;
Be careful with broad grants and review whether the application really needs each permission.
23. Revoke Unnecessary Permissions
PostgreSQL provides:
GRANT
REVOKE
for controlling privileges.
For example:
REVOKE DELETE
ON users
FROM app_user;
If the application never deletes users directly, removing that permission can reduce risk.
24. Don’t Give DELETE Permission Without a Reason
For some applications, the runtime service may need:
SELECT
INSERT
UPDATE
but not:
DELETE
You can explicitly design permissions around the application’s actual operations.
The smaller the permission set, the smaller the potential blast radius of a compromised account.
25. Protect Backups
Database security doesn’t stop at the live PostgreSQL server.
Backups may contain the same sensitive information as production.
Protect them with:
Encryption
Access controls
Secure storage
Retention policies
Audit logging
Restricted credentials
A stolen backup can be just as damaging as a compromised production database.
26. Encrypt Backups
Backups should be encrypted both:
At rest
and, where applicable:
During transfer
For example:
PostgreSQL
↓
Encrypted Backup
↓
Secure Storage
Don’t assume that backup storage is automatically safe just because the production database is secure.
27. Test Backup Restoration
A backup that has never been restored is an assumption, not a proven recovery strategy.
Regularly test:
Backup
↓
Restore
↓
Database verification
↓
Application verification
This confirms that backups are usable when you actually need them.
28. Use Point-in-Time Recovery When Appropriate
PostgreSQL supports techniques for point-in-time recovery using base backups and WAL archiving.
This can allow recovery to a specific point in time, depending on your backup architecture.
For example:
Monday 10:00
Monday 11:00
Monday 12:00
↓
Accidental deletion
↓
Recover to 11:59
This can be extremely valuable for production systems.
29. Don’t Treat Replicas as Backups
A read replica isn’t a replacement for backups.
If someone accidentally executes:
DROP TABLE customers;
the destructive change may be replicated to the standby depending on the replication architecture.
A proper recovery strategy should include independent backups.
30. Protect Database Logs
PostgreSQL logs can contain valuable operational information.
They may also contain sensitive information depending on logging configuration.
Protect log files using:
Access controls
Secure storage
Appropriate retention
Monitoring
Log redaction where needed
Avoid unnecessarily logging sensitive application data.
31. Enable Useful Logging
Production PostgreSQL deployments should have enough logging to investigate problems and security incidents.
Depending on your requirements, consider logging:
Connection attempts
Authentication failures
Disconnections
DDL activity
Important administrative events
Long-running queries
Don’t enable every possible log option without considering:
Storage
Performance
Privacy
Operational usefulness
32. Monitor Failed Authentication Attempts
Repeated authentication failures can indicate:
Misconfiguration
Credential problems
Brute-force attempts
Unauthorized access
Compromised applications
Monitor authentication failures and investigate unusual patterns.
For example:
Normal:
2 failures/day
Sudden:
5,000 failures/hour
should trigger investigation.
33. Monitor Database Activity
Security monitoring should include database activity such as:
Unexpected connections
Unexpected users
Privilege changes
Schema changes
Unusual queries
Large exports
Unexpected traffic
Monitoring makes it easier to detect suspicious behavior.
34. Audit Privilege Changes
Changes such as:
CREATE ROLE
ALTER ROLE
GRANT
REVOKE
can significantly affect database security.
These operations should be controlled and auditable.
For production environments, consider maintaining an audit trail for important administrative changes.
35. Limit Administrative Access
Database administrators should not connect from arbitrary devices or networks.
Prefer:
Admin Device
↓
VPN / Secure Access
↓
Private Network
↓
PostgreSQL
rather than exposing administrative database access publicly.
36. Use Firewalls
Network firewalls should restrict who can reach PostgreSQL.
For example:
Application Servers
↓
Port 5432
↓
PostgreSQL
Only trusted networks should be allowed to reach the database port where practical.
Remember that changing PostgreSQL authentication rules does not replace network-level controls.
37. Don’t Assume Port 5432 Is Security
Changing PostgreSQL’s default port can reduce noise from simplistic scans, but it is not a real security control.
For example:
5432 → 6543
doesn’t protect a database if:
Internet
↓
Open database port
The important controls are:
Firewall
Private networking
Authentication
Authorization
Encryption
Monitoring
38. Use Network Segmentation
A production architecture might look like:
Internet
↓
Load Balancer
↓
Application
↓
Private Network
↓
PostgreSQL
The database belongs in a protected network segment rather than directly alongside publicly accessible services.
39. Protect the PostgreSQL Configuration
Important PostgreSQL configuration files should have appropriate operating-system permissions.
Examples include:
postgresql.conf
pg_hba.conf
pg_ident.conf
Only authorized users should be able to modify them.
An attacker who can alter authentication configuration may be able to bypass intended access controls.
40. Secure the PostgreSQL Server
Database security also depends on the operating system.
Protect:
Operating system
Filesystem
SSH access
Database process
Configuration files
Backup directories
Logs
Secrets
Use standard server-hardening practices.
PostgreSQL cannot compensate for a compromised host.
41. Use Secret Management
For production applications, consider using a dedicated secret manager.
Conceptually:
Secret Manager
↓
Database Password
↓
Application
↓
PostgreSQL
Benefits can include:
- Centralized secrets
- Access control
- Rotation
- Auditing
- Reduced exposure in source code
42. Don’t Store Secrets in Git
Never commit:
DATABASE_URL
DB_PASSWORD
API_SECRET
Private keys
Production credentials
to source control.
Even if you delete the file later, secrets may remain in Git history.
Use environment variables or a proper secret-management system.
43. Secure the Database URL
A connection string might look like:
postgresql://user:password@host:5432/database
Treat it as a secret if it contains credentials.
Don’t expose it through:
Frontend JavaScript
Public repositories
Client-side logs
Error messages
Screenshots
Public configuration
44. Don’t Connect Directly From the Browser
A browser application generally shouldn’t connect directly to a privileged PostgreSQL database.
Avoid:
Browser
↓
PostgreSQL
Prefer:
Browser
↓
Backend API
↓
PostgreSQL
The backend can control:
Authentication
Authorization
Validation
Rate limiting
Query construction
Database permissions
45. Validate Input at the Application Layer
Parameterized SQL prevents SQL injection, but validation is still important.
For example:
Age
→ Must be a valid number
Email
→ Must follow expected format
Pagination
→ Must stay within reasonable limits
Sort field
→ Must be allowlisted
Use both:
Input validation
+
Parameterized queries
46. Avoid Excessively Broad Queries
An authenticated user shouldn’t automatically be able to request:
SELECT *
FROM customers;
if the application only needs:
SELECT id, name
FROM customers
WHERE id = $1;
Return only the data required by the operation.
This follows the principle of least privilege at the data-access level.
47. Protect Sensitive Columns
Not every database user needs access to every column.
Sensitive information might include:
Personal information
Internal identifiers
Security-related data
Financial information
Application secrets
Consider whether the application really needs access to each sensitive field.
Views and carefully designed permissions can sometimes help separate sensitive data from general application access.
48. Don’t Store Passwords as Plain Text
Application passwords should never be stored as plain text.
Instead, passwords should be securely hashed using a password hashing algorithm appropriate for password storage.
For example:
User Password
↓
Password Hashing
↓
Stored Password Hash
The database should not contain the original password.
PostgreSQL’s role passwords and your application’s user passwords are separate concepts; secure both appropriately.
49. Be Careful With Sensitive Data in Logs
Avoid logging:
Passwords
Access tokens
API keys
Credit card information
Sensitive personal data
Database credentials
For example, don’t log an entire request object if it contains authentication credentials.
Logs should help debugging without becoming another source of data exposure.
50. Use Timeouts
Security and reliability can overlap.
Appropriate timeouts can prevent resources from being held indefinitely.
Consider:
Connection timeout
Statement timeout
Idle session timeout
Application request timeout
For example:
SET statement_timeout = '30s';
The correct timeout depends on the application’s workload.
Don’t apply aggressive global limits without testing them.
51. Limit Long-Running Queries
Long-running queries can consume:
CPU
Memory
Connections
Locks
I/O
They can also become a denial-of-service vector if attackers can trigger expensive queries repeatedly.
Use:
Indexes
Query limits
Pagination
Statement timeouts
Rate limiting
Query optimization
to reduce unnecessary database workload.
52. Use Pagination
Avoid returning millions of rows in one request.
Bad:
SELECT *
FROM orders;
for a public API endpoint.
Prefer controlled access:
SELECT id, total, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 50;
For very large datasets, consider cursor/keyset pagination instead of relying exclusively on large OFFSET values.
53. Protect Against Resource Exhaustion
Attackers don’t always need SQL injection.
They may abuse expensive legitimate operations.
For example:
Huge search
↓
Expensive query
↓
High CPU
↓
Database slowdown
Protect the application with:
Rate limiting
Pagination
Query limits
Timeouts
Caching
Input validation
Efficient indexes
54. Be Careful With Extensions
PostgreSQL extensions are powerful, but production environments should control which extensions are installed.
Before adding an extension, evaluate:
Source
Maintenance
Security
Permissions
Compatibility
Operational impact
Don’t install arbitrary extensions without understanding what privileges and capabilities they require.
55. Review Functions and Procedures
PostgreSQL functions can execute powerful operations.
Review:
Function ownership
Execution privileges
Security definer functions
Search path behavior
Dynamic SQL
Be particularly careful with SECURITY DEFINER functions because they execute with the privileges of their owner.
A poorly designed security-definer function can unintentionally create a privilege-escalation path.
56. Be Careful With search_path
PostgreSQL’s search_path determines how unqualified object names are resolved.
Security-sensitive functions should avoid ambiguous object resolution.
For example, a security-definer function that executes SQL using an unsafe search_path can potentially reference an attacker-controlled object.
Use explicit schemas and carefully configured search paths where appropriate.
57. Use Separate Environments
Don’t use the same database for:
Development
Testing
Staging
Production
Prefer:
Development → Dev DB
Testing → Test DB
Staging → Staging DB
Production → Production DB
This reduces the chance that development tools accidentally modify production data.
58. Never Give Developers Production Credentials by Default
Production access should be controlled.
Developers can usually work with:
Local database
Development database
Sanitized staging data
rather than having permanent unrestricted access to production.
For exceptional production troubleshooting, use controlled, auditable access.
59. Protect Production Data in Non-Production Environments
If production data is copied to development or staging, sensitive information can spread to systems with weaker security.
Where possible:
Production Data
↓
Sanitize / Anonymize
↓
Non-production Environment
Don’t copy sensitive production datasets everywhere simply for convenience.
60. Use Database Migrations Carefully
Migration systems often have powerful database permissions.
Protect migration credentials and ensure migrations are:
Reviewed
Tested
Version-controlled
Auditable
Reversible where practical
Avoid running arbitrary SQL from untrusted sources.
PostgreSQL Security Checklist
Before deploying PostgreSQL to production, check:
☐ Application does not use a superuser
☐ Least-privilege roles are configured
☐ Strong credentials are used
☐ Secrets are not committed to Git
☐ TLS is configured where required
☐ pg_hba.conf is restricted
☐ Database is not unnecessarily publicly exposed
☐ Firewall rules are configured
☐ PostgreSQL is supported and patched
☐ Parameterized queries are used
☐ Input validation is implemented
☐ RLS is used where appropriate
☐ Backups are encrypted
☐ Backup restoration is tested
☐ Authentication failures are monitored
☐ Important database activity is logged
☐ Production access is restricted
☐ Sensitive data is protected
☐ Query/resource limits are considered
☐ Security incidents can be investigated
Example Secure Production Architecture
A typical secure architecture could look like:
Internet
↓
Load Balancer
↓
Application API
↓
┌────────┴────────┐
↓ ↓
Cache PostgreSQL
↓
Private Network
↓
Restricted DB Access
The application connects using:
Dedicated Role
↓
Least Privileges
↓
TLS
↓
PostgreSQL
And backups follow a separate protected path:
PostgreSQL
↓
Encrypted Backup
↓
Secure Backup Storage
A Practical Role Design
A production system might use roles such as:
app_runtime
app_migration
app_readonly
db_admin
For example:
app_runtime
→ Normal application queries
app_migration
→ Schema changes
app_readonly
→ Reporting / read-only access
db_admin
→ Administrative operations
This is much safer than using one powerful account for everything.
Security Layers
A strong PostgreSQL security strategy uses multiple layers:
1. Application Security
↓
2. Network Security
↓
3. Authentication
↓
4. Authorization
↓
5. Database Constraints
↓
6. Encryption
↓
7. Logging & Monitoring
↓
8. Backup & Recovery
No single feature should be expected to protect the entire system.
Common PostgreSQL Security Mistakes
Using postgres for the Application
Problem: Excessive privileges.
Solution: Create a dedicated application role.
Opening PostgreSQL to the Internet
Problem: Increases attack surface.
Solution: Use private networking and firewall restrictions.
Building SQL With String Concatenation
Problem: SQL injection risk.
Solution: Use parameterized queries.
Storing Passwords in Source Code
Problem: Credentials can leak through source control.
Solution: Use environment variables or a secret manager.
Treating Replicas as Backups
Problem: Destructive changes may replicate.
Solution: Maintain independent backups.
Giving Every Service Full Database Access
Problem: A compromised service can access everything.
Solution: Use separate roles and least privilege.
Ignoring Updates
Problem: Known vulnerabilities may remain unpatched.
Solution: Maintain a regular patch and upgrade process.
Giving Developers Permanent Production Access
Problem: Increases accidental and intentional data exposure risk.
Solution: Use controlled and auditable production access.
Final Thoughts
PostgreSQL security isn’t one setting you turn on before deploying an application.
It’s a combination of:
Authentication
+
Authorization
+
Network Security
+
Encryption
+
Least Privilege
+
Secure SQL
+
Data Integrity
+
Backups
+
Monitoring
+
Operational Controls
The most important practices are:
Use dedicated roles
↓
Follow least privilege
↓
Protect credentials
↓
Use TLS
↓
Restrict network access
↓
Use parameterized SQL
↓
Protect sensitive data
↓
Secure backups
↓
Monitor database activity
↓
Keep PostgreSQL updated
For production applications, security should be designed from the beginning rather than added after a database incident.
A secure PostgreSQL deployment combines strong authentication, least-privilege access, encrypted connections, restricted networking, safe application queries, protected backups, and continuous monitoring.
