User does not have CONNECT privilege.

[Mojo90]

I always get the following error when trying to containerise with docker-compose:

SQLSTATE[08006] [7] FATAL: permission denied for database "nextcloud" DETAIL: User does not have CONNECT privilege.

My docker-compose looks like the following:

version: '3'

services:

  proxy:
    image: budry/jwilder-nginx-proxy-arm:latest
    labels:
      - "com.github.jrcs.letsencrypt_nginx_proxy_companion.nginx_proxy=true"
    container_name: nextcloud-proxy
    networks:
      - nextcloud_network
    ports:
      - 80:80
      - 443:443
    volumes:
      - ./proxy/conf.d:/etc/nginx/conf.d:rw
      - ./proxy/vhost.d:/etc/nginx/vhost.d:rw
      - ./proxy/html:/usr/share/nginx/html:rw
      - ./proxy/certs:/etc/nginx/certs:ro
      - /etc/localtime:/etc/localtime:ro
      - /var/run/docker.sock:/tmp/docker.sock:ro
    restart: unless-stopped

letsencrypt:
    image: budry/jrcs-letsencrypt-nginx-proxy-companion-arm
    container_name: nextcloud-letsencrypt
    depends_on:
      - proxy
    networks:
      - nextcloud_network
    volumes:
      - ./proxy/certs:/etc/nginx/certs:rw
      - ./proxy/vhost.d:/etc/nginx/vhost.d:rw
      - ./proxy/html:/usr/share/nginx/html:rw
      - /etc/localtime:/etc/localtime:ro
      - /var/run/docker.sock:/var/run/docker.sock:ro
    restart: unless-stopped

  db:
    image: postgres
    container_name: nextcloud-postgres
    networks:
      - nextcloud_network
    environment:
      POSTGRES_PASSWORD: TEST
      POSTGRES_USER: nextcloud
      POSTGRES_DB: nextcloud
      PGDATA: /var/lib/psql/data
    volumes:
      - db:/var/lib/psql
      - /etc/localtime:/etc/localtime:ro
    restart: unless-stopped

  app:
    image: nextcloud:latest
    container_name: nextcloud-app
    networks:
      - nextcloud_network
    depends_on:
      - letsencrypt
      - proxy
      - db
    volumes:
      - nextcloud:/var/www/html
      - ./app/config:/var/www/html/config
      - ./app/custom_apps:/var/www/html/custom_apps
      - ./app/data:/var/www/html/data
      - ./app/themes:/var/www/html/themes
      - /etc/localtime:/etc/localtime:ro
    environment:
      - VIRTUAL_HOST=192.168.0.118
      - LETSENCRYPT_HOST=192.168.0.118
      - LETSENCRYPT_EMAIL=moritz@develapp.me
    restart: unless-stopped

volumes:
  nextcloud:
  db:

networks:
  nextcloud_network:                                                                                                                                                                                  

I also tried different versions and standard configuration from Postgres (with postgres User) but all the time the same error occurs.

[wglambert]

The environment variables are only applied if the data directory is empty, as they are used during and initialization.
https://github.com/docker-library/docs/tree/master/postgres#environment-variables

Warning: the Docker specific variables will only have an effect if you start the container with a data directory that is empty; any pre-existing database will be left untouched on container startup.

So you'd have to manually do that configuration on your database, or run a script using the /docker-entrypoint-initdb.d/ to apply any modifications
https://github.com/docker-library/docs/tree/master/postgres#initialization-scripts

[Mojo90]

So it is the same with a fresh new build.
I also logged in to the postgres container and removed the data directory and restarted the container --> same error.
I also logged in to the postgres container and added manually the connect privilege with

nextcloud=# GRANT CONNECT ON DATABASE nextcloud TO nextcloud;
GRANT

--> same error.
So really not sure what's going on here and why this error persists.

[wglambert]

When you do a docker restart the entrypoint isn't run again as it's only when a container is started for the first time (otherwise every docker stop/start or restart would re-initialize the container and wipe it)

For manually adding privileges to the database

$ docker run --rm --network=nextcloud_network -d -e MYSQL_ROOT_PASSWORD=pass --name postgres postgres
24977d8147ff6568eacc00e5fada594405cb55de6ef671ef5efb9686c10fa123

$ docker exec -it postgres psql -U postgres                                                                                                                           
psql (11.1 (Debian 11.1-1.pgdg90+1))                                                                                                                                                          
Type "help" for help.                                                                                                                                                                         
                                                                                                                                                                                              
postgres=# create database nextcloud_db;                                                                                                                                                      
CREATE DATABASE                                                                                                                                                                               
postgres=# create user nextcloud with encrypted password 'pass';
CREATE ROLE
postgres=# grant all privileges on database nextcloud_db to nextcloud;
GRANT
postgres=# \l+
                                                                     List of databases
     Name     |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges    |  Size   | Tablespace |                Description
