Change #1310601 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/deployment-charts@master] istio: expose a TLS passthrough port for PostgreSQL on dse-k8s

https://gerrit.wikimedia.org/r/1310601

Change #1310602 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/deployment-charts@master] cloudnative-pg-cluster: omit empty backup encryption from Cluster manifests

https://gerrit.wikimedia.org/r/1310602

Change #1310603 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/deployment-charts@master] cloudnative-pg-cluster: add SNI-based TLS passthrough ingress

https://gerrit.wikimedia.org/r/1310603

Change #1311036 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] service: add k8s-ingress-dse-postgresql TLS passthrough service

https://gerrit.wikimedia.org/r/1311036

Change #1310601 merged by jenkins-bot:

[operations/deployment-charts@master] istio: expose a TLS passthrough port for PostgreSQL on dse-k8s

https://gerrit.wikimedia.org/r/1310601

This is moving forward quite well. We need several things to be in place before we can make ingress work, because this will be the first time that we have used a non-HTTP based service under ingress.

Firstly, we need to have a new port open on our istio-ingressgateway pods, which run in the istio-system namespace.
That part is configured in this patch: https://gerrit.wikimedia.org/r/1310601

Then we need the ability for clusters that use the cloudnative-pg-cluster chart to be able to specify that they wish to use ingress.
That will be handled by this patch: https://gerrit.wikimedia.org/r/1310603

Then. we need a new LVS service in the service catalog, to allow us to load-balance across this new port.
That is handled by this change: https://gerrit.wikimedia.org/r/1311036

Then we need to create the new cluster itself, which will be the first to request ingress. At this point, we will be able to switch the service into production mode.

Change #1310603 merged by jenkins-bot:

[operations/deployment-charts@master] cloudnative-pg-cluster: add SNI-based TLS passthrough ingress

https://gerrit.wikimedia.org/r/1310603

btullis opened https://gitlab.wikimedia.org/repos/data-engineering/postgresql-kubernetes/-/merge_requests/22

Add a vanilla postgresql-17 image variant

btullis merged https://gitlab.wikimedia.org/repos/data-engineering/postgresql-kubernetes/-/merge_requests/22

Add a vanilla postgresql-17 image variant

Change #1311463 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/deployment-charts@master] admin_ng: add the postgresql-superset-metrics namespace on dse-k8s-eqiad

https://gerrit.wikimedia.org/r/1311463

Change #1311469 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/deployment-charts@master] cloudnative-pg-cluster: allow configuring a node maintenance window

https://gerrit.wikimedia.org/r/1311469

Change #1311480 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] kubernetes: add deploy tokens for the postgresql-superset-metrics namespace

https://gerrit.wikimedia.org/r/1311480

Change #1311469 abandoned by Btullis:

[operations/deployment-charts@master] cloudnative-pg-cluster: allow configuring a node maintenance window

Reason:

Decided not to do this. Using pod disruption budgets instead.

https://gerrit.wikimedia.org/r/1311469

Change #1311036 merged by Btullis:

[operations/puppet@production] service: add k8s-ingress-dse-postgresql TLS passthrough service

https://gerrit.wikimedia.org/r/1311036

Change #1311486 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] kubernetes: add the vanilla postgresql-17 image to common_images

https://gerrit.wikimedia.org/r/1311486

Change #1311463 merged by jenkins-bot:

[operations/deployment-charts@master] admin_ng: add the postgresql-superset-metrics namespace on dse-k8s-eqiad

https://gerrit.wikimedia.org/r/1311463

btullis opened https://gitlab.wikimedia.org/repos/data-engineering/postgresql-kubernetes/-/merge_requests/23

Put the postgresql major version in the image tag, not the image name

Change #1311796 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/deployment-charts@master] dse-k8s: add the postgresql-superset-metrics deployment

https://gerrit.wikimedia.org/r/1311796

