Docker: Unable to connect to Postgres with docker-compose

[cclloyd]

When trying to set up nextcloud it keeps telling me that it can't connect to the database, or that password authentication failed for user 'postgres'. (I can connect via psql so the username/password is correct)

version: '3.5'

services:
    db:
        image: postgres
        restart: always
        ports:
          - "5432:5432"
        volumes:
          - ./db/nextcloud.sql:/docker-entrypoint-initdb.d/nextcloud.sql
          - postgres-data:/var/lib/postgresql/data
    nextcloud:
        image: nextcloud
        ports:
          - 8080:80
        depends_on:
          - db
        volumes:
          - nextcloud:/var/www/html
        restart: always
        environment:
          POSTGRES_DB: 'nextcloud'
          POSTGRES_USER: 'postgres'
          POSTGRES_PASSWORD: 'postgres'
          POSTGRES_HOST: 'db:5432'

volumes:
  nextcloud:
  postgres-data:
  db:

[beenje]

I got it to work with the following compose file:

version: '3.5'

services:
  db:
    image: postgres
    restart: always
    volumes:
      - "./pgdata:/var/lib/postgresql/data/pgdata"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: secret
      PGDATA: /var/lib/postgresql/data/pgdata
  nextcloud:
    image: nextcloud
    ports:
      - 8080:80
    depends_on:
      - db
    volumes:
      - "./html:/var/www/html"
    restart: always
    environment:
      POSTGRES_DB: 'nextcloud'
      POSTGRES_USER: 'postgres'
      POSTGRES_PASSWORD: 'secret'
      POSTGRES_HOST: 'db'
      NEXTCLOUD_ADMIN_USER: admin
      NEXTCLOUD_ADMIN_PASSWORD: admin

I was initially giving the POSTGRES_DB: 'nextcloud' variable to the postgres image but that didn't work. I was getting permission denied for database "nextcloud".
According to this link https://help.nextcloud.com/t/cant-install-nextcloud-pgsql-connect-privilege/12643 the database should not be created manually.
You should let the nextcloud installer create the database. I don't know what you have in your ./db/nextcloud.sql file but you should probably remove it if you use it to create the nextcloud database.

As I understand you have to pass a postgres superuser to the nextcloud image. The installer will create the database and even a specific user to access it.

[SnowMB]

Just ran into the same issue, thank you for the explanation :grin:

[SnowMB]

This issue is apparently present in our examples section and comes up a couple of times. I will join the bug requests in this issue.

[lqhuang]

@SnowMB Hey, Marc. I just saw your commits to fix this bug with set POSTGRES_DATABASE=not_used few hours ago. But I had tried new configuration, and the same problem is still there. I'm not very familiar with docker repo postgres. Hence, I want to know what do you think or what do you want to do to let db be initialized by nextcloud-app instead of be initialized by itself? Thanks for your efforts.

[victornoel]

@SnowMB for the record, with the same docker-compose file, I have machines where I have this problem and others where I don't.

