starred/modoboa-modoboa
1
0
Fork 0
mirror of https://github.com/modoboa/modoboa.git synced 2026-04-26 01:16:01 +03:00
Code Issues 60 Projects Releases 10 Packages Wiki Activity

[GH-ISSUE #1702] Postgresql: could not accept SSL connection: Success #1341

New issue
Closed
opened 2026-02-27 11:16:36 +03:00 by kerem · 18 comments
kerem commented 2026-02-27 11:16:36 +03:00
Owner
Copy link

Originally created by @cremesk on GitHub (Mar 20, 2019).
Original GitHub issue: https://github.com/modoboa/modoboa/issues/1702

No bug! Only for security.

Impacted versions

  • Modoboa: 1.3.1
  • installer used: Yes
  • Webserver: Nginx

Steps to reproduce

$ tail -f /var/log/postgresql/postgresql-9.6-main.log
2019-03-20 14:02:36.302 UTC [13497] [unknown]@[unknown] LOG:  could not accept SSL connection: Success
2019-03-20 14:02:36.422 UTC [13503] [unknown]@[unknown] LOG:  could not accept SSL connection: Success
2019-03-20 14:03:07.046 UTC [13599] [unknown]@[unknown] LOG:  could not accept SSL connection: Success
2019-03-20 14:03:07.181 UTC [13605] [unknown]@[unknown] LOG:  could not accept SSL connection: Success
2019-03-20 14:03:38.301 UTC [13619] [unknown]@[unknown] LOG:  could not accept SSL connection: Success
2019-03-20 14:03:38.417 UTC [13625] [unknown]@[unknown] LOG:  could not accept SSL connection: Success
2019-03-20 14:04:09.310 UTC [13765] [unknown]@[unknown] LOG:  could not accept SSL connection: Success
2019-03-20 14:04:09.443 UTC [13771] [unknown]@[unknown] LOG:  could not accept SSL connection: Success
2019-03-20 14:04:40.070 UTC [13785] [unknown]@[unknown] LOG:  could not accept SSL connection: Success
2019-03-20 14:04:40.214 UTC [13791] [unknown]@[unknown] LOG:  could not accept SSL connection: Success

Hey, my Postgresql with default modoboa settings show this log.
How can i fix ssl for postgresql?
Steps:

  • Needs to change Hostname to fqdn (settings.py)?
  • change postgresql settings to currently used letsencrypt cert?

Thanks

/srv/modoboa/instance/instance/settings.py

DATABASES = {
    
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'modoboa',
        'USER': 'modoboa',
        'PASSWORD': 'xxx',
        'HOST': '127.0.0.1',
        'PORT': '',
        'ATOMIC_REQUESTS': True,
        
    },

    'amavis': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'amavis',
        'USER': 'amavis',
        'PASSWORD': 'xxx',
        'HOST': '127.0.0.1',
        'PORT': '',
        'ATOMIC_REQUESTS': True,
        
    },

}

/etc/postgresql/9.6/main/postgresql.conf

# - Security and Authentication -

#authentication_timeout = 1min          # 1s-600s
ssl = true                              # (change requires restart)
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
                                        # (change requires restart)
