SSL Support for Postgres

[s4ke]

First of all, I want to thank you for making my life easier by creating this ansible playbook. Because I think it's a great idea to have this I want to contribute the following:

Currently, the playbook allows to configure custom postgresql servers. This is great to hear, but I think the playbook should then also enforce the usage of SSL encryption. In my custom version of the playbook I managed to do so by adding the following lines to the homeserver.yml.j2 template:

database:
  # The database engine name
  name: "psycopg2"
  args:
    user: {{ matrix_synapse_database_user|to_json }}
    password: {{ matrix_synapse_database_password|to_json }}
    database: "{{ matrix_synapse_database_database }}"
    host: "{{ matrix_synapse_database_host }}"
    sslcert: "/data/certs/postgres_chat_client/client.crt"
    sslkey: "/data/certs/postgres_chat_client/client.key"
    sslrootcert: "/data/certs/postgres_chat_client/rootCA.crt"
    sslmode: "verify-full"
    cp_min: 5
    cp_max: 10

To make this work you only have to copy the certificates to the correct location and chown it to the matrix user.

Btw: The documentation states that database servers running on the docker host are not supported, but I managed to configure my postgres database on the host with the role geerlingguy.postgresql quite fine. Yes, you will have to allow the postgres database to bind to the outside IP and also allow connections to that ip, but that can issue can be fixed by adding correct firewall rules and a sane pg_hba.conf:

---
---
- hosts: database
  become: true
  vars:
    ansible_ssh_private_key_file: "{{playbook_dir}}/ssh_keys/ansible_rsa"
    ansible_user: "ansible"
    check_free_diskspace_min_available: 5
    apt_update_cache: True
    apt_restart_after_dist_upgrade: True
    setup_additional_groups:
      - name: "postgres"
        requires_root_password: True
    setup_additional_users:
      - name: "postgres"
        group: "postgres"
        is_sudo: False
  roles:
    - role: check-free-space
    - role: user-setup
    - role: force-reconnect

- hosts: database
  become: true
  vars:
    ansible_ssh_private_key_file: "{{playbook_dir}}/ssh_keys/ansible_rsa"
    ansible_user: "ansible"
  tasks:
    - name: "ensure /etc/ansible exists"
      file:
        path: /etc/ansible
        state: directory
    - name: "ensure /etc/ansible/certs exists"
      file:
        path: /etc/ansible/certs
        state: directory
    - name: "copy postgres_chat certs"
      copy:
        src: "{{playbook_dir}}/files/certs/database/{{inventory_hostname}}/postgres_chat"
        dest: "/etc/ansible/certs"
        mode: 0700
        owner: postgres
        group: postgres

    - name: Ensure files in /etc/ansible/certs/postgres_chat 0400
      command: find /etc/ansible/certs/postgres_chat -type f -exec chmod 0400 {} \;

    - name: Allow incoming access to the postgres port 5432 for docker servers and the database ip
      ufw:
        rule: allow
        direction: in
        src: "{{ item }}"
        to_port: 5432
      with_items:
        - <ip>
        - 172.17.0.0/16
        - 172.18.0.0/16

- hosts: database
  become: true
  vars:
    ansible_ssh_private_key_file: "{{playbook_dir}}/ssh_keys/ansible_rsa"
    ansible_user: "ansible"
    check_free_diskspace_min_available: 5
    apt_update_cache: True

    postgresql_user: postgres
    postgresql_group: postgres

    postgresql_locales:
      - 'en_US.UTF-8'
        
    postgresql_users:
      - name: matrix
        password: "<password>"
        state: present

  roles:
    - role: geerlingguy.postgresql

