Portainer Stack / Postgres Upgrade

[patrickelam]

Describe the bug
I am trying to migrate from an unraid template authentik install that, until recently had been very stable to using a portainer stack. The old setup used beryju/authentik:latest and postgres 12, and I'm trying to move to the latest docker-compose file from the docs, and have spent a good 7-8 hours trying different combinations of things. However, I keep getting errors authenticating with the db.

To Reproduce
Steps to reproduce the behavior:

Starting fresh, I've copied the volume mounts used by the unraid-defined containers to a new location, then point the portainer stack mounts there. I used postgres 12 in the stack initially (since the db was created with 12), but the authentication error above was happening. I've dumped the db from the (unraid) pg12 container, used pg16 and imported the backup (as the docs suggest), same authentication error. I tried to change the password hashing to md5 and reset the password in the pg16 container, server container still can't authenticate. I validated that the password was indeed correct in the pg16 container by using psql -U authentik_reader -d authentik -W to force a password entry, and I have the right password.

There seem to be issues 1 2 using portainer, but conceptually, there shouldn't be. Instead of using the .env file, I added the environment variables AUTHENTIK_POSTGRESQL__HOST, AUTHENTIK_POSTGRESQL__USER, AUTHENTIK_POSTGRESQL__PASSWORD, etc to the server containers.

Expected behavior
The server/worker containers should connect to the database container using the password defined in the $AUTHENTIK_POSTGRESQL__PASSWORD env variable.

Logs

2025-01-06 06:00:41.486 UTC [98] FATAL:  password authentication failed for user "authentik_reader"

2025-01-06 06:00:41.486 UTC [98] DETAIL:  Password does not match for user "authentik_reader".

	Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 128: "host all all all md5"

Version and Deployment (please complete the following information):

• authentik version: latest
• Deployment: portainer stack (docker compose)

Additional context
I'm at a loss as to where to go from here. I don't want to not use portainer, as that is how I have everything else organized now. The database seems to have the correct password for the correct user. The server can connect to the db (networking-wise). The server has the correct user/pw set as environment variables. What am I missing? Thanks!

[patrickelam]

Also, is there any official position on md5 hashing vs scram-sha-256? I understand the later is better, and I wouldn't think it should matter... but at a certain point I figured I'd try changing the hashing of the pw in the pg16 container to md5 (since the pg12 db pw is md5 hashed) for kicks (obviously then reset the pw, etc), but that didn't help either.

[hanley-development]

Describe the bug I am trying to migrate from an unraid template authentik install that, until recently had been very stable to using a portainer stack. The old setup used beryju/authentik:latest and postgres 12, and I'm trying to move to the latest docker-compose file from the docs, and have spent a good 7-8 hours trying different combinations of things. However, I keep getting errors authenticating with the db.

To Reproduce Steps to reproduce the behavior:

Starting fresh, I've copied the volume mounts used by the unraid-defined containers to a new location, then point the portainer stack mounts there. I used postgres 12 in the stack initially (since the db was created with 12), but the authentication error above was happening. I've dumped the db from the (unraid) pg12 container, used pg16 and imported the backup (as the docs suggest), same authentication error. I tried to change the password hashing to md5 and reset the password in the pg16 container, server container still can't authenticate. I validated that the password was indeed correct in the pg16 container by using psql -U authentik_reader -d authentik -W to force a password entry, and I have the right password.

There seem to be issues 1 2 using portainer, but conceptually, there shouldn't be. Instead of using the .env file, I added the environment variables AUTHENTIK_POSTGRESQL__HOST, AUTHENTIK_POSTGRESQL__USER, AUTHENTIK_POSTGRESQL__PASSWORD, etc to the server containers.

Expected behavior The server/worker containers should connect to the database container using the password defined in the $AUTHENTIK_POSTGRESQL__PASSWORD env variable.

Logs

2025-01-06 06:00:41.486 UTC [98] FATAL:  password authentication failed for user "authentik_reader"

2025-01-06 06:00:41.486 UTC [98] DETAIL:  Password does not match for user "authentik_reader".

	Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 128: "host all all all md5"

Version and Deployment (please complete the following information):

• authentik version: latest
• Deployment: portainer stack (docker compose)

Additional context I'm at a loss as to where to go from here. I don't want to not use portainer, as that is how I have everything else organized now. The database seems to have the correct password for the correct user. The server can connect to the db (networking-wise). The server has the correct user/pw set as environment variables. What am I missing? Thanks!

I use the stack.env for these. I eventually would like to move them to security but for now since portainer doesnt use the normal .env file:

services:
  postgresql:
    image: docker.io/library/postgres:16-alpine
    container_name: authentik_postgresql
    restart: unless-stopped
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -d $${POSTGRES_DB} -U $${POSTGRES_USER}"]
      start_period: 20s
      interval: 30s
      retries: 5
      timeout: 5s
    volumes:
      - database:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: ${PG_PASS}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_DB: ${POSTGRES_DB}
    env_file:
      - stack.env

[patrickelam]

I've tried both referencing the the stack.env file (how portainer references their variables) as well as hard-coding the values. Both ways seem to inject the environment variables fine, as I can exec into the container and echo the variables. In the db container, I can echo $POSTGRES_PASSWORD and in the server and worker containers, I can echo $AUTHENTIK_POSTGRESQL__PASSWORD and both times I can get the same password. In the db container, I can manually log in with psql -U authentik_reader -d authentik -W and then entering the password. Everything seems to be working, except I still get the Password does not match for user "authentik_reader" error, even though, as far as I can tell, every step in the process has the same password.

[rissson]

$POSTGRES_PASSWORD is usually only used on first startup. You may need to log in as postgres to the database and change the user password to make sure it's the correct one: https://www.postgresql.org/docs/8.0/sql-alteruser.html

[patrickelam]

When I spin up the postgres 16 container for the first time in order to do the upgrade as the docs say to do, it initializes the db and user correctly, as far as I can tell (though it's hashing via scram-sha, and not md5 by default). To test there isn't a rule letting me login because I'm "local" in the pg_hba.conf file, I spun up a second postgres:16-alpine container and could sucessfully connect to the other container psql -U authentik_reader -h authentik_postgres -d authentik. No problem.

BUT when I spin up the stack (or the last step in the above doc page), I still get the following error: password authentication failed for user "authentik_reader", even though a successful connection JUST WORKED from a different postgres client (again, with the same pw). This makes me wonder if there is something wonky happening with how the authentik server connects to the db, or at least that it behaves differently than psql does?