Day 13: Security Best Practices – Locking Down Your PostgreSQL Data

Day 13: Security Best Practices – Locking Down Your PostgreSQL Data

Welcome back to our 15‑day PostgreSQL journey! Yesterday we explored transactions and concurrency control. Today we pivot to something equally important – keeping your data safe. Databases, like houses, don’t come with a magic button labeled “Make Me Secure.” They ship with sensible defaults, but defaults are designed to work “out of the box,” not necessarily to protect against malicious neighbors. In this guide we’ll walk through how to build a proper security fence around your Postgres instance without turning it into Fort Knox.

We’ll focus on three pillars often abbreviated as AAA – Authentication, Authorization and Accounting. Think of them as who gets in, what they can do and how we’ll know if something goes wrong. Along the way we’ll sprinkle in real‑world analogies, show practical SQL examples and keep the tone friendly. Ready? Let’s bolt that database door!

---

What You'll Learn

1. Why Security Matters – understanding threats and why “it won’t happen to me” is dangerous.
2. Authentication Methods – from password logins to Kerberos; configuring pg_hba.conf like a bouncer list.
3. Authorization & Roles – designing role hierarchies, the principle of least privilege and why SUPERUSERs are like chainsaws.
4. Row‑Level Security – shielding sensitive rows from prying eyes.
5. Encryption in Transit & At Rest – enabling TLS/SSL and when to consider pgcrypto.
6. Audit & Monitoring – why logging isn’t just for compliance; introduction to pgAudit.
7. Hands‑On Challenge – tasks to practice setting up roles, TLS and row‑level policies.

---

1 — Why Security Matters

Picture this: you hand keys to your apartment to every friend who visits. “It’s just for now,” you tell yourself. Months later you have no idea who still has a copy. Database security often starts the same way – quick and permissive so developers can get moving – and hardens only after an incident.

PostgreSQL doesn’t hide this complexity. As Percona points out, security begins by asking three simple but critical questions: Who gets in? What can they do? And how will we know if something goes wrong?【875810658030688†L255-L274】. Answering these questions requires intention; otherwise that “temporary” permissive configuration becomes permanent【875810658030688†L267-L274】.

Threats to consider

• Unsecured network connections – Anyone between your app and the database could eavesdrop without TLS encryption【640492761319988†L348-L389】.
• Weak or default passwords – MD5 is outdated. PostgreSQL supports SCRAM‑SHA‑256 and recommends using it over MD5【640492761319988†L476-L489】.
• Privilege creep – Roles copied and repurposed until nobody knows who has what; GRANTs handed out broadly then forgotten【875810658030688†L295-L302】.
• Missing audit trail – Without proper logging, you won’t know who touched what when an incident happens【875810658030688†L314-L330】.

The good news? Postgres gives you the tools to mitigate these risks. You just need to use them intentionally.

---

2 — Authentication: Who Gets in?

Authentication is your database’s front door. It determines whether a connection request should even be considered. The configuration lives in the pg_hba.conf file, which defines connection types, databases, users, client addresses and the authentication method【640492761319988†L400-L431】. When a connection is attempted, Postgres walks this file top‑down, and the first matching line wins【640492761319988†L402-L406】.

2.1 Choosing an authentication method

PostgreSQL supports several methods, but not all are equal. Percona suggests grouping them into three categories【875810658030688†L278-L287】:

• Internal authentication – Password‑based logins such as SCRAM‑SHA‑256 are the default and should be used over MD5【640492761319988†L476-L489】.
• Operating‑system authentication – peer uses the OS user account; good for local connections where both OS and database users align【640492761319988†L455-L463】.
• External authentication – LDAP or Kerberos integrate with enterprise identity systems【640492761319988†L491-L513】. Kerberos is preferred because passwords never reach the Postgres server【640492761319988†L511-L513】.

Avoid the trust method unless you’re in a lab environment. trust allows any matching client to connect without further checks【640492761319988†L439-L453】. This is like leaving your front door wide open.

