1

Topic: Unable to sync Domain Address book

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release):  2019040201 2019-04-09 10:17:27 iRedMail Easy: https://www.iredmail.org/easy.html

- Deployed with iRedMail Easy: yes
- Linux/BSD distribution name and version:   CentOS Linux release 7.6.1810 (Core)
- Store mail accounts in which backend (LDAP/MySQL/PGSQL):   postgreSQL
- Web server (Apache or Nginx):  nginx
- Manage mail accounts with iRedAdmin-Pro?  yes
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

No vmail.mailbox address column causing issues with sogo views sogo.aliases and sogo.maillists?

Error occurring in sogod while attempting to sync Global Address Book:

Apr 15 11:39:31 sogod [21054]: [ERROR] <0x0x56270a5382d0[SQLSource]> could not run SQL 'SELECT c_uid FROM maillists WHERE domain = 'mydomain.com'': <PostgreSQL72Exception: 0x56270b680bb0> NAME:PostgreSQL72FatalError REASON:fatal pgsql error (channel=<0x0x56270c7b54c0[PostgreSQL72Channel]: connection=<0x0x56270bde3f90[PGConnection]:  connection=0x0x56270c66db30>>): ERROR:  permission denied for relation maillists

vmail=# \c sogo
You are now connected to database "sogo" as user "postgres".
sogo=# \d+ maillists
                         View "public.maillists"
   Column   |          Type          | Modifiers | Storage  | Description
------------+------------------------+-----------+----------+-------------
c_uid      | character varying(255) |           | extended |
c_name     | character varying(255) |           | extended |
c_password | character varying(255) |           | extended |
c_cn       | character varying(255) |           | extended |
mail       | character varying(255) |           | extended |
domain     | character varying(255) |           | extended |
View definition:
SELECT maillists.c_uid, maillists.c_name, maillists.c_password, maillists.c_cn,
    maillists.mail, maillists.domain
   FROM dblink('host=127.0.0.1
                           port=5432
                           dbname=vmail
                           user=vmail
                           password==****************'::'::text, 'SELECT address  AS c_uid,
                                  name     AS c_name,
                                  ''       AS c_password,
                                  name     AS c_cn,
                                  address  AS mail,
                                  domain   AS domain
                             FROM mailbox
                            WHERE enablesogo=1 AND active=1'::text) maillists(c_uid character varying(255), c_name character varying(255), c_password character varying(255), c_cn character varying(255), mail character varying(255), domain character varying(255));

sogo=# \c vmail
You are now connected to database "vmail" as user "postgres".
vmail-# SELECT address  AS c_uid,
vmail-#                                   name     AS c_name,
vmail-#                                   ''       AS c_password,
vmail-#                                   name     AS c_cn,
vmail-#                                   address  AS mail,
vmail-#                                   domain   AS domain
vmail-#                              FROM mailbox
vmail-#                             WHERE enablesogo=1 AND active=1;
ERROR:  column "address" does not exist
LINE 1: SELECT address  AS c_uid,
               ^





Apr 15 11:39:31 sogod [21054]: [ERROR] <0x0x56270a537f10[SQLSource]> could not run SQL 'SELECT c_uid FROM aliases WHERE domain = 'mydomain.com'': <PostgreSQL72Exception: 0x56270c69f430> NAME:PostgreSQL72FatalError REASON:fatal pgsql error (channel=<0x0x56270af71f70[PostgreSQL72Channel]: connection=<0x0x56270b29a080[PGConnection]:  connection=0x0x56270b996090>>): ERROR:  permission denied for relation aliases


sogo-# \d+ aliases
                          View "public.aliases"
   Column   |          Type          | Modifiers | Storage  | Description
------------+------------------------+-----------+----------+-------------
c_uid      | character varying(255) |           | extended |
c_name     | character varying(255) |           | extended |
c_password | character varying(255) |           | extended |
c_cn       | character varying(255) |           | extended |
mail       | character varying(255) |           | extended |
domain     | character varying(255) |           | extended |
View definition:
SELECT aliases.c_uid, aliases.c_name, aliases.c_password, aliases.c_cn,
    aliases.mail, aliases.domain
   FROM dblink('host=127.0.0.1
                           port=5432
                           dbname=vmail
                           user=vmail
                           password=****************'::text, 'SELECT address  AS c_uid,
                                  name     AS c_name,
                                  ''       AS c_password,
                                  name     AS c_cn,
                                  address  AS mail,
                                  domain   AS domain
                             FROM mailbox
                            WHERE enablesogo=1 AND active=1'::text) aliases(c_uid character varying(255), c_name character varying(255), c_password character varying(255), c_cn character varying(255), mail character varying(255), domain character varying(255));


How can I correct this problem?

Thank you.

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2

Re: Unable to sync Domain Address book

ehoover wrote:

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release):  2019040201 2019-04-09 10:17:27 iRedMail Easy: https://www.iredmail.org/easy.html

You can use the ticket system on iRedMail Easy web portal. smile

ehoover wrote:

Apr 15 11:39:31 sogod [21054]: [ERROR] <0x0x56270a5382d0[SQLSource]> could not run SQL 'SELECT c_uid FROM maillists WHERE domain = 'mydomain.com'': <PostgreSQL72Exception: 0x56270b680bb0> NAME:PostgreSQL72FatalError REASON:fatal pgsql error (channel=<0x0x56270c7b54c0[PostgreSQL72Channel]: connection=<0x0x56270bde3f90[PGConnection]:  connection=0x0x56270c66db30>>): ERROR:  permission denied for relation maillists

2 issues with this address book:

- Incorrect SQL command used by both SQL VIEWs: aliases, maillists.
- Incorrect owner on SQL VIEWs.

I will roll out a fix on iRedMail Easy soon. Stay tuned.
After rollout, you can simply re-deploy SOGo component to get it fixed.

3

Re: Unable to sync Domain Address book

Hi @ehoover,

This issue has been fixed on iRedMail Easy platform, please fix it by following steps below:

- login to iRedMail Easy web portal
- go to your mail server profile page, click tab "Components"
- click the "Re-Deploy" button besides "SOGo" component.

That's it.

Let me know if it doesn't work for you.

4

Re: Unable to sync Domain Address book

Fixed.  Thank you!