PostgreSQL in SSL mode returns 'server does not support SSL, but SSL was required'

Hi Hope you're doing great!

I a trying to enable SSL communications between a Postgres docker container and a FastAPI container, however as you can tell from the title, something is not working :/

I read the documentation and supposedly I have set up everything accordingly. For instance:

1. I created the root CA, and then a key and signed cert for the postgres server with the root CA.

2. I have configured the postgresql.conf file in this manner:

... defaults

# - SSL -

ssl = on
ssl_ca_file = '/var/lib/postgresql/data/root.crt'
ssl_cert_file = '/var/lib/postgresql/data/server.crt'
ssl_key_file = '/var/lib/postgresql/data/server.key'
#ssl_crl_file = ''
#ssl_crl_dir = ''
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_min_protocol_version = 'TLSv1.2'
#ssl_max_protocol_version = ''
#ssl_dh_params_file = ''
#ssl_passphrase_command = ''
#ssl_passphrase_command_supports_reload = off

... defaults

This is what my tree looks like:

.
├── docker-compose.yaml
├── Dockerfile
├── main.py
├── postgresql.conf
├── README.md
├── ssl-client
│   ├── root.crt
│   ├── root.csr
│   ├── root.key
│   ├── server.crt
│   ├── server.csr
│   └── server.key
└── ssl-server
    ├── pgdata  [error opening dir]   -> because it is using its own user (999)
    ├── root.crt
    ├── root.csr
    ├── root.key
    ├── server.crt
    ├── server.csr
    └── server.key

and this is how I am passing everything to the container:

version: '3.8'
services:
  app:
    container_name: python-app
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - '8000:80'
    depends_on:
      - postgres
    networks:
      - postgres-ssl

  postgres:
    container_name: postgres
    image: postgres:latest
    environment:
      POSTGRES_USER: username
      POSTGRES_PASSWORD: password
      POSTGRES_DB: database
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - ./postgresql.conf:/etc/postgresql/postgresql.conf
      - ./ssl-server:/var/lib/postgresql/data/
    ports:
      - '5432:5432'
    networks:
      - postgres-ssl

networks:
  postgres-ssl:
    name: postgres-ssl

Lastly, here is my python code, although everything is working as expected here, as I am getting the error above:

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from typing import List

app = FastAPI()

DATABASE_URL = "postgresql+psycopg2://username:password@postgres/database?sslmode=require"

engine = create_engine(DATABASE_URL)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


class Student(Base):
    __tablename__ = 'students'

    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String, nullable=False)

class StudentRequest(BaseModel):
    name: str

    class Config:
        orm_mode = True

class StudentCreate(BaseModel):
   name :str

@app.post("/items/", response_model=StudentRequest)
def create_item(item: StudentCreate):
    db = SessionLocal()
    db_item = Student(**item.dict())
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    db.close()
    return db_item

@app.get("/items/", response_model=List[StudentRequest])
def get_items():
    db = SessionLocal()
    items = db.query(Student).all()
    db.close()
    return items

@app.get("/")
async def read_root():
    return {"message": "Hello, FastAPI with PostgreSQL using SSL!"}

If I try to 'override' the default configuration file by mounting it in the same PGDATA path, I get an error:

Here is the docker-compose for this case:

version: '3.8'
services:
  app:
    container_name: python-app
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - '8000:80'
    depends_on:
      - postgres
    networks:
      - postgres-ssl

  postgres:
    container_name: postgres
    image: postgres:latest
    environment:
      POSTGRES_USER: username
      POSTGRES_PASSWORD: password
      POSTGRES_DB: database
      PGDATA: /etc/postgresql/data
    volumes:
      - ./postgresql.conf:/etc/postgresql/data/postgresql.conf
      - ./ssl-server:/etc/postgresql/data/ssl-server
    ports:
      - '5432:5432'
    networks:
      - postgres-ssl

networks:
  postgres-ssl:
    name: postgres-ssl

And the error I get at startup:

postgres  | The files belonging to this database system will be owned by user "postgres".
postgres  | This user must also own the server process.
postgres  | 
postgres  | 
postgres  | The database cluster will be initialized with locale "en_US.utf8".
postgres  | The default database encoding has accordingly been set to "UTF8".
postgres  | The default text search configuration will be set to "english".
postgres  | 
postgres  | Data page checksums are disabled.
postgres  | 
postgres  | 
postgres  | initdb: error: directory "/etc/postgresql/data" exists but is not empty
postgres  | initdb: hint: If you want to create a new database system, either remove or empty the directory "/etc/postgresql/data" or run initdb with an argument other than "/etc/postgresql/data".
postgres exited with code 1

Am I missing something here? How come this is not setting up SSL connections? :/

I am very confused honestly, if you could help me out here, it would be very, very much appreciated!

Thank you in advance and have a wonderful day!

If PGDATA is set to /var/lib/postgresql/data/pgdata, then putting your custom config file in /etc/postgresql/ isn't going to do anything. It will just ignore it and use the default.

Furthermore, the certs and stuff need to be inside PGDATA, not siblings to it.