I have no idea what is the difference between those (they use the same version of docker, and those failing are using docker-compose 1.21.1 while the working one is using 1.22.0 but I don't think this is the source of the problem...).

This is with postgres:10-alpine and nextcloud:fpm-alpine.

The docker-compose is:

version: '3'

networks:
  proxytanet:
    external: true

services:
  db:
    image: postgres:10-alpine
    restart: unless-stopped
    env_file:
      - .env
    volumes:
      - ${CHATONS_ROOT_DIR:-/srv/chatons}/nextcloud/db:/var/lib/postgresql/data

  app:
    image: nextcloud:fpm-alpine
    restart: unless-stopped
    env_file:
      - .env
    volumes:
      - ${CHATONS_ROOT_DIR:-/srv/chatons}/nextcloud/app:/var/www/html
    networks:
      - proxytanet
      - default

  web:
    build: .
    restart: unless-stopped
    volumes:
      - ${CHATONS_ROOT_DIR:-/srv/chatons}/nextcloud/app:/var/www/html:ro
    labels:
      traefik.enable: "true"
      traefik.backend: "nextcloud"
      traefik.frontend.rule: "Host: cloud.${CHATONS_DOMAIN:-local}, www.cloud.${CHATONS_DOMAIN:-local}"
      traefik.docker.network: "proxytanet"
    networks:
      - proxytanet
      - default

.env:

POSTGRES_PASSWORD=xxxxx
POSTGRES_USER=nextcloud
POSTGRES_HOST=db

Dockerfile:

FROM nginx:alpine

COPY nginx.conf /etc/nginx/nginx.conf

(nginx is for example https://raw.githubusercontent.com/nextcloud/docker/master/.examples/docker-compose/insecure/mariadb/fpm/web/nginx.conf).

[victornoel]

To be completely clear, I'm having the same error as in #287.

[lqhuang]

@victornoel Yeah. I also found there is a little probability of success. Sometimes failed, sometimes succeeded. Of course, usually failed. But if you have succeeded, it would be no problems for a long time. Very interesting and confused. Not easy to reproduce.

[MaxiReglisse]

A big thank you to Beenje for his docker-compose.yml file !

Like him, instead of using a single db.env file, as suggested in docker-compose examples, I use one per service : db-app.env and db-db.env

$ cat db-app.env
POSTGRES_DB=nextcloud 
POSTGRES_USER=postgres 
POSTGRES_PASSWORD=secret
POSTGRES_HOST=db
NEXTCLOUD_ADMIN_USER=admin
NEXTCLOUD_ADMIN_PASSWORD=admin

$ cat db-db.env 
POSTGRES_USER=postgres
POSTGRES_PASSWORD=secret

Below the docker-compose.yml that works:

$ cat docker-compose.yml 
services:
  db:
    image: postgres
    restart: always
    ports:
      - 5432:5432
    volumes:
      - db:/var/lib/postgresql/data
    env_file:
      - db-db.env
  app:
    image: nextcloud:production-apache
    restart: always
    ports:
      - 8080:80
    volumes:
      - nextcloud:/var/www/html
    environment:
      - POSTGRES_HOST=db
    env_file:
      - db-app.env
    depends_on:
      - db
volumes:
  db:
  nextcloud:

I tried different solutions for several hours, but still in vain ... for example, the following db.env, used in both services, db and app:

$ cat db.env 
POSTGRES_DB=nextcloud
POSTGRES_USER=postgres
POSTGRES_PASSWORD=secret
POSTGRES_HOST=db
NEXTCLOUD_ADMIN_USER=admin
NEXTCLOUD_ADMIN_PASSWORD=admin

generates the following error:

User does not have CONNECT privilege.

And in /var/www/html/data/nextcloud.log, we can observe the absence of user ("user":"--"), that the variable POSTGRES_USER is set to postgres or to nextcloud.

{"reqId":"DWMRPtu3TjDtVoiSY09H","level":2,"time":"2018-10-23T10:54:36+00:00","remoteAddr":"172.18.0.1","user":"--","app":"no app in context","method":"POST","url":"\/index.php","message":"Error occurred while checking PostgreSQL version, assuming >= 9","userAgent":"Mozilla\/5.0 (X11; Linux x86_64) AppleWebKit\/537.36 (KHTML, like Gecko) Ubuntu Chromium\/69.0.3497.81 Chrome\/69.0.3497.81 Safari\/537.36","version":"13.0.7.2"}

which probably explains the error: "User does not have CONNECT privilege."

[oddsund]

Based on this issue in upstream Nextcloud Server(https://github.com/nextcloud/server/issues/11311), the bug is triggered by some 10.X update in postgres docker images, which fixed another bug.

The current workaround, until Nextcloud Server is fixed, is to add an environment variable to the postgres container. It is important that the variable is only applied to the postgres container, as that is the whole workaround.

The environment variable to add to the postgres container ONLY is the following;
POSTGRES_DB=whatever
Where whatever can be whatever you want, as long as it's different from the POSTGRES_DB environment variable in the nextcloud container.

Same problem seems to occur with postgres:9, so some 9.X update affected this as well.

[SnowMB]

@oddsund did sum it up pretty well. My pr just aims to add the workaround for our existing examples so that they work out of the box. Nothing more, nothing less.

For a real solution to this issue we have to wait for upstream to fix this.

[am97]

I got the same issue with the example postgres/fpm docker-compose.yml. Adding POSTGRES_DB=whatever to the postgres container did non work.

Another workaround is:

1. Remove POSTGRES_DB=nextcloud and POSTGRES_USER=nextcloud from db.env
2. Run docker-compose up -d
3. Manually create the postgres user and database. Example:

root@vps:~/# docker exec -it fpm_db_1 bash
bash-4.4# su postgres
/ $ createuser -P nextcloud
Enter password for new role: 
Enter it again: 
/ $ createdb -O nextcloud nextcloud

[lukasmrtvy]

It does NOT work for me:

docker run -d \
--restart always \
--network nextcloud-bridge \
--name nextcloud \
-p 8080:80 \
-e POSTGRES_DB=postgres \
-e POSTGRES_USER=postgres \
-e POSTGRES_PASSWORD=postgres \
-e POSTGRES_HOST=nextcloud-postgres \
-e NEXTCLOUD_ADMIN_PASSWORD=admin \
-e NEXTCLOUD_ADMIN_USER=admin \
nextcloud:latest

docker run -d \
--restart always \
--network nextcloud-bridge \
--name nextcloud-postgres \
-e POSTGRES_PASSWORD=postgres \
-d postgres:11.0-alpine

Results in:

retrying install...
Error while trying to create admin user: Failed to connect to the database: An exception occured in driver: SQLSTATE[08006] [7] FATAL:  permission denied for database "postgres"
DETAIL:  User does not have CONNECT privilege

[brot]

Anyone already found a solution? Tried to setup nextcloud with the docker-compose example in ".examples/docker-compose/with-nginx-proxy/postgres/fpm", but failed.
I tried this setup already in August 2018 and back then it worked really well

[sbaerlocher]

We have the same problem with and if the database already exists in the postgres, then the installer failed. If the database does not yet exist, the installer will run without any problem. In earlier installations the installer ran with an existing database without any problems.

Is there a time point to fix this in the installer for postgres?

[Ofinka]

Facing same issue when trying to run nextcloud on k8s with postgres as a data source.

I could probably live without creating database by postgres container (thus having extra instance of postgres only for nextcloud purposes) but nextcloud should be able to at least reuse database which has been created by his other pod (for example if original pod fails).

[Viktova]

@Ofinka here's a working environment using postgresql

version: '3.6'
services:
  db:
    image: postgres:latest
    restart: always
    volumes:
      - db:/var/lib/postgresql/data
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=*****
    container_name: db

  redis:
    image: redis:alpine
    command: redis-server --appendonly yes --appendfsync always --save 900 1 --save 300 10 --save 60 1000     
    volumes:
      - redis:/data
    restart: always
    container_name: redis

  nextcloud:
    image: nextcloud:fpm
    links:
      - db
      - redis
    container_name: nextcloud
    volumes:
      - nextcloud:/var/www/html
    restart: always
    environment:
      - POSTGRES_HOST=db
      - POSTGRES_DB=nextcloud
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=*******
      - REDIS_HOST=redis
    depends_on:
      - db
      - redis

  web:
    image: nginx:alpine
    container_name: web
    ports:
      - 8080:80
    links:
      - nextcloud
    volumes:
      - ./nginx.conf:/etc/nginx/nginx.conf:ro
      - nextcloud:/var/www/html
    restart: always
    depends_on:
      - nextcloud

volumes:
  redis:
  db:
  nextcloud:

I'm proxying the dockerized nginx into an external (local nginx installed on the server + letsencrypt)
I don't want to use a dockerized acme, i prefer dealing with https the old school way

keep in mind that you must keep POSTGRES_USER in both containers set to postgres
you only need to change the password

hope this helps

P.S: i know working with the user postgres is impractical and not recommended but it doesn't work otherwise