sql delete problem
Tóth Csaba
tsabi at tsabi.hu
Sun Jan 24 17:40:15 UTC 2016
Dear List!
I have a dovecot installment with postgresql based user management.
My problem is i see this in postgresql's log file:
root at c13:/var/lib/postgresql/9.4/main/pg_log# cat
postgresql-2016-01-24_12.log | grep 26105
2016-01-24 16:34:43 CET [26105-1] maildata at mail ERROR: permission
denied for relation i3sysadm_email_account
2016-01-24 16:34:43 CET [26105-2] maildata at mail STATEMENT: DELETE FROM
i3sysadm_email_account WHERE userid = 'account at mydomain.com'
This is the error message from dovecot side (i have a lot, so this is
from another date):
root at c10:/var/log/dovecot# cat mail.log | grep "Jan 24 17:41:29"
Jan 24 17:41:29 dict: Info: pgsql(127.0.0.1): Disconnected from
database, retrying commit
Jan 24 17:41:29 dict: Error: sql dict: commit failed: ERROR: permission
denied for relation i3sysadm_email_account (query: DELETE FROM
i3sysadm_email_account WHERE userid = 'account at mydomain.com')
Why the hack dovecot want to delete my e-mail account?
Luckily it don't have the permission..
I tried to look in the source code for "delete from", but founded just a
general sql handle method.
One possiblity is dovecot try to delete a cascaded information, but what
can it be?
Please help, how can i fix this.
Thanks in advance,
Csaba
root at c10:/var/log/dovecot# dovecot --version
2.2.9
root at c10:/var/log/dovecot# dovecot -n
# 2.2.9: /etc/dovecot/dovecot.conf
# OS: Linux 3.16.0-44-generic x86_64 Ubuntu 14.10
auth_cache_negative_ttl = 0
auth_failure_delay = 3 secs
auth_master_user_separator = *
auth_mechanisms = plain login
auth_socket_path = /var/run/dovecot/auth-userdb
base_dir = /var/run/dovecot/
dict {
expire = sqlite:/etc/dovecot/dovecot-dict-sql.conf.ext
quota = pgsql:/etc/dovecot/dovecot-dict-sql.conf.ext
}
disable_plaintext_auth = no
first_valid_uid = 5000
hostname = mail.mydomain.com
lda_mailbox_autocreate = yes
listen = 87.229.104.6, ::
lmtp_rcpt_check_quota = yes
log_path = /var/log/dovecot/mail.log
login_log_format_elements = user=<%u> method=%m rip=%r lip=%l mpid=%e %c
login_trusted_networks = 192.168.0.0/24
mail_access_groups = dovenull
mail_gid = dovenull
mail_location = sdbox:~/sdbox
mail_plugins = quota
mail_uid = dovenull
managesieve_notify_capability = mailto
managesieve_sieve_capability = fileinto reject envelope
encoded-character vacation subaddress comparator-i;ascii-numeric
relational regex imap4flags copy include variables body enotify
environment mailbox date ihave
namespace inbox {
inbox = yes
location =
mailbox Drafts {
auto = subscribe
special_use = \Drafts
}
mailbox Junk {
auto = subscribe
special_use = \Junk
}
mailbox Sent {
auto = subscribe
special_use = \Sent
}
mailbox "Sent Messages" {
special_use = \Sent
}
mailbox Trash {
auto = subscribe
special_use = \Trash
}
prefix =
}
passdb {
args = /etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
plugin {
acl = vfile:/etc/dovecot/global-acls:cache_secs=300
acl_shared_dict = file:/var/lib/dovecot/shared-mailboxes
quota = dict:User quota::proxy::quota
quota_grace = 10%%
quota_rule = *:storage=100M
recipient_delimiter = +
sieve = ~/.dovecot.sieve
sieve_dir = ~/sieve
}
protocols = " imap lmtp sieve pop3"
service auth-postlogin {
executable = script-login /etc/dovecot/update_date_smtp.sh
user = $default_internal_user
}
service auth-worker {
unix_listener auth-worker {
group = $default_internal_user
mode = 0660
user = dovecot
}
user = $default_internal_user
}
service auth {
executable = auth auth-postlogin
unix_listener /var/spool/postfix/private/dovecot-auth {
group = postfix
mode = 0660
user = postfix
}
user = $default_internal_user
}
service dict {
unix_listener dict {
group = $default_internal_user
mode = 0666
user = $default_internal_user
}
}
service imap-login {
inet_listener imap {
port = 143
}
inet_listener imaps {
port = 993
ssl = yes
}
}
service imap-postlogin {
executable = script-login /etc/dovecot/update_date_imap.sh
user = $default_internal_user
}
service imap {
executable = imap imap-postlogin
}
service lmtp {
executable = lmtp -L
inet_listener lmtp {
address = 127.0.0.1
port = 24
}
unix_listener /var/spool/postfix/private/dovecot-lmtp {
group = postfix
mode = 0600
user = postfix
}
}
service pop3-login {
inet_listener pop3 {
port = 110
}
inet_listener pop3s {
port = 995
ssl = yes
}
}
service pop3-postlogin {
executable = script-login /etc/dovecot/update_date_pop3.sh
user = $default_internal_user
}
service pop3 {
executable = pop3 pop3-postlogin
}
shutdown_clients = no
ssl_cert = </etc/dovecot/dovecot.pem
ssl_cipher_list =
ALL:!LOW:!SSLv2:ALL:!aNULL:!ADH:!eNULL:!EXP:RC4+RSA:+HIGH:+MEDIUM
ssl_key = </etc/dovecot/private/dovecot.pem
ssl_protocols = !SSLv2 !SSLv3
submission_host = 127.0.0.1:25
userdb {
args = /etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
verbose_proctitle = yes
protocol lmtp {
info_log_path = /var/log/dovecot/lmtp.log
mail_plugins = quota quota sieve
postmaster_address = postmaster at mydomain.com
}
protocol lda {
mail_plugins = quota sieve
}
protocol imap {
imap_client_workarounds = delay-newmail
mail_max_userip_connections = 10
mail_plugins = quota imap_quota
}
protocol pop3 {
mail_max_userip_connections = 10
pop3_client_workarounds = outlook-no-nuls oe-ns-eoh
}
root at c10:/etc/dovecot# grep -v '^ *\(#.*\)\?$' dovecot-sql.conf.ext
driver = pgsql
connect = host=127.0.0.1 port=5432 dbname=mail user=maildata password=*
default_pass_scheme = SSHA512
user_query = SELECT userdb_home AS home, userdb_uid AS uid, userdb_gid
AS gid, \
mail_location AS mail, quota_rule \
FROM i3sysadm_email_account WHERE userid = '%Lu' AND active is true
AND active_domain is true
password_query = SELECT userid AS user, password, \
userdb_home, userdb_uid, userdb_gid, \
mail_location, quota_rule \
FROM i3sysadm_email_account WHERE userid = '%Lu' \
and active IS true AND active_domain IS true
iterate_query = SELECT userid FROM i3sysadm_email_account \
WHERE active IS true AND active_domain IS true
More information about the dovecot
mailing list