How to Configure Postgres Access

PostgreSQL, often referred to as Postgres, is one of the most powerful, open-source relational database systems in the world. Renowned for its reliability, extensibility, and strict adherence to SQL standards, it powers everything from small web applications to enterprise-scale data warehouses. However, the strength of Postgres lies not only in its feature-rich architecture but also in its ability to be securely configured for controlled access. Properly configuring Postgres access ensures that only authorized users and systems can interact with your database, minimizing the risk of data breaches, unauthorized modifications, or performance degradation due to unmanaged connections.

Many administrators assume that Postgres is secure by default and while it does ship with conservative defaults, these are not sufficient for production environments. Without explicit configuration of network listeners, authentication methods, user roles, and firewall rules, your database can be exposed to the public internet or internal network threats. This guide provides a comprehensive, step-by-step walkthrough on how to configure Postgres access securely and effectively, covering everything from initial setup to advanced access control mechanisms.

Whether youre deploying Postgres on a local development machine, a virtual private server, or a cloud platform like AWS RDS or Google Cloud SQL, understanding how to control who can connect, how they authenticate, and what they can do is critical. This tutorial will equip you with the knowledge to implement enterprise-grade access controls, avoid common misconfigurations, and maintain compliance with security standards.

Step-by-Step Guide

Step 1: Locate and Understand Postgres Configuration Files

Before making any changes to access settings, you must identify where Postgres stores its configuration files. These files vary slightly depending on your operating system and installation method (package manager, Docker, compiled from source, etc.), but the core files remain consistent.

The two most critical files are:

• postgresql.conf Controls server-level settings, including network binding and port configuration.
• pg_hba.conf Defines Host-Based Authentication rules, determining who can connect and how they authenticate.

To locate these files, connect to your Postgres instance using the psql client and run:

SHOW config_file;
SHOW hba_file;

This will return the full paths to both files. Common locations include:

• Linux (APT): /etc/postgresql/[version]/main/
• Linux (YUM/RPM): /var/lib/pgsql/[version]/data/
• macOS (Homebrew): /usr/local/var/postgres/
• Docker: Typically mounted volumes or inside the container at /var/lib/postgresql/data/

Always back up these files before editing:

cp /etc/postgresql/15/main/postgresql.conf /etc/postgresql/15/main/postgresql.conf.bak
cp /etc/postgresql/15/main/pg_hba.conf /etc/postgresql/15/main/pg_hba.conf.bak

Step 2: Configure Network Listening (postgresql.conf)

By default, Postgres only accepts connections from localhost (127.0.0.1). This is secure for development but prevents remote access. To allow external connections, you must modify the listen_addresses parameter in postgresql.conf.

Open the file in your preferred editor:

sudo nano /etc/postgresql/15/main/postgresql.conf

Find the line:

listen_addresses = 'localhost'

Uncomment it and set it to accept connections from specific IPs or all interfaces:

• To allow connections from any IP: listen_addresses = '*'
• To allow specific IPs: listen_addresses = '127.0.0.1,192.168.1.10,10.0.0.5'
• To listen only on IPv6: listen_addresses = '::1'

Also ensure the port is set correctly (default is 5432):

port = 5432

After making changes, restart the Postgres service:

sudo systemctl restart postgresql

Verify the change using:

sudo netstat -tlnp | grep 5432

You should see Postgres listening on the specified address(es). If you're using a firewall (e.g., UFW, firewalld, or iptables), ensure port 5432 is allowed for the intended IP ranges.

Step 3: Configure Authentication Rules (pg_hba.conf)

While postgresql.conf controls *where* Postgres listens, pg_hba.conf controls *who* can connect and *how*. This file uses a line-based format with six fields:

type  database  user  address  method  [options]

Each line defines a rule that matches incoming connections based on connection type, database, user, client IP, and authentication method.

Common Connection Types

• host TCP/IP connections (IPv4)
• hostssl TCP/IP connections requiring SSL
• hostnossl TCP/IP connections without SSL
• local Unix domain sockets (local connections only)

Authentication Methods

Postgres supports multiple authentication methods. The most common include:

• trust Allows connection without password (only for trusted networks avoid in production)
• password Sends password in plain text (insecure unless used over SSL)
• md5 Sends password hashed with MD5 (deprecated; avoid if possible)
• scram-sha-256 Modern, secure password hashing (recommended)
• peer Uses OS user identity (Linux/Unix only; ideal for local admin access)
• cert Client certificate authentication
• ldap Authenticate against LDAP server

