Introduction

PostgreSQL is one of the most powerful, open-source relational databases in the world, trusted by enterprises, startups, and developers for its reliability, extensibility, and security. But even the most robust database can be compromised if user access is not properly managed. Creating a PostgreSQL user isnt just about running a CREATE USER commandits about establishing a foundation of trust. A poorly configured user account can become the weakest link in your security chain, exposing sensitive data to unauthorized access, privilege escalation, or even full system compromise.

This guide walks you through the top 10 proven methods to create a PostgreSQL user you can trust. Each step is designed with security, auditability, and operational integrity in mind. Whether you're setting up a new application database, migrating from another system, or auditing existing users, these practices ensure your PostgreSQL environment remains resilient against modern threats.

Trust in database users isnt assumedits engineered. By following these guidelines, you move beyond basic configuration and implement a security-first mindset that aligns with industry standards like CIS PostgreSQL Benchmark, NIST guidelines, and OWASP Database Security recommendations.

Why Trust Matters

Database security begins with user identity and access control. In PostgreSQL, every connection and query is tied to a user role. If that role is misconfigured, overprivileged, or poorly authenticated, the entire system is at risk. Trust isnt about whether a user is knownits about whether their access is justified, limited, monitored, and verifiable.

Consider these real-world consequences of untrusted users:

• A developer account with superuser privileges accidentally drops a production table.
• A compromised application user with write access exfiltrates customer data via SQL injection.
• An old, unused account with no password expiration becomes a backdoor for attackers.

According to the 2023 IBM Cost of a Data Breach Report, compromised credentials are the leading cause of data breaches in database environmentsaccounting for over 25% of incidents. Most of these breaches stem from excessive privileges, weak authentication, or lack of role separation.

Trustworthy PostgreSQL users are characterized by:

• Principle of Least Privilege: Only the minimum permissions needed to perform a task.
• Strong Authentication: Password policies, SSL enforcement, and integration with external identity systems.
• Account Lifecycle Management: Creation, rotation, and deactivation are documented and automated.
• Activity Monitoring: Logs track who did what, when, and from where.
• No Default or Shared Accounts: Every user is individually identifiable.

Creating a trustworthy PostgreSQL user isnt a one-time taskits an ongoing discipline. The following ten methods form the core of that discipline, each addressing a critical dimension of trustworthiness.

Top 10 How to Create Postgres User You Can Trust

1. Use Role-Based Access Control (RBAC) with Explicit Privileges

PostgreSQLs role system is the cornerstone of secure access. Never grant superuser privileges unless absolutely necessary. Instead, create dedicated roles for specific functions: application_read, application_write, reporting, backup_admin, etc.

Start by creating a role without login rights to serve as a group:

CREATE ROLE app_write;

Then assign permissions to that role:

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_write;
GRANT USAGE ON SCHEMA public TO app_write;

Finally, create a user and add them to the role:

CREATE USER app_user WITH PASSWORD 'StrongP@ssw0rd!2024';
GRANT app_write TO app_user;

This approach ensures that if the user account is compromised, the attacker inherits only the permissions of the rolenot full database control. It also simplifies audits: you can review permissions at the role level rather than tracking individual users.

Always avoid using the default postgres superuser for application connections. Reserve it for administrative tasks only.

2. Enforce Strong Password Policies with SCRAM-SHA-256

Passwords are still the most common authentication method in PostgreSQLand the most vulnerable. By default, PostgreSQL uses MD5 hashing, which is cryptographically broken. Always enforce SCRAM-SHA-256, the modern, secure authentication protocol introduced in PostgreSQL 10.

To enforce SCRAM-SHA-256, modify your pg_hba.conf file:

host    all             all             0.0.0.0/0               scram-sha-256

Then restart PostgreSQL:

sudo systemctl restart postgresql

When creating users, ensure the password is stored using SCRAM:

CREATE USER secure_user WITH PASSWORD 'A1b2C3d4E5f6!@
$%';

PostgreSQL will automatically use SCRAM-SHA-256 if the client supports it and pg_hba.conf is configured correctly. You can verify this by checking the pg_authid table:

SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'secure_user';

Look for a password hash starting with SCRAM-SHA-256$ to confirm secure storage.

