Patroni PostgresSQL-klusteri TLS ongelma

SamCer · torstaina klo 19:38

Yritän pystyttää Patronin avulla PostgresSQL-klusteria ja sen jo sainkin toimimaan, mutta nyt kun yritän ottaa TLS:sää käyttöön liikenteen salaukseen ja serverin/clientin autentikointiin, niin se tuottaa ongelmia.

Alkuinfoa:

pgs1 = 192.168.88.25
pgs2 = 192.168.88.26
pgs3 = 192.168.88.27
PostgresSQL versio = 18.0
Patroni versio = 4.0.6
Patronin DCS = Consul
Consul versio = v1.21.5

Patronin logitus valittaa ssl.SSLError: [SSL: SSLV3_ALERT_BAD_CERTIFICATE] sslv3 alert bad certificate (_ssl.c:1000). Alla logi kokonaisuudessaan aina virheeseen asti:

Rich (BB code):

Sep 25 19:12:54 pgs1.omadomain.fi systemd[1]: Started patroni.service - Runners to orchestrate a high-availability PostgreSQL.
░░ Subject: A start job for unit patroni.service has finished successfully
░░ Defined-By: systemd
░░ Support: http://www.ubuntu.com/support
░░
░░ A start job for unit patroni.service has finished successfully.
░░
░░ The job identifier is 150895.
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,483 WARNING: ttl=2 can't be smaller than 20, adjusting...
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,483 INFO: No PostgreSQL configuration items changed, nothing to reload.
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,499 INFO: Deregister service postgresql-cluster/pgs1
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,513 INFO: Lock owner: None; I am pgs1
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,524 INFO: Deregister service postgresql-cluster/pgs1
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,535 INFO: trying to bootstrap a new cluster
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,536 INFO: Lock owner: None; I am pgs1
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,536 INFO: not healthy enough for leader race
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,537 WARNING: Could not register service: unknown role type uninitialized
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: The files belonging to this database system will be owned by user "postgres".
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: This user must also own the server process.
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: The database cluster will be initialized with locale "en_US.UTF-8".
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: The default text search configuration will be set to "english".
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: Data page checksums are enabled.
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: fixing permissions on existing directory /var/lib/postgresql/data ... ok
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: creating subdirectories ... ok
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: selecting dynamic shared memory implementation ... posix
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: selecting default "max_connections" ...
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:54,553 INFO: bootstrap in progress
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: 100
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: selecting default "shared_buffers" ... 128MB
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: selecting default time zone ... Europe/Helsinki
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: creating configuration files ... ok
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: running bootstrap script ... ok
Sep 25 19:12:54 pgs1.omadomain.fi patroni[181762]: performing post-bootstrap initialization ... ok
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181762]: syncing data to disk ... ok
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181762]: initdb: warning: enabling "trust" authentication for local connections
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181762]: initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181762]: Success. You can now start the database server using:
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181762]:     /usr/lib/postgresql/18/bin/pg_ctl -D /var/lib/postgresql/data -l logfile start
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181779]: 2025-09-25 19:12:55.333 EEST [181779] LOG:  starting PostgreSQL 18.0 (Ubuntu 18.0-1.pgdg24.04+3) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181779]: 2025-09-25 19:12:55.333 EEST [181779] LOG:  listening on IPv4 address "0.0.0.0", port 5432
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:55,336 INFO: postmaster pid=181779
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181779]: 2025-09-25 19:12:55.340 EEST [181779] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181786]: 2025-09-25 19:12:55.355 EEST [181786] LOG:  database system was shut down at 2025-09-25 19:12:54 EEST
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181787]: 2025-09-25 19:12:55.360 EEST [181787] FATAL:  the database system is starting up
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181780]: localhost:5432 - rejecting connections
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181789]: 2025-09-25 19:12:55.370 EEST [181789] FATAL:  the database system is starting up
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181788]: localhost:5432 - rejecting connections
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181779]: 2025-09-25 19:12:55.382 EEST [181779] LOG:  database system is ready to accept connections
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:55,537 INFO: Lock owner: None; I am pgs1
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:55,537 INFO: not healthy enough for leader race
Sep 25 19:12:55 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:55,548 INFO: bootstrap in progress
Sep 25 19:12:56 pgs1.omadomain.fi patroni[181793]: localhost:5432 - accepting connections
Sep 25 19:12:56 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:56,385 INFO: establishing a new patroni heartbeat connection to postgres
Sep 25 19:12:56 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:56,402 INFO: Register service postgresql-cluster, params {'service_id': 'postgresql-cluster/pgs1', 'address': '192.168.88.25', 'port': 5432, 'check': {'http': 'https://192.168.88.25:8008/primary', 'interval': '5s', 'DeregisterCriticalServiceAfter': '100.0s'}, 'tags': ['traefik.enable=true', 'traefik.tcp.routers.pgsql.entrypoints=postgres', 'traefik.tcp.routers.pgsql.rule=HostSNI(`*`)', 'traefik.tcp.services.pgsql.loadbalancer.server.port=5432', 'primary', 'master'], 'enable_tag_override': True}
Sep 25 19:12:56 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:56,443 INFO: running post_bootstrap
Sep 25 19:12:56 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:56,466 ERROR: User creation is not be supported starting from v4.0.0. Please use "bootstrap.post_bootstrap" script to create users.
Sep 25 19:12:56 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:56,467 WARNING: Could not activate Linux watchdog device: Can't open watchdog device: [Errno 2] No such file or directory: '/dev/watchdog'
Sep 25 19:12:56 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:56,510 INFO: initialized a new cluster
Sep 25 19:12:57 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:57,563 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:12:58 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:58,481 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:12:59,469 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]: Exception in thread Thread-6 (process_request_thread):
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]: Traceback (most recent call last):
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3.12/threading.py", line 1073, in _bootstrap_inner
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:     self.run()
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3.12/threading.py", line 1010, in run
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:     self._target(*self._args, **self._kwargs)
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3/dist-packages/patroni/api.py", line 1703, in process_request_thread
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:     request.do_handshake()
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3.12/ssl.py", line 1320, in do_handshake
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]:     self._sslobj.do_handshake()
Sep 25 19:12:59 pgs1.omadomain.fi patroni[181756]: ssl.SSLError: [SSL: SSLV3_ALERT_BAD_CERTIFICATE] sslv3 alert bad certificate (_ssl.c:1000)
Sep 25 19:13:00 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:13:00,469 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:13:01 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:13:01,469 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:13:02 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:13:02,470 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:13:03 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:13:03,470 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]: 2025-09-25 19:13:04,469 INFO: no action. I am (pgs1), the leader with the lock
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181784]: 2025-09-25 19:13:04.624 EEST [181784] LOG:  checkpoint starting: force wait
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]: Exception in thread Thread-7 (process_request_thread):
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]: Traceback (most recent call last):
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3.12/threading.py", line 1073, in _bootstrap_inner
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:     self.run()
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3.12/threading.py", line 1010, in run
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:     self._target(*self._args, **self._kwargs)
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3/dist-packages/patroni/api.py", line 1703, in process_request_thread
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:     request.do_handshake()
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:   File "/usr/lib/python3.12/ssl.py", line 1320, in do_handshake
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]:     self._sslobj.do_handshake()
Sep 25 19:13:04 pgs1.omadomain.fi patroni[181756]: ssl.SSLError: [SSL: SSLV3_ALERT_BAD_CERTIFICATE] sslv3 alert bad certificate (_ssl.c:1000)
Sep 25 19:13:05 pgs1.omadomain.fi patroni[181784]: 2025-09-25 19:13:05.130 EEST [181784] LOG:  checkpoint complete: wrote 4 buffers (0.0%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.414 s, sync=0.015 s, total=0.507 s; sync files=6, longest=0.009 s, average=0.003 s; distance=8843 kB, estimate=8843 kB; lsn=0/2000080, redo lsn=0/2000028
Sep 25 19:13:05 pgs1.omadomain.fi patroni[181784]: 2025-09-25 19:13:05.165 EEST [181784] LOG:  checkpoint starting: force wait
Sep 25 19:13:05 pgs1.omadomain.fi patroni[181784]: 2025-09-25 19:13:05.331 EEST [181784] LOG:  checkpoint complete: wrote 0 buffers (0.0%), wrote 0 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.024 s, sync=0.001 s, total=0.166 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16384 kB, estimate=16384 kB; lsn=0/3000080, redo lsn=0/3000028

Patronin conffi luodaan ansiblen playbookilla automaattisesti, niin että ainoastaan pgs1:sen conffi sisältää bootstrap-lohkon kun oli ongelmaa saada bootstrap tapahtumaan kun kaikki jäi odottamaan leaderia ja muilla on muuten sama, mutta se bootstrap-lohko puuttuu ja tietenkin ip-osoitteet ja hostin nimet muokattu kyseiseen hostiin sopivaksi.

pgs1:~$ cat /etc/patroni/config.yml

YAML:

scope: postgresql-cluster
name: pgs1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.88.25:8008
  certfile: /var/lib/postgresql/ssl/patroni-rest-api-pgs1.pem
  keyfile: /var/lib/postgresql/ssl/patroni-rest-api-pgs1.key
  cafile: /var/lib/postgresql/ssl/patroni-ca.pem
  verify_client: none

ctl:
  cacert: /var/lib/postgresql/ssl/patroni-ca.pem
  certfile: /var/lib/postgresql/ssl/patroni-ctl-pgs1.pem
  keyfile: /var/lib/postgresql/ssl/patroni-ctl-pgs1.key

consul:
  url: https://127.0.0.1:8501
  verify: true
  cacert: /var/lib/postgresql/ssl/consul-agent-ca.pem
  cert: /var/lib/postgresql/ssl/consul-client-pgs1-cert.pem
  key: /var/lib/postgresql/ssl/consul-client-pgs1-key.pem
  register_service: true
  dc: debsu-dc
  service_tags:
    - traefik.enable=true
    - traefik.tcp.routers.pgsql.rule=HostSNI(`*`)
    - traefik.tcp.routers.pgsql.entrypoints=postgres
    - traefik.tcp.services.pgsql.loadbalancer.server.port=5432

bootstrap:
  dcs:
    ttl: 2
    loop_wait: 1
    retry_timeout: 5
    maximum_lag_on_failover: 1048576
    postgresql:
      parameters:
        ssl: 'on'
        ssl_ca_file: /var/lib/postgresql/ssl/patroni-ca.pem
        ssl_cert_file: /var/lib/postgresql/ssl/server.pem
        ssl_key_file: /var/lib/postgresql/ssl/server.key
        max_connections: 100
        shared_buffers: 256MB
        wal_level: replica
        hot_standby: "on"
        wal_keep_size: 64
        max_wal_senders: 10
        max_replication_slots: 10
      pg_hba:
        - hostssl replication pgrepl 127.0.0.1/32 md5
        - hostssl replication pgrepl 192.168.88.25/24 md5
        - hostssl replication pgrepl 192.168.88.26/24 md5
        - hostssl replication pgrepl 192.168.88.27/24 md5
        - hostssl all all 127.0.0.1/32 md5
        - hostssl all all 0.0.0.0/0 md5
  initdb:
    - encoding: UTF8
    - locale: en_US.UTF-8
    - data-checksums
  users:
    pgadmin:
      password: <salasana>
      options:
        - superuser
        - createdb
        - createrole
    pgrepl:
      password: <salasana>
      options:
        - replication

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.88.25:5432
  data_dir: /var/lib/postgresql/data
  bin_dir: /usr/lib/postgresql/18/bin
  authentication:
    replication:
      username: pgrepl
      password: <salasana>
    superuser:
      username: pgadmin
      password: <salasana>

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

Tässä vielä esimerkkinä pgs2:sen conffi:

pgs2:~$ cat /etc/patroni/config.yml

YAML:

scope: postgresql-cluster
name: pgs2

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.88.26:8008
  certfile: /var/lib/postgresql/ssl/patroni-rest-api-pgs2.pem
  keyfile: /var/lib/postgresql/ssl/patroni-rest-api-pgs2.key
  cafile: /var/lib/postgresql/ssl/patroni-ca.pem
  verify_client: none

ctl:
  cacert: /var/lib/postgresql/ssl/patroni-ca.pem
  certfile: /var/lib/postgresql/ssl/patroni-ctl-pgs2.pem
  keyfile: /var/lib/postgresql/ssl/patroni-ctl-pgs2.key

consul:
  url: https://127.0.0.1:8501
  verify: true
  cacert: /var/lib/postgresql/ssl/consul-agent-ca.pem
  cert: /var/lib/postgresql/ssl/consul-client-pgs2-cert.pem
  key: /var/lib/postgresql/ssl/consul-client-pgs2-key.pem
  register_service: true
  dc: debsu-dc
  service_tags:
    - traefik.enable=true
    - traefik.tcp.routers.pgsql.rule=HostSNI(`*`)
    - traefik.tcp.routers.pgsql.entrypoints=postgres
    - traefik.tcp.services.pgsql.loadbalancer.server.port=5432

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.88.26:5432
  data_dir: /var/lib/postgresql/data
  bin_dir: /usr/lib/postgresql/18/bin
  authentication:
    replication:
      username: pgrepl
      password: <salasana>
    superuser:
      username: pgadmin
      password: <salasana>

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

Kaikki certifikaatit luodaan myös samassa playbookissa ja Patronin rest-api-, ctl- ja postgres-certifikaatit luodaan käyttäen samaa itseluotua Patroni CA:ta. Consulia varten on oma CA ja Consulin ja Patronin välinen yhteys pelaa kun ~~Consuliin ilmestyy service~~ ja KV-varantoon tiedot tiedot tuosta klusterista.

Alla esimerkkinä pgs1:sen Patroni certit:

pgs1:~# openssl x509 -noout -text -in /var/lib/postgresql/ssl/patroni-ca.pem

Koodi:

Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            46:7d:22:e3:30:71:4d:f7:57:6b:c3:ae:3e:03:b3:9d:26:9c:46:67
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN = Patroni CA
        Validity
            Not Before: Sep 25 15:35:35 2025 GMT
            Not After : Sep 23 15:35:35 2035 GMT
        Subject: CN = Patroni CA
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (4096 bit)
                Modulus:
                    <modulus>
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Key Usage: critical
                Digital Signature, Certificate Sign, CRL Sign
            X509v3 Basic Constraints: critical
                CA:TRUE
            X509v3 Subject Key Identifier:
                20:4B:EA:E7:B0:2A:5A:50:CF:39:A1:03:D6:EA:70:B0:82:6A:30:23
    Signature Algorithm: sha256WithRSAEncryption
    Signature Value:
        <vallue>

pgs1:~# openssl x509 -noout -text -in /var/lib/postgresql/ssl/patroni-rest-api-pgs1.pem

Koodi:

Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            0e:04:d4:39:7d:8c:a3:de:3b:62:dd:57:f0:c7:9d:1e:8d:45:2b:26
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN = Patroni CA
        Validity
            Not Before: Sep 25 15:35:36 2025 GMT
            Not After : Sep 24 15:35:36 2030 GMT
        Subject: CN = localhost
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    <modulus>
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Subject Alternative Name:
                IP Address:192.168.88.25, IP Address:127.0.0.1, DNS:localhost, DNS:pgs1, DNS:pgs1.int.debsu.eu
            X509v3 Key Usage:
                Digital Signature, Key Encipherment
            X509v3 Extended Key Usage:
                TLS Web Server Authentication
            X509v3 Subject Key Identifier:
                03:21:42:29:09:38:78:18:ED:AE:01:69:00:D3:59:AC:66:A6:71:BC
            X509v3 Authority Key Identifier:
                20:4B:EA:E7:B0:2A:5A:50:CF:39:A1:03:D6:EA:70:B0:82:6A:30:23
    Signature Algorithm: sha256WithRSAEncryption
    Signature Value:
        <value>

pgs1:~# openssl x509 -noout -text -in /var/lib/postgresql/ssl/patroni-ctl-pgs1.pem

Koodi:

Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            38:57:56:2c:52:e3:be:be:f2:42:c4:80:90:56:17:5e:e8:62:76:cc
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN = Patroni CA
        Validity
            Not Before: Sep 25 15:35:39 2025 GMT
            Not After : Sep 24 15:35:39 2030 GMT
        Subject: CN = localhost
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    <modulus>
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Subject Alternative Name:
                IP Address:192.168.88.25, IP Address:127.0.0.1, DNS:localhost, DNS:pgs1, DNS:pgs1.int.debsu.eu
            X509v3 Key Usage:
                Digital Signature, Key Encipherment
            X509v3 Extended Key Usage:
                TLS Web Client Authentication
            X509v3 Subject Key Identifier:
                99:BB:60:4A:04:09:D4:5C:11:86:86:AF:DD:A6:25:9A:9F:4A:1E:84
            X509v3 Authority Key Identifier:
                20:4B:EA:E7:B0:2A:5A:50:CF:39:A1:03:D6:EA:70:B0:82:6A:30:23
    Signature Algorithm: sha256WithRSAEncryption
    Signature Value:
        <value>

pgs1:~# openssl x509 -noout -text -in /var/lib/postgresql/ssl/patroni-postgres-pgs1.pem

Koodi:

Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            12:00:36:0b:b8:4a:cf:f0:ec:d5:c1:90:3c:e7:60:9c:49:1e:af:0b
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN = Patroni CA
        Validity
            Not Before: Sep 25 15:35:42 2025 GMT
            Not After : Sep 24 15:35:42 2030 GMT
        Subject: CN = localhost
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    <modulus>
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Subject Alternative Name:
                IP Address:192.168.88.25, IP Address:127.0.0.1, DNS:localhost, DNS:pgs1, DNS:pgs1.int.debsu.eu
            X509v3 Key Usage:
                Digital Signature, Key Encipherment
            X509v3 Extended Key Usage:
                TLS Web Server Authentication
            X509v3 Subject Key Identifier:
                F1:B1:98:AB:0C:19:61:A4:A6:20:86:15:7A:40:5C:FA:96:7B:73:22
            X509v3 Authority Key Identifier:
                20:4B:EA:E7:B0:2A:5A:50:CF:39:A1:03:D6:EA:70:B0:82:6A:30:23
    Signature Algorithm: sha256WithRSAEncryption
    Signature Value:
        <value>

Patronin käyttämien certtien käyttöoikeudet on määritelty seuraavanlaisesti:

pgs1:~# ls -l /var/lib/postgresql/ssl/

Bash:

total 40
-rw-r--r-- 1 postgres postgres 1785 Sep 25 19:12 consul-agent-ca.pem
-rw-r--r-- 1 postgres postgres 1631 Sep 25 19:12 consul-client-pgs1-cert.pem
-rw------- 1 postgres postgres 1679 Sep 25 19:12 consul-client-pgs1-key.pem
-rw-r--r-- 1 postgres postgres 1785 Sep 25 19:12 patroni-ca.pem
-rw------- 1 postgres postgres 1679 Sep 25 19:12 patroni-ctl-pgs1.key
-rw-r--r-- 1 postgres postgres 1566 Sep 25 19:12 patroni-ctl-pgs1.pem
-rw------- 1 postgres postgres 1675 Sep 25 19:12 patroni-postgres-pgs1.key
-rw-r--r-- 1 postgres postgres 1566 Sep 25 19:12 patroni-postgres-pgs1.pem
-rw------- 1 postgres postgres 1675 Sep 25 19:12 patroni-rest-api-pgs1.key
-rw-r--r-- 1 postgres postgres 1566 Sep 25 19:12 patroni-rest-api-pgs1.pem
lrwxrwxrwx 1 root     root       49 Sep 25 19:12 server.key -> /var/lib/postgresql/ssl/patroni-postgres-pgs1.key
lrwxrwxrwx 1 root     root       49 Sep 25 19:12 server.pem -> /var/lib/postgresql/ssl/patroni-postgres-pgs1.pem

Mikä noissa certifikaateissa on vialla, että se siittä valittaa? Käsittääkseni se valittaa tuosta REST API:n certifikaatista, mutta mikä siinä on ongelmana? Olen yrittänyt käydä läpi tuota ongelmaa ChatGPT:n ja Google Geminin avulla, mutta se ei edisty niiden avullakaan.

[edit] Olen kokeillut vaihtaa certtien CN:ksi ip-osoitteen, localhostin, tai <hostname>.omadomain.fi, mutta ne eivät auta.

[edit] Komento

curl -k https://pgs3:8008/cluster  --cacert /var/lib/postgresql/ssl/patroni-ca.pem --cert /var/lib/postgresql/ssl/patroni-ctl-pgs1.pem --key /var/lib/postgresql/ssl/patroni-ctl-pgs1.key | jq .

palauttaa:

Koodi:

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   547    0   547    0     0  11154      0 --:--:-- --:--:-- --:--:-- 11395
{
  "members": [
    {
      "name": "pgs1",
      "role": "leader",
      "state": "running",
      "api_url": "https://192.168.88.25:8008/patroni",
      "host": "192.168.88.25",
      "port": 5432,
      "timeline": 1
    },
    {
      "name": "pgs2",
      "role": "replica",
      "state": "streaming",
      "api_url": "https://192.168.88.26:8008/patroni",
      "host": "192.168.88.26",
      "port": 5432,
      "timeline": 1,
      "lag": 0
    },
    {
      "name": "pgs3",
      "role": "replica",
      "state": "streaming",
      "api_url": "https://192.168.88.27:8008/patroni",
      "host": "192.168.88.27",
      "port": 5432,
      "timeline": 1,
      "lag": 0
    }
  ],
  "scope": "postgresql-cluster"
}

joten eikö tuo meinaa, että certit toimii kyllä?
[edit] Se näköjään palauttaa saman, vaikka ei käytäkkään noita certtejä komennolla pgs1:~# curl -k https://pgs3:8008/cluster | jq .
[edit] Toimii OK myös ilman -k-vipua, eli komennolla

curl https://pgs3:8008/cluster  --cacert /var/lib/postgresql/ssl/patroni-ca.pem --cert /var/lib/postgresql/ssl/patroni-ctl-pgs1.pem --key /var/lib/postgresql/ssl/patroni-ctl-pgs1.key | jq .

Torz · torstaina klo 21:45

curlin -k vipu nimenomaan skippaa certin tarkistuksen, jolloin ei ole väliä mitä syötät --cacert optiolla.

Lähtisin siitä kokeile ilman -k vipua ja koita selvittää mikä se virhe on, johtuuko domainista vai varmenneketjun puutteesta vai mistä. Eli tarkempaa virhettä selville ja sen avulla tekoäly varmaankin osaa jo auttaa eteenpäin.

SamCer · torstaina klo 22:27

Torz sanoi:
curlin -k vipu nimenomaan skippaa certin tarkistuksen, jolloin ei ole väliä mitä syötät --cacert optiolla.

Lähtisin siitä kokeile ilman -k vipua ja koita selvittää mikä se virhe on, johtuuko domainista vai varmenneketjun puutteesta vai mistä. Eli tarkempaa virhettä selville ja sen avulla tekoäly varmaankin osaa jo auttaa eteenpäin.

Ilman -k -vipua toimii ongelmitta, eli komennolla

curl https://pgs3:8008/cluster  --cacert /var/lib/postgresql/ssl/patroni-ca.pem --cert /var/lib/postgresql/ssl/patroni-ctl-pgs1.pem --key /var/lib/postgresql/ssl/patroni-ctl-pgs1.key | jq .

SamCer · eilen klo 11:36

Se ongelma olikin Consulin ja Patronin välinen yhteys. Kun Patronin configista pisti asetuksen consul.register_service: true muotoon consul.register_service: false, niin certti virhe katosi. Tuon keksin kun löysin Patronin github-sivuilta samaa ongelmaa käsittelevän issuen ja keskustelun, Consul service checks failing when TLS is enabled on Patroni Rest API · Issue #3009 · patroni/patroni ja Once again; TLS issues with RESTAPI · patroni patroni · Discussion #3319. Jatko selvittelyn jälkeen sain sen nyt toimimaan ihan tyydyttävällä tavalla:

Dumppasin Patronin erillisen CA:n ja allekirjoitin/-tan kaikki certit jatkossa yhdellä CA:lla, nyt tuolla Consulille tehdyllä CA:lla
Muutin Patronin configista asetuksen restapi.verify_client: required muotoon restapi.verify_client: none

Haluaisin tuon restapi.verify_client: none asetuksen muotoon restapi.verify_client: required, mutta silloin Consulissa tuon Patroni servicen healtcheckit ei toimi. Ilmeisesti siksi kun ne eivät osaa käyttää Consul agentin certtiä, joka on allekirjoitettu ja luotettu samalla CA:lla, kun ottaa yhteyttä Patronin REST API:in. Viisaammat voisi opastaa onko tuota mahdollista jotenkin saada toimimaan? Vai pitääkö mun tehdä itse Patronin service ja healtcheckit Consuliin.

Noi Consulissa olevat servicet ja healtcheckit on tarpeen kun tuon Patroni klusterin eteen tulee Traefik reverse Proxy, joka hoitaa reitityksen leaderille automaattisesti Consulista saadun tiedon perusteella ja tiputtaa automaattisesti epäterveet nodet pois.

Toimiva Patroni conffi pgs1:stä esimerkkinä:

YAML:

scope: postgresql-cluster
name: pgs1

log:
  level: DEBUG
  dir: /var/lib/postgresql

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.88.25:8008
  certfile: /var/lib/postgresql/ssl/patroni-rest-api-pgs1.pem
  keyfile: /var/lib/postgresql/ssl/patroni-rest-api-pgs1.key
  cafile: /var/lib/postgresql/ssl/consul-agent-ca.pem
  verify_client: none

ctl:
  cacert: /var/lib/postgresql/ssl/consul-agent-ca.pem
  certfile: /var/lib/postgresql/ssl/patroni-ctl-pgs1.pem
  keyfile: /var/lib/postgresql/ssl/patroni-ctl-pgs1.key
  insecure: false

consul:
  url: https://127.0.0.1:8501
  verify: true
  cacert: /var/lib/postgresql/ssl/consul-agent-ca.pem
  cert: /var/lib/postgresql/ssl/consul-client-pgs1-cert.pem
  key: /var/lib/postgresql/ssl/consul-client-pgs1-key.pem
  register_service: true
  dc: debsu-dc
  service_tags:
    - traefik.enable=true
    - traefik.tcp.routers.pgsql.rule=HostSNI(`*`)
    - traefik.tcp.routers.pgsql.entrypoints=postgres
    - traefik.tcp.services.pgsql.loadbalancer.server.port=5432

bootstrap:
  dcs:
    ttl: 2
    loop_wait: 1
    retry_timeout: 5
    maximum_lag_on_failover: 1048576
    postgresql:
      parameters:
        ssl: 'on'
        ssl_ca_file: /var/lib/postgresql/ssl/consul-agent-ca.pem
        ssl_cert_file: /var/lib/postgresql/ssl/server.pem
        ssl_key_file: /var/lib/postgresql/ssl/server.key
        max_connections: 100
        shared_buffers: 256MB
        wal_level: replica
        hot_standby: "on"
        wal_keep_size: 64
        max_wal_senders: 10
        max_replication_slots: 10
        logging_collector: 'on'
        log_directory: 'pg_log'
        log_filename: 'postgresql-%Y-%m-%d_%H%M%S.log'
        log_min_messages: debug1
      pg_hba:
        - hostssl replication pgrepl 127.0.0.1/32 md5
        - hostssl replication pgrepl 192.168.88.25/24 md5
        - hostssl replication pgrepl 192.168.88.26/24 md5
        - hostssl replication pgrepl 192.168.88.27/24 md5
        - hostssl all all 127.0.0.1/32 md5
        - hostssl all all 0.0.0.0/0 md5
        - local all postgres peer
  initdb:
    - encoding: UTF8
    - locale: en_US.UTF-8
    - data-checksums
  users:
    pgadmin:
      password: <passu>
      options:
        - superuser
        - createdb
        - createrole
    pgrepl:
      password: <passu>
      options:
        - replication

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.88.25:5432
  data_dir: /var/lib/postgresql/data
  bin_dir: /usr/lib/postgresql/18/bin
  authentication:
    replication:
      username: pgrepl
      password: <passu>
    superuser:
      username: pgadmin
      password: <passu>

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

zepi · eilen klo 15:02

SamCer sanoi:
Se ongelma olikin Consulin ja Patronin välinen yhteys. Kun Patronin configista pisti asetuksen consul.register_service: true muotoon consul.register_service: false, niin certti virhe katosi. Tuon keksin kun löysin Patronin github-sivuilta samaa ongelmaa käsittelevän issuen ja keskustelun, Consul service checks failing when TLS is enabled on Patroni Rest API · Issue #3009 · patroni/patroni ja Once again; TLS issues with RESTAPI · patroni patroni · Discussion #3319. Jatko selvittelyn jälkeen sain sen nyt toimimaan ihan tyydyttävällä tavalla:

Dumppasin Patronin erillisen CA:n ja allekirjoitin/-tan kaikki certit jatkossa yhdellä CA:lla, nyt tuolla Consulille tehdyllä CA:lla

Muutin Patronin configista asetuksen restapi.verify_client: required muotoon restapi.verify_client: none

Haluaisin tuon restapi.verify_client: none asetuksen muotoon restapi.verify_client: required, mutta silloin Consulissa tuon Patroni servicen healtcheckit ei toimi. Ilmeisesti siksi kun ne eivät osaa käyttää Consul agentin certtiä, joka on allekirjoitettu ja luotettu samalla CA:lla, kun ottaa yhteyttä Patronin REST API:in. Viisaammat voisi opastaa onko tuota mahdollista jotenkin saada toimimaan? Vai pitääkö mun tehdä itse Patronin service ja healtcheckit Consuliin.

Noi Consulissa olevat servicet ja healtcheckit on tarpeen kun tuon Patroni klusterin eteen tulee Traefik reverse Proxy, joka hoitaa reitityksen leaderille automaattisesti Consulista saadun tiedon perusteella ja tiputtaa automaattisesti epäterveet nodet pois.

Aika tyypillinen ongelma on se, se sun custom CA ei ole jonkun kikkareen trusted rootissa. Vaihtoehdot on joko käyttää certtiä joka on luotettu (julkinen jostain lets encryptistä tjsp) tai sitten koittaa keksiä miten sen oman CA:n saa sinne tarvittavan kilkkeen trustediin.

Tässä kohtaa pitää varoittaa, etten tiedä mitään Consulista, mutta aika tyypillistä on, että eri komponentit riippuen ohjelmointikielestä / konffeista jne. käyttävät eri trusted rootteja jne. Javakilke käyttää jotain omaa CAStoreaan ja osa käyttää käyttöjärjestelmän ja selain käyttää omaansa jne.

Torak · eilen klo 22:32

Tämä vois antaa vähän enemmän tietoa.
openssl s_client -connect https://pgs3:8008/cluster:8080

Käsittääkseni itsekirjoitetuista avaimissa pitää olla oikeus TSL käyttöön komennolla (open sll ini)
extendedKeyUsage = clientAuth

Patroni PostgresSQL-klusteri TLS ongelma

SamCer

Torz

SamCer

SamCer

zepi

Torak

Uutiset

Uutisia lyhyesti

Uusimmat viestit

Statistiikka

Hinta.fi

Arvostamme yksityisyyttäsi