#ssl_prefer_server_ciphers = on         # (change requires restart)
#ssl_ecdh_curve = 'prime256v1'          # (change requires restart)
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'          # (change requires restart)
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'         # (change requires restart)
#ssl_ca_file = ''                       # (change requires restart)
#ssl_crl_file = ''                      # (change requires restart)
#password_encryption = on
#db_user_namespace = off
#row_security = on
Originally created by @cremesk on GitHub (Mar 20, 2019). Original GitHub issue: https://github.com/modoboa/modoboa/issues/1702 No bug! Only for security. # Impacted versions * Modoboa: 1.3.1 * installer used: Yes * Webserver: Nginx # Steps to reproduce ``` $ tail -f /var/log/postgresql/postgresql-9.6-main.log 2019-03-20 14:02:36.302 UTC [13497] [unknown]@[unknown] LOG: could not accept SSL connection: Success 2019-03-20 14:02:36.422 UTC [13503] [unknown]@[unknown] LOG: could not accept SSL connection: Success 2019-03-20 14:03:07.046 UTC [13599] [unknown]@[unknown] LOG: could not accept SSL connection: Success 2019-03-20 14:03:07.181 UTC [13605] [unknown]@[unknown] LOG: could not accept SSL connection: Success 2019-03-20 14:03:38.301 UTC [13619] [unknown]@[unknown] LOG: could not accept SSL connection: Success 2019-03-20 14:03:38.417 UTC [13625] [unknown]@[unknown] LOG: could not accept SSL connection: Success 2019-03-20 14:04:09.310 UTC [13765] [unknown]@[unknown] LOG: could not accept SSL connection: Success 2019-03-20 14:04:09.443 UTC [13771] [unknown]@[unknown] LOG: could not accept SSL connection: Success 2019-03-20 14:04:40.070 UTC [13785] [unknown]@[unknown] LOG: could not accept SSL connection: Success 2019-03-20 14:04:40.214 UTC [13791] [unknown]@[unknown] LOG: could not accept SSL connection: Success ``` Hey, my Postgresql with default modoboa settings show this log. How can i fix ssl for postgresql? Steps: - Needs to change Hostname to fqdn (settings.py)? - change postgresql settings to currently used letsencrypt cert? Thanks /srv/modoboa/instance/instance/settings.py ``` DATABASES = { 'default': { 'ENGINE': 'django.db.backends.postgresql_psycopg2', 'NAME': 'modoboa', 'USER': 'modoboa', 'PASSWORD': 'xxx', 'HOST': '127.0.0.1', 'PORT': '', 'ATOMIC_REQUESTS': True, }, 'amavis': { 'ENGINE': 'django.db.backends.postgresql_psycopg2', 'NAME': 'amavis', 'USER': 'amavis', 'PASSWORD': 'xxx', 'HOST': '127.0.0.1', 'PORT': '', 'ATOMIC_REQUESTS': True, }, } ``` /etc/postgresql/9.6/main/postgresql.conf ``` # - Security and Authentication - #authentication_timeout = 1min # 1s-600s ssl = true # (change requires restart) #ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers # (change requires restart) #ssl_prefer_server_ciphers = on # (change requires restart) #ssl_ecdh_curve = 'prime256v1' # (change requires restart) ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' # (change requires restart) ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' # (change requires restart) #ssl_ca_file = '' # (change requires restart) #ssl_crl_file = '' # (change requires restart) #password_encryption = on #db_user_namespace = off #row_security = on ```
kerem closed this issue 2026-02-27 11:16:36 +03:00
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@tonioo commented on GitHub (Mar 20, 2019):

@cremesk Is your server loaded?

<!-- gh-comment-id:474867164 --> @tonioo commented on GitHub (Mar 20, 2019): @cremesk Is your server loaded?
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@cremesk commented on GitHub (Mar 20, 2019):

@tonioo yes all work correctly and is online.
But I think more secure is good ;)

<!-- gh-comment-id:474868606 --> @cremesk commented on GitHub (Mar 20, 2019): @tonioo yes all work correctly and is online. But I think more secure is good ;)
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@tonioo commented on GitHub (Mar 20, 2019):

@cremesk By loaded I mean under heavy charge. Maybe not enough resource?

<!-- gh-comment-id:474887691 --> @tonioo commented on GitHub (Mar 20, 2019): @cremesk By loaded I mean under heavy charge. Maybe not enough resource?
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@cremesk commented on GitHub (Mar 20, 2019):

@tonioo my bad. No i think it all okay.

net.netfilter.nf_conntrack_max = 200000, net.netfilter.nf_conntrack_count = 30
net.netfilter.nf_conntrack_max = 200000, net.netfilter.nf_conntrack_count = 20

4 CPU's
load average: 0.51, 0.51, 0.55
free -m
              total        used        free      shared  buff/cache   available
Mem:           6144        1437        4524         583         182        4524
Swap:          4096           0        4096
<!-- gh-comment-id:474901476 --> @cremesk commented on GitHub (Mar 20, 2019): @tonioo my bad. No i think it all okay. ``` net.netfilter.nf_conntrack_max = 200000, net.netfilter.nf_conntrack_count = 30 net.netfilter.nf_conntrack_max = 200000, net.netfilter.nf_conntrack_count = 20 4 CPU's load average: 0.51, 0.51, 0.55 free -m total used free shared buff/cache available Mem: 6144 1437 4524 583 182 4524 Swap: 4096 0 4096 ```
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@tonioo commented on GitHub (Mar 20, 2019):

@cremesk How many users do you have?

