Support of pgsql for postfix container

[monofox]

Summary

Currently, for lookups in extra.cfg for postfix within data/conf/postfix/ it is only possible to use either MySQL / MariaDB or local files with pcre, hash.

Feature request is to install the postfix-pgsql within the Dockerfile for Postfix in data/Dockerfiles/postfix/Dockerfile. With this, it is possible to add additional lookups from other containers using PostgreSQL without the need to build own container. E.g.:

virtual_alias_maps = proxy:mysql:/opt/postfix/conf/sql/mysql_virtual_alias_maps.cf,
  proxy:mysql:/opt/postfix/conf/sql/mysql_virtual_resource_maps.cf,
  proxy:mysql:/opt/postfix/conf/sql/mysql_virtual_spamalias_maps.cf,
  proxy:mysql:/opt/postfix/conf/sql/mysql_virtual_alias_domain_maps.cf,
  proxy:pgsql:/opt/postfix/conf/mailman/pgsql_virtual_alias_maps.cf

Motivation

Main advantage is, to make mailcow easily extendable for all users running partially other databases without need of keeping track to update or build own postfix container or whole project.

Additional context

postfix-mailcow_1    | May 12 19:32:25 2d6af700816b postfix/proxymap[387]: error: unsupported dictionary type: pgsql

[jiapei100]

@monofox

Did you find the answer? I've got the same question ...

What is happening?
Please do not stop the stack while we are initializing the database or do other preparations.

What is happening? - Nginx cannot connect to an upstream server or other services are not ready yet.
This is fine if mailcow was just installed or updated and can take a few minutes to complete.
Please check the logs or contact support if the error persists.

Quick debugging
Check Nginx and PHP logs:

docker compose logs --tail=200 php-fpm-mailcow nginx-mailcow
Make sure your SQL credentials in mailcow.conf (a link to .env) do fit your initialized SQL volume. If you see an access denied, you might have the wrong mailcow.conf:

source mailcow.conf ; docker compose exec mysql-mailcow mysql -u${DBUSER} -p${DBPASS} ${DBNAME}
In case of a previous failed installation, create a backup of your existing data, followed by removing all volumes and starting over (NEVER do this with a production system, it will remove ALL data):

BACKUP_LOCATION=/tmp/ ./helper-scripts/backup_and_restore.sh backup all
docker compose down --volumes ; docker compose up -d
Make sure your timezone is correct. Use "America/New_York" for example, do not use spaces. Check [here](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones) for a list.


Click to learn more about [getting support.](https://mailcow.github.io/mailcow-dockerized-docs/#get-support)

Well, for my Ubuntu 22.04, I have:

⋊> /o/mailcow-dockerized on master ⨯ sudo systemctl status postgresql                                                                                                 14:14:33
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Wed 2023-06-21 23:24:26 PDT; 14h ago
   Main PID: 1001 (code=exited, status=0/SUCCESS)
        CPU: 2ms

Jun 21 23:24:26 localhost systemd[1]: Starting PostgreSQL RDBMS...
Jun 21 23:24:26 localhost systemd[1]: Finished PostgreSQL RDBMS.
⋊> /o/mailcow-dockerized on master ⨯ sudo systemctl status mysql                                                                                                      14:16:01
○ mysql.service
     Loaded: masked (Reason: Unit mysql.service is masked.)
     Active: inactive (dead)
⋊> /o/mailcow-dockerized on master ⨯    

I wanna connect mailcow with postgresql. How to do that?
Thank you ....

[monofox]

Hi @jiapei100 ,

to be honest, i'm not running mailcow itself with postgresql, only third-party applications, which i've integrated into the lookup-tables.
Are you trying to use the postgresql on the host system instead of docker container? To be honest, i'm not able to help you there and i'm also not sure, if all the SQL queries of Mailcow are compatible to PostgreSQL.

I've my own gitlab-fork of mailcow which is freq. updated and patching the dockerfile for postfix: https://git.monobear.eu/monobear/mailcow-dockerized/-/blob/master/data/Dockerfiles/postfix/Dockerfile

With this, in the docker-compose.override.yml, i'm referring to my own postfix docker image.

And in the data/conf/postfix/sql/extra.cf i can easily use proxy:pgsql:.

But as i've written: for Mailcow itself, i stick to the Mailcow-Standard (= MariaDB) and i recommend you to do the same.

[jiapei100]

@monofox

Thank you very much... Quite clear explaination...

I'm now done: mailcow + mariadb. That's good enough...
Hopefully in the future, mailcow would be able to let us choose our own DB.

And BTW, if I installed mariadb on my host, mailcow docker container should be using it, isn't it? Because it is a container anyway... I think a better design is to let mailcow docker container choose which DB intalled on the host that mailcow is going to adopt?

Anyway, have a nice day...