PostgreSQL Configuration for Poweradmin¶

Overview¶

This guide explains how to configure Poweradmin to use PostgreSQL as your database backend.

Requirements¶

PostgreSQL 14 or newer (older versions have reached upstream end-of-life; see postgresql.org/support/versioning)
PHP with PDO PostgreSQL extension enabled
PostgreSQL user with appropriate privileges

Configuration Steps¶

Create a configuration file at /config/settings.php based on the example below:

<?php
/**
 * Poweradmin PostgreSQL Configuration
 */
return [
    /**
     * Database Settings
     */
    'database' => [
        'type' => 'pgsql',            // Set database type to PostgreSQL
        'host' => 'localhost',        // PostgreSQL server hostname
        'port' => '5432',             // Default PostgreSQL port
        'user' => 'poweradmin',       // Database username
        'password' => 'your_password', // Database password (change this!)
        'name' => 'powerdns',         // Database name
        'charset' => 'UTF8',          // PostgreSQL uses uppercase charset names
        'file' => '',                 // Not used for PostgreSQL
        'debug' => false,             // Set to true to see SQL queries for debugging

        // SSL/TLS Settings (optional, added in 4.1.0)
        'ssl' => false,               // Enable SSL/TLS connection
        'ssl_verify' => false,        // Verify server certificate (requires ssl=true)
        'ssl_ca' => '',               // Path to CA certificate file (sslrootcert)
        'ssl_key' => '',              // Path to client private key (sslkey)
        'ssl_cert' => '',             // Path to client certificate (sslcert)
    ],

    // Other configuration sections remain the same as in settings.defaults.php
];

Database Creation¶

Creating the Database¶

CREATE DATABASE powerdns ENCODING 'UTF8';
CREATE USER poweradmin WITH ENCRYPTED PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE powerdns TO poweradmin;
\c powerdns
GRANT ALL ON SCHEMA public TO poweradmin;

Allowing Remote Connections¶

A default PostgreSQL install only listens on the loopback interface, so this step is needed when Poweradmin runs on a different host than the database.

In postgresql.conf, set the addresses the server listens on:

listen_addresses = '192.0.2.20'

In pg_hba.conf, add a rule for the client. Put it before any broader rule, since the first match wins:

# TYPE  DATABASE   USER         ADDRESS           METHOD
host    powerdns   poweradmin   192.0.2.10/32     scram-sha-256

Then reload the server (systemctl reload postgresql). On Debian and Ubuntu both files live under /etc/postgresql/<version>/main/; on RHEL-based systems they are in the data directory, typically /var/lib/pgsql/data/.

Restrict the address to the hosts that need it rather than using 0.0.0.0/0, and see SSL/TLS Configuration for encrypting the connection.

Schema Installation¶

The SQL schema files are located in the sql/ directory:

For a new installation: Use sql/poweradmin-pgsql-db-structure.sql
For PowerDNS schema: Check the appropriate version in sql/pdns/[version]/schema.pgsql.sql. Only 4.5 through 4.9 are bundled; for PowerDNS 5.x use the official schema

psql -U poweradmin -d powerdns -f sql/poweradmin-pgsql-db-structure.sql
psql -U poweradmin -d powerdns -f sql/pdns/[version]/schema.pgsql.sql

PostgreSQL-Specific Considerations¶

Sequences¶

PostgreSQL uses sequences for auto-incrementing primary keys. If you're migrating from MySQL or experiencing issues with IDs, you may need to reset sequences:

SELECT setval('sequence_name', (SELECT MAX(id) FROM table_name));

Case Sensitivity¶

PostgreSQL is case-sensitive for identifiers unless quoted. All table and column names in Poweradmin should be accessed in lowercase.

Performance Tuning¶

VACUUM: Schedule regular VACUUM ANALYZE operations to maintain database health
Indexing: Consider additional indexes for query patterns specific to your installation
Statement Timeout: For web applications, consider setting statement_timeout to prevent long-running queries

SSL/TLS Configuration¶

Added in version 4.1.0

Poweradmin supports SSL/TLS encrypted connections to PostgreSQL servers using the sslmode DSN parameter.

SSL Settings¶

Setting	Description	Default
`ssl`	Enable SSL/TLS connection	`false`
`ssl_verify`	Verify server certificate (requires `ssl=true`)	`false`
`ssl_ca`	Path to CA certificate file (sslrootcert)	Empty
`ssl_key`	Path to client private key (sslkey)	Empty
`ssl_cert`	Path to client certificate (sslcert)	Empty

SSL Mode Mapping¶

Poweradmin maps the settings to PostgreSQL sslmode values:

ssl	ssl_verify	PostgreSQL sslmode
`false`	-	`prefer` (try SSL, fall back to non-SSL)
`true`	`false`	`require` (require SSL, no cert verification)
`true`	`true`	`verify-full` (require SSL + verify cert + hostname)

Example: SSL with Certificate Verification¶

'database' => [
    'type' => 'pgsql',
    'host' => 'postgres.example.com',
    'port' => '5432',
    'user' => 'poweradmin',
    'password' => 'your_password',
    'name' => 'powerdns',
    'ssl' => true,
    'ssl_verify' => true,
    'ssl_ca' => '/path/to/ca-cert.pem',
],

Example: SSL without Verification¶

'database' => [
    'type' => 'pgsql',
    'host' => 'postgres.example.com',
    'port' => '5432',
    'user' => 'poweradmin',
    'password' => 'your_password',
    'name' => 'powerdns',
    'ssl' => true,
    'ssl_verify' => false,
],

Backwards Compatibility Note¶

By default (ssl=false), Poweradmin uses sslmode=prefer, which attempts SSL connections but falls back to non-SSL if the server doesn't support it. This maintains backwards compatibility with existing configurations.