PostgreSQL and Docker

Configuration
1

Hey guys,
I’ve some how messed up my PostgresSQL Installation and I cant get it working and as i’m using Docker I haven’t really found any guides useful.

The problem i’m having is docker is not creating any user or database when it creates, without any user I cant log in to SQL in the Docker image and there is the security in the docker container that doesnt let any postgresSQL command run as root so I cant seem to fix it by manually creating the user that way.

I’ve tried cleaning up and removing the image/container and manually tracking down and deleting any leftover volume/shared folder but it still wont create the user on the creation of a new container.

Also is it weird when I run Docker System prune -a and it deletes all the stopped PostgreSQL stuff that it leaves the working folder (home/hass/svr/docker/postgresSQL) still there? Currently I have to take ownership of it and delete it manually

My Docker compose Config
postdb1:
container_name: postdb1
restart: unless-stopped
image: postgres
volumes:
- /home/hass/svr/docker/postgresql:/var/lib/postgresql/data
- /etc/localtime:/etc/localtime:ro
environment:
- POSTGRES_DB=‘homeassistant’
- POSTGRES_USER=‘hass’
- POSTGRES_PASSWORD=‘XXXXXX’
ports:
- “5432:5432”

My Hass Config
recorder:
purge_keep_days: 5
db_url: postgresql://hass:[email protected]:5432/homeassistant

2

sounds like a permissions issue on your mapped volume.

what do the logs of the container tell you?

3

Sorry, im not sure how to check docker logs ill google it and check once i’ve finished dad duties.
I removed everything and did a fresh build of postgresql and there is nothing in the portainer event log about any build issues for it

Postgresql error

postdb1 | 2018-11-28 04:01:42.423 UTC [67] FATAL: password authentication failed for user “hass”
postdb1 | 2018-11-28 04:01:42.423 UTC [67] DETAIL: Role “hass” does not exist.
postdb1 | Connection matched pg_hba.conf line 95: “host all all all md5”

4

cat /home/hass/svr/docker/postgresql/pg_hba.conf

The error states host all all all md5, but I don’t think “md5” is supposed to be there.

nano /home/hass/svr/docker/postgresql/postgresql.conf
make sure listen_addresses = '*' is set

ls -la /home/hass/svr/docker/postgresql and tell me what the permissions say, and who the owner/group are

5

First off, thanks for writing the command/statements thats really helpful as im not fulent in linux and have to google everything.

I have to sudo all those commands to get them to run, not sure if thats a concern or not.

cat /home/hass/svr/docker/postgresql/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

nano /home/hass/svr/docker/postgresql/postgresql.conf does have listen_addresses = ‘*’ set correctly and its not commented out.

and finally sudo ls -la /home/hass/svr/docker/postgresql

total 132
drwx------ 19 lxd  root    4096 Nov 28 06:14 .
drwxr-xr-x  9 root root    4096 Nov 28 04:01 ..
drwx------  6 lxd  nobody  4096 Nov 28 04:01 base
drwx------  2 lxd  nobody  4096 Nov 28 04:02 global
drwx------  2 lxd  nobody  4096 Nov 28 04:01 pg_commit_ts
drwx------  2 lxd  nobody  4096 Nov 28 04:01 pg_dynshmem
-rw-------  1 lxd  nobody  4535 Nov 28 04:01 pg_hba.conf
-rw-------  1 lxd  nobody  1636 Nov 28 04:01 pg_ident.conf
drwx------  4 lxd  nobody  4096 Nov 28 04:06 pg_logical
drwx------  4 lxd  nobody  4096 Nov 28 04:01 pg_multixact
drwx------  2 lxd  nobody  4096 Nov 28 04:01 pg_notify
drwx------  2 lxd  nobody  4096 Nov 28 04:01 pg_replslot
drwx------  2 lxd  nobody  4096 Nov 28 04:01 pg_serial
drwx------  2 lxd  nobody  4096 Nov 28 04:01 pg_snapshots
drwx------  2 lxd  nobody  4096 Nov 28 04:01 pg_stat
drwx------  2 lxd  nobody  4096 Nov 28 06:14 pg_stat_tmp
drwx------  2 lxd  nobody  4096 Nov 28 04:01 pg_subtrans
drwx------  2 lxd  nobody  4096 Nov 28 04:01 pg_tblspc
drwx------  2 lxd  nobody  4096 Nov 28 04:01 pg_twophase
-rw-------  1 lxd  nobody     3 Nov 28 04:01 PG_VERSION
drwx------  3 lxd  nobody  4096 Nov 28 04:01 pg_wal
drwx------  2 lxd  nobody  4096 Nov 28 04:01 pg_xact
-rw-------  1 lxd  nobody    88 Nov 28 04:01 postgresql.auto.conf
-rw-------  1 lxd  nobody 23750 Nov 28 04:01 postgresql.conf
-rw-r--r--  1 root root    1024 Nov 28 06:14 .postgresql.conf.swp
-rw-------  1 lxd  nobody    36 Nov 28 04:01 postmaster.opts
-rw-------  1 lxd  nobody    94 Nov 28 04:01 postmaster.pid