<!-- gh-comment-id:474912673 --> @tonioo commented on GitHub (Mar 20, 2019): @cremesk How many users do you have?
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@cremesk commented on GitHub (Mar 20, 2019):

@tonioo

Identities | 54

<!-- gh-comment-id:474913101 --> @cremesk commented on GitHub (Mar 20, 2019): @tonioo Identities | 54
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@cremesk commented on GitHub (Mar 20, 2019):

# - Memory -

shared_buffers = 1024MB                 # min 128kB
                                        # (change requires restart)
#huge_pages = try                       # on, off, or try
                                        # (change requires restart)
#temp_buffers = 8MB                     # min 800kB
#max_prepared_transactions = 0          # zero disables the feature
                                        # (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
work_mem = 32MB                         # min 64kB
maintenance_work_mem = 64MB             # min 1MB
#replacement_sort_tuples = 150000       # limits use of replacement selection sort
#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem
#max_stack_depth = 2MB                  # min 100kB
dynamic_shared_memory_type = posix      # the default is the first option
<!-- gh-comment-id:474914378 --> @cremesk commented on GitHub (Mar 20, 2019): ``` # - Memory - shared_buffers = 1024MB # min 128kB # (change requires restart) #huge_pages = try # on, off, or try # (change requires restart) #temp_buffers = 8MB # min 800kB #max_prepared_transactions = 0 # zero disables the feature # (change requires restart) # Caution: it is not advisable to set max_prepared_transactions nonzero unless # you actively intend to use prepared transactions. work_mem = 32MB # min 64kB maintenance_work_mem = 64MB # min 1MB #replacement_sort_tuples = 150000 # limits use of replacement selection sort #autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem #max_stack_depth = 2MB # min 100kB dynamic_shared_memory_type = posix # the default is the first option ```
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@tonioo commented on GitHub (Mar 20, 2019):