Example Configuration

Heres a secure, production-ready pg_hba.conf configuration:

TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     scram-sha-256
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
hostssl all             app_user        192.168.1.10/32         scram-sha-256
hostssl all             readonly_user   10.0.0.0/24             scram-sha-256
hostssl all             admin_user      203.0.113.5/32          cert

Explanation:

• local all postgres peer Local OS user "postgres" can connect as Postgres superuser without password (secure because it requires OS-level access).
• local all all scram-sha-256 All local users must authenticate with a secure password hash.
• host all all 127.0.0.1/32 scram-sha-256 Local TCP connections require password authentication.
• hostssl all app_user 192.168.1.10/32 scram-sha-256 Only the application server at 192.168.1.10 can connect as "app_user" over SSL with password.
• hostssl all readonly_user 10.0.0.0/24 scram-sha-256 Any machine in the internal subnet can connect as "readonly_user" with password over SSL.
• hostssl all admin_user 203.0.113.5/32 cert Only a specific admin IP can connect using a client certificate (highest security).

After editing pg_hba.conf, reload the configuration without restarting the server:

sudo systemctl reload postgresql

Step 4: Create and Manage Database Users

Postgres uses roles to manage permissions. A role can be a user, a group, or both. To create a user with login privileges:

CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password_123';

For better security, avoid using the default postgres superuser for applications. Instead, create dedicated roles with minimal privileges:

CREATE ROLE readonly_user WITH LOGIN PASSWORD 'read_only_pass_456' NOCREATEDB NOCREATEROLE;

To grant specific permissions to a database:

GRANT CONNECT ON DATABASE myapp_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

For read-only access:

GRANT CONNECT ON DATABASE myapp_db TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;

Use ALTER ROLE to modify existing roles:

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';
ALTER ROLE app_user SET statement_timeout = '30s';

These settings prevent long-running or idle transactions from locking resources.

Step 5: Enable SSL Encryption

Even if you restrict access via IP and authentication, transmitting passwords or data in plain text over the network is risky. Enabling SSL ensures encrypted communication between clients and the server.

First, generate or obtain SSL certificates. For testing, you can generate a self-signed certificate:

cd /etc/postgresql/15/main/
sudo openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=postgres.example.com"
sudo chmod 600 server.key
sudo chown postgres:postgres server.crt server.key

Then, enable SSL in postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = ''  
Optional: if using CA-signed certs

Restart Postgres and verify SSL is active:

psql -h localhost -U app_user -d myapp_db -c "SELECT ssl_is_used();"

If it returns t, SSL is active. Always use hostssl (not host) in pg_hba.conf to enforce encrypted connections.

Step 6: Test Remote Access

After configuring everything, test access from a remote machine:

psql -h your-server-ip -p 5432 -U app_user -d myapp_db

If authentication fails:

• Check the pg_hba.conf entry matches the client IP and method.
• Verify the server is listening on the correct IP using netstat or ss.
• Ensure no firewall is blocking port 5432.
• Confirm the user exists and has the correct password.

Use log_connections = on in postgresql.conf to log connection attempts for debugging:

log_connections = on
log_disconnections = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

Restart and check logs at /var/log/postgresql/postgresql-[version]-main.log.

Step 7: Harden Further with Connection Limits and Resource Control

Prevent abuse and denial-of-service attacks by limiting connections and resource usage:

• max_connections = 100 Set based on your applications needs.
• superuser_reserved_connections = 3 Reserve connections for admin use.
• idle_in_transaction_session_timeout = 300000 Kill idle transactions after 5 minutes.
• statement_timeout = 30000 Cancel queries running longer than 30 seconds.
• lock_timeout = 10000 Prevent long waits on locks.

Apply these in postgresql.conf and reload:

sudo systemctl reload postgresql

Best Practices

Use Role-Based Access Control (RBAC)

Never grant superuser privileges to application accounts. Create dedicated roles for each service or function (e.g., web_app, reporting, etl_worker). Assign only the permissions required follow the principle of least privilege.

Enforce SSL for All Remote Connections

Even on private networks, use hostssl in pg_hba.conf. Avoid host and password methods unless absolutely necessary. Prefer scram-sha-256 over md5 or plain password.

Disable Default Superuser for Applications

The postgres superuser should only be used for administrative tasks. Applications must connect using limited roles. If an application is compromised, limiting its database privileges reduces potential damage.

Regularly Audit User Permissions

Run periodic audits to identify unused or overprivileged roles:

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolconnlimit FROM pg_roles WHERE rolname NOT LIKE 'pg_%';