# now that the basic setup is done setup everything for matrix
- hosts: database
  become: true
  vars:
    ansible_ssh_private_key_file: "{{playbook_dir}}/ssh_keys/ansible_rsa"
    ansible_user: "ansible"
    check_free_diskspace_min_available: 5
    apt_update_cache: True

    postgresql_user: postgres
    postgresql_group: postgres

    postgresql_locales:
      - 'en_US.UTF-8'

    postgresql_global_config_options:
      - option: unix_socket_directories
        value: '{{ postgresql_unix_socket_directories | join(",") }}'
      - option: "ssl"
        value: "on"
      - option: "ssl_ca_file"
        value: "/etc/ansible/certs/postgres_chat/rootCA.crt"
      - option: "ssl_cert_file"
        value: "/etc/ansible/certs/postgres_chat/server.crt"
      - option: "ssl_key_file"
        value: "/etc/ansible/certs/postgres_chat/server.key"
      - option: listen_addresses
        value: "*"
        
    postgresql_hba_entries:
      - { type: local, database: all, user: postgres, auth_method: peer }
      - { type: local, database: homeserver, user: matrix, auth_method: md5 }
      - { type: hostssl, database: homeserver, address: "<outside_ip>", user: matrix, auth_method: md5, auth_options: "clientcert=1" }
     # for docker
      - { type: hostssl, database: homeserver, address: "172.17.0.0/16", user: matrix, auth_method: md5, auth_options: "clientcert=1" }
      - { type: hostssl, database: homeserver, address: "172.18.0.0/16", user: matrix, auth_method: md5, auth_options: "clientcert=1" }

    postgresql_databases:
      - name: homeserver
        lc_collate: 'en_US.UTF-8'
        lc_ctype: 'en_US.UTF-8'
        encoding: 'UTF-8'
        owner: 'matrix'
        state: present

    postgresql_users:
      - name: matrix
        password: "<password>"
        state: present
        #encrypted: # defaults to not set
        #priv: # defaults to not set
        #role_attr_flags: # defaults to not set
        #db: # defaults to not set
        #login_host: # defaults to 'localhost'
        #login_password: # defaults to not set
        #login_user: # defaults to '{{ postgresql_user }}'
        #login_unix_socket: # defaults to 1st of postgresql_unix_socket_directories
        #port: # defaults to not set

  roles:
    - role: geerlingguy.postgresql

[s4ke]

About the first play of the playbook: These are my custom roles that are definitely not up to par to be uploaded to ansible-galaxy. They work for me however. I think it's self explanatory what they do, though.

[spantaleev]

Thanks for your input!

It would be nice support Synapse talking to a Postgres database over SSL.

We'd need a few matrix_synapse_ variables to allow specifying sslcert, sslkey, sslrootcert and sslmode, and a way to mount the certificate files into the Synapse container. The latter already exists as matrix_synapse_container_additional_volumes.

If those things are in place, one could copy over the certificate files manually to the server (or using another playbook), and configure this playbook to make use of them. That's probably enough to cover most use-cases?

---

Setting up a an external Postgres server is something which is probably outside of the scope of this playbook, but.. we can definitely mention it on some new documentation page in docs/ and even provide your example playbook for setting it up.

People could also use another externally installed Postgres (such as the one provided by Amazon RDS) over an encrypted connection, by tweaking the playbook variables.

---

Let me know if you have interest on adding support for the above or if I should pick it up some time.

In any case, thanks for making it clear how it's meant to be used! It would be great to support it. 👍

[s4ke]

Yeah, maybe I will clean up my proof of concept on the weekend. For now, here is how I add my certs to the matrix server:

- hosts: chat
  become: true
  tags:
    - start
  vars:
    ansible_ssh_private_key_file: "{{playbook_dir}}/ssh_keys/ansible_rsa"
    ansible_user: "ansible"
  tasks:
    - name: "ensure /matrix/synapse/config/certs exists"
      file:
        path: /matrix/synapse/config/certs
        state: directory

    - name: "copy postgres_chat_client certs"
      copy:
        src: "{{playbook_dir}}/files/certs/chat/{{inventory_hostname}}/postgres_chat_client"
        dest: "/matrix/synapse/config/certs"
        mode: 0700
        owner: matrix
        group: matrix

    - name: Ensure files in /matrix/synapse/config/certs are 0400
      command: find "/matrix/synapse/config/certs" -type f -exec chmod 0400 {} \;

- name: "Set up a Matrix server"
  hosts: chat
  vars:
    ansible_ssh_private_key_file: "{{playbook_dir}}/ssh_keys/ansible_rsa"
    ansible_user: "ansible"
  become: true

  roles:
    - matrix-base
    - matrix-mailer
    - matrix-coturn
    - matrix-postgres
    - matrix-corporal
    - matrix-synapse
    - matrix-riot-web
    - matrix-mxisd
    - matrix-nginx-proxy
    - matrix-common-after

I use the tag start so I can just use the matrix user being created during setup :).

[spantaleev]

Any plan to continue working on this?

[s4ke]

Sorry, no. We have discontinued the matrix servers in my company for now.

[spantaleev]

I see!

I've mentioned this limitation in the docs (1e1c793), so people using an external Postgres server would be made aware of it.

If someone is keen on picking this up and adding support, I'd be happy to merge it.
For now, I'll close this as it doesn't affect the general use case and there's no one willing to work on it.

[T3chTobi]

Are there any news? I'd love to use that feature, but I'm stuck at setting it up

[EdGeraghty]

Would it be possible to add @glebarez's pgssl package?

pgSSL is a proxy for PostgreSQL that wraps plain TCP connections (sslmode=disable) into SSL and provides client certificate to the backend PostgreSQL server. This way it allows SSL encryption and certificate-based authentication for plain-text PostgreSQL clients.