Prisma1: prisma not establishing secure connection when sslkey and sslcert being provided

Created on 9 Oct 2018 · 5Comments · Source: prisma/prisma1

Describe the bug
Can't connect to postgres database (on google cloud) when only secured connections are allowed to connect to this instance is enabled.

To Reproduce
Steps to reproduce the behavior:

Create an ssl protected postgres database and retrieve key-file and cert-file (e.g. on cloud.google.com)
Create Dockerfile

FROM prismagraphql/prisma:1.17

COPY ./client-cert.crt /app/client-cert.crt
COPY ./client-key.key /app/client-key.key

# change permissions
RUN chmod 0600 /app/client-key.key
RUN chmod 0600 /app/client-cert.crt

# set env vars as described here https://www.postgresql.org/docs/9.6/static/libpq-ssl.html
ENV PGSSLCERT=/app/client-cert.crt
ENV PGSSLKEY=/app/client-key.key

set ssl: true for PRISMA_CONFIG
start container

Expected behavior
Connection is being establied

Versions (please complete the following information):

OS: OSX Mojave
prisma CLI: prisma/1.17.1 (darwin-x64) node-v8.11.3
Prisma Server: 1.17.0

* ERROR *

prisma_1  | Oct 09, 2018 4:35:40 PM org.postgresql.core.v3.ConnectionFactoryImpl log
prisma_1  | WARNING: SQLException occurred while connecting to XXX.XXX.XXX.XXX:5432
prisma_1  | org.postgresql.util.PSQLException: FATAL: connection requires a valid client certificate
prisma_1  |     at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:473)
prisma_1  |     at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:205)
prisma_1  |     at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
prisma_1  |     at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
prisma_1  |     at org.postgresql.Driver.makeConnection(Driver.java:452)
prisma_1  |     at org.postgresql.Driver.connect(Driver.java:254)
prisma_1  |     at slick.jdbc.DriverDataSource.getConnection(DriverDataSource.scala:101)
prisma_1  |     at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:341)
prisma_1  |     at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:193)
prisma_1  |     at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:430)
prisma_1  |     at com.zaxxer.hikari.pool.HikariPool.access$500(HikariPool.java:64)
prisma_1  |     at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:570)
prisma_1  |     at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:563)
prisma_1  |     at java.util.concurrent.FutureTask.run(FutureTask.java:266)
prisma_1  |     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
prisma_1  |     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
prisma_1  |     at java.lang.Thread.run(Thread.java:748)

prisma_1  | Oct 09, 2018 4:35:40 PM org.postgresql.Driver connect
prisma_1  | SEVERE: Connection error:
prisma_1  | org.postgresql.util.PSQLException: FATAL: connection requires a valid client certificate
prisma_1  |     at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:473)
prisma_1  |     at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:205)
prisma_1  |     at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
prisma_1  |     at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
prisma_1  |     at org.postgresql.Driver.makeConnection(Driver.java:452)
prisma_1  |     at org.postgresql.Driver.connect(Driver.java:254)
prisma_1  |     at slick.jdbc.DriverDataSource.getConnection(DriverDataSource.scala:101)
prisma_1  |     at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:341)
prisma_1  |     at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:193)
prisma_1  |     at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:430)
prisma_1  |     at com.zaxxer.hikari.pool.HikariPool.access$500(HikariPool.java:64)
prisma_1  |     at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:570)
prisma_1  |     at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:563)
prisma_1  |     at java.util.concurrent.FutureTask.run(FutureTask.java:266)
prisma_1  |     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
prisma_1  |     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
prisma_1  |     at java.lang.Thread.run(Thread.java:748)

kinfeature areconnectopostgres areengine

Source

passionkind

Most helpful comment

I think I've found a solution that works in this case. Since I'm using a postgres database from Google Cloud SQL, Google offers an SQL-Proxy.

by changing my Dockerfile to

FROM prismagraphql/prisma:1.17

COPY ./prerun_hook.sh /app/prerun_hook.sh
RUN chmod +x /app/prerun_hook.sh

WORKDIR /
RUN wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
RUN chmod +x cloud_sql_proxy

CMD /app/start.sh

and have the following in the prerun_hook.sh

#! /bin/bash

echo ${GOOGLE_SQL_PROXY_KEYFILE} > /keyfile.json
/cloud_sql_proxy -instances=${GOOGLE_SQL_PROXY_INSTANCE}=tcp:localhost:5432 -credential_file=/keyfile.json &

where GOOGLE_SQL_PROXY_KEYFILE is the contents of the keyfile for the cloud proxy and the instance name is my postgres instance name

I can spin up the google sql proxy on 5432 locally and prisma can connect to it. The connection is then secure.

more information on cloud proxy here

however, I'd be super happy if you could take a look at it and let me know what you think about this solution

passionkind on 9 Oct 2018

👍3

All 5 comments

I think I've found a solution that works in this case. Since I'm using a postgres database from Google Cloud SQL, Google offers an SQL-Proxy.

by changing my Dockerfile to

FROM prismagraphql/prisma:1.17

COPY ./prerun_hook.sh /app/prerun_hook.sh
RUN chmod +x /app/prerun_hook.sh

WORKDIR /
RUN wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
RUN chmod +x cloud_sql_proxy

CMD /app/start.sh

and have the following in the prerun_hook.sh

#! /bin/bash

echo ${GOOGLE_SQL_PROXY_KEYFILE} > /keyfile.json
/cloud_sql_proxy -instances=${GOOGLE_SQL_PROXY_INSTANCE}=tcp:localhost:5432 -credential_file=/keyfile.json &

where GOOGLE_SQL_PROXY_KEYFILE is the contents of the keyfile for the cloud proxy and the instance name is my postgres instance name

I can spin up the google sql proxy on 5432 locally and prisma can connect to it. The connection is then secure.

more information on cloud proxy here

however, I'd be super happy if you could take a look at it and let me know what you think about this solution

passionkind on 9 Oct 2018

👍3

@passionkind : I think that is a very good solution! The Prisma server runs on the JVM and dealing with certificates is quite cumbersome there and not possible out of the box with some simple env vars. Just for reference here's the manual provided how do that with the driver we are using: https://jdbc.postgresql.org/documentation/91/ssl-client.html

However the cloud proxy seems like a much easier solution to me.

mavilein on 11 Oct 2018

OK, thank you for reviewing it.

But one question remains: what is the prisma config ssl flag for ?

passionkind on 11 Oct 2018

@passionkind : The ssl flag is used to determine whether Prisma should establish a connection over SSL to the Postgres server or not.
Your initial question was about using ssl client certificates. Those are used as a method of authenticating against the Postgres server instead of using user and password.

mavilein on 11 Oct 2018

Just ran into this configuring for MySQL as well. Good solution for connecting a remote Prisma server to Google SQL. If you're running Prisma inside Google Cloud you should probably be using private IPs anyway.