Postgres - Support for forcing SSL and Certificate Validation settings. (sslmode=require or stronger)

[CallMeTerdFerguson]

I had reason to go ahead and stand up a Postgres instance for another purpose recently, so decided to migrate my paperless over to postgres from sqlite. In setting up paperless, I realized that my configuration (and preference) for my postgres instance is to force SSL (just encryption right now, not the full on bi-directional cert verification handshake, though support for that would be good too). Is there a configuration somewhere I am missing to let Paperless know it should use SSL for the db connection? If not, is it possible to add that to the roadmap? For the time being I've added an entry to pg_hba.conf to allow non-SSL connections to just the paperless DB, but would prefer all traffic to/from the postgres server be encrypted long term, especially since data from paperless could be sensitive in nature.

[jonaswinkler]

There's a parameter in libpq called sslmode (https://www.postgresql.org/docs/13/libpq-ssl.html). Would the ability to provide a value for that in paperless cover your needs?

[CallMeTerdFerguson]

You beat me to it, was just looking for what would need changed... You are correct. For what I care most about (forcing SSL), yes I think that should do it. Being able to specify sslmode=require or sslmode=prefer would allow you to ensure or allow, respectively, that you are at least using SSL.

If you wanted to enable the MITM protection that I referred to earlier using the certificate based handshake, you would need both to be able to specify sslmode=verify-ca or sslmode=verify-full and to adjust any parameters needed to ensure that the user the process is running under has an accessible ~/.postgresql directory to put the required certs/keys in. I'm guessing this wouldn't matter much for the bare bones install since the end user has control there but it may impact the docker based image if it's running under a home-less user. For me personally, the cert verification is a nice to have as compared to the SSL encryption, as I have better confidence against a MITM attack on my small home network than I do against rogue processes eavesdropping packets on the network.

[C0nsultant]

Have been using NG with postgres and a valid SSL cert in production for weeks now, works like a charm. The connection is SSL by default. No special care needs to be taken other than setting ssl = on, ssl_cert_file and ssl_key_file in postgresql.conf. Agree that an option to verify/force it from NG is a good idea. If you're paranoid enough, you can also force SSL from within postgres (hostssl in pg_hba.conf).

root@postgresql:~# sudo -u postgres psql
psql (11.9 (Debian 11.9-0+deb10u1))
Type "help" for help.

postgres=# SELECT datname,usename, ssl, client_addr 
postgres-#   FROM pg_stat_ssl
postgres-#   JOIN pg_stat_activity
postgres-#     ON pg_stat_ssl.pid = pg_stat_activity.pid;
   datname   |   usename   | ssl | client_addr  
-------------+-------------+-----+--------------
 ...
 paperlessng | paperlessng | t   | A.B.C.D
 ...
 paperlessng | paperlessng | t   | A.B.C.D
 ...
 paperlessng | paperlessng | t   | A.B.C.D
 ...
 paperlessng | paperlessng | t   | A.B.C.D
 ...
(37 rows)

[CallMeTerdFerguson]

Have been using NG with postgres and a valid SSL cert in production for weeks now, works without a charm. The connection is SSL by default. No special care needs to be taken other than setting ssl = on, ssl_cert_file and ssl_key_file in postgresql.conf.

Would you mind sharing what you have in your pg_hba.conf? Below is how I had to set mine up. Previously I did not have the last line and I got connection errors when attempting to connect from paperless because no non-ssl option existed, which was my desire. Only after adding the last line allowing paperless to connect WITHOUT SSL was I able to connect. I have other applications connecting properly as configured, but paperless didn't want to use an SSL connection for me for some reason. Also, are you running paperless from docker or bare metal? If bare metal, on the same physical machine as the DB?

//local host entries cut for brevity
hostssl     all              all                   all            scram-sha-256
host    paperless    paperless  all     scram-sha-256
`

[C0nsultant]

There's nothing special about the configuration in pg_hba.conf:

host paperlessng paperlessng A.B.C.D/32   md5

For the sake of it, I just tried the same thing but used hostssl. That also works. Both are actually using SSL when connected. (I should probably upgrade to hostssl and scram-sha-256... oh well :sweat_smile:)

Also, are you running paperless from docker or bare metal?

Not using docker, it's running inside an LXC container using the bare-metal setup.

If bare metal, on the same physical machine as the DB?

NG and the postgres server are not running on the same host.

[CallMeTerdFerguson]

Hmmm... seems I've some more digging to do then. Our configurations seem very similar but the behavior I get is different. The only difference for me is I'm running inside of docker, though I'm not sure what difference that might make off hand...

[C0nsultant]

I'm not familiar with the docker setup at all, but there should not be difference between the two when it comes to database connections. Can you provide context to the error? Both sides should report something. Using DEBUG when starting NG may help identify the problem.

[CallMeTerdFerguson]

I managed to resolve the issue.

For posterity's sake, the error I was getting was "no pg_hba.conf entry for host"x.x.x.x", user"x", database"x", SSL off". Despite the fact that the default rule that the postgres docker adds to pg_hba.conf is host all all all md5, matching broke for me when I updated that to hostssl all all all md5 with paperless but not other consumers where I am able to force SSL. For some reason hostssl doesn't match on ADDRESS all the same way that host does when SSL is set to accept instead of require. Don't ask me why, based on the postgres documentation, it seems as though it should. Altering the rule to be hostssl all all 0.0.0.0/0 md5 and hostssl all all ::/0 md5 for ipv4 and ipv6 respectively fixes the problem. Ironically, the all addresses were placeholders while I got things running and I had planned to restrict the CIDR's anyway, but since I expected the rule to match on anything SSL, I mistakenly presumed that paperless was not able to connect via SSL.

As far as this issue goes, I'll update the title to reflect the more accurate ask of allowing end users to optionally force SSL and Certificate Verification.

[jonaswinkler]

Fyi, the default value for sslmode is prefer, which will use SSL if available.

[CallMeTerdFerguson]

Yes, I was aware of that, though never dug into the paperless code to see how you were calling and, since other apps where I could force SSL use were working with my postgres instance, I mistakenly assumed that paperless was configured not to allow SSL connections to the DB at all. That was my bad, as I definitely assigned blame for my issue that belonged with postgres to paperless. Apologies. Thanks again for your work on this app and your super responsive willingness to take suggested enhancements!