Additionally, enforce password complexity using the pg_password_policy extension (available in PostgreSQL 14+) or external tools like LDAP integration. Avoid dictionary words, predictable patterns, and reuse across systems.

3. Disable Login for Non-Interactive Roles

Many administrators create roles with login permissions even when those roles are meant to be used as group containers. This is a security anti-pattern.

Roles that represent functional groups (e.g., read_only, admin_team) should never have the LOGIN attribute. Only user accounts that need to connect to the database should be granted login rights.

Example of correct usage:

-- Create a group role without login
CREATE ROLE read_only;
-- Grant permissions
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
-- Create a user who can login and inherits permissions
CREATE USER jane_doe WITH PASSWORD 'SecurePass123!' LOGIN;
GRANT read_only TO jane_doe;

Example of incorrect usage (avoid this):

CREATE USER read_only WITH PASSWORD 'abc123' LOGIN; -- DON'T DO THIS

Non-login roles are invisible to attackers scanning for login-capable accounts. They also make permission management cleaner and reduce the attack surface. Always audit your roles periodically:

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles WHERE rolcanlogin = true;

Any role with LOGIN that isnt tied to a specific human or service account should be reviewed and potentially converted to a non-login group role.

4. Restrict Access by IP Address and Network

PostgreSQL does not have built-in firewalling, but pg_hba.conf provides granular control over who can connect and from where. Trustworthy users are not just authenticatedthey are also geographically and network-constrained.

Configure pg_hba.conf to allow connections only from trusted networks:

Allow app server (192.168.1.10) to connect
host    myappdb       app_user        192.168.1.10/32         scram-sha-256
Allow internal monitoring system
host    myappdb       monitor_user    192.168.1.50/32         scram-sha-256
Deny all others
host    all           all             0.0.0.0/0               reject

Never use trust or ident authentication in production environments. Even in internal networks, password-based authentication with SCRAM-SHA-256 is mandatory.

For cloud deployments, combine pg_hba.conf with VPC security groups or AWS Security Groups to enforce network-level restrictions. This creates defense-in-depth: even if an attacker bypasses authentication, they still cannot reach the PostgreSQL port.

Regularly audit your pg_hba.conf entries. Remove outdated IPs, consolidate overlapping rules, and ensure no wildcard entries (like 0.0.0.0/0) are allowed for privileged users.

5. Implement Connection Limits and Session Timeouts

Unlimited connections and idle sessions create opportunities for resource exhaustion and persistent access by compromised accounts. Trustworthy users are not only restricted in what they can dobut also in how long and how often they can do it.

Set connection limits per user in PostgreSQL:

ALTER USER app_user CONNECTION LIMIT 10;

This ensures the application user cannot open more than 10 concurrent connections, preventing connection pool abuse or denial-of-service attacks.

Additionally, enforce session timeouts to automatically terminate idle connections:

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

The first setting kills transactions that remain idle for more than five minutes. The second kills any query that runs longer than 30 seconds. This prevents slow queries from tying up resources and stops malicious scripts from running indefinitely.

For applications that use connection pooling (like PgBouncer), set pool limits at the pooler level and enforce stricter timeouts at the PostgreSQL user level. This layered approach ensures no single user can monopolize database resources.

6. Use SSL/TLS for All Connections

Unencrypted database traffic is a ticking time bomb. Passwords, queries, and data can be intercepted over untrusted networkseven internal ones. Trustworthy PostgreSQL users require encrypted communication.

Enable SSL in postgresql.conf:

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

Generate a valid certificate (self-signed or from a trusted CA) and place it in PostgreSQLs data directory. Then enforce SSL in pg_hba.conf:

hostssl all             all             0.0.0.0/0               scram-sha-256

The hostssl directive ensures only SSL-encrypted connections are accepted. host (non-SSL) connections are rejected.

Client applications must also be configured to use SSL. For example, in a Node.js app using pg:

const client = new Client({
connectionString: 'postgresql://app_user:password@db.example.com:5432/mydb',
ssl: {
rejectUnauthorized: true,
ca: fs.readFileSync('/path/to/root.crt')
}
});

