postgresql connection security: best practices for secure database access

why postgresql connection security matters

postgresql connection security controls who can connect to your database, how they authenticate, what they can access, and whether data is protected while traveling across a network. a database may be hidden behind an application, but it is still a valuable target because it often contains user accounts, payment information, business records, and private application data.

good security is important for beginners learning coding, full-stack development, and devops. a secure connection setup reduces the risk of stolen passwords, unauthorized database access, data leaks, and accidental changes in production.

postgresql security should be implemented in layers:

• network security: limit which systems can reach the postgresql server.
• authentication: require strong and secure login methods.
• authorization: give each user or application only the permissions it needs.
• encryption: protect credentials and data during transmission.
• monitoring: record and review connection activity.
• operational security: protect backups, secrets, updates, and deployment pipelines.

use separate postgresql roles for people and applications

do not use the postgresql postgres superuser for normal application traffic. a superuser can bypass most permission checks, so a stolen application credential could result in complete database compromise.

create separate roles for different purposes. for example, you might use one role for application queries, another for database migrations, and another for read-only reporting.

-- run these commands as a trusted database administrator

create role app_runtime login password 'replace-with-a-strong-secret';

create role app_migrations login password 'replace-with-a-different-secret';

create role reporting_user login password 'replace-with-a-read-only-secret';

-- allow the application to connect to one database
grant connect on database shop_db to app_runtime;

-- allow the migration role to connect
grant connect on database shop_db to app_migrations;

-- allow the reporting role to connect
grant connect on database shop_db to reporting_user;

in a real environment, do not place passwords directly in source code or commit them to git. the passwords above are examples only. store credentials in a secrets manager or a protected environment variable.

follow the principle of least privilege

the principle of least privilege means that every role receives only the permissions required for its job. if an application only needs to read and update customer records, it should not have permission to create new databases, change roles, or delete unrelated tables.

-- select the correct database first
\c shop_db

-- permit the application to use the public schema
grant usage on schema public to app_runtime;

-- grant only the required table permissions
grant select, insert, update on table customers to app_runtime;
grant select, insert, update on table orders to app_runtime;

-- permit the application to use sequences created for identity columns
grant usage, select on all sequences in schema public to app_runtime;

for future tables, configure default privileges carefully. these commands should be executed by the role that creates the tables.

alter default privileges in schema public
grant select, insert, update on tables to app_runtime;

alter default privileges in schema public
grant usage, select on sequences to app_runtime;

review permissions regularly. unused roles, old contractors, test accounts, and former applications should be disabled or removed.

-- list roles
\du

-- disable an account that no longer needs access
alter role old_application nologin;

-- remove a role after confirming that it is no longer used
drop role old_application;

choose secure authentication methods

postgresql supports several authentication methods through the pg_hba.conf file. for password-based authentication, scram-sha-256 is the preferred modern option. it is stronger than the older md5 method and should be used when your postgresql version and clients support it.

configure postgresql to store new passwords using scram:

alter system set password_encryption = 'scram-sha-256';

after changing this setting, assign a new password to each role so postgresql stores the password using the selected format:

alter role app_runtime password 'use-a-long-random-password';

use a password generated by a password manager or secrets platform. avoid names, dictionary words, application names, and passwords reused in other services.

understand pg_hba.conf

the pg_hba.conf file defines which clients may connect, which database and role they may use, and how they must authenticate. postgresql checks the rules from top to bottom and uses the first matching rule, so rule order is important.

a simple secure example might look like this:

# local unix socket connections
local   all             postgres                                peer

# allow the application server to connect using scram
host    shop_db         app_runtime       10.20.0.15/32          scram-sha-256

# allow a private application subnet to connect
host    shop_db         app_runtime       10.20.0.0/24           scram-sha-256

# reject all other remote access
host    all             all               0.0.0.0/0              reject

important fields in a pg_hba.conf rule include:

• local or host: whether the connection uses a local socket or tcp/ip.
• database: the database the client wants to access.
• user: the postgresql role being used.
• address: the allowed client ip address or network range.
• method: the authentication method, such as scram-sha-256 or cert.

use narrow network ranges whenever possible. a rule such as 0.0.0.0/0 means every ipv4 address and is usually too permissive. if you must use a broad rule temporarily for testing, replace it with a restricted rule before deploying to production.

after editing the file, reload postgresql without restarting the entire service:

sudo systemctl reload postgresql

you can also reload the configuration from sql:

select pg_reload_conf();

encrypt postgresql connections with tls

authentication protects the login process, but it does not automatically guarantee that network traffic is protected. without encryption, credentials and database queries may be exposed if traffic passes through an untrusted network.

use tls, also called ssl in postgresql configuration, for connections between application servers and the database. first, check whether the server has tls enabled:

show ssl;

on a managed database platform, tls certificates and server configuration are often provided by the provider. for a self-managed server, configure a trusted certificate and private key with appropriate file permissions. a typical configuration may include:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

require tls in pg_hba.conf by using the hostssl connection type:

# only encrypted connections are accepted
hostssl  shop_db  app_runtime  10.20.0.15/32  scram-sha-256

# reject unencrypted connections to this database
hostnossl shop_db  all  10.20.0.0/24  reject

application connection strings should verify the server certificate instead of merely enabling encryption. for example:

postgresql://app_runtime:secret@db.example.internal:5432/shop_db?sslmode=verify-full&sslrootcert=/etc/ssl/certs/company-ca.crt

the verify-full option checks both the certificate authority and the database host name. avoid using sslmode=disable in production. also avoid relying on sslmode=require when certificate verification is required, because encryption without identity verification may still allow a man-in-the-middle attack.

restrict network access

postgresql should rarely be exposed directly to the public internet. place it inside a private network, such as a cloud vpc or protected data-center subnet, and allow connections only from approved application servers, administration systems, or secure vpn endpoints.

use multiple network controls:

• configure a firewall or cloud security group to allow postgresql traffic only from trusted sources.
• use the default postgresql port, 5432, only inside a protected network. changing the port is not a replacement for authentication or firewall rules.
• do not assign a public ip address to the database unless there is a strong, documented reason.
• use a vpn, bastion host, or private endpoint for administrative access.
• separate development, testing, and production networks.
• allow only the application servers that actually need database access.

for example, a linux firewall rule should be restricted to a known application subnet rather than allowing all sources:

# example only; adapt this to your firewall and environment
sudo ufw allow from 10.20.0.0/24 to any port 5432 proto tcp
sudo ufw deny 5432/tcp

firewall configuration should be tested from both an approved and an unapproved host. a secure rule is not useful if it has not been verified.

protect credentials and connection strings

a postgresql connection string commonly contains a host name, database name, user name, and password. treat the entire connection string as sensitive when it includes credentials.

do not write secrets in:

• public git repositories
• frontend javascript bundles
• blog posts, screenshots, or tutorials using real credentials
• issue trackers and chat messages
• unprotected configuration files
• application logs and error messages

backend applications can read a connection string from a protected environment variable:

# example environment variable
database_url=postgresql://app_runtime:redacted@db.internal:5432/shop_db?sslmode=verify-full

in devops workflows, use a secrets manager provided by your cloud platform or ci/cd system. limit which deployment jobs can read the secret, prevent it from appearing in build logs, and rotate it when a team member leaves or a potential leak occurs.

never place database credentials in a frontend application. code delivered to a browser can be inspected by every user. a full-stack application should connect to postgresql from a trusted backend service, while the frontend communicates with that backend through an authenticated api.

use secure application connection practices

use parameterized queries

connection security does not prevent sql injection. your application must also handle user input safely. always use parameterized queries or a well-configured database library instead of concatenating input into sql strings.

unsafe code can allow an attacker to change the meaning of a query:

// unsafe example
const query = "select * from users where email = '" + email + "'";

a parameterized query keeps the sql command separate from the user-provided value:

// safer node.js example using the pg library
const result = await pool.query(
  'select id, email from users where email = $1',
  [email]
);

parameterized queries are an important coding practice for javascript, python, java, php, and other programming languages. they protect the database even when a user submits unexpected characters or malicious input.

use connection pooling carefully

a connection pool reuses a limited number of database connections instead of opening a new connection for every request. pooling can improve performance, but it must be configured responsibly.

const { pool } = require('pg');

const pool = new pool({
  connectionstring: process.env.database_url,
  max: 10,
  idletimeoutmillis: 30000,
  connectiontimeoutmillis: 5000,
  ssl: {
    rejectunauthorized: true
  }
});

set a reasonable maximum connection count. too many connections can exhaust postgresql memory and cause denial-of-service conditions. consider the total number of application instances, workers, background jobs, and administrative connections when choosing pool sizes.

for serverless applications or large deployments, a managed pooler such as pgbouncer may help control connection growth. configure the pooler and database roles so that credentials are not unnecessarily exposed to every service.

set connection and query timeouts

timeouts help prevent a slow or unresponsive database connection from consuming application resources indefinitely.

const pool = new pool({
  connectionstring: process.env.database_url,
  connectiontimeoutmillis: 5000,
  statement_timeout: 10000,
  query_timeout: 10000
});

choose values based on the application. do not use very short timeouts for legitimate long-running jobs, but do not allow ordinary web requests to run unlimited queries either.

secure administrative access

database administrators need stronger controls than ordinary application users. administrative access should be limited to trusted people and systems.