Revoke unnecessary permissions:

REVOKE CONNECT ON DATABASE myapp_db FROM old_user;
DROP ROLE IF EXISTS old_user;

Use Connection Poolers

For high-traffic applications, use connection poolers like pgBouncer or PgPool-II. They reduce the number of direct connections to Postgres, improve performance, and allow better control over client access.

Implement Network Segmentation

Place your database in a private subnet, accessible only from application servers. Use VPCs, security groups, or VLANs to isolate database traffic. Never expose Postgres directly to the public internet.

Enable Logging and Monitoring

Enable detailed logging for connections, errors, and slow queries:

log_statement = 'mod'          
Log all data-modifying statements
log_min_duration_statement = 1000  
Log queries taking longer than 1 second
log_temp_files = 0             
Log all temporary files

Integrate logs with centralized tools like ELK Stack, Datadog, or Prometheus + Grafana for alerting.

Keep Postgres Updated

PostgreSQL releases security patches regularly. Subscribe to the PostgreSQL Security Page and apply updates promptly. Outdated versions may contain known vulnerabilities exploitable via network access.

Backup Configuration Files

Always version-control your postgresql.conf and pg_hba.conf files using Git or similar tools. This ensures you can roll back changes and audit configuration drift over time.

Tools and Resources

Essential Tools

• psql The command-line client for interacting with Postgres. Essential for testing and debugging.
• pgAdmin A web-based GUI for managing Postgres servers, roles, and permissions. Useful for visualizing access rules.
• pgBouncer Lightweight connection pooler that reduces connection overhead and enforces client limits.
• pgAudit An extension that provides detailed audit logging of all database activity, including access attempts.
• pg_stat_statements Tracks execution statistics for SQL statements. Helps identify misbehaving queries or users.
• fail2ban Can be configured to block IPs after multiple failed login attempts to Postgres.

Security Extensions

• pgcrypto Provides cryptographic functions for encrypting data at rest within the database.
• pg_hba_file A function to read and validate pg_hba.conf programmatically.
• row-level security (RLS) Allows fine-grained access control at the row level using policies.

Online Resources

• PostgreSQL Official Documentation Host-Based Authentication
• Runtime Configuration Connection Settings
• PostgreSQL Privileges and Roles
• pgcrypto GitHub Repository
• PGTune Configuration Generator

Automation and Infrastructure as Code

For scalable deployments, use automation tools:

• Ansible Playbooks to deploy and configure Postgres across multiple servers.
• Terraform Provision cloud-based Postgres instances with secure access rules.
• Docker Compose Define secure Postgres containers with mounted config files and environment variables.

Example Docker Compose snippet:

version: '3.8'
services:
postgres:
image: postgres:15
ports:
- "5432:5432"
volumes:
- ./pg_hba.conf:/etc/postgresql/pg_hba.conf
- ./postgresql.conf:/etc/postgresql/postgresql.conf
environment:
POSTGRES_DB: myapp_db
POSTGRES_USER: app_user
POSTGRES_PASSWORD: secure_password_123
healthcheck:
test: ["CMD-SHELL", "pg_isready -U app_user -d myapp_db"]
interval: 10s
timeout: 5s
retries: 5

Real Examples

Example 1: Securing a Web Application

Scenario: Youre deploying a Django app with a Postgres backend on a cloud VPS.

• Server IP: 203.0.113.10
• App server IP: 203.0.113.11
• Database name: myapp_db
• App user: django_user

Configuration:

• postgresql.conf: listen_addresses = '203.0.113.10'
• pg_hba.conf: hostssl myapp_db django_user 203.0.113.11/32 scram-sha-256
• SSL: Enabled with Lets Encrypt certificate
• Firewall: Only allow TCP 5432 from 203.0.113.11
• Database permissions: GRANT CONNECT, USAGE ON SCHEMA public TO django_user; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO django_user;

Result: Only the Django app server can connect, over encrypted channel, with minimal permissions. The database is not accessible from the public internet.

Example 2: Multi-Tenant SaaS Platform

Scenario: A SaaS platform serving 50+ clients, each with their own schema in a shared database.

• Each client gets a unique role: client_a, client_b, etc.
• Each role has access only to its schema: client_a_schema, client_b_schema
• Application connects via a single connection pooler (pgBouncer) using a master role.
• Client roles are created dynamically via API.

Configuration:

