Connect to database with unix socket

[kucherenko]

Connect to database with unix socket

Description

I'm trying to connect to database with unix socket and use following property:

DATABASE_URL=jdbc:postgresql://localhost:5432/fusionauth?socket=/path/to/socket

or

DATABASE_URL=jdbc:postgresql:///path/to/socket:5432/fusionauth

But can't connect to the database.

In log I can see following error:

Jul 21, 2020 9:31:22.988 AM INFO com.inversoft.maintenance.db.DatabaseSilentConfigurationWorkflowTask - Attempt 1 of 10. Patiently waiting for database to become available [DOWN].

[voidmain]

This article from PostreSQL and their JDBC driver might be helpful: https://jdbc.postgresql.org/documentation/head/connect.html

It looks like you need to specify a Unix Socket Factory and ensure that the JAR file for that is on the classpath. FusionAuth doesn't support this currently, so use at your own risk.

[kucherenko]

Thank you for the answer.

One more question, is it possible to connect to mysql database via unix socket?

[voidmain]

Here's an article I found on that: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-unix-socket.html

There's likely a lot of resources out there, so just search around and see what you can find for Unix sockets and JDBC.

[kucherenko]

Thank you for the answer

In general I going to run fusionauth in GCP Cloud Run. For database connection GCP suggest use sockets only. I have couple of ideas how I can implement it:

1. Use junixsocket for connection to the socket, but I can't understand how I can provide junixsocket.file and socketFactory to the fusionauth container via ENV variables.
2. Use tools like socat for make forwarding between tcp<>socket in the container and use localhost as connection to database, I've made small PoC here but still no result.

Probably somebody faced with the issue and have the solution, I will be happy if you can share it with me.

[voidmain]

My guess is you'll have to experiment with this by adding various properties and settings described in those articles I linked to. However, this might not be possible without modifying the FusionAuth database code. If that's the case, then we'd need for the community to upvote this feature request in order to get it prioritized.

Or if you have a business requirement for this and have a support contract in place, then we can build it for you under a professional services contract.

[Mistic92]

I have the same issue. I want to test fusionauth on Cloud Run but can't make it working with unix socket. Dis you found solution for that?

[robotdan]

This may be helpful. https://stackoverflow.com/a/25920897/3892636

[yinzara]

While the full configuration is quite a bit more complex, you could connect to the Cloud SQL instance through a private IP.

This is a relatively involved process that you can find more info at: https://cloud.google.com/sql/docs/mysql/connect-run#private-ip

This will incur the cost of a single f1-micro instance as it needs to keep the "Serverless VPC Connector" live but you can use it for all of your Cloud Run services in the same project.

[Thammada]

@kucherenko @Mistic92 were you able to run FusionAuth on Cloud Run? I'm planning to run FusionAuth on Cloud Run and would like to know how it performs.

[yinzara]

I was able to get it running. I'm not sure it's the best solution for it, but it definitely worked.

[Mistic92]

Yep, it can work through private ip but I think it should support unix socket too.

[atrauzzi]

Looking to get up and running with simple socket connections on google cloud run, running into some issues with com.inversoft.maintenance.db.JDBCURL failing validation on the connection string.

ERROR com.inversoft.maintenance.db.JDBCURL - Could not parse jdbcString [jdbc:postgresql:///fusionauth?socket=/cloudsql/my-project:my-region:my-database/.s.PGSQL.5432&user=my-user&password=my-password]
Default
ERROR com.inversoft.maintenance.db.JDBCMaintenanceModeDatabaseService - Configuration [database.url] is invalid. It must begin with either jdbc:mysql: or jdbc:postgresql:

Looking at the postgres docs for connection strings,

So, I believe something akin to the following should be possible:

jdbc:postgresql:///fusionauth?socket=/cloudsql/my-project:my-region:my-database/.s.PGSQL.5432&user=my-user&password=my-password

I'm thinking the first step would be to get the FusionAuth level validation to back off so that the connection string can at least be tried on the driver. After that, I'll probably have other things to fix waiting for me, but this is the current snag at the moment.

[kostacasa]

+1 from another Cloud Run based deployment that would benefit from capability of using UNIX sockets!

[jobannon]

Thanks for the feedback @kostacasa!

For others in this thread, PgBouncer can be used to proxy between a JDBC connection and a UNIX socket.

• https://www.pgbouncer.org/

When adding another proxy, it is always a good idea to test and confirm that this solution will work with the workload and applications infra that is in place (your mileage may vary).

[paulwer]

does this maybe help: https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory/blob/main/docs/jdbc-mysql.md

or did someone manage to get a solution for google cloud run?

[kostacasa]

@paulwer we are running FusionAuth on Cloud Run currently and decided to connect to Cloud SQL instance via JDBC and not use UNIX sockets. That works fine.

[paulwer]

i've created a custom docker file, which copies the mentioned google jar file. works like a charm :)

[kostacasa]

i've created a custom docker file, which copies the mentioned google jar file. works like a charm :)

Interesting! Did you have to make any modifications to FusionAuth configuration to use Google's socket factory instead of the default one?

[mooreds]

@paulwer if you'd like to contribute that Dockerfile, we have a community repo: https://github.com/fusionauth/fusionauth-contrib. Just sayin'.