• use individual administrator accounts rather than sharing one account.
• require multi-factor authentication for vpns, cloud consoles, and bastion hosts.
• use short-lived credentials or certificates where practical.
• connect through a private network or bastion host.
• keep a record of administrative actions.
• do not use superuser access for routine application queries.

for temporary access, create a role with an expiration date:

create role temporary_auditor
login
password 'temporary-strong-secret'
valid until '2026-12-31 23:59:59+00';

grant connect on database shop_db to temporary_auditor;

remove or disable temporary access as soon as the task is complete. time-limited access is safer than leaving permanent credentials active.

monitor connections and security events

monitoring helps you identify unexpected access, repeated authentication failures, unusual connection locations, and excessive connection counts. postgresql logging should be enabled according to your privacy and compliance requirements.

useful settings may include:

log_connections = on
log_disconnections = on
log_line_prefix = '%m [%p] user=%u,db=%d,app=%a,client=%h '
log_min_duration_statement = 1000

be careful with statement logging. sql statements may contain personal data or other sensitive values. store logs securely, limit access to them, and define an appropriate retention period.

you can inspect current sessions with:

select
  pid,
  usename,
  datname,
  client_addr,
  application_name,
  state,
  backend_start,
  query_start
from pg_stat_activity
where datname is not null
order by query_start desc;

look for connections from unexpected ip addresses, unknown application names, inactive sessions that remain open for too long, and accounts that should no longer be active.

keep postgresql, drivers, and dependencies updated

security updates may fix vulnerabilities in postgresql, operating systems, database drivers, connection poolers, and cloud tools. create a maintenance process that includes:

• tracking postgresql security announcements.
• testing upgrades in a staging environment.
• applying operating system and database patches promptly.
• updating application drivers and orm packages.
• testing authentication and tls settings after upgrades.
• documenting rollback and recovery procedures.

in a devops pipeline, database configuration should be reviewed as code when possible. use code review for changes to firewall rules, pg_hba.conf, roles, permissions, and deployment secrets.

protect backups and replication connections

database security also includes backups. an encrypted postgresql server does not protect an unencrypted backup stored in an open bucket or shared file system.

• encrypt backups both at rest and during transfer.
• restrict backup access to specific administrators and services.
• use separate credentials for backup jobs.
• test restoring backups regularly.
• define how long backups should be retained.
• keep production and backup credentials separate.

replication connections require special attention because they can provide access to a large amount of database data. create a dedicated replication role and restrict its source address in pg_hba.conf.

create role replica_user
with replication login password 'use-a-dedicated-replication-secret';

-- pg_hba.conf example
hostssl  replication  replica_user  10.30.0.12/32  scram-sha-256

do not reuse the main application password for replication, backups, migrations, or monitoring.

common postgresql security mistakes

• using the superuser in application code: a compromised application could control the entire database server.
• allowing connections from everywhere: broad network rules increase the attack surface.
• using plain or weak passwords: prefer long random secrets and scram authentication.
• disabling tls: unencrypted connections can expose credentials and data.
• putting credentials in frontend code: browser code is visible to users.
• building sql with string concatenation: use parameters to prevent sql injection.
• ignoring default privileges: new tables may accidentally be exposed to roles that should not access them.
• sharing administrator accounts: individual accounts improve accountability and access control.
• leaving test roles active in production: remove temporary and unused access.
• forgetting backups: a secure database still needs a tested recovery plan.

practical postgresql connection security checklist

use this checklist when building or reviewing a postgresql environment:

• run applications with dedicated, non-superuser roles.
• grant only the tables, schemas, and operations each role requires.
• use scram-sha-256 for password authentication.
• review pg_hba.conf from top to bottom and remove broad rules.
• allow database traffic only from approved private networks.
• require tls and use certificate verification where possible.
• store credentials in a secrets manager, not in source code.
• use parameterized queries in every application.
• configure connection pools, connection limits, and timeouts.
• use separate roles for runtime queries, migrations, reporting, backups, and replication.
• enable appropriate connection logging and review it regularly.
• patch postgresql, drivers, operating systems, and dependencies.
• encrypt backups and test database restoration.
• review permissions and disable unused accounts.

final recommendations

secure postgresql access is not one setting; it is a combination of network controls, strong authentication, encryption, carefully designed roles, safe coding, and regular monitoring. start with the basics: keep the database private, use scram passwords, require tls, avoid superuser accounts, and protect your connection strings.

as your full-stack or devops project grows, automate security checks and review access as part of every deployment. a small application can use the same habits as a large production system: least privilege, secure secrets, parameterized queries, limited network access, and tested backups. these practices improve reliability as well as security, and they provide a strong foundation for responsible software engineering and seo-friendly technical documentation.