SSL connection error with "PG_META_DB_SSL_ROOT_CERT" and "rejectUnauthorized" misconfiguration

[tkpte]

If I am not mistaken, pg-meta doesn't support mutual TLS as we are only allowed to specify the sslmode and sslrootcert via PG_META_DB_SSL_MODE and PG_META_DB_SSL_ROOT_CERT respectively. There's no way to specifiy sslcert and sslkey.

In addition to being very inconsistent with Postgrest and Gotrue, this way of configuring SSL doesn't work for some reason.

Example error:

{"level":"error","time":"2023-11-05T12:27:06.650Z","pid":20,"hostname":"279049c55c32","reqId":"req-1","error":{"message":"self signed certificate"},"request":{"method":"GET","url":"/schemas","pg
":"10.100.7.2","opt":""}}

Example config 1:

environment:
  PG_META_DB_SSL_MODE: verify-ca
  PG_META_DB_SSL_ROOT_CERT: /certs/example.crt
volumes:
  - ./example.crt:/certs/example.crt:ro

Example config 2 (tried a hack):

environment:
  PG_META_DB_SSL_MODE: verify-ca&sslrootcert=/certs/example.crt&sslcert=/certs/example.crt&sslkey=/certs/example.key
volumes:
  - ./example.crt:/certs/example.crt:ro
  - ./example.key:/certs/example.key:ro

I would be grateful if someone could point out my mistakes. However, I believe that pg-meta should be refactored to work just like Postgrest and Gotrue.

Example with Postgrest:

environment:
  PGRST_DB_URI:
postgres://${POSTGRES_USER_REST}:${POSTGRES__PASSWORD}@${POSTGRES__HOST}:${POSTGRES__PORT}/${POSTGRES__DB}?sslmode=verify-ca&sslrootcert=/certs/example.crt&sslcert=/certs/example.crt&sslkey=/example-rest.key
volumes:
  - ./example.crt:/certs/example.crt:ro
  - ./example.key:/certs/example.key:ro

Example with Gotrue:

environment:
  GOTRUE_DB_DATABASE_URL:
postgres://${POSTGRES_USER_AUTH}:${POSTGRES__PASSWORD}@${POSTGRES__HOST}:${POSTGRES__PORT}/${POSTGRES__DB}?sslmode=verify-ca&sslrootcert=/certs/example.crt&sslcert=/certs/example.crt&sslkey=/example-rest.key
volumes:
  - ./example.crt:/certs/example.crt:ro
  - ./example.key:/certs/example.key:ro

[tkpte]

It's not explicitly stated, but we can use the PG_META_DB_URL env variable to define the connection string, like we would with GOTRUE_DB_DATABASE_URL or PGRST_DB_URI. Example:

PG_META_DB_URL: postgres://${POSTGRES_USER_META}:${POSTGRES__PASSWORD}@${POSTGRES__HOST}:${POSTGRES__PORT}/${POSTGRES__DB}?sslmode=verify-ca&sslrootcert=/certs/example.crt&sslcert=/certs/example.crt&sslkey=/example-rest.key

However, there is a mistake in the configuration for "rejectUnauthorized" (See):

config.ssl.rejectUnauthorized = sslmode !== 'no-verify'

Should be:

config.ssl.rejectUnauthorized = sslmode === 'verify-full'

According to the official docs, only the "verify-full" mode should verify that the CN (or SAN) of the cert used by pg-meta corresponds to the Postgres node's hostname.

I am not sure exactly how the "pg" client should be configured, does it just use "rejectUnauthorized: true" for "verify-full" and "rejectUnauthorized: false" for "verify-ca" and adds additional checks for everything except CN?

One thing is sure, Postgrest and Gotrue got this right as the exact same certificate with a custom CN that doesn't correspond to the hostname is accepted when using "verify-ca", but not when using "verify-full". So there is definitely a misconfiguration in pg-meta.

[soedirgo]

Hi, thanks for reporting this. The rejectUnauthorized issue should be fixed in https://github.com/supabase/postgres-meta/pull/709.

For PG_META_DB_SSL_ROOT_CERT, it actually accepts the contents of the cert, not the path.

[soedirgo]

Closing this for now, but feel free to create a new issue if it persists.