Add custom pg_hba.conf in postgres docker container?

Question

I want to expose 5432 port to be accessible externally, but before doing that I want to limit it to only specific IPs, so I want to do that via pg_hba.conf

If I use docker default settings, communication between containers, works fine (by specified networks).

But if I specify pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

host    all             all             all                     md5

And try to use this pg_hba.conf file (which is actually straight copy from container itself):

psycopg2.OperationalError: could not translate host name "db" to address: Temporary failure in name resolution (where db is postgres db service in docker-compose.yml).

My compose has this for postgres:

  other: # other service that connects to postgres using psycopg2
    networks:
      - backend
  db:
    image: postgres:13
    command: ["postgres", "-c", "config_file=/etc/postgresql.conf"]
    ports:
      - 5432:5432  # for external communications
    volumes:
      - db-data:/var/lib/postgresql/data
      - path/to/cfg:/etc/postgresql.conf
      - path/to/hba:/etc/postgresql/pg_hba.conf
  networks:
    - backend

  networks:
    backend:

And then in postgresql.conf I have edited this line:

hba_file = '/etc/postgresql/pg_hba.conf'

Also config has this set: listen_addresses = '*'

Inspecting docker network:

docker network inspect bridge 
[
    {
        "Name": "bridge",
        "Id": "fe4fe8b8eb4e0c06d43428eadd2bb3f44a2ac581fe55618b4c70c5c28c107b8d",
        "Created": "2022-11-26T09:34:03.461094956+02:00",
        "Scope": "local",
        "Driver": "bridge",
        "EnableIPv6": false,
        "IPAM": {
            "Driver": "default",
            "Options": null,
            "Config": [
                {
                    "Subnet": "172.17.0.0/16",
                    "Gateway": "172.17.0.1"
                }
            ]
        },
        "Internal": false,
        "Attachable": false,
        "Ingress": false,
        "ConfigFrom": {
            "Network": ""
        },
        "ConfigOnly": false,
        "Containers": {},
        "Options": {
            "com.docker.network.bridge.default_bridge": "true",
            "com.docker.network.bridge.enable_icc": "true",
            "com.docker.network.bridge.enable_ip_masquerade": "true",
            "com.docker.network.bridge.host_binding_ipv4": "0.0.0.0",
            "com.docker.network.bridge.name": "docker0",
            "com.docker.network.driver.mtu": "1500"
        },
        "Labels": {}
    }
]

P.S. I checked

PostgreSQL in Docker - pg_hba.conf to allow access from host to container

And How can I allow connections by specifying docker-compose host names in postgres's pg_hba.conf file?

But none seem to make any difference.

UPDATE:

I retried again, using same thing. Just specifying pg_hba.conf and now I don't get that error. Not sure how it was solved. But pg_hba.conf is picked up just fine and I can limit access with it.

Did you try `depends_on: - db` for the `other` service? https://docs.docker.com/compose/compose-file/compose-file-v3/#depends_on — arne, Jan 18 '23 at 19:54
Yes its there. But I don't think its related. Its only relevant when service is being loaded, so it would wait for dependency. But even when service is loaded fully, you can't connect to it, if I add `pg_hba.conf`. — Andrius, Jan 19 '23 at 07:42
@RichardRublev it does not have much. The only difference from default conf is just that I have some custom tunings (for postgres to better use machine resources) and that I specify `hba_file` to use custom one. — Andrius, Jan 19 '23 at 12:49
I created this gist that attempts to replicate the issue https://gist.github.com/Spalaiokostas/446873a9c6a1e535b226201f3ce47f1d but everything works as expected. Is there any other configuration that we are missing? — Spyros Palaiokostas, Jan 19 '23 at 15:11
Are you using the `network_mode: bridge` configuration on any of the service definitions in the docker compose file? — Spyros Palaiokostas, Jan 19 '23 at 17:49
@spyrospal I only use named networks without any options, like ```networks: backend: frontend: ``` And thats it. — Andrius, Jan 21 '23 at 14:59
Does simple `ping` works from any other container to the `db` one? — imy, Jan 23 '23 at 12:46

Slava Kuravsky · Answer 1 · 2023-01-20T08:07:55.147

1

Try to keep both config files on the default place (in /var/lib/postgresql/data) and revert the change for hba_file location. Make sure the files have the same permission and ownership as in container: 600 postgres:postgres (999:999)

EDIT:

Just looked in the docs. They start pg with custom config like this:

$ docker run -d --name some-postgres -v "$PWD/my-postgres.conf":/etc/postgresql/postgresql.conf -e POSTGRES_PASSWORD=mysecretpassword postgres -c 'config_file=/etc/postgresql/postgresql.conf'

where postgres before "-c" is the image name. I think you should redirect "-c" part to entrypoint.

EDIT2:

You're right, according to the entrypoint script this call with "postgres" as a parameter also works.

edited Jan 20 '23 at 08:07

answered Jan 19 '23 at 08:44

Slava Kuravsky

2,702
10
16

No, its a bad idea to put custom config files in data dir, because this one is used by another volume to have databases persist between containers recreation (`db-data:/var/lib/postgresql/data`). For this reason `postgresql.conf` is also moved outside and I use command to specify path to it. – Andrius Jan 19 '23 at 12:46
I see, there is not much what can go wrong here. I think you start pg container wrong, check my updated answer. – Slava Kuravsky Jan 19 '23 at 13:14
I dont think I start it wrong. I basically start it the same way. Its just I use docker compose to run it instead. – Andrius Jan 19 '23 at 14:48
In `command: ["postgres", "-c", "config_file=/etc/postgresql.conf"]` postgres is a part of command, but there is no command `postgres` - it's the image name. "-c" part goes to its entrypoint. – Slava Kuravsky Jan 19 '23 at 15:00
Just use `command: ["-c", "config_file=/etc/postgresql.conf"]` – Slava Kuravsky Jan 19 '23 at 15:04
Postgres starts either way. But I agree, its weird to use `postgres`. I picked it up from here: https://stackoverflow.com/a/49228881/1455384 to me it looked weird that `postgres` is used as "command", but it worked, so I left it..:) – Andrius Jan 19 '23 at 16:49
So you omitted creating a custom settings file on different location as I suggested and it worked? :) – Slava Kuravsky Jan 20 '23 at 07:26
No. Its working either way. – Andrius Jan 21 '23 at 14:56

score 0 · Answer 2 · answered Jan 19 '23 at 09:45

You need to add the POSTGRES_PASSWORD as an environment variable on the docker-compose.yml for the the db container, otherwise the postgres will failed with:

tmp-db-1     | Error: Database is uninitialized and superuser password is not specified.
tmp-db-1     |        You must specify POSTGRES_PASSWORD to a non-empty value for the
tmp-db-1     |        superuser. For example, "-e POSTGRES_PASSWORD=password" on "docker run".
tmp-db-1     |
tmp-db-1     |        You may also use "POSTGRES_HOST_AUTH_METHOD=trust" to allow all
tmp-db-1     |        connections without a password. This is *not* recommended.
tmp-db-1     |
tmp-db-1     |        See PostgreSQL documentation about "trust":
tmp-db-1     |        https://www.postgresql.org/docs/current/auth-trust.html

If it still fails could you share the logs of the db container?

I can use postgres and connect to it just fine (i pass needed credentials) if I dont use `pg_hba.conf`. I pass POSTGRES_PASSWORD (in prod I pass POSTGRES_PASSWORD_FILE) and it connects. — Andrius, Jan 19 '23 at 12:43

Add custom pg_hba.conf in postgres docker container?

2 Answers2