POWERDNS - Cluster with PostgreSQL Backend

PowerDNS Configuration

PowerDNS needs to be configured to use PostgreSQL as the backend for managing DNS data. Below is an example of a typical PowerDNS configuration file (`pdns.conf`) when using PostgreSQL.

# Configuration for PowerDNS with PostgreSQL Backend
launch=gpgsql
gpgsql-host=127.0.0.1
gpgsql-user=pdns
gpgsql-password=yourpassword
gpgsql-dbname=pdns
gpgsql-port=5432
gpgsql-pool-size=20
gpgsql-ssl=0

Configuration Parameters

- `launch=gpgsql`: Specifies the backend to use, which in this case is PostgreSQL. - `gpgsql-host`: The hostname or IP address of the PostgreSQL database server. - `gpgsql-user`: The PostgreSQL username for PowerDNS to authenticate. - `gpgsql-password`: The password associated with the PostgreSQL user. - `gpgsql-dbname`: The name of the PostgreSQL database to use for storing DNS records. - `gpgsql-port`: The port on which PostgreSQL is running (default is 5432). - `gpgsql-pool-size`: Sets the maximum number of connections PowerDNS will use to the PostgreSQL database. - `gpgsql-ssl`: Indicates whether SSL should be used for communication with the PostgreSQL server (0 for no, 1 for yes).

PostgreSQL Database Schema

The PostgreSQL backend for PowerDNS uses specific tables to store DNS records. Below is the typical schema used by PowerDNS.

-- Table for storing domains
CREATE TABLE domains (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    type VARCHAR(6) NOT NULL DEFAULT 'MASTER',
    last_check INT,
    created SERIAL NOT NULL
);

-- Table for storing DNS records
CREATE TABLE records (
    id SERIAL PRIMARY KEY,
    domain_id INT REFERENCES domains(id),
    name VARCHAR(255) NOT NULL,
    type VARCHAR(6) NOT NULL,
    content TEXT,
    ttl INT DEFAULT 3600,
    prio INT DEFAULT 0,
    disabled BOOLEAN DEFAULT FALSE,
    ordername VARCHAR(255),
    auth BOOLEAN DEFAULT TRUE
);

-- Table for storing supermasters (optional)
CREATE TABLE supermasters (
    ip VARCHAR(45) NOT NULL,
    nameserver VARCHAR(255) NOT NULL,
    account VARCHAR(255) NOT NULL
);

Database Table Descriptions

- **domains**: Stores information about each domain served by PowerDNS, including its name, type (e.g., MASTER or SLAVE), and creation time. - **records**: Contains the DNS records for each domain, including the type (A, AAAA, MX, etc.), content (IP addresses, mail servers, etc.), TTL, and additional information. - **supermasters**: Optional table that can be used to allow automatic DNS record creation for specific IPs. This is useful for dynamic DNS setups.

PowerDNS Cluster Setup

In a clustered setup, multiple PowerDNS servers can be configured to connect to the same PostgreSQL backend, ensuring consistent and synchronized DNS data across all nodes.

Each PowerDNS instance should have the same `pdns.conf` file, pointing to the same PostgreSQL backend, and should use a load balancing mechanism (e.g., DNS load balancing, round-robin DNS, or a dedicated load balancer) to distribute query traffic across the servers.

= High Availability

PowerDNS supports high availability in clustered setups. The PostgreSQL backend can be replicated across multiple PostgreSQL servers to ensure fault tolerance. This can be done by configuring streaming replication or using a PostgreSQL cluster solution like Patroni or pgpool-II.

Example configuration for PostgreSQL replication setup:

# PostgreSQL primary server configuration
wal_level = replica
max_wal_senders = 10
hot_standby = on

# PostgreSQL standby server configuration
primary_conninfo = 'host=primary-db-server port=5432 user=replica password=replica_password'

This allows the PostgreSQL database to be replicated across multiple servers, ensuring data redundancy in case of failure.

DNS Record Management

DNS records can be managed through SQL queries directly on the PostgreSQL backend or through PowerDNS' API. Below are examples of how to manage DNS records using SQL.

Adding a DNS Record

-- Example: Add an A record for example.com
INSERT INTO records (domain_id, name, type, content, ttl)
SELECT id, 'www.example.com', 'A', '192.0.2.1', 3600
FROM domains WHERE name = 'example.com';

This SQL query inserts an A record for `www.example.com` pointing to `192.0.2.1`.

Updating a DNS Record

-- Example: Update the A record for www.example.com
UPDATE records
SET content = '198.51.100.1'
WHERE name = 'www.example.com' AND type = 'A';

This SQL query updates the A record for `www.example.com` to point to `198.51.100.1`.

Deleting a DNS Record

-- Example: Delete the A record for www.example.com
DELETE FROM records
WHERE name = 'www.example.com' AND type = 'A';

This SQL query deletes the A record for `www.example.com`.

Using PowerDNS API for Record Management

PowerDNS also provides an API for managing DNS records programmatically. Below is an example of using `curl` to add a DNS record via the PowerDNS API.

# Example: Add an A record using the PowerDNS API
curl -X POST "http://localhost:8081/api/v1/servers/localhost/zones/example.com" \
    -H "X-API-Key: your_api_key" \
    -H "Content-Type: application/json" \
    -d '{
            "rrsets": [
                {
                    "name": "www.example.com.",
                    "type": "A",
                    "ttl": 3600,
                    "records": [{"content": "192.0.2.1"}]
                }
            ]
        }'

This command sends a POST request to add an A record for `www.example.com` with the IP address `192.0.2.1`.

Monitoring and Logging

Monitoring is essential in a clustered PowerDNS environment to ensure that all servers are functioning properly. PowerDNS can be monitored using standard system monitoring tools such as `Prometheus`, `Grafana`, or `Nagios`.

For example, to monitor PowerDNS' performance using Prometheus, the following configuration is required in `pdns.conf`:

# Enable Prometheus exporter
export-metrics=yes
export-metrics-interval=30

This will expose PowerDNS metrics at the `/metrics` endpoint, which can then be scraped by Prometheus.

Logging is also important for debugging and auditing DNS operations. PowerDNS supports detailed logging via syslog or directly to a file. Below is an example of enabling detailed logging in `pdns.conf`:

# Enable logging to syslog
log-dns-queries=yes
loglevel=9

Useful Links

• [PowerDNS Documentation](https://doc.powerdns.com/)
• [PowerDNS API Documentation](https://doc.powerdns.com/authoritative/http-api/)
• [PostgreSQL Documentation](https://www.postgresql.org/docs/)
• [PostgreSQL Replication Setup](https://www.postgresql.org/docs/current/replication-setup.html)
• [PowerDNS on GitHub](https://github.com/PowerDNS/pdns)
• [PowerDNS with PostgreSQL Setup](https://doc.powerdns.com/authoritative/backends/gpgsql.html)
• [Patroni - PostgreSQL High Availability](https://github.com/zalando/patroni)
• [pgpool-II Documentation](https://www.pgpool.net/mediawiki/index.php/Main_Page)