Always validate certificate chains and avoid sslmode=allow or sslmode=disable in production. Use sslmode=require or sslmode=verify-full for maximum security.

SSL not only protects data in transitit also helps prevent man-in-the-middle attacks and ensures clients are connecting to the legitimate server.

7. Regularly Rotate Passwords and Keys

Passwords should never be static. Trustworthy users have credentials that are rotated on a schedule, regardless of whether a breach is suspected.

Set a password expiration policy:

ALTER USER app_user VALID UNTIL '2024-12-31';

Combine this with automated scripts that rotate passwords every 6090 days using a secrets management system (like HashiCorp Vault, AWS Secrets Manager, or even a secure internal key store).

Example automation workflow:

1. Script generates a new random 32-character password.
2. Password is stored in secrets manager with audit log.
3. Script updates the PostgreSQL user: ALTER USER username WITH PASSWORD 'new_password';
4. Script pushes the new password to all dependent services via configuration management (Ansible, Terraform, etc.).
5. Old password is revoked and audit log is updated.

For service accounts using certificate-based authentication, rotate certificates on the same schedule. Never reuse keys or certificates across environments.

Keep a rotation calendar and integrate it into your change management process. Document every rotation and verify that applications continue to function after the change.

8. Audit User Activity with Logging and Monitoring

Trust is earned through visibility. You cannot trust a user if you cannot see what they do. PostgreSQLs logging system must be configured to capture detailed activity.

Enable logging in postgresql.conf:

log_statement = 'all'
log_destination = 'csvlog'
logging_collector = on
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = '1d'
log_rotation_size = '100MB'

log_statement = all logs every SQL command executed. For performance-sensitive environments, use log_statement = mod to log only INSERT, UPDATE, DELETE, and DROP statements.

Use tools like pgBadger or Grafana with Loki to parse and visualize logs. Set up alerts for:

• Multiple failed login attempts from a single IP.
• Execution of DROP TABLE or TRUNCATE by non-admin users.
• Connections from unusual geographic locations or times.

Store logs in a separate, write-once system (e.g., AWS S3 with object lock, or a SIEM) to prevent tampering. Never keep logs on the same server as the database.

Regularly review logs for anomalies. A trustworthy user leaves a clear, traceable trail. A malicious actor will try to erase or obscure theirsso your logging must be robust enough to detect even subtle deviations.

9. Avoid Shared Accounts and Enforce Individual Identity

Shared accounts are a security nightmare. If ten developers use the same dev_user account, you cannot determine who made a change. You cannot revoke access for one person without affecting others. You cannot assign accountability.

Trustworthy PostgreSQL environments require one user per person or service. Each account must be uniquely identifiable.

For developers, create individual accounts:

CREATE USER dev_john WITH PASSWORD '...';
CREATE USER dev_sarah WITH PASSWORD '...';
GRANT read_only TO dev_john, dev_sarah;

For services (e.g., CI/CD pipelines, backup scripts), create dedicated service accounts:

CREATE USER backup_svc WITH PASSWORD '...' NOLOGIN;
CREATE USER ci_cd_svc WITH PASSWORD '...' NOLOGIN;

Then grant them only the permissions they need:

GRANT pg_read_all_data TO backup_svc;
GRANT INSERT, UPDATE ON audit_log TO ci_cd_svc;

Use a centralized identity provider (LDAP, Active Directory, or SAML) if possible. PostgreSQL supports external authentication via PAM, LDAP, and Kerberos. This allows you to manage user lifecycles in one place and automatically disable accounts when employees leave.

Never, under any circumstances, use postgres or admin as a login for applications or shared access.

10. Conduct Regular Access Reviews and Role Cleanup

Permissions drift over time. Users change roles, applications are decommissioned, and old accounts linger. Trustworthy PostgreSQL environments require proactive, scheduled access reviews.

Establish a quarterly access review process:

1. Generate a report of all users and their permissions:

SELECT r.rolname AS role_name,
m.rolname AS member_name,
m.rolcanlogin AS can_login
FROM pg_auth_members am
JOIN pg_roles r ON am.roleid = r.oid
JOIN pg_roles m ON am.member = m.oid
ORDER BY r.rolname, m.rolname;