I can use the below statement to get edit rights to the volume but I dont want to jump the gun if its not the right thing to do
sudo chown -R hass:hass /home/hass/svr/docker/postgresql

6

try it and see, but I don’t think that is the answer

7

Hey all,
figured I should update this, if anyone else encounters this problem.
Sorry to day I gave up, in the end I did a fresh build again and installed mysql this time and its all working fine.
Maybe its was related to enviroment varibles in docker only getting set on the first time its built, or something else I have no idea.

Thanks again Flamingm0e, and for putting up with all questions going from raspbian to a NUC/Ubuntu/Docker install is definitely challenging. I thought I would be able to write a guide at the end of it but I haven had reproduce-able results and haven’t got everything working the way I want yet.

1 Like
8

@flamingm0e is the man. Thanks for keeping up with me as I am also migrating from a raspberry to a NUC.

9

so I am having exactly the same problem as @FletchNZ I installed postgresql using docker container, once installed I cannot access the directory /srv/docker/postgres. So I changed the owner to root for postgres and I still cannot access the folder. So I decided to chmod -R 757 postgres and then I was able to enter the postgres directory.

However after installing psotgres I cannot access my home assistant, glances, tasmota admin.
So I decided to delete the postgres directory and from portainer delete the container and image etc. Done a restart and I still cannot access HA etc. Checked the /srv/docker directory and I see postgres back again with the same ownership and accessing issues as described above. Therefore I reinstalled postgres using docker compose hoping that it would work and get HA etc back running but to no avail.

here is me before doing any changes :

bachoo786@nuc:/srv/docker$ ls -l
total 36
-rw-rw-rw-  1 root      root      3038 Dec 15 16:33 docker-compose.yaml
drwxr-xr-x  2 bachoo786 bachoo786 4096 Dec 12 23:11 duckdns
drwxr-xr-x  2 root      root      4096 Dec 15 16:09 glances
drwxrwxrwx 10 root      root      4096 Dec 15 16:34 homeassistant
drwxr-xr-x 11 bachoo786 bachoo786 4096 Dec 13 22:16 letsencrypt
drwxr-xr-x  5 nobody    root      4096 Dec 10 17:34 mosquitto
drwxrwxrwx  4 root      root      4096 Dec 10 00:46 portainer
drwx------ 19       999 root      4096 Dec 15 16:34 postgres
drwxr-xr-x  4 root      root      4096 Dec 14 16:03 tasmoadmin

and this is after allowing root to become the owner of postgres:

bachoo786@nuc:/srv/docker$ sudo chown root:root /srv/docker/postgres
bachoo786@nuc:/srv/docker$ ls -l
total 36
-rw-rw-rw-  1 root      root      3038 Dec 15 16:33 docker-compose.yaml
drwxr-xr-x  2 bachoo786 bachoo786 4096 Dec 12 23:11 duckdns
drwxr-xr-x  2 root      root      4096 Dec 15 16:09 glances
drwxrwxrwx 10 root      root      4096 Dec 15 16:37 homeassistant
drwxr-xr-x 11 bachoo786 bachoo786 4096 Dec 13 22:16 letsencrypt
drwxr-xr-x  5 nobody    root      4096 Dec 10 17:34 mosquitto
drwxrwxrwx  4 root      root      4096 Dec 10 00:46 portainer
drwx------ 19 root      root      4096 Dec 15 16:34 postgres
drwxr-xr-x  4 root      root      4096 Dec 14 16:03 tasmoadmin

Here is my docker compose file:

  postgres:
    container_name: postgres
    image: postgres
    restart: unless-stopped
#    network_mode: host
    volumes:
    - /srv/docker/postgres:/var/lib/postgresql/data
    - /etc/localtime:/etc/localtime:ro
    environment:
    - POSTGRES_DB=homeassistant
    - POSTGRES_USER=hass
    - POSTGRES_PASSWORD=XXX

So I have been reading up this thread and see what @flamingm0e said. In my case the log from pg_hba.conf is the following:

 # 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

I think its to do with ownership. When I checked the permissions for the postgres directory I have 999. Here it is:

bachoo786@nuc:/srv/docker/postgres$ ls -l
total 116
drwxr-xrwx 6 999 docker  4096 Dec 15 16:14 base
drwxr-xrwx 2 999 docker  4096 Dec 15 16:15 global
drwxr-xrwx 2 999 docker  4096 Dec 15 16:14 pg_commit_ts
drwxr-xrwx 2 999 docker  4096 Dec 15 16:14 pg_dynshmem
-rwxr-xrwx 1 999 docker  4535 Dec 15 16:14 pg_hba.conf
-rwxr-xrwx 1 999 docker  1636 Dec 15 16:14 pg_ident.conf
drwxr-xrwx 4 999 docker  4096 Dec 15 16:15 pg_logical
drwxr-xrwx 4 999 docker  4096 Dec 15 16:14 pg_multixact
drwxr-xrwx 2 999 docker  4096 Dec 15 16:14 pg_notify
drwxr-xrwx 2 999 docker  4096 Dec 15 16:14 pg_replslot
drwxr-xrwx 2 999 docker  4096 Dec 15 16:14 pg_serial
drwxr-xrwx 2 999 docker  4096 Dec 15 16:14 pg_snapshots
drwxr-xrwx 2 999 docker  4096 Dec 15 16:15 pg_stat
drwxr-xrwx 2 999 docker  4096 Dec 15 16:15 pg_stat_tmp
drwxr-xrwx 2 999 docker  4096 Dec 15 16:14 pg_subtrans
drwxr-xrwx 2 999 docker  4096 Dec 15 16:14 pg_tblspc
drwxr-xrwx 2 999 docker  4096 Dec 15 16:14 pg_twophase
-rwxr-xrwx 1 999 docker     3 Dec 15 16:14 PG_VERSION
drwxr-xrwx 3 999 docker  4096 Dec 15 16:14 pg_wal
drwxr-xrwx 2 999 docker  4096 Dec 15 16:14 pg_xact
-rwxr-xrwx 1 999 docker    88 Dec 15 16:14 postgresql.auto.conf
-rwxr-xrwx 1 999 docker 23750 Dec 15 16:14 postgresql.conf
-rwxr-xrwx 1 999 docker    36 Dec 15 16:14 postmaster.opts

Can anyone please help me? @flamingm0e

10

This has messed everything up bloody hell cannot access all my domains that I created for tasmota, ha, glances etc and on top of that mosquitto doesnt work either.

@FletchNZ did you remove docker altogether and done everything again or did you have to reinstall yous OS ?

11

HA has nothing to do with the database container. If you set up HA to use the database and the database is broken, then either fix the database or remove the connection to the database from your config.

Running docker containers in no way affects the base os. You don’t need to reinstall your OS if a docker container goes haywire

12

I completely formatted my drive and reinstalled the OS (Ubuntu18), it may not have been necessary but I has messed around with so many things I preferred to get rid of it all an start fresh.

Really sorry you getting it, it was a pain in the Arse. I have no idea why the environment variables user/pass aren’t getting passed through.

13

That’s alright buddy.

How did you install mysql using docker compose?

14

Have a look at the official documentation on mariadb docker

https://hub.docker.com/_/mariadb/

1 Like
15

I was seeing the same FATAL: password authentication failed for user when moving to a postgres database. Here’s what I did to troubleshoot and resolve it, hopefully it will help someone else.

I made sure to stop and remove the postgres image (either docker-compose down or docker-compose stop sql && docker-compose rm sql

  • Make sure to stop the services and remove the postgresql data directory (the one containing pg_hba.conf).

  • Check home-assistant’s configuration.yaml

    recorder: 
  db_url: "postgresql://postgres:password@sql/hass"

  • Check docker-compose.yaml

    sql:
  container_name: sql
  image: postgres
  volumes:
    - /etc/localtime:/etc/localtime:ro
    - sql/data:/var/lib/postgresql/data
    - sql/etc/postgresql:/etc/postgresql
    - sql//var/log/postgresql:/var/log/postgresql
  environment:
    - "POSTGRES_USER=postgres"
    - "POSTGRES_PASSWORD=password"
    - "POSTGRES_DB=hass"
  networks:
    - backend

home-assistant:
  ports:
    - "8123:8123"
  networks:
    - frontend
    - backend
...

To do some manual troubleshooting:

  • Connect to home-assistant container: docker exec -it home-assistant /bin/bash
  • Install psql client: apt update && apt install -y postgresql-client and
  • Try to connect to the database manually: psql -U postgres -h sql -d hass. You should be prompted for a password.
  • Check the database is created and events are being logged
    root@4f7a862c8b0e:/# psql -U postgres -d hass
psql (11.3 (Debian 11.3-1.pgdg90+1))
Type "help" for help.

hass=# \l
                               List of databases
 Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 hass      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres\
(4 rows)

hass=# \c hass
You are now connected to database "hass" as user "postgres".
hass=# select * from events limit 10;
...
1 Like