Matrix Container Cannot Connect to PostgreSQL Database

[novams]

I have configured a Matrix-Synapse Docker container on a public server, using the image here https://github.com/silvio/docker-matrix and am using Traefik (https://github.com/containous/traefik) for routing / proxying. I have a PostgreSQL database also on the same server (not in a container) that I am attempting to use as the database for Matrix. Note, I already have this database working with a non-container version of Matrix, so I believe this is a networking issue.

Currently, the container Matrix will start fine; however, when it attempts to use psycopg2 to access the postgres, it fails with:

2018-01-13 13:04:15,576 - twisted - 131 - ERROR - -     conn = _connect(dsn, connection_factory=connection_factory, async=async)
2018-01-13 13:04:15,576 - twisted - 131 - ERROR - - psycopg2.OperationalError: could not connect to server: Connection timed out
2018-01-13 13:04:15,576 - twisted - 131 - ERROR - - 	Is the server running on host "<IP_ADDRESS>" and accepting
2018-01-13 13:04:15,576 - twisted - 131 - ERROR - - 	TCP/IP connections on port 5432?

From my understanding, this typically indicates that the postgres service hasn't been configured to accept connections from the accessing server. Thus, I made the (presumably) appropriate changes to postgres' postgresql.conf (listen_address = '*') and pg_hba.conf (a discussion of this can be seen here: https://stackoverflow.com/questions/31249112/allow-docker-container-to-connect-to-a-local-host-postgres-database):

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host	all		all		0.0.0.0/0		md5
host	all		all		172.19.0.0/16		md5
host	all		all		172.17.0.0/16		md5

So, I first added 0.0.0.0/0 as supposedly that will allow connections from all IP's. However, I continued to receive the same error, so then I added the Docker network that I had created for Traefik and Matrix to talk to each other (called proxy in the below config files) 172.19.0.0/16, which also didn't work. Finally, I also added the default bridge network for good measure 172.17.0.0/16 but to no avail.

I have also made sure to include the IP address of the public server (running postgres) in matrix's homeserver.yaml file (take my word for it):

# Database configuration
database:
	# The database engine name
	name: "psycopg2"
	# Arguments to pass to the engine
	args:
	# Path to the database
	user: "synapse_user"
	password: "<PASSWORD>"
	database: "synapsetest"
	host: "<IP_ADDRESS>"
	cp_min: 5
	cp_max: 10

Also, running netstat shows postgres is indeed listening as it should:

$ sudo netstat -tulpn | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      17657/postgres  
tcp6       0      0 :::5432                 :::*                    LISTEN      17657/postgres  

So at this point I'm not quite sure what else to try, as from the configurations I've made, it doesn't make sense to me that a container with an IP of 172.17.0.2 can't access postgres. For further information, I am also including the docker-compose.yml file for the Matrix container; note, however, I'm having routing issues in general with it and am receiving Bad Gateway errors when trying to access the web client, so there are problems there too:

version: '2'

services:

  matrix:
    build:
      context: .
    image: "avhost/docker-matrix"
    hostname: matrix
    command: start

    labels:
      - "traefik.backend=matrix"
      - "traefik.docker.network=proxy"
      - "traefik.frontend.rule=Host:matrix.myserver.com"
      - "traefik.enable=true"
      - "traefik.port=8449"
    restart: always
    volumes:
      - /opt/matrix-synapse-docker:/data
    networks:
      - proxy
    expose:
      - "8449"
    ports:
      - "8009:8009"

networks:
  proxy:
    external: true

Finally, for completion sake, here is the full homeserver.yml configuration file and full output of the Matrix container's log:

tls_certificate_path: "/data/matrix.myserver.com.tls.crt"
tls_private_key_path: "/data/matrix.myserver.com.tls.key"
tls_dh_params_path: "/data/matrix.myserver.com.tls.dh"

no_tls: False

server_name: "matrix.myserver.com"

pid_file: /data/homeserver.pid

web_client: True 

soft_file_limit: 0

listeners:
  # Main HTTPS listener
  # For when matrix traffic is sent directly to synapse.
  -
    # The port to listen for HTTPS requests on.
    port: 8449

    # This is a 'http' listener, allows us to specify 'resources'.
    type: http

    tls: true

    x_forwarded: false

    # List of HTTP resources to serve on this listener.
    resources:
      -
        # List of resources to host on this listener.
        names:
          - client     # The client-server APIs, both v1 and v2
          - webclient  # The bundled webclient.

        compress: true

      - names: [federation]  # Federation APIs
        compress: false

  # Unsecure HTTP listener,
  # For when matrix traffic passes through loadbalancer that unwraps TLS.
  - port: 8009
    tls: false
    bind_address: ''
    type: http

    x_forwarded: false

    resources:
      - names: [client, webclient]
        compress: true
      - names: [federation]
        compress: false

# Database configuration
database:
  # The database engine name
  name: "psycopg2"
  # Arguments to pass to the engine
  args:
    # Path to the database
    user: "synapse_user"
    password: "<PASSWORD>"
    database: "synapsetest"
    host: "<IP_ADDRESS>"
    cp_min: 5
    cp_max: 10

event_cache_size: "10K"

verbose: 0

log_file: "/data/homeserver.log"

log_config: "/data/matrix.myserver.com.log.config"

rc_messages_per_second: 0.2

# Number of message a client can send before being throttled
rc_message_burst_count: 10.0

# The federation window size in milliseconds
federation_rc_window_size: 1000

# The number of federation requests from a single server in a window
# before the server will delay processing the request.
federation_rc_sleep_limit: 10

# The duration in milliseconds to delay processing events from
# remote servers by if they go over the sleep limit.
federation_rc_sleep_delay: 500

# The maximum number of concurrent federation requests allowed
# from a single server
federation_rc_reject_limit: 50

# The number of federation requests to concurrently process from a
# single server
federation_rc_concurrent: 3

media_store_path: "/data/media_store"

uploads_path: "/uploads"

# The largest allowed upload size in bytes
max_upload_size: "10M"

# Maximum number of pixels that will be thumbnailed
max_image_pixels: "32M"

dynamic_thumbnails: false

# List of thumbnail to precalculate when an image is uploaded.
thumbnail_sizes:
- width: 32
  height: 32
  method: crop
- width: 96
  height: 96
  method: crop
- width: 320
  height: 240
  method: scale
- width: 640
  height: 480
  method: scale
- width: 800
  height: 600
  method: scale

url_preview_enabled: False

# The largest allowed URL preview spidering size in bytes
max_spider_size: "10M"

recaptcha_public_key: "YOUR_PUBLIC_KEY"

recaptcha_private_key: "YOUR_PRIVATE_KEY"

enable_registration_captcha: False

recaptcha_siteverify_api: "https://www.google.com/recaptcha/api/siteverify"

# Enable registration for new users.
enable_registration: True

registration_shared_secret: "<SECRET>"

user_creation_max_duration: 1209600000

bcrypt_rounds: 12

allow_guest_access: False

trusted_third_party_id_servers:
    - matrix.org
    - vector.im
    - riot.im

report_stats: False

# A list of event types that will be included in the room_invite_state
room_invite_state_types:
    - "m.room.join_rules"
    - "m.room.canonical_alias"
    - "m.room.avatar"
    - "m.room.name"


# A list of application service config file to use
app_service_config_files: []

expire_access_token: False

signing_key_path: "/data/matrix.myserver.com.signing.key"

old_signing_keys: {}

key_refresh_interval: "1d" # 1 Day.

# The trusted servers to download signing keys from.
perspectives:
  servers:
    "matrix.org":
      verify_keys:
        "ed25519:auto":
          key: "KEY"

# Whether to allow non server admins to create groups on this server
enable_group_creation: false

-=> start turn
-=> start riot.im client
groupadd: group 'matrix' already exists
-=> start matrix
useradd: user 'matrix' already exists
2018-01-13 13:02:06,480 CRIT Supervisor running as root (no user in config file)
2018-01-13 13:02:06,481 INFO Included extra file "/conf/supervisord-matrix.conf" during parsing
2018-01-13 13:02:06,481 INFO Included extra file "/conf/supervisord-turnserver.conf" during parsing
2018-01-13 13:02:06,482 INFO supervisord started with pid 1
2018-01-13 13:02:07,484 INFO spawned: 'matrix' with pid 13
2018-01-13 13:02:07,486 INFO spawned: 'turnserver' with pid 14
2018-01-13 13:02:08,487 INFO success: matrix entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
2018-01-13 13:02:08,488 INFO success: turnserver entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
2018-01-13 13:04:15,572 - twisted - 131 - ERROR - - Traceback (most recent call last):
2018-01-13 13:04:15,572 - twisted - 131 - ERROR - -   File "/usr/lib/python2.7/runpy.py", line 162, in _run_module_as_main
2018-01-13 13:04:15,573 - twisted - 131 - ERROR - -     "__main__", fname, loader, pkg_name)
2018-01-13 13:04:15,573 - twisted - 131 - ERROR - -   File "/usr/lib/python2.7/runpy.py", line 72, in _run_code
2018-01-13 13:04:15,573 - twisted - 131 - ERROR - -     exec code in run_globals
2018-01-13 13:04:15,573 - twisted - 131 - ERROR - -   File "/usr/local/lib/python2.7/dist-packages/synapse/app/homeserver.py", line 484, in <module>
2018-01-13 13:04:15,574 - twisted - 131 - ERROR - -     main()
2018-01-13 13:04:15,574 - twisted - 131 - ERROR - -   File "/usr/local/lib/python2.7/dist-packages/synapse/app/homeserver.py", line 479, in main
2018-01-13 13:04:15,574 - twisted - 131 - ERROR - -     hs = setup(sys.argv[1:])
2018-01-13 13:04:15,574 - twisted - 131 - ERROR - -   File "/usr/local/lib/python2.7/dist-packages/synapse/app/homeserver.py", line 339, in setup
2018-01-13 13:04:15,575 - twisted - 131 - ERROR - -     db_conn = hs.get_db_conn(run_new_connection=False)
2018-01-13 13:04:15,575 - twisted - 131 - ERROR - -   File "/usr/local/lib/python2.7/dist-packages/synapse/app/homeserver.py", line 280, in get_db_conn
2018-01-13 13:04:15,575 - twisted - 131 - ERROR - -     db_conn = self.database_engine.module.connect(**db_params)
2018-01-13 13:04:15,575 - twisted - 131 - ERROR - -   File "/usr/lib/python2.7/dist-packages/psycopg2/__init__.py", line 164, in connect
2018-01-13 13:04:15,576 - twisted - 131 - ERROR - -     conn = _connect(dsn, connection_factory=connection_factory, async=async)
2018-01-13 13:04:15,576 - twisted - 131 - ERROR - - psycopg2.OperationalError: could not connect to server: Connection timed out
2018-01-13 13:04:15,576 - twisted - 131 - ERROR - - 	Is the server running on host "<IP_ADDRESS>" and accepting
2018-01-13 13:04:15,576 - twisted - 131 - ERROR - - 	TCP/IP connections on port 5432?
2018-01-13 13:04:15,576 - twisted - 131 - ERROR - - 
2018-01-13 13:04:15,665 INFO exited: matrix (exit status 1; not expected)
2018-01-13 13:04:16,667 INFO spawned: 'matrix' with pid 34
2018-01-13 13:04:17,668 INFO success: matrix entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)

Any help with all this would be greatly appreciated. Of course I can also provide more details if necessary. Thank you!

[neugartf]

would have been nice to publish the solution before closing it :(

[pilot51]

This solved it for me: https://stackoverflow.com/a/31249288/382130

To summarize what I did on Debian 10:

1. In /etc/postgresql/11/main/postgresql.conf, uncomment listen_addresses = 'localhost' and change to listen_addresses = '*'. This requires restarting the DB server.
2. In /etc/postgresql/11/main/pg_hba.conf, add host all all 172.17.0.0/16 md5 to allow connections from docker containers. I did this through Webmin.
3. In homeserver.yaml, set the host database arg to the IPv4 address on eth0, found with ip a. It is probably in the 192.168.x.x range for most residential servers. Be sure it is a static IP, usually configured in your router, to prevent it from breaking down the road.
4. It should now work. In my case, I was trying to migrate from SQLite to PostgreSQL. This helped me do that in docker.