--------------+----------+----------+------------+------------+------------------------+---------+------------+--------------------------------------------
 nextcloud_db | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres          +| 7545 kB | pg_default | 
              |          |          |            |            | postgres=CTc/postgres +|         |            | 
              |          |          |            |            | nextcloud=CTc/postgres |         |            | 
. . .

Making a new container to connect to the first one using the created user and database

$ docker run --rm --network=nextcloud_network -d -e MYSQL_ROOT_PASSWORD=pass --name postgres1 postgres
e10141ec0a28d376cd955f3999304ee36cebd9b3087c1dbdd3bf74215aefeb0d

$ docker exec -it postgres1 psql -U nextcloud -h postgres -d nextcloud_db
psql (11.1 (Debian 11.1-1.pgdg90+1))
Type "help" for help.

nextcloud_db=>

[Mojo90]

Yes, I did it like that beforehand and that worked.
But what I am wondering is: why do I have the chance to add the credentials as environment variables, but it is not added accordingly to the psql server? Or even when I add nothing it does not work with standard credentials (postgres / postgres) as mentioned. That is a bit weird.

[wglambert]

The env var configuration is done through the entrypoint, which only runs once when the container is first initialized. The env vars are only used if the entrypoint itself initializes the database, if one is already present then nothing is changed.
https://github.com/docker-library/postgres/blob/master/11/docker-entrypoint.sh

If you set nothing then the entrypoint only allows local connections (unix socket) and the Postgres user will have no password

$ docker run -d --rm --name postgres postgres             
f700af7f1ec993048f223515366166e27676c954f67d9487738d207c6377c54e

$ docker exec -it postgres psql -U postgres
psql (11.1 (Debian 11.1-1.pgdg90+1))
Type "help" for help.

postgres=#

[wglambert]

Going to close since this looks resolved

[charlag]

Hi
I believe there's still something wrong there
I delete volume and files and start docker-compose, I see that init sequence is run but I still cannot connect.

version: '3.2'

volumes:
  db:
    driver: local
    driver_opts:
      type: none
      o: bind
      device: /mnt/external/nextcloud/db

services:
  db:
    image: arm32v7/postgres
    restart: always
    volumes:
      - db:/var/lib/postgresql/data
    env_file:
      - db.env
  app:
    image: nextcloud:fpm
    links:
      - db
    volumes:
      - type: bind
        source: /mnt/external/nextcloud/data
        target: /var/www/html
    restart: always
    ports:
      - 9000:9000
    environment:
      - POSTGRES_HOST=db

POSTGRES_USER=nextcloud
POSTGRES_DB=nextcloud
POSTGRES_PASSWORD=some-pass

docker-compose logs

(there was another thing for some reason where password did not match, I did \password manually, now we have the same privilege error)

