x509 TLS Ceritification

• Required Files
  • Development
    • Server Certification
    • Client Certification
  • Production

Required files

For development:

Server Certification

postgres.key

openssl genrsa -out postgres.key 4096

postgres.csr

openssl req -new -key postgres.key -out postgres.csr -subj "/C=GB/ST=Dev/L=Local/O=techexpo_crm/OU=dev_postgres/CN=localhost"

server-ext.cnf

# server-ext.cnf

authorityKeyIdentifier = keyid, issuer
basicConstraints = CA:FALSE
keyUsage = digitalSignature, keyEncipherment
extendedKeyUsage = serverAuth
subjectAltName = @alt_names

[alt_names]
DNS.1 = techexpo-postgres
DNS.2 = localhost
IP.1 = 127.0.0.1

postgres.crt

openssl x509 -req -in postgres.csr -CA ../root-ca.crt -CAkey ../root-ca.key -CAcreateserial -out postgres.crt -days 365 -sha256 -extfile server-ext.cnf

Client Certification

postgres-dbeaver-<USERNAME>.key

The client in this example is DBeaver database manager
⚠️ Replace <USERNAME> with the respective postgres username/s

openssl genrsa -out postgres-dbeaver-<USERNAME>.key 4096

postgres-dbeaver-<USERNAME>.csr

⚠️ Important: Postgres utilizes the CN field in mTLS to match against the username attempting to log-on to its service.
⚠️ Replace <USERNAME> with the respective postgres username/s

openssl req -new -key postgres-dbeaver-<USERNAME>.key -out postgres-dbeaver-<USERNAME>.csr -subj "/C=GB/ST=Dev/L=Local/O=techexpo_crm/OU=dev_postgres/CN=<USERNAME>"

postgres-dbeaver-<USERNAME>.crt

openssl x509 -req -in postgres-dbeaver-<USERNAME>.csr -CA ../root-ca.crt -CAkey ../root-ca.key -CAcreateserial -out postgres-dbeaver-<USERNAME>.crt -days 365 -sha256 -extfile ../client-ext.cnf

For production:

Refer to Production Certification