Postgres logs spammed with: role "root" does not exist

[VitalyVaryvdin]

Hello,

I've successfully deployed Zitadel with docker-compose. Noticed postgres logs spamming `role "root" does not exist" every 10 seconds

This is how it looks:

db-1       | 2024-04-23 08:03:00.480 UTC [553] LOG:  statement: begin read only
db-1       | 2024-04-23 08:03:00.496 UTC [553] LOG:  execute stmtcache_01c410bd0caf58e6d8ed40669b2b414235547cdef24452e9: SELECT created_at, event_type, "sequence", "position", payload, creator, "owner", instance_id, aggregate_type, aggregate_id, revision FROM eventstore.events2 WHERE instance_id = $1 AND ((aggregate_type = $2 AND event_type = ANY($3)) OR (aggregate_type = $4 AND event_type = $5) OR (aggregate_type = $6 AND event_type = $7)) AND "position" > $8 AND "position" < (SELECT COALESCE(EXTRACT(EPOCH FROM min(xact_start)), EXTRACT(EPOCH FROM now())) FROM pg_stat_activity WHERE datname = current_database() AND application_name = 'zitadel_es_pusher' AND state <> 'idle') ORDER BY "position", in_tx_order LIMIT $9 OFFSET $10
db-1       | 2024-04-23 08:03:00.496 UTC [553] DETAIL:  parameters: $1 = '261283497950314499', $2 = 'project', $3 = '{project.added,project.changed,project.deactivated,project.reactivated,project.removed}', $4 = 'org', $5 = 'org.removed', $6 = 'instance', $7 = 'instance.removed', $8 = '1713177517.2672336', $9 = '200', $10 = '1'
db-1       | 2024-04-23 08:03:00.501 UTC [553] LOG:  statement: commit
db-1       | 2024-04-23 08:03:00.502 UTC [552] LOG:  execute stmtcache_a614765bc81cd263e6301614a7396ccd287ac629e218f5ed: INSERT INTO projections.current_states (
db-1       | 	    projection_name
db-1       | 	    , instance_id
db-1       | 	    , aggregate_id
db-1       | 	    , aggregate_type
db-1       | 	    , "sequence"
db-1       | 	    , event_date
db-1       | 	    , "position"
db-1       | 	    , last_updated
db-1       | 	    , filter_offset
db-1       | 	) VALUES (
db-1       | 	    $1
db-1       | 	    , $2
db-1       | 	    , $3
db-1       | 	    , $4
db-1       | 	    , $5
db-1       | 	    , $6
db-1       | 	    , $7
db-1       | 	    , now()
db-1       | 	    , $8
db-1       | 	) ON CONFLICT (
db-1       | 	    projection_name
db-1       | 	    , instance_id
db-1       | 	) DO UPDATE SET
db-1       | 	    aggregate_id = $3
db-1       | 	    , aggregate_type = $4
db-1       | 	    , "sequence" = $5
db-1       | 	    , event_date = $6
db-1       | 	    , "position" = $7
db-1       | 	    , last_updated = statement_timestamp()
db-1       | 	    , filter_offset = $8
db-1       | 	;
db-1       | 2024-04-23 08:03:00.502 UTC [552] DETAIL:  parameters: $1 = 'projections.projects4', $2 = '261283497950314499', $3 = '262867965743988738', $4 = 'project', $5 = '1', $6 = '2024-04-15 10:38:37.267212+00', $7 = '1713177517.267236', $8 = '1'
db-1       | 2024-04-23 08:03:00.502 UTC [552] LOG:  statement: commit
db-1       | 2024-04-23 08:03:01.948 UTC [561] FATAL:  role "root" does not exist

zitadel docker:

  zitadel:
    restart: 'always'
    networks:
      - 'zitadel'
    image: 'ghcr.io/zitadel/zitadel:latest'
    command: 'start-from-init --masterkey ""'
    environment:
      - 'ZITADEL_DATABASE_POSTGRES_HOST=db'
      - 'ZITADEL_DATABASE_POSTGRES_PORT=5432'
      - 'ZITADEL_DATABASE_POSTGRES_DATABASE=zitadel'
      - 'ZITADEL_DATABASE_POSTGRES_USER_USERNAME=zitadel'
      - 'ZITADEL_DATABASE_POSTGRES_USER_PASSWORD=zitadel'
      - 'ZITADEL_DATABASE_POSTGRES_USER_SSL_MODE=disable'
      - 'ZITADEL_DATABASE_POSTGRES_ADMIN_USERNAME=postgres'
      - 'ZITADEL_DATABASE_POSTGRES_ADMIN_PASSWORD=postgres'
      - 'ZITADEL_DATABASE_POSTGRES_ADMIN_SSL_MODE=disable'
    expose:
      - 8080
    depends_on:
      db:
	condition: 'service_healthy'

db docker:

db:
    restart: 'always'
    image: postgres:16-alpine
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    volumes:
      - ./pg_data:/var/lib/postgresql/data
    networks:
      - 'zitadel'
    healthcheck:
      test: ["CMD-SHELL", "pg_isready", "-d", "postgres", "-u", "postgres"]
      interval: '10s'
      timeout: '30s'
      retries: 5
      start_period: '20s'
    expose:
      - 5432

Any clue why Zitadel is trying to do something as root user?

[hifabienne]

@eliobischof @stebenz can you help here?

[stebenz]

@VitalyVaryvdin
Hi,
As I tried to reproduce your problem, but only started the postgres container without ZITADEL, and still got the error logs in the postgres container, I guess it's related to your configuration there.

Please try with an additional environment variable "PGUSER=postgres", to set the user correctly.

[VitalyVaryvdin]

This is pretty weird, tbh.
I've never received this error with postgres in docker before. Looking at SQL queries log thought it was related to Zitadel.

However, the issue is solved with PGUSER env.

Thanks for such a fast reply, appreciate it!

upd: I've apparently used -u instead of -U for the user in the healthcheck, thus user wasn't specified, I suppose