Skip to main content
PostgreSQL
CHAPTER 25 Intermediate

PostgreSQL Security Best Practices

Updated: May 16, 2026
7 min read

# CHAPTER 25

PostgreSQL Security Best Practices

1. Introduction

The database is the beating heart of an organization. It contains passwords, credit cards, and proprietary secrets. A single security breach can result in millions of dollars in damages and legal liabilities. Out of the box, PostgreSQL is highly secure, but it is up to you to configure the internal locks. In this chapter, we will master Data Control Language (DCL) to build an impenetrable fortress using Roles, Permissions, and the Principle of Least Privilege.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Understand the Principle of Least Privilege.
  • Create and manage Database Roles (Users and Groups).
  • Grant and Revoke specific table permissions using DCL.
  • Secure network connections via pg_hba.conf.
  • Prevent the catastrophic usage of the postgres superuser.

3. The Fatal Mistake: The postgres Superuser

When you install PostgreSQL, it creates a default user named postgres. This is the "Superuser". It has god-like powers. It can read every password, bypass every security check, and instantly drop the entire database.

The #1 Security Rule: NEVER connect your PHP/Node.js web application to the database using the postgres superuser account. If your web application is compromised by a hacker, and the app connects as postgres, the hacker owns your entire server. The web application must be given a restricted, low-level account.

4. Creating Roles (Users and Groups)

In PostgreSQL, users and groups are both just called Roles. Let's create two new roles for our company.
sql
12345678 
-- 1. Create a Role that acts as a Group (No login allowed)
CREATE ROLE data_analyst;

-- 2. Create a Role that acts as a User (Login allowed, with a secure password)
CREATE ROLE sarah_smith WITH LOGIN PASSWORD 'SuperSecret123!';

-- 3. Add Sarah to the data_analyst group
GRANT data_analyst TO sarah_smith;

5. Managing Permissions (GRANT and REVOKE)

This is Data Control Language (DCL). We must explicitly define exactly what the data_analyst group is allowed to do.

The Principle of Least Privilege: A user should only be given the absolute minimum permissions necessary to do their job. Nothing more.

sql
12345678 
-- Analysts should be able to view the data, but NEVER modify it!
GRANT SELECT ON ALL TABLES IN SCHEMA public TO data_analyst;

-- Explicitly block them from accidental modifications
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM data_analyst;

-- If Sarah logs in and tries to run `DELETE FROM users;`, 
-- PostgreSQL will throw a "Permission Denied" fatal error!

6. Securing the Application Role

Let's create the account that our PHP website will use to connect to the database.
sql
1234567 
CREATE ROLE web_app WITH LOGIN PASSWORD 'AppPassword99!';

-- The app needs to read and write data
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO web_app;

-- However, the web app should NEVER be allowed to CREATE or DROP tables!
-- (If a hacker finds an SQL injection, they cannot drop your tables).

7. Network Security: pghba.conf

PostgreSQL controls who can connect over the network using a file named pghba.conf (Host-Based Authentication). By default, PostgreSQL only accepts connections from localhost (the server itself). If you need to connect pgAdmin from your home laptop to a cloud database, you must carefully edit this file.

Example pg_hba.conf entry:

text
12 
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    university_db   web_app         192.168.1.100/32        scram-sha-256

*(This strict rule says: ONLY the webapp user can connect to universitydb, and they MUST be connecting from the exact IP address 192.168.1.100, using an encrypted password).*

8. Mini Project: The Multi-Tier Security Schema

Let's design the security landscape for a hospital database.
sql
1234567891011 
-- Create roles
CREATE ROLE doctor LOGIN PASSWORD 'doc_pass';
CREATE ROLE billing_clerk LOGIN PASSWORD 'bill_pass';

-- Doctors can read/write patient medical records, but cannot touch billing.
GRANT SELECT, INSERT, UPDATE ON medical_records TO doctor;
REVOKE ALL ON billing_records FROM doctor;

-- Billing clerks can read/write invoices, but cannot read medical histories!
GRANT SELECT, INSERT, UPDATE ON billing_records TO billing_clerk;
REVOKE ALL ON medical_records FROM billing_clerk;

9. Common Mistakes

  • Granting ALL: Beginners often run GRANT ALL PRIVILEGES ON DATABASE mydb TO webapp;. This is incredibly dangerous. It violates the Principle of Least Privilege and opens massive attack vectors if the app is compromised.
  • Exposing Port 5432 to the Internet: You should never open port 5432 to the public internet 0.0.0.0/0. Database ports should be hidden behind firewalls and only accessible via private VPC networks or SSH tunnels.

10. Best Practices

  • Row-Level Security (RLS): For advanced security (like SaaS multi-tenant apps), PostgreSQL allows you to secure data down to the individual *row*. You can create a policy where User A can run SELECT * FROM orders;, but PostgreSQL will silently filter the results so they only see their own orders, physically preventing them from viewing User B's orders!

11. Exercises

  1. 1. What SQL command is used to remove a specific permission from a Role?
  1. 2. Why is connecting a web application to PostgreSQL using the postgres superuser account considered a fatal security flaw?

12. SQL Challenges

Write the DCL syntax to create a new role named intern with login capabilities and a password. Then, grant the intern the ability to SELECT from the publicdirectory table, but explicitly REVOKE their ability to DELETE from it.
sql
123 
CREATE ROLE intern WITH LOGIN PASSWORD 'intern123';
GRANT SELECT ON public_directory TO intern;
REVOKE DELETE ON public_directory FROM intern;

13. MCQ Quiz with Answers

Question 1

What is the fundamental concept behind the "Principle of Least Privilege"?

Question 2

Which PostgreSQL configuration file dictates which IP addresses and network hosts are allowed to attempt a connection to the database?

14. Interview Questions

  • Q: Explain the difference between GRANT and REVOKE. Give a specific example of how you would configure a read-only reporting account for a Data Analyst.
  • Q: Describe how a poorly configured pg_hba.conf file could lead to a catastrophic data breach.

15. FAQs

Q: Does PostgreSQL encrypt data at rest? A: PostgreSQL does not natively encrypt the physical .dat files on the hard drive (Transparent Data Encryption). To achieve encryption at rest, enterprise deployments utilize the underlying Linux Operating System (e.g., LUKS disk encryption) or cloud provider volume encryption (AWS EBS Encryption).

16. Summary

Security is not a feature; it is the foundation. By actively replacing the postgres superuser with strictly limited custom Roles, managing exact permissions via GRANT and REVOKE, and locking down network connectivity, you transform your database from an open target into a hardened, enterprise-grade vault.

17. Next Chapter Recommendation

Our database is perfectly structured, lightning fast, backed up, and heavily secured. The database engineering is complete. Now, we must bridge the gap to the Software Engineers. In Chapter 26: Connecting PostgreSQL with PHP, we will learn how to write the backend code required to talk to our secure PostgreSQL server.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter Importing, Exporting, Backup, and Recovery
Next Chapter Connecting PostgreSQL with PHP

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6