And they all have active IMAP/SMTP connections? Maybe you could tune your postgres config a bit if it is not already done
(https://friendsmeet.win/posts/662)

<!-- gh-comment-id:474914666 --> @tonioo commented on GitHub (Mar 20, 2019): And they all have active IMAP/SMTP connections? Maybe you could tune your postgres config a bit if it is not already done (https://friendsmeet.win/posts/662)
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@cremesk commented on GitHub (Mar 20, 2019):

Not all. ~ 20 connections currently.
I have try to tune my config. Same Issue.

<!-- gh-comment-id:474920395 --> @cremesk commented on GitHub (Mar 20, 2019): Not all. ~ 20 connections currently. I have try to tune my config. Same Issue.
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@cremesk commented on GitHub (Mar 21, 2019):

My postgresql.conf:

uptime 24days load average: 0.36, 0.58, 0.72
Host: 4CPU's | 16GB RAM (used 6,2G) | 8GB SWAP (12KB used) | SSD (Raid1) 200GB (used 41GB)
LXC: Modoboa
4 CPU's | 6GB RAM(used 2,3G) + 4 GB SWAP(used 0)

postgresql.txt

<!-- gh-comment-id:475208028 --> @cremesk commented on GitHub (Mar 21, 2019): My postgresql.conf: uptime 24days load average: 0.36, 0.58, 0.72 Host: 4CPU's | 16GB RAM (used 6,2G) | 8GB SWAP (12KB used) | SSD (Raid1) 200GB (used 41GB) LXC: Modoboa 4 CPU's | 6GB RAM(used 2,3G) + 4 GB SWAP(used 0) [postgresql.txt](https://github.com/modoboa/modoboa/files/2991971/postgresql.txt)
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@tonioo commented on GitHub (Mar 22, 2019):

@cremesk Unfortunately I'm not a postgres expert... @kryskool Do you have any idea?

<!-- gh-comment-id:475581343 --> @tonioo commented on GitHub (Mar 22, 2019): @cremesk Unfortunately I'm not a postgres expert... @kryskool Do you have any idea?
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@cremesk commented on GitHub (Mar 28, 2019):

I take a look in my /etc/postgresql/9.6/main/pg_hba.conf

i need to add and try this line?
hostssl all all 127.0.0.1/32 md5

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer  
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the   
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5
<!-- gh-comment-id:477781317 --> @cremesk commented on GitHub (Mar 28, 2019): I take a look in my /etc/postgresql/9.6/main/pg_hba.conf i need to add and try this line? `hostssl all all 127.0.0.1/32 md5` ``` # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres peer #host replication postgres 127.0.0.1/32 md5 #host replication postgres ::1/128 md5 ```
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@cremesk commented on GitHub (Mar 29, 2019):

I try this. But i have the same issue.

<!-- gh-comment-id:477957700 --> @cremesk commented on GitHub (Mar 29, 2019): I try this. But i have the same issue.
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@cremesk commented on GitHub (Apr 5, 2019):

@tonioo small update:
[unknown]@[unknown] LOG: could not accept SSL connection: Success
Meens we use a plain connection not secured with ssl.

To Setup a SSL-Connection we need this setup:
https://www.postgresql.org/docs/9.6/ssl-tcp.html

<!-- gh-comment-id:480403147 --> @cremesk commented on GitHub (Apr 5, 2019): @tonioo small update: `[unknown]@[unknown] LOG: could not accept SSL connection: Success` Meens we use a plain connection not secured with ssl. To Setup a SSL-Connection we need this setup: [https://www.postgresql.org/docs/9.6/ssl-tcp.html](https://www.postgresql.org/docs/9.6/ssl-tcp.html)
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@kryskool commented on GitHub (Apr 9, 2019):

My postgresql.conf:

uptime 24days load average: 0.36, 0.58, 0.72
Host: 4CPU's | 16GB RAM (used 6,2G) | 8GB SWAP (12KB used) | SSD (Raid1) 200GB (used 41GB)
LXC: Modoboa
4 CPU's | 6GB RAM(used 2,3G) + 4 GB SWAP(used 0)

postgresql.txt

Hi @cremesk

Try:
max_connections = 200
work_mem = 12MB
maintenance_work_mem = 128MB
wal_buffers = 64MB
effective_cache_size = 512MB

To log slow query on postgresql.log

log_min_duration_statement = 100

It's not a good idea to enable SSL on localhost, use it only on remote PostgreSQL Server

Regards,

<!-- gh-comment-id:481341545 --> @kryskool commented on GitHub (Apr 9, 2019): > > > My postgresql.conf: > > uptime 24days load average: 0.36, 0.58, 0.72 > Host: 4CPU's | 16GB RAM (used 6,2G) | 8GB SWAP (12KB used) | SSD (Raid1) 200GB (used 41GB) > LXC: Modoboa > 4 CPU's | 6GB RAM(used 2,3G) + 4 GB SWAP(used 0) > > [postgresql.txt](https://github.com/modoboa/modoboa/files/2991971/postgresql.txt) Hi @cremesk Try: max_connections = 200 work_mem = 12MB maintenance_work_mem = 128MB wal_buffers = 64MB effective_cache_size = 512MB # To log slow query on postgresql.log log_min_duration_statement = 100 It's not a good idea to enable SSL on localhost, use it only on remote PostgreSQL Server Regards,
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@tonioo commented on GitHub (Apr 29, 2019):

@cremesk Is it better now?

<!-- gh-comment-id:487546405 --> @tonioo commented on GitHub (Apr 29, 2019): @cremesk Is it better now?
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@cremesk commented on GitHub (Apr 30, 2019):

sorry for the delay..

Yes it works fine.
I think the "problem" was only:
ssl = true # (change requires restart)

<!-- gh-comment-id:487883744 --> @cremesk commented on GitHub (Apr 30, 2019): sorry for the delay.. Yes it works fine. I think the "problem" was only: `ssl = true # (change requires restart)`
kerem commented 2026-02-27 11:16:37 +03:00
Author
Owner
Copy link

@franpog859 commented on GitHub (Sep 8, 2023):

@cremesk, what exactly was the problem and how did you solve it? Did you remove the ssl = true line/ did you change it to false/ did you restart the service?

<!-- gh-comment-id:1711459639 --> @franpog859 commented on GitHub (Sep 8, 2023): @cremesk, what exactly was the problem and how did you solve it? Did you remove the `ssl = true` line/ did you change it to `false`/ did you restart the service?
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
master
fix/sievefilters-allow-email-in-rule-name
dependabot/npm_and_yarn/frontend/axios-1.15.0
translations_04c0b653215cb5fa48ab101eae1a1917_ja_JP
translations_5d93982e9fd6dab991db4faf93ce9ded_ja_JP
fix/login-issue-3995
fix/sievefilters-issues
add-vitepress-openapi
2974-dmarc-empty-email
2.3.x
updated-doc-2.3.0
feature/OIDC
delete-all-alarms
translations_modoboa-admin-locale-en-lc-messages-djangojs-po--master_af_ZA
translations_modoboa-admin-locale-en-lc-messages-djangojs-po--master_ar
translations_modoboa-admin-locale-en-lc-messages-djangojs-po--master_ca
1.x
translations_modoboa-admin-locale-en-lc-messages-djangojs-po--master_sk
translations_modoboa-admin-locale-en-lc-messages-djangojs-po--master_tr
translations_modoboa-admin-locale-en-lc-messages-djangojs-po--master_es_ES
translations_modoboa-admin-locale-en-lc-messages-djangojs-po--master_uk
translations_modoboa-admin-locale-en-lc-messages-djangojs-po--master_zh_CN
translations_modoboa-admin-locale-en-lc-messages-djangojs-po--master_de_DE
translations_modoboa-admin-locale-en-lc-messages-djangojs-po--master_da_DK
1.8.x
1.7.x
1.6.x
1.4.x
1.3.x
1.2.x
2.8.2
2.8.1
2.8.0
2.7.2
2.7.1
2.7.0
2.6.5
2.6.4
2.6.3
2.6.2
2.6.1
2.6.0
2.5.1
2.5.0
2.4.11
2.4.10
2.4.9
2.4.8
2.4.7
2.4.6
2.4.5
2.4.4
2.4.3
2.4.2
2.4.1
2.4.0
2.3.6
2.3.5
2.3.4
2.3.3
2.3.2
2.3.1
2.3.0
2.3.0-beta.4
2.3.0-beta.3
2.3.0-beta.2
2.3.0-beta.1
2.2.4
2.2.3
2.2.2
2.2.1
2.2.0
2.1.2
2.1.1
2.1.0
2.0.5
2.0.4
2.0.3
2.0.2
2.0.1
2.0.0
2.0.0-beta.3
2.0.0-beta.2
2.0.0-beta.1
1.17.0
1.16.1
1.16.0
1.15.0
1.14.0
1.13.1
1.13.0
1.12.2
1.12.1
1.12.0
1.11.1
1.11.0
1.10.7
1.10.6
1.10.5
1.10.4
1.10.3
1.10.2
1.10.1
1.10.0
1.9.1
1.9.0
1.8.3
1.8.2
1.8.1
1.8.0
1.7.4
1.7.3
1.7.2
1.7.1
1.7.0
1.6.3
1.6.2
1.6.1
1.6.0
1.5.3
1.5.2
1.5.1
1.5.0
1.4.5
1.4.4
1.4.3
1.4.2
1.4.1
1.4.0
1.3.5
1.3.4
1.2.2
1.3.3
1.3.2
1.3.1
1.3.0
1.2.1
1.2.0
1.2.0-rc2
1.2.0-rc1
1.1.7
1.1.6
1.1.5
1.1.4
1.1.3
1.1.2
1.1.1
1.1.0
1.0.1
1.0.0
0.9.5
0.9.4
0.9.3
0.9.2
0.9.1
0.9
0.8.8
0.8.7
0.8.6.1
0.8.6
0.8.5
0.8.4
0.8.3
0.8.3-rc1
0.8.2
0.8.1
0.8
0.8-rc2
0.8-rc1
0.7.2
0.7.1
0.7
0.6.1
0.6
0.5
0.3
0.2
0.1
Labels
Clear labels   
bug

   
bug

   
dependencies

   
design

   
documentation

   
duplicate

   
enhancement

   
enhancement

   
enhancement

   
feedback-needed

   
help-needed

   
help-needed

   
installer

   
invalid

   
looking-for-sponsors

   
modoboa-contacts

   
new-ui

   
new-ui

   
pr

   
pull-request

Mirrored from GitHub Pull Request

  
pyconfr

   
python

   
question

   
security

   
stale

   
webmail

   
wontfix
No labels
bug
bug
dependencies
design
documentation
duplicate
enhancement
enhancement
enhancement
feedback-needed
help-needed
help-needed
installer
invalid
looking-for-sponsors
modoboa-contacts
new-ui
new-ui
pr
pull-request
pyconfr
python
question
security
stale
webmail
wontfix
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
starred/modoboa-modoboa#1341
No description provided.