How to Secure PostgreSQL Connections and Roles

Securing PostgreSQL is not only about turning on a password.

A production-safe setup usually depends on several layers working together:

• network exposure kept tight
• host-based authentication rules that allow only the right clients
• encrypted connections with real verification
• strong password authentication or stronger alternatives
• roles that follow least privilege
• schema and object privileges granted deliberately
• and application roles that do not quietly own or bypass everything

That is why PostgreSQL security is really two related problems:

1. Who is allowed to connect?
2. What are they allowed to do after they connect?

This guide focuses on both.

1. Start by Reducing the Attack Surface

The safest PostgreSQL connection is the one that was never exposed broadly in the first place.

Before you even get to role design, think about where the server listens and who can reach it.

Good first principles include:

• do not expose PostgreSQL directly to the public internet unless there is a very strong operational reason
• keep database traffic on private network paths where possible
• segment environments so development, staging, and production do not all share the same exposure model
• treat bastions, VPN access, private networking, or tightly restricted service-to-service connectivity as part of database security, not separate concerns

A lot of PostgreSQL breaches are not exotic. They come from:

• a broadly reachable port
• weak host-based rules
• or application credentials with too much power

If the network path is already too open, role hardening alone will not save you.

2. Treat pg_hba.conf as a Security Policy, Not a File You Copy Once

pg_hba.conf is one of the most important PostgreSQL security controls because it determines which clients can attempt which authentication methods from which sources.

One of the most important operational details is that PostgreSQL checks these records in order. That means a broad allow rule near the top can quietly weaken everything below it.

Practical rules for pg_hba.conf

• put the most specific and restrictive rules first
• avoid broad catch-all network ranges when smaller ranges are known
• prefer explicit allow rules over vague “we will tighten it later” patterns
• use reject lines deliberately when they help fence off dangerous ranges
• separate admin, application, and replication rules so they are easy to audit

Good mindset

Think of pg_hba.conf as:

• firewall-like policy for database authentication

not:

• a config artifact you last touched six months ago

A bad pg_hba.conf often turns a good role design into a weak security system.

3. Use TLS and Actually Verify the Server

Encryption in transit should be the normal baseline for PostgreSQL connections.

The important part is not only that TLS exists on the server. The important part is that clients are configured to verify the server properly.

PostgreSQL’s libpq SSL docs are very clear here: if only the server is configured and the client is not verifying appropriately, sensitive information can still be exposed before the client knows the server is the one it expected.

Better client-side posture

For strong verification, use a connection mode like:

• sslmode=verify-full
• or, at minimum in some environments, sslmode=verify-ca

and provide a trusted root certificate.

Why this matters

There is a big difference between:

• “the traffic is encrypted somehow” and
• “the client has verified it is talking to the right PostgreSQL server”

That distinction is especially important in:

• shared infrastructure
• multi-tenant environments
• containerized service meshes
• cross-host internal traffic
• cloud environments where trust boundaries are easy to oversimplify

Good rule

If the connection is important enough to secure, it is important enough to verify.

4. Prefer hostssl for Sensitive Paths

When you know a connection path must use TLS, reflect that in pg_hba.conf.

Using hostssl records helps ensure the rule only matches SSL/TLS connections.

That matters because it stops an insecure non-SSL path from accidentally matching a broader host rule instead.

Good use cases for hostssl

• production application traffic
• admin traffic
• replication traffic over untrusted or semi-trusted networks
• anything carrying credentials or sensitive data

Common mistake

Teams sometimes enable SSL on the server but leave pg_hba.conf too permissive, so a less secure path is still available if the client connects the wrong way.

That is not ideal security posture. That is optional security posture.

5. Use SCRAM-SHA-256 and Plan Away From MD5

For password-based PostgreSQL authentication, SCRAM-SHA-256 should be the default target.

PostgreSQL’s current docs note that:

• password_encryption defaults to scram-sha-256
• MD5 password support is deprecated and will be removed in a future release

That means old MD5-based habits should be treated as migration work, not long-term design.

Practical advice

• use SCRAM-SHA-256 for new roles
• phase out MD5-stored passwords where older environments still have them
• avoid the plain password authentication method on untrusted networks
• inventory old clients that may still depend on legacy behavior before you tighten everything globally

