Postgres无法加载ssl证书

Question

我正在尝试使用自生成的SSL运行具有安全连接的postgres docker容器

我使用openssl {crt,key}创建了证书，并将它们映射到postgres的docker容器中。最初错误是

postgres  | 2023-04-08 11:15:25.669 UTC [1] FATAL:  private key file "/etc/ssl/postgres.key" has group or world access
postgres  | 2023-04-08 11:15:25.669 UTC [1] DETAIL:  File must have permissions u=rw (0600) or less if owned by the database user, or permissions u=rw,g=r (0640) or less if owned by root.
postgres  | 2023-04-08 11:15:25.669 UTC [1] LOG:  database system is shut down

修复权限后，我得到了这个错误，我找不到任何有效的解决方案。

postgres  | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres  | 
postgres  | 2023-04-08 11:16:12.762 UTC [1] FATAL:  could not load server certificate file "/etc/ssl/postgres.crt": SSL error code 2147483661
postgres  | 2023-04-08 11:16:12.769 UTC [1] LOG:  database system is shut down

证书是这样创建的:

openssl req -x509 
-sha256 -days 356 
-nodes 
-newkey rsa:4096 
-subj "/CN=$domain/C=US/L=$location" 
-keyout $name.key 
-out $name.crt

这是我的docker撰写文件。

version: "3"
services:
postgres:
container_name: postgres
image: postgres:15.2-alpine3.17
hostname: postgre
restart: on-failure:3
networks:
net:
ipv4_address: 173.19.0.10
ports:
- 5432:5432
environment:
- POSTGRES_USER=root
- POSTGRES_PASSWORD=somepassword
- POSTGRES_DB=system
- PGDATA=/var/lib/postgres/data
command: postgres -c ssl=on -c ssl_cert_file=/etc/ssl/postgres.crt -c ssl_key_file=/etc/ssl/postgres.key
volumes:
- /var/data/postgres:/var/lib/postgres/data
- /etc/config/certs:/etc/ssl
networks:
net:
driver: bridge
ipam:
config:
- subnet: 173.19.0.0/24
gateway: 173.19.0.1

Answer 1

终于!!在周末浪费了6个小时后，我解决了这个问题。结果发现它与

无关

1. 证书腐败
2. 行终止于^M windows - linux问题
3. 证书文件中的PEM文件格式
4. 文件位置

原来是权限问题。我使用了

chmod 640 postgres.*

是postgres日志消息中推荐的，这是非常不正确的。正确的权限是

chmod 400 postgres.* 

一旦做了这个更改，postgres容器就开始正常工作了。以下是日志

postgres  | PostgreSQL init process complete; ready for start up.
postgres  | 
postgres  | 2023-04-08 19:16:40.206 UTC [1] LOG:  starting PostgreSQL 15.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r4) 12.2.1 20220924, 64-bit
postgres  | 2023-04-08 19:16:40.207 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres  | 2023-04-08 19:16:40.207 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres  | 2023-04-08 19:16:40.208 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres  | 2023-04-08 19:16:40.216 UTC [52] LOG:  database system was shut down at 2023-04-08 19:16:40 UTC
postgres  | 2023-04-08 19:16:40.223 UTC [1] LOG:  database system is ready to accept connections

下面是psql 的验证

postgre:/#  psql "dbname=system host=localhost user=root password=somepassword port=5432 sslmode=require"
psql (15.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.