Do multiple postgres server require multiple TLS certificats or one?

jhoelzel commented 11 months ago

Applies To

Selfhosted postgres docs, but i suppose others too

Details

I would like to see a mention for multiple database INSTANCES. We are testing teleport right now for our org and would like to find out if multiple servers require multiple certs, or one cert with multiple hostnames.

How will we know this is resolved?

It would be amazing to add a little note to the self hosted docs that make this clear

Related Issues

i serach issues and discussions and could not find a mention. if i overlooked it im sorry.

Sidenode: Thanks for an awesome product

jhoelzel commented 11 months ago

bump

greedy52 commented 9 months ago

would like to find out if multiple servers require multiple certs, or one cert with multiple hostnames.

Either would work but it's recommended to use separated TLS certs for different instances/hostnames.

jhoelzel commented 9 months ago

would like to find out if multiple servers require multiple certs, or one cert with multiple hostnames.

Either would work but it's recommended to use separated TLS certs for different instances/hostnames.

i tried that and i would always only be able to connect to one of the two instances i have provisioned. It would return a certificate error for the other

greedy52 commented 9 months ago

i tried that and i would always only be able to connect to one of the two instances i have provisioned. It would return a certificate error for the other

Hmm.. what error were you getting? By default, the Database Service will verify both the CA and the server name so the server cert must be signed with all hostnames of the instances, in order to work. Alternatively, you can change the verification mode (usually not recommended):

# Lists statically registered databases proxied by this agent.
  databases:
    # Name of the database proxy instance, used to reference in CLI.
  - name: "prod"
    protocol: "postgres"

    # Database connection endpoint. Must be reachable from Database Service.
    uri: "postgres.example.com:5432"

    # Optional TLS configuration.
    tls:
      # TLS verification mode. Valid options are:
      # 'verify-full' - performs full certificate validation (default).
      # 'verify-ca' - the same as `verify-full`, but skips the server name validation.
      # 'insecure' - accepts any certificate provided by database (not recommended).
      mode: verify-full

https://goteleport.com/docs/database-access/reference/configuration/

But in general, it's recommended that each instance serves its own cert.

jhoelzel commented 9 months ago

well the problem starts on the pgadmin end, it simply would not recognize my cert. My environment is two postgres clusters setup with two zalando postgres operators in different namespaces.

I am providing both tls certificates in different secrets, yet i was never able to connect using pgadmin in the end.

Therefore i reverted to use one cert for both clusters

jhoelzel commented 7 months ago

@zmb3 do you care to comment on how this is not planned please? Does that mean its not implemented in OSS not planned at all or simply not planned to update the docs?

greedy52 commented 7 months ago

well the problem starts on the pgadmin end, it simply would not recognize my cert. My environment is two postgres clusters setup with two zalando postgres operators in different namespaces.

I am providing both tls certificates in different secrets, yet i was never able to connect using pgadmin in the end.

Therefore i reverted to use one cert for both clusters

Sorry, I am a little confused about the setup now. Does pgadmin access the database, through Teleport or directly?

BTW, you can always use your own self-signed TLS cert for your databases, instead of tctl auth sign. In this case, for mTLS between Teleport and your database:

