1 Topic by tinotom (edited by tinotom 2015-05-05 03:19:45)

  • tinotom
  • Member
  • Offline
  • Registered: 2015-05-05
  • Posts: 9

Topic: freebsd + postgresql + amavisd issue

======== Required information ====
- iRedMail version: 0.9.0
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): PGSQL
- Web server (Apache or Nginx): NGINX
- Linux/BSD distribution name and version: freebsd 10.1-STABLE jail
- Related log if you're reporting an issue: May  4 19:06:38 mail postgres[73053]: [11-1] ERROR:  relation "mailaddr" does not exist at character 31
====
Hi,

I've exactly this issue: http://www.iredmail.com/bbs/topic2925-i … resql.html .

It *seems* that the amavisd DBI commands do not connect correctly to the amavisd database before performing the sql query. I've checked and the tables DO exist in the db schema.

Please find below an excerpt of the log:

May  4 19:06:38 mail postgres[73053]: [11-1] ERROR:  relation "mailaddr" does not exist at character 31
May  4 19:06:38 mail postgres[73053]: [11-2] STATEMENT:  SELECT id,priority,email FROM mailaddr WHERE email IN ('@.', 'XXXXXX@XXXXXXX', '@XXXXXX', '@.XXXXXX', '@XXXXXX', '@.XXXXXX', '209.85.215.52', '209.*.*.52', '209.85.*.*', '209.*.215.52', '*.*.215.52', '209.85.*.52', '*.85.215.52', '*.*.*.52', '209.85.215.*', '*.*.*.*', '209.*.*.*') ORDER BY priority DESC
May  4 19:06:38 mail postgres[73062]: [11-1] ERROR:  relation "users" does not exist at character 61
May  4 19:06:38 mail postgres[73062]: [11-2] STATEMENT:  SELECT policy_name,message_size_limit
May  4 19:06:38 mail postgres[73062]: [11-3]                     FROM users, policy
May  4 19:06:38 mail postgres[73062]: [11-4]                     WHERE
May  4 19:06:38 mail postgres[73062]: [11-5]                        (users.policy_id=policy.id)
May  4 19:06:38 mail postgres[73062]: [11-6]                        AND (users.email IN ('tino@XXXXXX', '@XXXXXX', '@XXXXXX', '@.'))
May  4 19:06:38 mail postgres[73062]: [11-7]                     ORDER BY users.priority DESC
May  4 19:06:38 mail postgres[73062]: [11-8]                     

Thank you,

tino

----

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

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,164

Re: freebsd + postgresql + amavisd issue

tinotom wrote:

May  4 19:06:38 mail postgres[73053]: [11-1] ERROR:  relation "mailaddr" does not exist at character 31

It says SQL table "amavisd.mailaddr" doesn't exist. looks like your iRedMail installation was not successfully completed.

*) Do you have SQL database "amavisd"?
*) What tables do you have in database "amavisd"?

3 Reply by tinotom

  • tinotom
  • Member
  • Offline
  • Registered: 2015-05-05
  • Posts: 9

Re: freebsd + postgresql + amavisd issue

Hi,

thank you for your feedback ( and for bringing to us iRedMail ).

I am not aware I got errors from the installation/configuration. Everything seems to work smoothly ( sending/receiving emails, roundcube and so on ) but I get this error ( also on the "users" relation ).
With respect to the database:

% psql -h 192.168.2.233 postgres
psql (9.3.6)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=# \l
                              List of databases
     Name      |    Owner    | Encoding | Collate | Ctype | Access privileges 
---------------+-------------+----------+---------+-------+-------------------
 amavisd       | amavisd     | UTF8     | C       | C     | 
 cluebringer   | cluebringer | UTF8     | C       | C     | 
 iredadmin     | iredadmin   | UTF8     | C       | C     | 
 postgres      | pgsql       | UTF8     | C       | C     | 
 roundcubemail | pgsql       | UTF8     | C       | C     | 
 template0     | pgsql       | UTF8     | C       | C     | =c/pgsql         +
               |             |          |         |       | pgsql=CTc/pgsql
 template1     | pgsql       | UTF8     | C       | C     | =c/pgsql         +
               |             |          |         |       | pgsql=CTc/pgsql
 vmail         | vmail       | UTF8     | C       | C     | 
(8 rows)

postgres=#

and :

postgres=# \c amavisd 
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
You are now connected to database "amavisd" as user "pgsql".
amavisd=# \d
               List of relations
 Schema |      Name       |   Type   |  Owner  
--------+-----------------+----------+---------
 public | maddr           | table    | amavisd
 public | maddr_id_seq    | sequence | amavisd
 public | mailaddr        | table    | amavisd
 public | mailaddr_id_seq | sequence | amavisd
 public | msgrcpt         | table    | amavisd
 public | msgs            | table    | amavisd
 public | policy          | table    | amavisd
 public | policy_id_seq   | sequence | amavisd
 public | quarantine      | table    | amavisd
 public | users           | table    | amavisd
 public | users_id_seq    | sequence | amavisd
 public | wblist          | table    | amavisd
(12 rows)

amavisd=# \dt mailaddr 
          List of relations
 Schema |   Name   | Type  |  Owner  
--------+----------+-------+---------
 public | mailaddr | table | amavisd
(1 row)

amavisd=# select * from mailaddr ;
 id | priority | email 
----+----------+-------
(0 rows)

amavisd=#

and same for the "users" relation.
I'm available if others details/tries are needed.

Thank you,

4 Reply by stannicek (edited by stannicek 2015-05-05 15:08:03)

  • stannicek
  • Member
  • Offline
  • Registered: 2015-01-06
  • Posts: 35

Re: freebsd + postgresql + amavisd issue

Hi.

Please, check link below. The problem is the same.

http://www.iredmail.org/forum/topic9074 … ugins.html

Also make sure, your /opt/iRedAPD-1.X.X/settings.py contains :

amavisd_db_server = '127.0.0.1'
amavisd_db_port = '5432'
amavisd_db_name = 'amavisd'
amavisd_db_user = 'amavisd'
amavisd_db_password = 'xxxxxx'

Best Regards,
Stanislav

5 Reply by tinotom

  • tinotom
  • Member
  • Offline
  • Registered: 2015-05-05
  • Posts: 9

Re: freebsd + postgresql + amavisd issue

Hi Stanislav,

you are right: that was the point.
Thank you very much!

Best,

tino