2. Compare against HR records or service inventories to identify inactive or unauthorized users.
3. Revoke unused roles and delete orphaned accounts:

REVOKE app_write FROM former_employee;
DROP USER former_employee;

3. Document decisions and approvals.
4. Automate the process using scripts and integrate with your ITSM tool.

Also review role inheritance chains. Avoid deep nesting of roleseach additional layer increases complexity and risk. Keep roles flat and purpose-driven.

Use PostgreSQLs information_schema and pg_catalog views to generate automated compliance reports. Integrate these into your DevOps pipeline to fail builds if unauthorized users or excessive privileges are detected.

Comparison Table

FAQs

Can I use the default postgres user for my application?

No. The postgres user is a superuser with unrestricted access to all databases and system functions. Using it for application connections is a severe security violation. Always create a dedicated, least-privilege user for each application.

What happens if I dont use SCRAM-SHA-256?

If you use MD5 or trust authentication, passwords are either hashed with a broken algorithm or sent in plaintext. Attackers can capture credentials via packet sniffing or brute-force attacks. SCRAM-SHA-256 is the minimum standard for secure authentication in PostgreSQL.

How often should I rotate database passwords?

Best practice is every 60 to 90 days. For high-risk environments (financial, healthcare), rotate every 30 days. Use automation to avoid human error and ensure consistency.

Can I use SSH tunneling instead of SSL?

Yes, SSH tunneling can encrypt traffic between your application and PostgreSQL. However, it adds complexity and requires SSH key management. SSL is native to PostgreSQL and easier to audit. Use SSL unless you have a specific reason to prefer SSH.

What should I do if I suspect a PostgreSQL user has been compromised?

Immediately revoke the users access: REVOKE ALL PRIVILEGES FROM username; DROP USER username;. Then rotate all related passwords, review logs for malicious activity, and audit all roles the user had access to. Consider a full security review of your database environment.

Is it safe to use wildcards in pg_hba.conf (e.g., 0.0.0.0/0)?

Only if the user has strong authentication (SCRAM-SHA-256) and is restricted to minimal privileges. Never use wildcards for superusers or users with write access. Always prefer specific IPs or subnets.

How do I check which roles a user belongs to?

Run this query:

SELECT r.rolname AS role_name
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON am.member = m.oid
WHERE m.rolname = 'your_username';

Can PostgreSQL users be deleted permanently?

Yes. Use DROP USER username; to remove a user and all their permissions. Ensure no objects (tables, functions) are owned by the user before deletion, or use CASCADE to transfer ownership: DROP USER username CASCADE;

Whats the difference between CREATE USER and CREATE ROLE?

CREATE USER is equivalent to CREATE ROLE ... LOGIN. CREATE ROLE creates a role without login privileges by default. Use CREATE ROLE for group roles and CREATE USER (or CREATE ROLE ... LOGIN) for accounts that need to connect.

How do I enforce password complexity?

PostgreSQL does not enforce complexity natively. Use the pg_password_policy extension (PostgreSQL 14+), integrate with LDAP/AD policies, or use application-level validation before passing passwords to PostgreSQL.

Conclusion

Creating a PostgreSQL user you can trust is not a technical checkboxits a cultural commitment to security, accountability, and operational excellence. Each of the ten methods outlined in this guide addresses a critical vulnerability that, if left unaddressed, can lead to data breaches, compliance failures, and system compromise.

Trust is built through discipline: enforcing least privilege, encrypting connections, rotating credentials, logging activity, and regularly reviewing access. Its not enough to create a useryou must continuously validate that they remain trustworthy.

Start by auditing your current users using the queries provided. Identify any superusers used for applications, any accounts without SSL, any unused roles, or any shared credentials. Prioritize remediation based on risk.

Automate what you canpassword rotation, access reviews, and loggingso human error doesnt become your weakest link. Integrate these practices into your CI/CD pipelines and infrastructure-as-code templates so every new database deployment begins with trust.

PostgreSQL is a secure database. But security is not a featureits a practice. The most powerful tool you have is not a command or a configuration file. Its the consistent, deliberate application of these ten principles. Build your users with care. Protect your data with purpose. And never stop questioning: Do I really trust this user?