PostgreSQL cannot read certificate files

Server
1

Fedora here is log output:

Nov 05 22:20:36 melissa.colmena.biz systemd[1]: Starting PostgreSQL database server...                                                                                                          
Nov 05 22:20:37 melissa.colmena.biz postgresql-ctl[11790]: FATAL:  could not load server certificate file "/etc/letsencrypt/live/melissa.colmena.biz/fullchain.pem": Permission denied          
Nov 05 22:20:37 melissa.colmena.biz postgresql-ctl[11790]: LOG:  database system is shut down                                                                                                   
Nov 05 22:20:38 melissa.colmena.biz postgresql-ctl[11790]: pg_ctl: could not start server                                                                                                       
Nov 05 22:20:38 melissa.colmena.biz postgresql-ctl[11790]: Examine the log output.                                                                                                              
Nov 05 22:20:38 melissa.colmena.biz systemd[1]: postgresql.service: Control process exited, code=exited status=1                                                                                
Nov 05 22:20:38 melissa.colmena.biz systemd[1]: Failed to start PostgreSQL database server.                                                                                                     
Nov 05 22:20:38 melissa.colmena.biz systemd[1]: postgresql.service: Unit entered failed state.                                                                                                  
Nov 05 22:20:38 melissa.colmena.biz systemd[1]: postgresql.service: Failed with result 'exit-code'.

Why doesn’t PostgreSQL read the certificate file as root on startup before dropping privileges to user “postgres”? I am not sure I am comfortable allowing user “postgres” to read the private key file… I may use nginx instead to tunnel the connection over ssl. Are there any thoughts or general suggestions?

2

The fullchain.pem file doesn't include the private key.

3

That's probably something you should ask the developers of PostgreSQL, right?

4

No. I'm just making Republican database jokes because of the PostgreSQL elephant logo.

Really. This is frustrating.
I need to do something like

[root@melissa ~]# chgrp postgres /etc/letsencrypt/live/
[root@melissa ~]# chmod g+rx /etc/letsencrypt/live/

in order to fix those grandparent permission problems AND

[root@melissa ~]# chgrp postgres /etc/letsencrypt/archive/
[root@melissa ~]# chmod g+rx /etc/letsencrypt/archive/

AND, yes, I know EFF and NSA are not best of buddies or anything like that, but even those permissions are not enough, because PostgreSQL cannot read files that are not of NSA SELinux security context type "postgresql_db_t" and the command to change this in Fedora is

[root@melissa ~]# chcon -t postgresql_db_t some_file_or_other

but then I don't know if nginx and postfix will be able to read these files if they are no longer of security context type "etc_t" ...

5

You could use a --deploy-hook script to copy the files into an appropriate directory for postresql’s use with the expected ownership, permissions and context. See https://certbot.eff.org/docs/using.html#renewing-certificates for an example (read from “For example, if you have a daemon that does not read its certificates as the root user…” - it’s not specifically for postgresql/selinux but it gives the general idea). That way you also don’t interfere with nginx and postfix because you’re operating on copies, rather than the original files.

6

Much better answer. That will help me fix those permission and SELinux security context problems. Yes, I tried to change the security context to “postgresql_db_t” and I did get the database to start but then nginx can no longer read those files because it runs in the context "httpd_t" which is not allowed to read files of type "postgresql_db_t"

7

Well, you asked a much better question! :stuck_out_tongue:

That's why I'm suggesting to make a copy - and change the security context etc. of the copy to allow postgresql to access it. Then nginx can still read the original files as their context will not have changed.

1 Like
8

I generally think a review of how permissions work is in order here

A) I don’t want to let Postgres access my private keys - how do you suppose TLS encryption works without those?

B) The live directory can contain multiple certificate chains. The live directory is a symlink to the latest certificate in the archive folder for a certificate chain

C) Your approach to security seems intersting - are you sharing a certificate for web and postgres access if so why not just generate one for each

D) You can specify a directory outside the normal chain for specific certificate

E) You can then give postgres user (and the root user) access to that directory

F) You should aim for separating concern. If you don’t have a seprate subdomian for your database you can have two certificates with the same subdomain name

This saves mucking around with post hook scripts etc

Just my 5 cents

Andrei

9

Very much so. We have public and private keys -- only the symlinks to them are stable -- and access to them (as installed by certbot) is controlled by discretionary permissions on the grandparent directories, namely, owner root, group root, mode 0700 for

/etc/letsencrypt/live/
/etc/letsencrypt/archive/

Files in the /etc/ hierarchy are automatically assigned SELinux security context type "etc_t". PostgreSQL runs as user "postgres" group "postgres" and does not have the mandatory access control permissions to read files of type "etc_t".