Good mindset

Do not ask:

• “Does MD5 still work?” Ask:
• “Why are we still depending on something PostgreSQL has already marked for removal?”

That usually leads to better decisions.

6. Use Client Certificates for Higher-Trust Paths Where Appropriate

PostgreSQL supports client certificate checks through the clientcert option on hostssl records.

This is not always necessary for every application connection, but it can be a very strong control for:

• administrative connections
• privileged automation
• replication connections
• and environments where password-only auth is not enough

When it makes sense

Client certificates are especially useful when:

• the connecting population is small and well-managed
• credentials are highly sensitive
• you want stronger machine identity in addition to role/password controls
• or you want to require certificate-backed trust for privileged access

Practical warning

Do not deploy client certs casually. They introduce:

• certificate lifecycle management
• rotation requirements
• mapping decisions
• and operational overhead

They are powerful, but they should be used intentionally.

7. Separate Role Purposes Instead of Letting One Role Do Everything

One of the biggest PostgreSQL security mistakes is overloading one role with too many jobs.

Common bad pattern:

• one application role owns objects
• runs migrations
• handles runtime traffic
• can create more objects
• and quietly has broad powers because “it was easier”

That makes incident impact much worse.

Better role pattern

A safer production setup often separates roles like this:

• owner role: owns schemas and objects, usually NOLOGIN
• migration role: used by migrations and deployment tooling
• app runtime role: used by the application during normal traffic
• read-only role: used for analytics, dashboards, or support tooling
• admin role: highly privileged operational access, tightly controlled
• replication role: separate role for streaming replication when needed

This pattern prevents the runtime application connection from quietly having all the powers of deployment and administration.

8. Avoid Superuser for Application Traffic

This should be obvious, but it is still worth stating clearly:

application roles should almost never be superusers.

Superuser bypasses almost every meaningful permission boundary in PostgreSQL. It is not a convenience flag. It is a break-glass capability.

If a normal app connection is superuser, then:

• least privilege is gone
• row-level security assumptions may be invalidated
• accidental DDL becomes much easier
• dangerous functions and server-level actions become more reachable
• and credential theft becomes dramatically more damaging

Similar warning for other powerful attributes

Be very careful with:

• CREATEROLE
• CREATEDB
• REPLICATION
• BYPASSRLS

These are not harmless extras. They are meaningful privilege expansions and should be granted only when the role truly needs them.

9. Use NOLOGIN Group Roles and Grant Membership Deliberately

One very useful PostgreSQL pattern is to separate:

• roles that hold privileges from
• roles that actually log in

That usually means:

• create NOLOGIN roles for privilege bundles
• grant those roles to login roles as needed

Example pattern

create role app_rw nologin;
create role app_ro nologin;

create role app_api login password 'replace-me';
grant app_rw to app_api;

create role reporting_user login password 'replace-me';
grant app_ro to reporting_user;

This makes privilege structure easier to reason about and easier to change later.

Instead of granting object privileges to every login role individually, you manage a smaller set of privilege-bearing roles.

10. Grant Only the Privileges the Role Actually Needs

Least privilege is the core principle here.

A runtime application role typically needs a much smaller privilege set than developers often grant by default.

Common minimal needs

A normal application role may need:

• CONNECT on the database
• USAGE on one or more schemas
• SELECT, INSERT, UPDATE, DELETE on specific tables
• USAGE or SELECT/UPDATE on sequences if identity/sequence-backed writes need it
• maybe EXECUTE on specific functions

It usually does not need:

• object ownership
• schema creation
• role creation
• database creation
• bypass privileges
• broad cross-schema access

Good pattern

Grant only what the app needs for its runtime behavior, and let migrations or owners handle structural changes.

11. Review PUBLIC Privileges Explicitly

PostgreSQL grants some privileges to PUBLIC by default.

That is one of the reasons explicit privilege review matters, especially in shared environments or mature production databases.

Examples from the docs include default PUBLIC privileges such as:

• CONNECT and TEMPORARY on databases
• EXECUTE on functions and procedures
• USAGE on languages and data types

If you never review those defaults, you may be inheriting more openness than you intended.

Good places to review