• pg_hba.conf: Only allow connections from pgBouncer IP: hostssl all all 10.0.0.5/32 scram-sha-256
• pgBouncer configured with auth_type = md5 and auth_file = /etc/pgbouncer/userlist.txt
• Each client role has: GRANT USAGE ON SCHEMA client_a_schema TO client_a; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA client_a_schema TO client_a;
• Row-Level Security (RLS) enabled on sensitive tables for additional isolation.

Result: Clients are logically isolated, access is controlled via application logic and database roles, and no direct client-to-database connections are allowed reducing attack surface.

Example 3: Compliance-Driven Environment (HIPAA/GDPR)

Scenario: A healthcare application must comply with HIPAA regulations requiring encrypted data, audit logs, and access controls.

• SSL enforced for all connections
• Client certificate authentication used for backend services
• pgAudit extension installed and configured to log all SELECT, INSERT, UPDATE, DELETE
• Roles created per job function: clinician, admin, auditor
• Access logs sent to SIEM system (e.g., Splunk)
• Connection limits set to 25 max, idle timeout to 2 minutes
• Automatic password rotation enforced via external tool (e.g., HashiCorp Vault)

Result: Meets strict compliance requirements with full auditability, encryption, and least-privilege access.

FAQs

Can I use Postgres without any authentication?

Technically, yes using the trust method in pg_hba.conf allows any user to connect without a password. However, this is extremely insecure and should never be used in production or any environment exposed to untrusted networks.

Whats the difference between host and hostssl in pg_hba.conf?

host allows TCP connections without requiring encryption. hostssl requires SSL/TLS encryption. Always prefer hostssl to protect data in transit, even on private networks.

Why cant I connect even though my IP is allowed in pg_hba.conf?

Common causes include:

• Postgres isnt listening on the correct IP (check listen_addresses).
• Firewall is blocking port 5432.
• Client is connecting via IPv6 but rule is for IPv4 (or vice versa).
• Typo in username, database name, or IP address.
• Role doesnt exist or password is incorrect.

Check the Postgres logs they will tell you exactly why the connection was rejected.

How do I reset a forgotten Postgres password?

If you have OS access:

1. Stop the Postgres service: sudo systemctl stop postgresql
2. Start Postgres in single-user mode: sudo -u postgres postgres --single -D /var/lib/postgresql/15/main
3. Run: ALTER USER username WITH PASSWORD 'new_password';
4. Exit and restart normally.

Is it safe to expose Postgres to the public internet?

No. Exposing Postgres directly to the public internet is a severe security risk. Even with strong passwords, brute-force attacks, version exploits, and misconfigurations can lead to data breaches. Always use a reverse proxy, VPN, or application layer to mediate access.

How do I rotate database passwords securely?

Use a secrets manager (e.g., HashiCorp Vault, AWS Secrets Manager) to store and rotate passwords. Update the password in the secrets manager, then update the Postgres role:

ALTER USER app_user WITH PASSWORD 'new_secure_password';

Update the application configuration to use the new password. Restart the application only after confirming the new credentials work.

Can I use LDAP or Active Directory to authenticate Postgres users?

Yes. Postgres supports LDAP authentication. Configure it in pg_hba.conf using:

host    all             all             0.0.0.0/0               ldap ldapserver=ldap.example.com ldapprefix="cn=" ldapsuffix=",ou=users,dc=example,dc=com"

This allows centralized user management without creating individual Postgres roles.

What should I do if I accidentally lock myself out?

If youve misconfigured pg_hba.conf and can no longer connect:

• Log in to the server via SSH.
• Stop the Postgres service.
• Temporarily change pg_hba.conf to use peer or trust for local connections.
• Start Postgres and reconnect locally.
• Fix the configuration and reload.

Conclusion

Configuring Postgres access is not a one-time task its an ongoing practice that must evolve with your infrastructure, security policies, and compliance requirements. The steps outlined in this guide provide a solid foundation for securing your Postgres deployments, whether youre running a small personal project or a mission-critical enterprise system.

Remember: security is layered. No single configuration not even the most restrictive pg_hba.conf is sufficient on its own. Combine strong authentication, encrypted connections, role-based access control, network segmentation, logging, and regular audits to build a resilient, secure database environment.

By following the best practices and real-world examples provided here, youll not only prevent common vulnerabilities but also position your organization for scalability and compliance. Always test changes in a staging environment before deploying to production. Document your configurations. Monitor for anomalies. And never underestimate the power of a well-configured Postgres server.

PostgreSQL is one of the most secure databases available but only if you configure it properly. Take the time to do it right, and your data will thank you.