btullis merged https://gitlab.wikimedia.org/repos/data-engineering/postgresql-kubernetes/-/merge_requests/23

Put the postgresql major version in the image tag, not the image name

Change #1311486 merged by Btullis:

[operations/puppet@production] kubernetes: add the vanilla PostgreSQL 17 image to common_images

https://gerrit.wikimedia.org/r/1311486

Change #1311480 merged by Btullis:

[operations/puppet@production] kubernetes: add deploy tokens for the postgresql-superset-metrics namespace

https://gerrit.wikimedia.org/r/1311480

The connection string that we will use from Spark will be something like this:

jdbc:postgresql://postgresql-superset-metrics.svc.eqiad.wmnet:31432/superset_metrics?sslNegotiation=direct&sslmode=verify-full

The part that says: sslNegotiation=direct is the bit that will allow us to be able to support ingress to multiple PostgreSQL clusters behind one ingress gateway TCP port.
That's also the feature that requires us to use PostgreSQL version 17 or above.

This is also going to be our first cluster that doesn't use storage on Ceph RBD, but instead uses node-local storage on the dse-k8s-worker nodes.

This has the benefit that it doesn't incur the network write latency, nor write amplification. It does mean that PostgreSQL servers will be bound to specific hosts (dse-k8s-worker100[1-4]) and that we will need three nodes for safety, but I'm happy to be able to enable this feature for this cluster, for the first time.

I have created the radosgw user for this database.

btullis@cephosd1001:~$ sudo radosgw-admin user create --uid=postgresql-superset-metrics --display-name=postgresql-superset-metrics

btullis@stat1008:~$ read access_key
<redacted>
btullis@stat1008:~$ read secret_key
<redacted>
btullis@stat1008:~$ s3cmd --access_key=$access_key --secret_key=$secret_key --host=rgw.eqiad.dpe.anycast.wmnet --region=dpe --host-bucket=no mb s3://postgresql-superset-metrics.dse-k8s-eqiad
Bucket 's3://postgresql-superset-metrics.dse-k8s-eqiad/' created

I have also added the token to the private helmfile deployment values for the postgresql-superset-metrics namespace.

Change #1311796 merged by jenkins-bot:

[operations/deployment-charts@master] dse-k8s: add the postgresql-superset-metrics deployment

https://gerrit.wikimedia.org/r/1311796

I banged onto a weird issue related to the Superset UI, that wouldn't let me add a PG connection. It was complaining about the provided password not working, even though it was encoded correctly, and the following worked in a superset python shell:

runuser@superset-production-684d5665bb-7pwbz:/app$ superset shell
Loaded your LOCAL configuration at [/etc/superset/superset_config.py]
logging was configured successfully
2026-07-23 14:19:22,435:INFO:superset.utils.logging_configurator:logging was configured successfully
2026-07-23 14:19:22,440:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
Python 3.11.2 (main, Apr 28 2025, 14:11:48) [GCC 12.2.0] on linux
App: superset.app
Instance: /app/instance
>>> from sqlalchemy import create_engine, text
>>> uri = "postgresql://superset_metrics:REDACTED@postgresql-superset-metrics-rw.postgresql-superset-metrics:5432/superset_metrics"
>>> engine = create_engine(uri)
>>> with engine.connect() as conn:
...     print(conn.execute(text("select current_user, inet_server_addr()")).fetchone())
...
('superset_metrics', '10.67.24.119')

So clearly the password was correct.
After 1h of trying things out, I resorted to creating the database connection from a python shell, because I'm Good With Computers ™ :

