PostgreSQL connection to RDS with enforced SSL fails on permissions to certificates

[ignatenkobrain]

Current Behavior

I've configured netbox-docker to connect to the RDS PostgreSQL, and it fails to connect there (surprisingly migrations worked without issue).

... /root/.postgresql/postgresql.crt: Permission denied
connection to server at "netbox.cwfyakept1w9.us-east-1.rds.amazonaws.com" (172.16.255.153), port 5432 failed: FATAL: no pg_hba.conf entry for host "172.16.254.223", user "netbox", database "netbox", no encryption

Setting rds.force_ssl to 0 in RDS solves the issue but in such case SSL is not used which is 😿

Expected Behavior

No issues with permissions and SSL is used by default.

Docker Compose Version

N/A

Docker Version

N/A

The git Revision

N/A

The git Status

N/A

Startup Command

N/A

NetBox Logs

N/A

Content of docker-compose.override.yml

N/A

[cimnine]

My suspicion:
By default, the container does not run as root. Therefore it can't read from the home-directly of the root user, which is /root.

Therefore:
Put your certificates where the user can read them.

[ignatenkobrain]

I'm not sure if the solution is like you describe because I don't put any certificates as the RDS uses SSL certificate signed by AWS that is part of the trusted ca certificates. That means (IIUC) either postgres python module or something else does not respect ca certificates or they are not present or … not sure.

[justinTM]

having this same issue. solutions here have said to include client crt and key in ~/.postgresl but AWS docs say to skip these so idk

[marsteel]

Here is my workaround.

My Dockerfile

FROM netboxcommunity/netbox:v3.7.6-2.8.0

USER root
RUN curl -LJO https://truststore.pki.rds.amazonaws.com/eu-west-1/eu-west-1-bundle.pem -o /tmp/eu-west-1-bundle.pem
RUN mkdir -p /root/.postgresql
RUN curl  https://truststore.pki.rds.amazonaws.com/eu-west-1/eu-west-1-bundle.pem -o /root/.postgresql/postgresql.crt
RUN chmod -R 777 /root

For PEM files in other regions, https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html#UsingWithRDS.SSL.RegionCertificates

[jlrgraham]

Just wanted to add in here that this is not specific to RDS, this applies to my setup as well that is just a vanilla PostgreSQL host (nothing on AWS).

There seems to be something out of order with the way the process is getting started -- if we're not running as root then we shouldn't be trying to read the contents of /root for configuration, it's like the root user's environment is getting passed to the unit user in startup.

The error here also seems to be more that we hard fail in attempting to load an optional certificate file -- for example if we just add global execute (ie: chmod +x /root) to the root home then the process correctly sees that no /root/.postgresql exists and falls back to looking at the system or virtual environment provided certificate roots (which then succeeds as expected). That is, we're getting a hard fail here due to a permission denied exception when really the file doesn't exist and this is fine for normal operation.

[jlrgraham]

Just to follow on here, I ended up working around this by patching the existing DATABASE config object to point the sslcert value for the PostgreSQL connection arguments to a non-existent location that won't return a permission denied.

Using an extra.py configuration file of:

from netbox.configuration.configuration import DATABASE
DATABASE['OPTIONS']['sslcert'] = '/nonexistent/.postgresql/postgresql.crt'