I'm using psycopg2 inside an AWS lambda function and would like to connect to cockroachdb.

To connect locally I just add my cockroackdb (same for vanilla postgres) root.crt file to my machine in the default $HOME/.postgresql/root.crt location.

However, since I'd like to connect via a Lambda function, I'd rather not be deal with a file and instead use an ENV variable configured at the function level in AWS.

I've searched around and could not find a solution to specify because it appears that typically psycopg2 sslrootcert expects a file path.

Ideally I'd like to be able to do something like this:

cert = base64.b64decode(os.environ["ROOT_CRT"]).decode("ascii")

conn=psycopg2.connect(
      dbname="test",
      user="postgres",
      password="secret",
      host="127.0.0.1",
      port="5432",
      sslrootcert=cert)

Where sslrootcert= is pointing to a python variable based on the ENV variable where the cert was pasted to bypass a file based approach.

Any suggestions?

CodePudding user response：

If you're connecting to a Serverless Cluster, you might not need to specify an SSL root cert unless psycopg2 requires it. Serverless clusters utilize the system root certificates to make it a bit easier to connect here.

If you're using a non-serverless cluster, or it's required by the psycopg2 adapter it might be pretty rigid to specify a file path as the libpq library that psycopg2 wraps has a file path definition in its docs. Lambda functions have some storage in the /tmp directory, so you might be able to potentially write the certificate from the environment variable on initialization of the app, and use the filepath that it creates there.