brouberol@deploy1003:~$ k exec -it superset-production-684d5665bb-7pwbz -- bash
Defaulted container "superset-production" out of: superset-production, superset-production-assets, superset-production-tls-proxy, statsd-exporter, superset-production-renew-kerberos-token
runuser@superset-production-684d5665bb-7pwbz:/app$ superset shell
Loaded your LOCAL configuration at [/etc/superset/superset_config.py]
logging was configured successfully
2026-07-23 14:40:59,060:INFO:superset.utils.logging_configurator:logging was configured successfully
2026-07-23 14:40:59,064:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
Python 3.11.2 (main, Apr 28 2025, 14:11:48) [GCC 12.2.0] on linux
App: superset.app
Instance: /app/instance
>>> from superset import db
>>> from superset.models.core import Database
>>> database = Database(
...     database_name="PostgreSQL Superset Metrics", sqlalchemy_uri="postgresql+psycopg2://superset_metrics:REDACTED@postgresql-superset-metrics-rw.postgresql-superset-metrics.svc.cluster.local:5432/superset_metrics")
>>> db.session.add(database)
>>> db.session.commit()
>>>
now exiting InteractiveConsole...

I then created a temporary table in PG:

Did not find any relations.
superset_metrics=# CREATE TABLE books (id int PRIMARY KEY, title text);
CREATE TABLE
superset_metrics=# INSERT INTO books VALUES (1, 'lotr')
superset_metrics-# ;
INSERT 0 1
superset_metrics=# SELECT * FROM books;
 id | title
----+-------
  1 | lotr
superset_metrics=# ALTER TABLE books OWNER TO superset_metrics;
ALTER TABLE

I was then able to query that database from SQLLab

I'm now going to update the sqlalchemy_uri to use the -ro service name instead of -rw because I don't think we want to be able to write to the tables from superset. (done)

I think we're done!

Change #1314854 had a related patch set uploaded (by Brouberol; author: Brouberol):

[operations/deployment-charts@master] Allow remote authenticated connections with the superset_metrics user

https://gerrit.wikimedia.org/r/1314854

(I have dropped the books table)

Change #1314854 merged by Brouberol:

[operations/deployment-charts@master] Allow remote authenticated connections with the superset_metrics user

https://gerrit.wikimedia.org/r/1314854

brouberol@dns1004:~$ host postgresql-superset-metrics.svc.eqiad.wmnet
postgresql-superset-metrics.svc.eqiad.wmnet is an alias for k8s-ingress-dse.svc.eqiad.wmnet.
k8s-ingress-dse.svc.eqiad.wmnet has address 10.2.2.91

However the 31432 nodeport isn't exposed yet, as the pybal service needs to be put into production state first:

brouberol@dse-k8s-worker1009:~$ telnet 10.64.0.149 30443
Trying 10.64.0.149...
Connected to 10.64.0.149.
Escape character is '^]'.
^]
telnet> quit
Connection closed.
brouberol@dse-k8s-worker1009:~$ telnet 10.64.0.149 31432
Trying 10.64.0.149...
^C

Change #1315900 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] dse-k8s: Enable the k8s-ingress-dse-postgresql service

https://gerrit.wikimedia.org/r/1315900

Change #1315900 merged by Btullis:

[operations/puppet@production] dse-k8s: Enable the k8s-ingress-dse-postgresql service

https://gerrit.wikimedia.org/r/1315900

Change #1318075 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/deployment-charts@master] admin_ng: allow traffic to the ingressgateway PostgreSQL port on dse-k8s

https://gerrit.wikimedia.org/r/1318075

Change #1318075 merged by jenkins-bot:

[operations/deployment-charts@master] admin_ng: allow traffic to the ingressgateway PostgreSQL port on dse-k8s

https://gerrit.wikimedia.org/r/1318075

Change #1318167 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/deployment-charts@master] cloudnative-pg-cluster: allow issuing the server certificate from the WMF PKI

https://gerrit.wikimedia.org/r/1318167

Change #1318168 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/deployment-charts@master] postgresql-superset-metrics: issue the server certificate from the WMF PKI

https://gerrit.wikimedia.org/r/1318168

Change #1318167 merged by jenkins-bot:

[operations/deployment-charts@master] cloudnative-pg-cluster: allow issuing the server certificate from the WMF PKI