db_1   | The files belonging to this database system will be owned by user "postgres".
db_1   | This user must also own the server process.
db_1   | 
db_1   | The database cluster will be initialized with locale "en_US.utf8".
db_1   | The default database encoding has accordingly been set to "UTF8".
db_1   | The default text search configuration will be set to "english".
db_1   | 
db_1   | Data page checksums are disabled.
app_1  | [07-Jun-2019 17:56:20] NOTICE: fpm is running, pid 1
db_1   | 
db_1   | fixing permissions on existing directory /var/lib/postgresql/data ... ok
db_1   | creating subdirectories ... ok
db_1   | selecting default max_connections ... 100
db_1   | selecting default shared_buffers ... 128MB
db_1   | selecting dynamic shared memory implementation ... posix
db_1   | creating configuration files ... ok
db_1   | running bootstrap script ... ok
db_1   | performing post-bootstrap initialization ... ok
db_1   | syncing data to disk ... ok
app_1  | [07-Jun-2019 17:56:20] NOTICE: ready to handle connections
app_1  | 172.18.0.1 -  07/Jun/2019:17:56:49 +0000 "POST /index.php" 200
app_1  | 172.18.0.1 -  07/Jun/2019:17:57:01 +0000 "POST /index.php" 200
app_1  | 172.18.0.1 -  07/Jun/2019:18:00:25 +0000 "POST /index.php" 200
db_1   | 
db_1   | Success. You can now start the database server using:
db_1   | 
db_1   |     pg_ctl -D /var/lib/postgresql/data -l logfile start
db_1   | 
db_1   | 
db_1   | WARNING: enabling "trust" authentication for local connections
db_1   | You can change this by editing pg_hba.conf or using the option -A, or
db_1   | --auth-local and --auth-host, the next time you run initdb.
db_1   | waiting for server to start....2019-06-07 17:56:23.416 UTC [42] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1   | 2019-06-07 17:56:23.719 UTC [43] LOG:  database system was shut down at 2019-06-07 17:56:20 UTC
db_1   | 2019-06-07 17:56:23.778 UTC [42] LOG:  database system is ready to accept connections
db_1   |  done
db_1   | server started
db_1   | CREATE DATABASE
db_1   | 
db_1   | 
db_1   | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
db_1   | 
db_1   | 2019-06-07 17:56:25.603 UTC [42] LOG:  received fast shutdown request
db_1   | waiting for server to shut down....2019-06-07 17:56:25.645 UTC [42] LOG:  aborting any active transactions
db_1   | 2019-06-07 17:56:25.656 UTC [42] LOG:  background worker "logical replication launcher" (PID 49) exited with exit code 1
db_1   | 2019-06-07 17:56:25.657 UTC [44] LOG:  shutting down
db_1   | 2019-06-07 17:56:25.860 UTC [42] LOG:  database system is shut down
db_1   |  done
db_1   | server stopped
db_1   | 
db_1   | PostgreSQL init process complete; ready for start up.
db_1   | 
db_1   | 2019-06-07 17:56:26.003 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
db_1   | 2019-06-07 17:56:26.004 UTC [1] LOG:  listening on IPv6 address "::", port 5432
db_1   | 2019-06-07 17:56:26.056 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1   | 2019-06-07 17:56:26.206 UTC [60] LOG:  database system was shut down at 2019-06-07 17:56:25 UTC
db_1   | 2019-06-07 17:56:26.266 UTC [1] LOG:  database system is ready to accept connections
db_1   | 2019-06-07 17:56:49.786 UTC [67] FATAL:  password authentication failed for user "nextcloud"
db_1   | 2019-06-07 17:56:49.786 UTC [67] DETAIL:  Password does not match for user "nextcloud".
db_1   | 	Connection matched pg_hba.conf line 95: "host all all all md5"
db_1   | 2019-06-07 17:56:49.805 UTC [68] FATAL:  password authentication failed for user "nextcloud"
db_1   | 2019-06-07 17:56:49.805 UTC [68] DETAIL:  Password does not match for user "nextcloud".
db_1   | 	Connection matched pg_hba.conf line 95: "host all all all md5"
db_1   | 2019-06-07 17:56:50.113 UTC [69] FATAL:  password authentication failed for user "nextcloud"
db_1   | 2019-06-07 17:56:50.113 UTC [69] DETAIL:  Password does not match for user "nextcloud".
db_1   | 	Connection matched pg_hba.conf line 95: "host all all all md5"
db_1   | 2019-06-07 17:56:50.127 UTC [70] FATAL:  password authentication failed for user "nextcloud"
db_1   | 2019-06-07 17:56:50.127 UTC [70] DETAIL:  Password does not match for user "nextcloud".
db_1   | 	Connection matched pg_hba.conf line 95: "host all all all md5"
db_1   | 2019-06-07 17:56:50.142 UTC [71] FATAL:  password authentication failed for user "nextcloud"
db_1   | 2019-06-07 17:56:50.142 UTC [71] DETAIL:  Password does not match for user "nextcloud".
db_1   | 	Connection matched pg_hba.conf line 95: "host all all all md5"
db_1   | 2019-06-07 17:56:50.154 UTC [72] FATAL:  password authentication failed for user "nextcloud"
db_1   | 2019-06-07 17:56:50.154 UTC [72] DETAIL:  Password does not match for user "nextcloud".
db_1   | 	Connection matched pg_hba.conf line 95: "host all all all md5"
db_1   | 2019-06-07 17:57:01.777 UTC [73] FATAL:  password authentication failed for user "nextcloud"
db_1   | 2019-06-07 17:57:01.777 UTC [73] DETAIL:  Password does not match for user "nextcloud".
db_1   | 	Connection matched pg_hba.conf line 95: "host all all all md5"
db_1   | 2019-06-07 17:57:01.791 UTC [74] FATAL:  password authentication failed for user "nextcloud"
db_1   | 2019-06-07 17:57:01.791 UTC [74] DETAIL:  Password does not match for user "nextcloud".
db_1   | 	Connection matched pg_hba.conf line 95: "host all all all md5"
db_1   | 2019-06-07 18:00:26.468 UTC [98] FATAL:  permission denied for database "nextcloud"
db_1   | 2019-06-07 18:00:26.468 UTC [98] DETAIL:  User does not have CONNECT privilege.
db_1   | 2019-06-07 18:00:26.503 UTC [100] FATAL:  permission denied for database "nextcloud"
db_1   | 2019-06-07 18:00:26.503 UTC [100] DETAIL:  User does not have CONNECT privilege.

Doing GRANT CONNECT ON DATABASE nextcloud TO nextcloud; does not help.

I use derived arm32v7/postgres images if it makes a difference.

[charlag]

Uuuf, sorry, it seems to be realted to nextcloud/server#11311