From 0d6cf46b366b3459263bb320c8c33ef299bd7c82 Mon Sep 17 00:00:00 2001 From: "Jean-Marc Pigeon (Delson)" Date: Thu, 26 Jun 2025 10:28:43 -0400 Subject: [PATCH] Working out dovecot access on postgresql --- sql/datatest.sql | 12 ++++++------ sql/mailleur.sql | 14 +++++++------- support/addconfig.sh | 7 +++++-- 3 files changed, 18 insertions(+), 15 deletions(-) diff --git a/sql/datatest.sql b/sql/datatest.sql index 317694e..1207b63 100644 --- a/sql/datatest.sql +++ b/sql/datatest.sql @@ -6,19 +6,19 @@ //present password is crypte 'mailleur' //adding a list of local email -INSERT INTO emails (email,passwd) \ +INSERT INTO emails (email,password) \ values ('postmaster@example.com','$1$cBO23lDG$7l.Ooe7pF.gf8t6tD2mKp0'); -INSERT INTO emails (email,passwd) \ +INSERT INTO emails (email,password) \ values ('webmaster@example.com','$1$cBO23lDG$7l.Ooe7pF.gf8t6tD2mKp0'); //Set for Postgresql database -INSERT INTO emails (email,passwd) \ +INSERT INTO emails (email,password) \ values ('user1@posdb.example.com','$1$cBO23lDG$7l.Ooe7pF.gf8t6tD2mKp0'); -INSERT INTO emails (email,passwd) \ +INSERT INTO emails (email,password) \ values ('user2@posdb.example.com','$1$cBO23lDG$7l.Ooe7pF.gf8t6tD2mKp0'); //Set for MySQL database -INSERT INTO emails (email,passwd) \ +INSERT INTO emails (email,password) \ values ('user1@mardb.example.com','$1$cBO23lDG$7l.Ooe7pF.gf8t6tD2mKp0'); -INSERT INTO emails (email,passwd) \ +INSERT INTO emails (email,password) \ values ('user2@mardb.example.com','$1$cBO23lDG$7l.Ooe7pF.gf8t6tD2mKp0'); diff --git a/sql/mailleur.sql b/sql/mailleur.sql index 01adb0d..3df5cb8 100644 --- a/sql/mailleur.sql +++ b/sql/mailleur.sql @@ -49,13 +49,13 @@ /* */ /********************************************************/ CREATE TABLE emails ( - email TEXTUNIQUE, //User email - passwd TEXT //User password - DFLT '!', - space INTEGER //space used by user email - DFLT 0, - mxspace INTEGER //Maximun space available - DFLT 20000 //to user (20 Gig). + email TEXTUNIQUE, //User email + password TEXT //User password + DFLT '!', + space INTEGER //space used by user email + DFLT 0, + mxspace INTEGER //Maximun space available + DFLT 20000 //to user (20 Gig). ); GRANT SELECT,INSERT,UPDATE,DELETE ON emails TO mailapache; GRANT SELECT ON emails TO maildove; diff --git a/support/addconfig.sh b/support/addconfig.sh index 9335667..608a577 100755 --- a/support/addconfig.sh +++ b/support/addconfig.sh @@ -3,6 +3,7 @@ #Shell script to add local information to configuration file #----------------------------------------------------------------- export APPNAME=mailleur +export SPOOLDIR=/var/spool/$APPNAME/mails #loading all applications variables [ -f /etc/$APPNAME/$APPNAME.conf ] && . /etc/$APPNAME/$APPNAME.conf #overiding some variable by local setting @@ -25,12 +26,14 @@ case "$DB_TYPE" in "POSTGRESQL" ) echo "driver = pgsql" echo "connect= dbname=$APPNAME" - echo "password_query=SELECT passwd FROM emails WHERE email='%u'" + echo "password_query=SELECT password FROM emails WHERE email='%u'" + echo "user_query=SELECT 'mailleur' AS uid,'mail' AS gid,'$SPOOLDIR/%d/%n' AS home,'maildir:~/dovecot' AS mail,'*:storage='||mxspace||'M' AS quota_rule FROM email WHERE email='%u'" + ;; "MYSQL" ) echo "driver = mysql" echo "connect= host=localhost dbname=$APPNAME" - echo "password_query=SELECT passwd FROM emails WHERE email='%u'" + echo "password_query=SELECT password FROM emails WHERE email='%u'" ;; esac ) >> /etc/$APPNAME/dovecot/dovecot-sql.conf -- 2.47.3