https://gerrit.wikimedia.org/r/1318167

Change #1318168 merged by jenkins-bot:

[operations/deployment-charts@master] postgresql-superset-metrics: issue the server certificate from the WMF PKI

https://gerrit.wikimedia.org/r/1318168

I think that this should be working now. I managed to get the certificate to be signed by the WMF PKI as well.

btullis@stat1008:~$ openssl s_client -connect postgresql-superset-metrics.svc.eqiad.wmnet:31432 | openssl x509 -noout -subject -ext subjectAltName
depth=2 C = US, ST = California, L = San Francisco, O = "Wikimedia Foundation, Inc", OU = Cloud Services, CN = Wikimedia_Internal_Root_CA
verify return:1
depth=1 C = US, L = San Francisco, O = "Wikimedia Foundation, Inc", OU = SRE Foundations, CN = discovery2026
verify return:1
depth=0 CN = postgresql-superset-metrics-rw
verify return:1
subject=CN = postgresql-superset-metrics-rw
X509v3 Subject Alternative Name: 
    DNS:postgresql-superset-metrics-rw, DNS:postgresql-superset-metrics-rw.postgresql-superset-metrics, DNS:postgresql-superset-metrics-rw.postgresql-superset-metrics.svc, DNS:postgresql-superset-metrics-rw.postgresql-superset-metrics.svc.cluster.local, DNS:postgresql-superset-metrics-ro, DNS:postgresql-superset-metrics-ro.postgresql-superset-metrics, DNS:postgresql-superset-metrics-ro.postgresql-superset-metrics.svc, DNS:postgresql-superset-metrics-ro.postgresql-superset-metrics.svc.cluster.local, DNS:postgresql-superset-metrics-r, DNS:postgresql-superset-metrics-r.postgresql-superset-metrics, DNS:postgresql-superset-metrics-r.postgresql-superset-metrics.svc, DNS:postgresql-superset-metrics-r.postgresql-superset-metrics.svc.cluster.local, DNS:postgresql-superset-metrics.discovery.wmnet, DNS:postgresql-superset-metrics.svc.eqiad.wmnet

So we're now ready to hand over the password for the read/write database user to @Milimetric and make this available to Spark and/or Airflow jobs.

Change #1318686 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/dns@master] dse-k8s: Sort the discovery records alphabetically

https://gerrit.wikimedia.org/r/1318686

Change #1318687 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/dns@master] dse-k8s: Add a discovery record for postgresql-superset-metrics

https://gerrit.wikimedia.org/r/1318687

Change #1318686 merged by Btullis:

[operations/dns@master] dse-k8s: Sort the discovery records alphabetically

https://gerrit.wikimedia.org/r/1318686

Change #1318687 merged by Btullis:

[operations/dns@master] dse-k8s: Add a discovery record for postgresql-superset-metrics

https://gerrit.wikimedia.org/r/1318687

brouberol@deploy1003:~$ telnet postgresql-superset-metrics.discovery.wmnet 31432
Trying 10.2.2.91...
Connected to postgresql-superset-metrics.discovery.wmnet.
Escape character is '^]'.

Note: connecting only works with sslmode=verify-full sslnegotiation=direct sslrootcert=system for reasons laid out in https://phabricator.wikimedia.org/T432104#12132559, so I needed to install postgresql-client-17. In the case of Spark, a JDBC client supporting these options will need to be installed.

brouberol@kafka-jumbo1010:~$ psql "dbname=superset_metrics user=superset_metrics host=postgresql-superset-metrics.discovery.wmnet port=31432 sslmode=verify-full sslnegotiation=direct sslrootcert=system"
Password for user superset_metrics:
psql (17.10 (Debian 17.10-0+deb13u1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

superset_metrics=>

@Milimetric the PG password is available in your home directory on stat1010, filename pg,txt and is only readable by your user (and root). Please delete it when no longer required.