PostgreSQL Security & Setup Guide: Hardening, Tuning, & MariaDB Comparison

PostgreSQL (often just called Postgres) is an advanced, enterprise-class, open-source object-relational database system. Known for its strict adherence to SQL standards, robust feature set, and rock-solid data integrity, it is the database of choice for applications requiring complex data models and high reliability.

When deploying a database server, security is paramount. A misconfigured database is the most critical vulnerability in any infrastructure, potentially leading to catastrophic data breaches.

1. The Great Debate: PostgreSQL vs. MariaDB

Before diving into the setup, it’s crucial to ensure PostgreSQL is the right tool for your specific application. How does it compare to MariaDB (the open-source fork of MySQL)?

PostgreSQL

Architecture: Object-relational. It supports complex data types (arrays, hstore, JSONB) and allows for custom data types and functions.
Strengths: Strict data integrity (ACID compliant by design), excellent performance with complex JOINs and heavy write loads, advanced indexing (GiST, SP-GiST, GIN), and native synchronous replication. The PostGIS extension makes it the undisputed king of geospatial data.
Best For:
- Enterprise applications with complex business logic.
- Applications heavily reliant on JSON data (JSONB performs exceptionally well).
- Data warehousing and intensive analytical processing.
- Geographic Information Systems (GIS).

MariaDB

Architecture: Purely relational.
Strengths: Generally simpler to set up and administer. It boasts extremely fast read speeds for simple queries and offers various storage engines (like InnoDB/XtraDB, Aria, and ColumnStore).
Best For:
- Standard CMS platforms (WordPress, Joomla, Drupal) which are explicitly built around MySQL/MariaDB.
- Applications with high read-to-write ratios and relatively simple data structures.
- Lightweight web applications where rapid deployment is prioritized over complex data constraint enforcement.

The Verdict: If you are building a modern, custom application from scratch, PostgreSQL is generally the recommended choice due to its strictness, feature depth, and JSON handling. If you are hosting off-the-shelf PHP web apps, MariaDB is usually the path of least resistance.

2. Installation and Initial Setup

On Debian or Ubuntu-based systems, it is highly recommended to use the official PostgreSQL Apt Repository rather than the default OS repositories, as they often contain outdated versions.

# 1. Install the repository key and add the repository
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# 2. Update and Install PostgreSQL
sudo apt update
sudo apt install postgresql-16 postgresql-client-16

# 3. Ensure the service is running and enabled on boot
sudo systemctl enable --now postgresql

3. Core Hardening and Security Configuration

PostgreSQL’s security relies primarily on two configuration files: postgresql.conf (server settings) and pg_hba.conf (Client Authentication). These are typically located in /etc/postgresql/16/main/ (adjust the version number as needed).

A. Securing Network Interfaces (`postgresql.conf`)

By default, Postgres only listens on localhost. If your application runs on the same server, leave it this way. If your application runs in a separate container or server, you must bind it to a network interface.

Edit /etc/postgresql/16/main/postgresql.conf:

# NEVER use '*' (0.0.0.0) unless strictly protected by a hardware firewall.
# Bind only to the specific internal network IP (e.g., a WireGuard or VLAN IP).
listen_addresses = 'localhost, 10.0.0.50'

# Change the default port to reduce automated scanning noise (Optional but recommended)
port = 5432 

# Enforce strong password encryption for new passwords
password_encryption = scram-sha-256

B. Host-Based Authentication (`pg_hba.conf`)

The pg_hba.conf file controls who can connect, from where, and how they must authenticate. Crucial Rule: Order matters! The first record matching the connection type, client address, requested database, and user name is used to perform authentication.

Edit /etc/postgresql/16/main/pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only. 
# 'peer' means the OS user must match the database user.
local   all             postgres                                peer
local   all             all                                     peer

# IPv4 local connections:
# Force SCRAM-SHA-256 password authentication for network connections. 
# NEVER use 'trust' on network interfaces. Avoid 'md5' as it is cryptographically weak.
host    all             all             127.0.0.1/32            scram-sha-256