2.2 Configuring pg_hba.conf

A typical secure entry might look like this:

# TYPE  DATABASE   USER        ADDRESS             METHOD
# Local connections use scram
local   mydb       app_user                       scram-sha-256
# Accept connections only from application servers
hostssl mydb       app_user  10.0.0.0/24          scram-sha-256
# Deny everyone else
host    all        all        0.0.0.0/0           reject

• We specify hostssl for network connections so that traffic must be encrypted【640492761319988†L433-L434】.
• We use scram‑sha‑256 for password authentication, avoiding MD5【640492761319988†L476-L489】.
• We explicitly reject everything not matched above, preventing accidental exposures.

Restart the PostgreSQL service after updating pg_hba.conf to apply changes. Test your connection and ensure authentication works as expected.

2.3 SCRAM vs. MD5 in practice

If you’re still using MD5, switch to SCRAM. Both MD5 and SCRAM store hashed passwords to avoid sending them over the wire, but SCRAM uses a cryptographically secure hash and includes a salt and iteration count【640492761319988†L476-L485】. To migrate existing MD5 users:

1. Enable SCRAM in postgresql.conf:

   password_encryption = scram-sha-256

1. Update each user’s password:

   ALTER ROLE app_user WITH PASSWORD 'newStrongPassword';

Postgres will automatically store the password using the SCRAM algorithm. Existing MD5 hashes will continue to work until the password is reset.

---

3 — Network Boundaries and Transport Encryption

Even if authentication is strong, sending credentials over an unencrypted network is like whispering secrets in a crowded room. Postgres uses OpenSSL to encrypt connections via TLS【640492761319988†L348-L369】. Here’s how to enable it:

1. Generate certificates – Create a Certificate Authority (CA), a server certificate signed by the CA and (optionally) client certificates【640492761319988†L515-L531】.
2. Update postgresql.conf:

   ssl = on
   ssl_cert_file = '/etc/ssl/certs/server.crt'
   ssl_key_file  = '/etc/ssl/private/server.key'
   ssl_ca_file   = '/etc/ssl/certs/ca.crt'

1. Require TLS in pg_hba.conf by using hostssl or hostgssenc entries【640492761319988†L433-L434】.
2. Tune ciphers – Configure ssl_ciphers, ssl_min_protocol_version and related settings. Recommended values evolve; periodically review them【640492761319988†L373-L387】.

Once enabled, use connection parameters in your client:

psql "sslmode=verify-full host=mydb.example.com dbname=mydb user=app_user"

Remember: internal networks get compromised too. Encrypt everything, even traffic inside your VPC【875810658030688†L375-L392】.

---

4 — Authorization & Role‑Based Access Control (RBAC)

Authorization answers the question: “What can they do once they’re in?” PostgreSQL uses a flexible role system that can represent users, groups or service accounts. Early versions had separate user and group concepts, but since PostgreSQL 8.1 these were unified into roles【640492761319988†L579-L583】.

4.1 Role attributes & why SUPERUSER is dangerous

Roles have a set of attributes that control their capabilities: LOGIN, SUPERUSER, CREATEDB, CREATEROLE, REPLICATION, PASSWORD, BYPASSRLS and VALID UNTIL【640492761319988†L599-L610】.

• SUPERUSER bypasses nearly every permission check【640492761319988†L611-L613】. It’s like handing someone the master key and root access. Avoid using a superuser for day‑to‑day operations【640492761319988†L617-L619】.
• LOGIN roles represent end users or applications.
• Group roles aggregate privileges; membership is granted via GRANT role TO role【640492761319988†L585-L593】.

Create roles with the minimum privileges needed and avoid mixing CREATEROLE or CREATEDB with application accounts. For example:

-- Create a read-only role for analysts
CREATE ROLE reporting_role;
GRANT CONNECT ON DATABASE mydb TO reporting_role;
GRANT USAGE ON SCHEMA public TO reporting_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO reporting_role;