On database side, just append Teleport's CA to the list that database needs to trust (aka ssl_ca_file). To export Teleport's database CA:
```
$ tctl auth export --type db-client
```
(Use db instead of db-client for older versions that don't have a separate db-client CA yet)

Your database config may look something like this:

ssl = on
ssl_cert_file = '/path/to/your-self-signed-server-cert.crt'
ssl_key_file = '/path/to/your-self-signed-server-key.key'
ssl_ca_file = '/path/to/your-self-signed-server-ca-plus-teleport-ca.cas'

On Teleport side, configure Teleport to trust your self-hosted CA

# Lists statically registered databases proxied by this agent.
databases:
# Name of the database proxy instance, used to reference in CLI.
- name: "prod"
protocol: "postgres"

# Database connection endpoint. Must be reachable from Database Service.
uri: "postgres.example.com:5432"

# Optional TLS configuration.
tls:
  # TLS verification mode. 
  mode: verify-full
  # Optional path to the CA used to validate the database certificate.
  ca_cert_file: /path/to/your-CA

As for the documentation, we usually want to limit the main guide to a single/simple scenario so it's easy to follow. You are welcome to discuss your scenario here or better start a discussion thread.

zmb3 commented 7 months ago

I closed this not planned as it looks more like a question than a specific action item and has been open for months.

GitHub issues are not a great medium for questions - there are simply too many of them. GitHub discussions or the community slack workspace are a better place to ask about how to configure or deploy teleport.

jhoelzel commented 7 months ago

first of all, thank you two for your help!

What am i trying to do? I have dynamic feature environments, staging and production database servers using zalando postgres on k8s. I would like for the devs to be able to login using teleport using a user called "maintenance" in order to not have to create users in postgres for every dev. Since all queries are logged in teleport (which is amazing) we tick most compliance boxes. But basically i can only have 3 out of all users have access to prod because prod.

Henceforth i setup my certs like this:

tctl auth sign --format=db --host=postgres-cluster.postgres-production.svc,postgres-cluster,postgres-cluster.postgres-production,postgres-cluster.postgres-production.svc.cluster.local --out=server --ttl=2190h

and

tctl auth sign --format=db --host=*.postgres-preprod.svc,*.postgres-preprod,*.postgres-preprod.svc.cluster.local --out=server --ttl=26280h

and

tctl auth sign --format=db --host=*.postgres-staging.svc,*.postgres-staging,*.postgres-staging.svc.cluster.local --out=server --ttl=26280h

(there are multiple staging/preprods and only one prod)

that part works without issue and i get 4 separate cert files i upload to my secret manager.

After that i configure postgres to use the TLS secret for itself in order to login users through teleport:

  tls:
    secretName: "postgres-preprod-tls"
    certificateFile: "SERVER_CRT"
    privateKeyFile: "SERVER_KEY"
    caFile: "SERVER_CAS"

which works fine too unless i have multiple different certificates going.

If i try logging in through a cert though (NOT password auth) usually only one of them works and the other one return an error along the lines of "this cert is not valid"

my teleport helm chart contains this:

        databases:
          - name: production
            uri: "postgres-cluster.postgres-production.svc:5432"
            protocol: "postgres"
            static_labels:
              env: "prod"
          - name: staging
            uri: "postgres-cluster.postgres-staging.svc:5432"
            protocol: "postgres"
            static_labels:
              env: "staging"
          - name: preprod
            uri: "postgres-cluster.postgres-preprod.svc:5432"
            protocol: "postgres"
            static_labels:
              env: "training"
-- other preprod and staging env ommited for brevity--

So far i have only gotten it to work when i include everything in a single cert like this:

tctl auth sign --format=db --host=postgres-cluster.postgres-staging.svc,postgres-cluster,postgres-cluster.postgres-staging,postgres-cluster.postgres-statging.svc.cluster.local,postgres-cluster.postgres-production.svc,postgres-cluster.postgres-production,postgres-cluster.postgres-production.svc.cluster.local,*.postgres-feature.svc,*.postgres-feature,*.postgres-feature.svc.cluster.local --out=server --ttl=26280h

and that also has no real issues other than the fact that its insecure.

Maybe its also me that is overthinking this? My idea was that sombody having a login for a teleport user for staging, which will be the same on other environments, could theoretically simply reuse his cert because its valid for all? Given the fact of course that the user has also acces to k8s through teleport.

Whats my problem: After setting up everything like above and mounting the correct certs into the postgres instances i cant login using pgadmin. Well i can on one instance the other ones will tell me that ther cert used is not for them.

so basically my questions boil down to this:

am i overthinking this?
should teleport not be able to tell them apart using theìr hostname?
should the server CA not be the same anyway? will this even make a difference?

@zmb3 If you like you can gladly rename this issue to "Unable to login with multiple postgres instances" but yeah i formulated this as a quick question because i still believe its me thats making a mistake and having a "bug report" to update the docs would have benefited both parties :D

greedy52 commented 7 months ago

If i try logging in through a cert though (NOT password auth) usually only one of them works and the other one return an error along the lines of "this cert is not valid"

How are you connecting from pgadmin with the cert login? Do you use tsh?

Also want to confirm that, when you are doing tctl auth sign for each environement separately, did you use different --out=server_<env> so that there should be 3 different sets of certs (like one set for each target postgres server)?

jhoelzel commented 7 months ago

thank you for your help! I did it in 3 separate folders so that i would have 3 different cert "packages" which i store with the other secrets.

i am using TSH and also the Teleport connect client on a windows machine. I can make connections by using the provisioned user and leaving the password in pg admin blank. I can also set the dbs for applications for debugging, but i can not do that when im actually having different certs

greedy52 commented 7 months ago

Could you enable debug log on the Teleport Database service? What errors do you see on the client side and the Database Service side? Does tsh db connect with psql work?

I would also double-check that each Postgres server is only serving the TLS cert that signed with their hostname. You can check this on the Postgres config side or use a newer openssl on a machine that can reach the Postgres servers:

$ openssl s_client -starttls postgres -connect EXAMPLE.COM:5432 -showcerts

jhoelzel commented 7 months ago

Thank you again,

i will setup a testcluster next week and report back with everything end to end, is there anything else i can provide?

gravitational / teleport