• database-level CONNECT
• schema USAGE
• schema CREATE
• function EXECUTE
• default privileges for newly created objects

Practical example

If an application database should not allow arbitrary roles to connect:

revoke connect on database appdb from public;
grant connect on database appdb to app_api, reporting_user;

The point is not that every database must revoke everything from PUBLIC. The point is that it should be a decision, not an accident.

12. Harden the public Schema Deliberately

The public schema deserves explicit attention because it is easy to forget and easy to leave too open.

PostgreSQL’s schema docs note that everyone has USAGE on the public schema by default, and they also describe search-path hardening patterns such as removing public from the default search path in some environments.

Why this matters

If the public schema remains a casual dumping ground, you can end up with:

• confusing object resolution
• wider attack surface for shared databases
• less predictable function lookup
• and harder privilege reasoning

Better pattern

Treat public as something you explicitly decide how to use.

In many production setups, that means:

• using dedicated application schemas
• limiting create privileges in public
• and being deliberate about search_path

Example hardening steps may include:

revoke create on schema public from public;

and setting a safer role-level search path for application users.

13. Set search_path Carefully

search_path is not just a convenience setting. It is part of trust and object resolution.

If you let application roles resolve objects through broad or sloppy search paths, you increase the chance of:

• accidental object shadowing
• confusing function resolution
• or security-sensitive routines resolving in ways you did not intend

Better habits

• use explicit application schemas
• qualify objects where security sensitivity is high
• set predictable role-level search paths
• avoid leaving runtime behavior dependent on whatever happens to exist in public

A little discipline here goes a long way.

14. Separate Migration Roles From Runtime Roles

This is one of the cleanest security improvements you can make.

Bad pattern

Your app connects using the same role that:

• creates tables
• alters schemas
• creates functions
• and runs all production traffic

If that credential leaks, the attacker gets runtime access and structural control.

Better pattern

Use:

• a migration role for deployment and DDL
• a runtime role for normal application behavior

That way the application can:

• read and write what it needs

without also being able to:

• create arbitrary objects
• alter the schema
• or escalate through loose ownership patterns

This also makes privilege review much easier.

15. Be Careful With Role Attributes Like CREATEROLE, REPLICATION, and BYPASSRLS

PostgreSQL role attributes are powerful and should be granted with clear intent.

CREATEROLE

This is much broader than many teams expect. It is not something you hand out because a developer “might need it later.”

REPLICATION

Only replication roles should have this, and they should also be tightly constrained by network rules and credential handling.

BYPASSRLS

If you are using row-level security, this attribute is especially sensitive. PostgreSQL’s docs note that roles with BYPASSRLS always bypass row security, and superusers do too. That means this flag can quietly invalidate your tenant-boundary assumptions if granted carelessly.

Practical rule

If you cannot name the operational reason for one of these attributes, the role probably should not have it.

16. Use Connection Limits for Roles That Need Guardrails

PostgreSQL allows a role-level connection limit.

This is not a complete defense against overload, but it can be a useful guardrail for:

• support users
• analytics users
• special-purpose service roles
• or any role that should never fan out broadly

Example

alter role reporting_user connection limit 5;

This is not a replacement for proper pooling or workload control. It is an additional containment tool.

17. Lock Down Default Privileges for Future Objects

One of the most common privilege mistakes is securing today’s objects while forgetting tomorrow’s objects.

If your migrations keep creating new:

• tables
• sequences
• functions
• or types

then default privileges matter.

Why this matters

Without deliberate default privileges, new objects may end up with accessibility that does not match your intended model.

Example pattern

alter default privileges for role app_owner in schema app
revoke all on tables from public;

alter default privileges for role app_owner in schema app
grant select, insert, update, delete on tables to app_rw;

alter default privileges for role app_owner in schema app
grant select on tables to app_ro;

This makes the privilege model more consistent over time instead of relying on one-off grants after each migration.

18. Be Careful With Functions, Especially SECURITY DEFINER

Functions can create subtle privilege problems.

PostgreSQL’s docs explicitly warn that newly created functions are executable by PUBLIC by default and recommend revoking that access when needed, especially for security-definer functions.

Why this matters

A SECURITY DEFINER function runs with the privileges of its owner. That makes it powerful and potentially dangerous.

Better pattern

