PostgreSQL Configuration for BanTool

This guide provides a baseline configuration for PostgreSQL optimized for BanTool, assuming a server with approximately 4GB of RAM and SSD storage.

Configuration Rationale

Memory Settings

• shared_buffers = 512MB: This sets the amount of memory the database server uses for shared memory buffers. For a system with 4GB RAM, 512MB (approx. 12.5%) is a safe starting point, leaving room for the OS and other applications.
• effective_cache_size = 3GB: This helps the query planner estimate how much memory is available for disk caching by the operating system. It should be set to roughly 60-70% of total RAM. It doesn't allocate memory but influences index usage.
• work_mem = 8MB: Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. Keeping this low (4-8MB) prevents Out-Of-Memory (OOM) errors when many connections are active.
• maintenance_work_mem = 128MB: Used for maintenance tasks like VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. 128MB is sufficient for small-to-medium datasets without hogging RAM.

Storage & I/O (SSD Optimization)

• random_page_cost = 1.1 & seq_page_cost = 1.0: Default values assume spinning disks (HDD) where random access is expensive. For SSDs, random access is nearly as fast as sequential. Lowering random_page_cost encourages the planner to use indexes more often.
• effective_io_concurrency = 200: Tells PostgreSQL that the storage subsystem can handle multiple concurrent I/O requests, which is true for SSDs/NVMe drives.

Checkpoints & WAL

• checkpoint_completion_target = 0.9: Spreads checkpoint writes out over almost the entire checkpoint interval, reducing I/O spikes that can stall the database.
• max_wal_size = 1GB: Limits the size of the Write-Ahead Log (WAL) on disk. 1GB is a balanced value for moderate write loads.
• wal_compression = on: Compresses WAL data, reducing disk I/O and storage usage at the cost of a small amount of CPU.

Connections

• max_connections = 50: Limits the number of concurrent connections. PostgreSQL processes are heavy; setting this too high can lead to context switching overhead. If you need more concurrency, use a connection pooler like PgBouncer.

Applying the Configuration

You can apply these settings directly via SQL commands without editing the postgresql.conf file manually.

-- Memory
ALTER SYSTEM SET shared_buffers = '512MB';
ALTER SYSTEM SET effective_cache_size = '3GB';
ALTER SYSTEM SET work_mem = '8MB';
ALTER SYSTEM SET maintenance_work_mem = '128MB';

-- SSD Optimization
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET seq_page_cost = 1.0;
ALTER SYSTEM SET effective_io_concurrency = 200;

-- Checkpoints & WAL
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET max_wal_size = '1GB';
ALTER SYSTEM SET wal_compression = on;

-- Connections
ALTER SYSTEM SET max_connections = 50;

-- Reload configuration to apply changes (some require restart)
SELECT pg_reload_conf();

-- Check current settings
SELECT
    name,
    source,
    setting,
    CASE
        WHEN unit IN ('kB','8kB','16kB','32kB','64kB') THEN
            pg_size_pretty(setting::bigint *
                CASE unit
                    WHEN 'kB' THEN 1024
                    WHEN '8kB' THEN 8 * 1024
                    WHEN '16kB' THEN 16 * 1024
                    WHEN '32kB' THEN 32 * 1024
                    WHEN '64kB' THEN 64 * 1024
                END
            )
        WHEN unit IN ('MB','GB') THEN
            pg_size_pretty(setting::bigint *
                CASE unit
                    WHEN 'MB' THEN 1024 * 1024
                    WHEN 'GB' THEN 1024 * 1024 * 1024
                END
            )
        ELSE
            setting   -- no conversion (cost, boolean, numeric values)
    END AS human_readable,
    unit
FROM pg_settings
WHERE name IN (
  'shared_buffers',
  'effective_cache_size',
  'work_mem',
  'maintenance_work_mem',
  'random_page_cost',
  'seq_page_cost',
  'effective_io_concurrency',
  'checkpoint_completion_target',
  'max_wal_size',
  'wal_compression',
  'max_connections'
)
ORDER BY name;

Note: Changing shared_buffers requires a server restart (sudo systemctl restart postgresql). Other parameters may be applied with just a reload.

User and Database Isolation Strategy

To ensure security and isolation, we create distinct users for each application or service. Each user is granted access only to their specific database and is explicitly prevented from connecting to others.

-- 1. Revoke default public access
-- By default, the 'public' role allows connection to all databases. We must revoke this first.
REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;
REVOKE CONNECT ON DATABASE template0 FROM PUBLIC;
REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;

-- 2. Create a template for creating isolated users
-- Repeat this block for each user/database pair you need (e.g., user_a/db_a, user_b/db_b)

-- === Dev USER ===
-- Create role with NO ability to create databases, no role creation or superuser rights
CREATE ROLE dev WITH LOGIN NOCREATEDB NOCREATEROLE NOSUPERUSER PASSWORD 'STRONG_PASSWORD_DEV';
CREATE DATABASE db_dev OWNER dev;

-- Explicitly allow connection only to their own DB
GRANT CONNECT ON DATABASE db_dev TO dev;
-- Ensure they cannot connect to other DBs (redundant if PUBLIC is revoked, but good practice)
REVOKE CONNECT ON DATABASE labs FROM PUBLIC;

-- === Manager USER ===
-- Higher privilege user with create DB rights
CREATE ROLE manager WITH LOGIN CREATEDB NOCREATEROLE NOSUPERUSER PASSWORD 'STRONG_PASSWORD_MANAGER';
CREATE DATABASE db_manager OWNER manager;

-- Explicitly allow connection only to their own DB
GRANT CONNECT ON DATABASE db_manager TO manager;
REVOKE CONNECT ON DATABASE db_manager FROM PUBLIC;

Granting Database Creation Privileges

If a user needs to create databases (e.g., for testing environments), you can grant the privilege explicitly:

-- Allow the user to create databases
ALTER ROLE dev CREATEDB;

-- Grant permission to create objects in the default tablespace
GRANT CREATE ON TABLESPACE pg_default TO dev;