PostgreSQL Security Missteps and Tips

This post was originally written in 2020 and was updated in 2025.

PostgreSQL security done right protects data, improves performance, keeps systems stable, and supports a healthier development cycle. Because PostgreSQL security can sprawl, this post focuses on the mechanisms you use most. 

Three files shape security in a PostgreSQL data cluster: postgresql.conf, pg_hba.conf, and pg_ident.conf. We’ll dig into postgresql.conf and pg_hba.conf here and note pg_ident.conf for context. You’ll see how to handle SSL, Unix domain socket permissions, and HBA rule order so you can harden access without slowing your team down.

About postgresql.conf

Secure Socket Layer

Enabling SSL is one of the simplest and most important steps you can take to protect PostgreSQL traffic. Without it, client sessions, including logins, queries, and replication streams,  are exposed to anyone who can sniff the network. That means sensitive information such as passwords, credit card data, or social security numbers could be intercepted.

SSL should be standard for:

• Superuser and administrative sessions
• Monitoring services (such as Percona Monitoring and Management)
• Maintenance activities like logical backups and cron jobs
• All replication processes between servers

Key parameters in postgresql.conf:

• ssl: Turns on SSL support. On Debian-based distributions (like Ubuntu), it’s enabled by default, but the default “ssl-snakeoil” self-signed certificate isn’t suitable for production. On Red Hat/CentOS, SSL is off by default and must be configured manually with either a self-signed certificate or, preferably, one issued by a trusted Certificate Authority such as Let’s Encrypt.
• ssl_cert_file: Points to the server certificate file.
• ssl_key_file: Points to the server’s private key file.

Tip: Always replace self-signed test certificates with trusted certificates before going live. Not doing so can leave your database traffic vulnerable to man-in-the-middle attacks.

UNIX Domain Sockets

PostgreSQL uses Unix domain sockets for local connections, and how you configure them directly affects who can talk to your database. These settings control which users and groups on the host system can access the socket file.

This matters in environments where many users or processes have shell access to the PostgreSQL host. For example:

• A connection pooler owned by a non-Postgres process may need to connect, but a regular Unix account used only for jumping between hosts should not.
• In rare cases, permissions can even be misconfigured to block the Postgres superuser from accessing its own service, leading to confusing failures that are difficult to troubleshoot.

Key parameters in postgresql.conf:

• unix_socket_directories: The directory where the server places the socket file used for local connections.
• unix_socket_group: The group that owns the socket file.
• unix_socket_permissions: The file permissions applied to the socket. The default (0777) lets anyone connect, which is rarely appropriate. Only the write permission is evaluated; read and execute bits have no effect.

Tip: Always restrict socket permissions to the minimum group or users required. Leaving the default wide open is a common but avoidable security gap.

About pg_hba.conf

If there’s one place PostgreSQL security goes wrong most often, it’s pg_hba.conf. This file defines who can connect, from where, and how they must authenticate. A single misstep here can expose your database to anyone who can reach it.

Think of pg_hba.conf like a firewall: rules are processed from top to bottom, and the first one that matches is applied. If you add a stricter rule lower in the file but leave a more permissive one above it, the stricter rule will never be used. This is a common mistake that silently leaves your database wide open.

Tip: Always review the order of your rules and remove outdated entries. Don’t rely on rules at the bottom of the file to tighten access; they’ll never be reached if something more permissive is listed above them.

The Golden Rules

pg_hba.conf can be strict or dangerously permissive; it all depends on how you write the rules. These guidelines cover the basics for keeping your database safe.

For localhost connections:

• Use domain sockets (TYPE local) for all local connections.
• Restrict administrative access to the postgres role and use peer authentication (METHOD peer).
• If multiple people need admin rights, have them escalate with sudo su – postgres. This way, their Unix account history still provides an audit trail.

For remote connections:

• Never use trust or plain password authentication.
• Prefer scram-sha-256 (METHOD scram) when your clients support it; otherwise, fall back to md5.
• Require SSL (TYPE hostssl) for all administrative connections.
• Do not allow the superuser account to be used for application processes.

For replication:

• Create a role dedicated exclusively to replication tasks.
• Require SSL for all replication traffic.
• Limit the CIDR range to just the primary and replica hosts.

One last safeguard:
Always end your pg_hba.conf with a reject rule for each user and database combination. This ensures that unexpected conditions don’t result in unintended access, and a simple way to account for Murphy’s Law.

Summary

The basics—SSL, Unix domain sockets, and pg_hba.conf—go a long way in hardening PostgreSQL. Add in tools like pg_ident.conf or enterprise integrations with LDAP, Kerberos, and Active Directory, and you can extend both control and visibility.

But security today isn’t just about configuring a few files. If you’re responsible for safeguarding PostgreSQL in a regulated environment, you need to meet strict compliance requirements while keeping systems available and flexible. That balance is where many teams struggle, and where having the right partner makes all the difference.

See how Percona for PostgreSQL delivers the security features and compliance coverage enterprises rely on, without lock-in or added licensing costs.

 

How Percona for PostgreSQL ensures compliance and security