Create and lock it down in one transaction so there is no exposure window:

begin;

create function app.admin_task(...)
returns void
language plpgsql
security definer
as $$
begin
  -- function body
end;
$$;

revoke all on function app.admin_task(...) from public;
grant execute on function app.admin_task(...) to app_admin;

commit;

If you use SECURITY DEFINER, treat it like security-sensitive code, not just database convenience.

19. Use Separate Roles for Replication, Reporting, and Admin

Different database activities deserve different credentials.

Replication role

Should have:

• LOGIN
• REPLICATION
• the narrowest network access possible
• stronger credential handling than ordinary app users

Reporting role

Usually needs:

• CONNECT
• read-only object access
• maybe a connection limit
• no DDL power

Admin role

Should be:

• tightly controlled
• not used by applications
• logged and monitored carefully
• separated from ordinary developer or service accounts

Credential separation reduces blast radius when something goes wrong.

20. Monitor Authentication Failures and Privileged Role Usage

Connection and role security are not set-and-forget topics.

You should monitor:

• failed login patterns
• unexpected sources or clients
• usage of high-privilege roles
• long-lived privileged sessions
• idle in transaction sessions under powerful roles
• schema changes performed by roles that should not normally perform them

The point is not only to harden the configuration. It is also to make misuse visible.

A Practical Production Role Pattern

Here is a simple, production-friendly role model:

1. Owner role

Owns schemas and objects, but does not log in.

create role app_owner nologin;

2. Migration role

Used by deployment tooling.

create role app_migrator login password 'replace-me';
grant app_owner to app_migrator;

3. Runtime privilege roles

create role app_rw nologin;
create role app_ro nologin;

4. Runtime login roles

create role app_api login password 'replace-me';
grant app_rw to app_api;

create role app_reporting login password 'replace-me';
grant app_ro to app_reporting;

5. Explicit grants

grant connect on database appdb to app_api, app_reporting;
grant usage on schema app to app_rw, app_ro;
grant select, insert, update, delete on all tables in schema app to app_rw;
grant select on all tables in schema app to app_ro;

This is not the only good pattern. But it is much safer than letting one login role own everything.

Common Mistakes to Avoid

Mistake 1: Application role is superuser

This is one of the worst avoidable decisions.

Mistake 2: Migration role and runtime role are the same

This expands blast radius unnecessarily.

Mistake 3: TLS exists but clients do not verify the server

Encryption without verification is weaker than many teams assume.

Mistake 4: pg_hba.conf has broad early allow rules

Rule order matters.

Mistake 5: PUBLIC privileges are never reviewed

Defaults deserve explicit decisions.

Mistake 6: public schema remains loosely managed

This makes privilege and object-resolution behavior harder to reason about.

Mistake 7: Roles get CREATEROLE, REPLICATION, or BYPASSRLS “temporarily”

Temporary high privilege often becomes permanent high privilege.

FAQ

What is the safest authentication method for PostgreSQL passwords?

For password-based PostgreSQL authentication, SCRAM-SHA-256 is the modern default and the safest common choice. MD5 password support is deprecated and should be phased out.

Should application roles be superusers in PostgreSQL?

No. Application roles should almost never be superusers. They should have only the minimum privileges needed to connect, read, and write the required objects.

Should I use TLS for PostgreSQL connections inside a private network?

Usually yes. Private networking helps, but TLS still protects credentials and traffic in transit and helps clients verify that they are connecting to the correct server.

What is the best PostgreSQL role pattern for production?

A strong pattern is to separate owner roles, migration roles, runtime application roles, read-only roles, and admin roles so that no single role silently does everything.

Conclusion

Securing PostgreSQL connections and roles is mostly about disciplined separation and deliberate trust.

That means:

• narrowing network exposure
• enforcing TLS properly
• preferring SCRAM-SHA-256
• treating pg_hba.conf like a real security policy
• separating owner, migration, runtime, and admin roles
• granting only the privileges each role actually needs
• reviewing PUBLIC and public schema exposure
• and treating powerful role attributes like serious security decisions, not conveniences

The best PostgreSQL security setup is not the one with the most exotic features.

It is the one where every connection path and every role has a clear reason to exist, a clear boundary, and the minimum power needed to do its job.