Changing the SELinux security context type of the files to make them readable by PostgreSQL makes them unreadable by other programs such as web servers which are not supposed to be allowed under MAC policy to read raw database files directly, but only by connecting to the database.

The suggestion of a separate certificate for the database is not completely unreasonable, except that I like to be economical: while they are apparently free at "letsencrypt", some providers charge for them.

The "deploy hook" will have to set umask, copy the key and certificate chain over, change the owner:group to "postgres:postgres", and set the SELinux mandatory access control security context type to “postgresql_db_t”.

It is not that I don't trust PostgreSQL or that I don't want user "postgres" to have access to the keys; it is just that this is the trouble I must go through to make that happen, without allowing unauthorized access to the keys.

Getting a separate certificate for the database would not do anything to make it easier to make the keys readable by the database server.

EDIT: THIS HAS BEEN TAKEN OUT OF CONTEXT. DO NOT FOLLOW THESE INSTRUCTIONS. USE THE DEPLOY HOOK IN MY FOLLOWING POST.

10

Now for the deploy hook:

/etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy

#!/bin/bash
umask 0177
cp /etc/letsencrypt/live/melissa.colmena.biz/fullchain.pem /var/lib/pgsql/data/server.crt
cp /etc/letsencrypt/live/melissa.colmena.biz/privkey.pem /var/lib/pgsql/data/server.key
chown postgres:postgres /var/lib/pgsql/data/server.crt /var/lib/pgsql/data/server.key
chcon -t postgresql_db_t /var/lib/pgsql/data/server.crt /var/lib/pgsql/data/server.key

Of course you have to replace “melissa.colmena.biz” with your own server’s domain name.
Don’t forget

# chmod +x /etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy
2 Likes
11

The connection string for my root database is then

postgresql://db.colmena.biz/postgres?sslmode=verify-full

I was using the LibreOffice PostgreSQL connector, which expected the SSL PKI root certificate bundle to be located at ~/.postgresql/root.crt in order to actually verify that the certificate is valid before connecting, so I had to do this.

[justina@localhost ~]$ mkdir .postgresql                                                                                                                                                        
[justina@localhost ~]$ ln -s /etc/pki/ca-trust/extracted/openssl/ca-bundle.trust.crt .postgresql/root.crt

I am adding this information just as an example of neat things you can do with ssl certificates…

12

cool

Not sure what Root program LibreOffice Use but the DST Root Cert (which is what Let’s Encrypt chains to) is pretty widely trusted.

Also for speed you may want to narrow down the root certs you load. The DST root cert can be found here https://letsencrypt.org/certificates/

:smiley: Thanks for sharing

I will post how to get certbot to dump the certs to a specific directory later on as another way of achieving the same goal

Andrei

13

For ultimately narrowing down trust, of course, you cannot beat a self-signed certficate, in which case you simply install your own self-signed root certificate at

~/.postgresql/root.crt

Then you do not even need to specify “sslmode=verify-full”

https://www.postgresql.org/docs/current/static/libpq-connect.html

and “sslmode=verify-ca” will suffice because in that case your own root cert is the only possible authority for your own self-signed certificates and it will not throw errors if you connect by IP address or non-fully-qualified hostname, etc.

Very simple and secure, if a bit tedious in the technical details, and nothing wrong with that at all, except that as your business network grows, then at some point you do have to move to a more general “widely trusted” system, and depend on law enforcement and projects like https://www.eff.org/observatory to catch forgers. Nothing is perfect.

1 Like
14

More at this particular point:

Specifically the LibreOffice Base PostreSQL connector, installed as a separate package in Fedora, uses whatever root cert it finds at "~/.postgresql/root.crt" unless another location is specified on the query string of the PostgreSQL connection URL. That is why I symlinked a standard vanilla root bundle there, and that is actually (according to the documentation) the standard location used by the PostgreSQL client connection library that any PostgreSQL client will use.

1 Like
15

No, it does not. However the private key does reside in the same directory (folder) with the same permissions. PostgreSQL is apparently not even attempting to load the private key file if it cannot load the server certificate first.

16

So do you want PostgreSQL to have access to the private key or not?
If it is going to do any encryption/decryption via the public cert it will have to have the private key.

17

That is quite true, and that is what I ended up doing. I copied the key over with umask 0177, changed the owner and group to postgres, and set the security context. Trying to give PostgreSQL access to the key without copying it to /var/lib/pgsql/data and giving it the appropriate security context is next to impossible without messing a lot of other stuff up when SELinux is enabled.

1 Like
18

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.