plaintext 4 lines · 1 tab

PostgreSQL hardening with pg_hba and strict role separation

Kai Nakamura Apr 2026

1 tab

local   all             postgres                                peer
hostssl app_production  app_user        10.0.0.0/16             scram-sha-256
hostssl app_production  reporting_user  10.0.1.0/24             scram-sha-256
host    all             all             0.0.0.0/0               reject

1 file · plaintext Explain with highlit

Database hardening starts with connection control and role hygiene. I separate app, migration, reporting, and admin roles so compromise impact is constrained. The default question is not what permissions are convenient, but which ones are actually necessary.

Related snips

sql

-- Simple function
CREATE OR REPLACE FUNCTION get_full_name(
  first_name VARCHAR,
  last_name VARCHAR
)
RETURNS VARCHAR AS $$

Stored procedures and functions in PostgreSQL

postgresql stored-procedures functions

by Maria Garcia 2 tabs

sql

-- EXPLAIN ANALYZE (actual execution statistics)
EXPLAIN ANALYZE
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'

Advanced query optimization techniques

database optimization query-performance

by Maria Garcia 2 tabs

sql

-- Publisher: Send notification
NOTIFY new_order, 'Order #12345 created';

-- Subscriber: Listen for notifications
LISTEN new_order;

PostgreSQL LISTEN/NOTIFY for pub-sub messaging

postgresql listen-notify pub-sub

by Maria Garcia 2 tabs

sql

-- Logical backup with pg_dump
-- Single database
-- pg_dump -h localhost -U postgres -d mydb -F c -f mydb_backup.dump

-- All databases
-- pg_dumpall -h localhost -U postgres -f all_databases.sql

Database backup and recovery strategies

database backup recovery

by Maria Garcia 2 tabs

sql

-- Prepared statements basics
-- PostgreSQL syntax
PREPARE get_user (INT) AS
SELECT id, username, email
FROM users
WHERE id = $1;

Query plan caching and prepared statements

postgresql performance query-plans

by Maria Garcia 2 tabs

sql

-- Install PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- Create table with geometry column
CREATE TABLE locations (
  id SERIAL PRIMARY KEY,

Geospatial data with PostGIS

postgresql postgis geospatial

by Maria Garcia 2 tabs

Share this code

Here's the card — post it anywhere.

PostgreSQL hardening with pg_hba and strict role separation — share card

Link copied