-- Create an application role
CREATE ROLE app_user LOGIN PASSWORD 'useSCRAM';
GRANT INSERT, UPDATE, SELECT ON TABLE orders TO app_user;

4.2 Least privilege and role hierarchies

A well‑designed system uses roles together with Access Control Lists (ACLs) to protect schema and data. EDB suggests that the schema should be owned by a non‑superuser role, not the role used by the application【640492761319988†L760-L772】. Create group roles representing job functions and grant these to login roles; avoid granting privileges directly to every user【640492761319988†L767-L774】. Regularly audit roles and remove unused privileges【875810658030688†L295-L302】.

The GRANT and REVOKE commands control object‑level access. Default privileges can be overridden so that, for example, a team automatically receives read/write rights on new tables【640492761319988†L790-L804】. Use them to enforce least privilege and keep ACLs manageable【640492761319988†L825-L830】.

4.3 Monitoring roles and SET ROLE

Postgres provides built‑in monitoring roles (pg_monitor, pg_read_all_settings, etc.) that allow you to delegate read‑only access to system statistics without giving superuser rights【640492761319988†L704-L721】. When you need to temporarily elevate privileges, use SET ROLE or SET SESSION AUTHORIZATION instead of granting persistent rights【640492761319988†L675-L702】 – think of them as sudo for SQL sessions.

---

5 — Row‑Level Security (RLS)

Sometimes object‑level privileges aren’t enough. Suppose you have a patients table and want doctors to see only their own patients’ records. Row‑Level Security lets you attach policies that filter which rows a user can see or modify. By default, RLS is disabled; you enable it per table:

ALTER TABLE patients ENABLE ROW LEVEL SECURITY;

Policies combine roles, operations and a USING expression. For example, to allow each doctor to access only their own patients:

CREATE POLICY patients_doctor_policy
    ON patients
    TO doctor_role
    USING (doctor_id = current_user);

Important caveats:

• Superusers and roles with BYPASSRLS always bypass policies, as do table owners【640492761319988†L839-L842】.
• A user might infer the existence of hidden rows through constraints like unique indexes【640492761319988†L842-L846】. Be mindful when designing constraints on sensitive fields.

RLS requires careful indexing. When policies filter by user, ensure indexes exist on the filtering columns (doctor_id in the example) to avoid performance degradation【640492761319988†L874-L876】. While RLS adds complexity, it’s essential in multi‑tenant applications and regulated industries like healthcare【640492761319988†L876-L878】.

---

6 — Encryption at Rest & pgcrypto

Postgres can’t magically encrypt every byte for you, but there are two main approaches:

1. File‑system encryption – Use full‑disk encryption provided by your operating system (BitLocker, FileVault, LUKS). It protects data if the disk is stolen but offers no protection when the server is running【640492761319988†L99-L117】.
2. Application‑level encryption – Use the pgcrypto extension to encrypt sensitive columns inside the database. pgcrypto provides high‑level PGP functions and low‑level symmetric encryption【640492761319988†L990-L1076】. For example:

   -- Create extension once per database
   CREATE EXTENSION IF NOT EXISTS pgcrypto;

   -- Insert encrypted credit card data
   INSERT INTO payments(user_id, cc_encrypted)
   VALUES (42, pgp_sym_encrypt('4111111111111111', 'supersecretkey'));

   -- Read and decrypt
   SELECT user_id,
          pgp_sym_decrypt(cc_encrypted, 'supersecretkey') AS cc_number
   FROM payments;

Key management becomes your responsibility. Don’t hard‑code keys in code. Consider using a key management service (KMS) and rotate keys periodically【640492761319988†L1127-L1161】.

---

7 — Accounting: Logging and Auditing

Authentication and authorization are useless if you can’t answer, “Who touched what and when?” PostgreSQL’s built‑in logging covers connection attempts and queries, but for deeper visibility you’ll want an audit trail. Percona recommends using the pgAudit extension to capture session‑level and object‑level information【875810658030688†L314-L330】.

7.1 Configuring pgAudit

pgAudit logs each statement with enough detail to rebuild what happened during a session. Here’s a simple setup:

1. Install the extension (may require your package manager):

   sudo apt install postgresql-14-pgaudit

1. Load the extension into your database:

   CREATE EXTENSION pgaudit;

1. Configure parameters in postgresql.conf:

   shared_preload_libraries = 'pgaudit'
   pgaudit.log = 'read, write, function'
   pgaudit.log_relation = on
   pgaudit.log_parameter = on

1. Reload PostgreSQL and inspect the logs. You’ll now see entries like AUDIT: SESSION, READ, SELECT… capturing the executed SQL and the user.

Combine database logs with OS and network logs to get a complete picture. This layered approach means if one layer fails, another catches it【875810658030688†L338-L349】.

---

8 — Common Mistakes & How to Avoid Them

Security can be overwhelming, but most pitfalls boil down to a few patterns. Here are typical missteps and how to fix them:

Mistake	Fix
Leaving PostgreSQL listening on all interfaces	Set listen_addresses to a specific IP and control access via firewalls【875810658030688†L356-L365】
Using trust or md5 in production	Use scram‑sha‑256 and avoid trust except in controlled environments【640492761319988†L439-L453】【640492761319988†L476-L489】
Granting privileges directly to every user	Create group roles reflecting job functions; assign privileges to groups and grant groups to users【640492761319988†L760-L774】
Running everything as a superuser	Create unprivileged roles for applications; use SET ROLE to elevate temporarily【640492761319988†L617-L619】【640492761319988†L675-L702】
Skipping TLS because traffic is “internal”	Enable SSL/TLS for all network traffic; configure certificates and ciphers【640492761319988†L348-L389】【875810658030688†L375-L392】
Ignoring audit logging until an incident	Install pgAudit and configure comprehensive logging from day one【875810658030688†L314-L330】

---

9 — Hands‑On Challenge

It’s your turn to lock down a Postgres instance. Use a scratch database or a Docker container so you can experiment safely. Try to:

1. Enable SCRAM and update passwords – Set password_encryption = scram-sha-256 and alter an existing role’s password.
2. Restrict access in pg_hba.conf – Require hostssl connections from a specific subnet and reject everything else.
3. Create roles and group roles – Set up sales_team and sales_app roles. Grant privileges on the orders table accordingly.
4. Enable row‑level security – On the orders table, allow each salesperson to see only their own orders.
5. Install pgAudit – Capture and inspect audit logs while performing queries.
6. Bonus: encrypt a credit_card column using pgcrypto and demonstrate storing/retrieving the data.

Share your pg_hba.conf entries, SQL commands or audit.log snippets in the comments. If you encounter issues, describe what happened. We learn best by trying and iterating.

---

Summary & Key Takeaways

Securing a PostgreSQL database isn’t about flipping one switch. It’s about layering defenses:

• Authentication – Use strong methods like SCRAM, avoid trust, and configure pg_hba.conf carefully【640492761319988†L400-L431】【640492761319988†L476-L489】.
• Network security – Restrict access at the network level and encrypt traffic using TLS【640492761319988†L348-L389】【875810658030688†L375-L392】.
• Authorization – Build thoughtful role hierarchies, follow least privilege and avoid superuser usage【640492761319988†L599-L619】.
• Row‑Level Security – Fine‑grained access control is essential when multiple tenants or users share a table【640492761319988†L839-L853】.
• Encryption at rest – Use OS‑level encryption and pgcrypto for sensitive fields【640492761319988†L99-L117】【640492761319988†L990-L1076】.
• Audit logging – Deploy pgAudit to know who did what and when【875810658030688†L314-L330】.

Security is an ongoing process. As you deploy updates or new features, revisit your roles, ACLs and encryption settings. Threats and best practices evolve, and so should your configuration. But with the knowledge you’ve gained today, you’re well on your way to being the guardian of your Postgres castle. Tune in tomorrow for Day 14, where we’ll dive into Backups and Disaster Recovery – because even the most secure systems need a plan for when things go sideways.