# Allow an internal application server to connect via password
host    my_database     my_app_user     10.0.0.100/32           scram-sha-256

# Reject all other connections explicitly (Good practice for logging)
host    all             all             0.0.0.0/0               reject

After modifying these files, reload the service:

sudo systemctl reload postgresql

4. Setting Up Databases and Roles (Least Privilege)

Never run your web application as the postgres superuser. Always create a dedicated database and a dedicated role (user) that only has access to that specific database.

Log into the database prompt as the administrative user:

sudo -u postgres psql

Execute the following SQL commands to securely provision an application:

-- 1. Create a dedicated role with a strong password. 
-- 'NOSUPERUSER' ensures they cannot alter server-wide settings.
CREATE ROLE my_app_user WITH LOGIN PASSWORD 'YourVeryStrongPasswordHere' NOSUPERUSER NOCREATEDB NOCREATEROLE;

-- 2. Create the database, owned by the new role.
CREATE DATABASE my_database OWNER my_app_user;

-- 3. Revoke public access (Hardening step)
-- By default, Postgres allows any user to connect to any database and create objects in the 'public' schema.
REVOKE ALL ON DATABASE my_database FROM PUBLIC;

-- 4. Grant specific connection rights back to the app user
GRANT CONNECT ON DATABASE my_database TO my_app_user;

-- 5. Switch to the new database
\c my_database

-- 6. Ensure the app user owns the public schema within this specific database
ALTER SCHEMA public OWNER TO my_app_user;

5. Common Problems and How to Fix Them

`FATAL: password authentication failed for user "X"`

The Problem: The user entered the wrong password, OR pg_hba.conf is misconfigured.
The Fix:
1. Verify the password.
2. Check pg_hba.conf. If the connection is coming over a network socket (even 127.0.0.1), make sure the METHOD is set to scram-sha-256 or md5, not peer. peer only works for local Unix sockets.
3. Did you reload Postgres after changing pg_hba.conf? (sudo systemctl reload postgresql).

`psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed`

The Problem: The client cannot find the local Unix socket to connect to the database.
The Fix:
1. Ensure the PostgreSQL service is actually running: sudo systemctl status postgresql.
2. Check the logs for startup errors: sudo journalctl -u postgresql. Often, this is caused by a syntax error in postgresql.conf.
3. If running via Docker or a custom compile, the socket directory might be /tmp/ instead of /var/run/postgresql/. Specify the host explicitly: psql -h /tmp/ -U postgres.

`FATAL: no pg_hba.conf entry for host "10.0.0.X", user "Y", database "Z"`

The Problem: The PostgreSQL server actively rejected the connection because the client’s IP address is not explicitly allowed in pg_hba.conf.
The Fix: Add a host entry in pg_hba.conf allowing 10.0.0.X to connect to database Z as user Y, then reload the service.

Out of Memory (OOM) / High RAM Usage

The Problem: The Linux kernel’s OOM killer terminates the Postgres process during heavy queries, or the server crashes.
The Fix: Postgres defaults are very conservative. If you have a dedicated DB server, you must tune it. Edit postgresql.conf:
- shared_buffers: Set to about 25% of total system RAM.
- work_mem: Start small (e.g., 4MB to 16MB). This is memory allocated per sort/hash operation, not globally. Setting this too high will instantly exhaust memory if you have hundreds of connections.
- max_connections: Reduce this if it’s unnecessarily high (default is 100). Use a connection pooler like PgBouncer if your app needs thousands of connections.

6. Pre-Flight Checklist

Before going live, ensure you have:

Switched password_encryption to scram-sha-256.
Replaced any trust authentication methods in pg_hba.conf with scram-sha-256.
Confirmed the application connects via a restricted, non-superuser role.
Set up a backup strategy (Use pg_dump for simple cron backups, or pgBackRest for enterprise point-in-time recovery).