Security Best Practices

-- Create the monitoring user
CREATE USER postgres_exporter WITH PASSWORD 'secure_password';

-- Grant the monitoring role
GRANT pg_monitor TO postgres_exporter;

-- Grant connection permission
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;

-- Set search path
ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog;

-- Create function to safely create user if not exists
CREATE OR REPLACE FUNCTION __tmp_create_user() returns void as $$
BEGIN
  IF NOT EXISTS (
    SELECT
    FROM   pg_catalog.pg_user
    WHERE  usename = 'postgres_exporter'
  ) THEN
    CREATE USER postgres_exporter;
  END IF;
END;
$$ language plpgsql;

SELECT __tmp_create_user();
DROP FUNCTION __tmp_create_user();

ALTER USER postgres_exporter WITH PASSWORD 'password';
ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog;

-- If deploying as non-superuser (e.g., AWS RDS), uncomment and replace <MASTER_USER>
-- GRANT postgres_exporter TO <MASTER_USER>;

GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
GRANT pg_monitor TO postgres_exporter;

CREATE SCHEMA IF NOT EXISTS postgres_exporter;
GRANT USAGE ON SCHEMA postgres_exporter TO postgres_exporter;

-- Wrapper function for pg_stat_activity
CREATE OR REPLACE FUNCTION get_pg_stat_activity() RETURNS SETOF pg_stat_activity AS
$$ SELECT * FROM pg_catalog.pg_stat_activity; $$
LANGUAGE sql
VOLATILE
SECURITY DEFINER;

CREATE OR REPLACE VIEW postgres_exporter.pg_stat_activity AS
  SELECT * from get_pg_stat_activity();

GRANT SELECT ON postgres_exporter.pg_stat_activity TO postgres_exporter;

-- Wrapper function for pg_stat_replication
CREATE OR REPLACE FUNCTION get_pg_stat_replication() RETURNS SETOF pg_stat_replication AS
$$ SELECT * FROM pg_catalog.pg_stat_replication; $$
LANGUAGE sql
VOLATILE
SECURITY DEFINER;

CREATE OR REPLACE VIEW postgres_exporter.pg_stat_replication AS
  SELECT * FROM get_pg_stat_replication();

GRANT SELECT ON postgres_exporter.pg_stat_replication TO postgres_exporter;

-- Wrapper for pg_stat_statements (if extension is installed)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

CREATE OR REPLACE FUNCTION get_pg_stat_statements() RETURNS SETOF pg_stat_statements AS
$$ SELECT * FROM public.pg_stat_statements; $$
LANGUAGE sql
VOLATILE
SECURITY DEFINER;

CREATE OR REPLACE VIEW postgres_exporter.pg_stat_statements AS
  SELECT * FROM get_pg_stat_statements();

GRANT SELECT ON postgres_exporter.pg_stat_statements TO postgres_exporter;

# Create a password file
echo "your_secure_password" > /secrets/postgres_password
chmod 600 /secrets/postgres_password

# Run the exporter
export DATA_SOURCE_URI="localhost:5432/postgres?sslmode=require"
export DATA_SOURCE_USER="postgres_exporter"
export DATA_SOURCE_PASS_FILE="/secrets/postgres_password"
./postgres_exporter

docker run \
  --net=host \
  -v /secrets/postgres_password:/secrets/password:ro \
  -e DATA_SOURCE_URI="localhost:5432/postgres?sslmode=require" \
  -e DATA_SOURCE_USER="postgres_exporter" \
  -e DATA_SOURCE_PASS_FILE="/secrets/password" \
  quay.io/prometheuscommunity/postgres-exporter

apiVersion: v1
kind: Secret
metadata:
  name: postgres-exporter-password
type: Opaque
stringData:
  password: "your_secure_password"
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-exporter
spec:
  template:
    spec:
      containers:
      - name: postgres-exporter
        image: quay.io/prometheuscommunity/postgres-exporter
        env:
        - name: DATA_SOURCE_URI
          value: "postgres:5432/postgres?sslmode=require"
        - name: DATA_SOURCE_USER
          value: "postgres_exporter"
        - name: DATA_SOURCE_PASS_FILE
          value: "/secrets/password"
        volumeMounts:
        - name: password
          mountPath: /secrets
          readOnly: true
      volumes:
      - name: password
        secret:
          secretName: postgres-exporter-password
          items:
          - key: password
            path: password
            mode: 0400

# Require SSL
DATA_SOURCE_URI="postgres.example.com:5432/postgres?sslmode=require"

# Verify CA certificate
DATA_SOURCE_URI="postgres.example.com:5432/postgres?sslmode=verify-ca&sslrootcert=/path/to/ca.crt"

# Full verification (hostname and CA)
DATA_SOURCE_URI="postgres.example.com:5432/postgres?sslmode=verify-full&sslrootcert=/path/to/ca.crt"

DATA_SOURCE_URI="postgres.example.com:5432/postgres?sslmode=verify-full&sslrootcert=/certs/ca.crt&sslcert=/certs/client.crt&sslkey=/certs/client.key"

# web-config.yml
tls_server_config:
  cert_file: /path/to/cert.pem
  key_file: /path/to/key.pem

./postgres_exporter --web.config.file=web-config.yml

# web-config.yml
basic_auth_users:
  prometheus: $2y$10$XYZ...  # bcrypt hash of password

htpasswd -nBC 10 "" | tr -d ':'

# Only allow Prometheus server
iptables -A INPUT -p tcp --dport 9187 -s prometheus-server-ip -j ACCEPT
iptables -A INPUT -p tcp --dport 9187 -j DROP

# Create isolated network
docker network create monitoring

# Run PostgreSQL
docker run --network monitoring --name postgres postgres

# Run exporter (can access postgres, not exposed externally)
docker run --network monitoring \
  -e DATA_SOURCE_URI="postgres:5432/postgres" \
  quay.io/prometheuscommunity/postgres-exporter

-- Grant monitoring role to RDS master user first
GRANT postgres_exporter TO your_rds_master_user;

-- Then grant pg_monitor
GRANT pg_monitor TO postgres_exporter;