How can I connect to a Cloud PostgreSQL database from dart code?

Question

I have a PostgreSQL database deployed in Google Cloud that I am trying to connect to from a Cloud Run instance. I have tried the following two packages, both of them eventually leading to the same exception:

• https://pub.dev/packages/postgres
• https://pub.dev/packages/database_adapter_postgre

The exception I am getting is: SocketException: Failed host lookup: '/cloudsql/{INSTANCE_CONNECTION_NAME}' (OS Error: Name or service not known, errno = -2)

I get here both times when trying to establish the connection, so in the case of the first package:

connection = new PostgreSQLConnection(
        '/cloudsql/{INSTANCE_CONNECTION_NAME}',
        5432,
        'postgres',
        username: 'username',
        password: 'password');
await connection.open(); // <-- exception thrown here

I have tried changing the host string to /cloudsql/INSTANCE_CONNECTION_NAME}/.s.PGSQL.5432, but that did not work. My first thought were permissions, the service account the Cloud Run instance is using ([email protected]) has the Cloud SQL Editor role (tried Client and Admin too).

Running the same database code locally from a dart console app, I can connect to my database via its public IP address as the host with both packages, so the database itself is up and running.

Can someone point me in the right direction with this exception/have an example code for any of the packages above to show how to connect it to a Cloud SQL instance from a Cloud Run?

Edit: I tried setting up a proxy locally to test out if the connection is wrong like so:

.\cloud_sql_proxy.exe -instances={INSTANCE_CONNECTION_NAME}=tcp:5433 psql

Then changing the connection host value in the code to localhost, and the port to 5433. To my surprise it works - so from locally I am seemingly able to connect to the DB using that connection string. It still doesn't work when I use it from a Cloud Run instance though. Any help is appreciated!

Alvar

Are you using Cloud Run fully managed? If so, I think is link can be helpful for you cloud.google.com/sql/docs/postgres/connect-run – Blatt

Yes, fully managed. This is the document I am following, yeah... Too bad they don't have a dart sample. I think I've gone through all the steps correctly, but will double check. – Alvar

Maybe I understood something wrong, but isn't the first argument of PostgreSQLConnection() the host (ip/dns)? And as far as I know, such a host can't start with "/". – Rocambole

Indeed it is the host, so it should be the IP/DNS name. I was looking at the examples in the document @SamuelRomero posted in the comment above, that's where I got the idea of putting in the host like that. What should the correct host be then? Should I just remove the leading / or is there an IP I should put in instead? – Alvar

Answer 1

It seems dart doesn't support connection through unix socket, you need to configure a IP (public or private, as you need).

Alternatively you can use pg which support unix socket connection

Hope this helps.

Mog

Sad to hear that. I know Node.js would work, but I'm specifically interested in a solution in dart. Do you have a source to follow whether that unix socket connection will be supported any time soon in dart? – Alvar

No sorry @Alvar , you could try to open a feature request issue... In the mean while you can fallback configuring a public or private IP for your DB and connect through it. Hope this helps – Mog

Answer 2

Just for those who come across this question in the future: as it stands right now, I had to resort to the suggestion posted by Daniele Ricci and use the public IP for the database. The one thing to point out here was that since Cloud Runs don't have a static IPv4 address to run from, the DB had to be set to allow connections from anywhere (had to add an authorized connection from 0.0.0.0/0), which is unsafe. Until the kind development team of dart figures out how to use UNIX sockets, this seems to be the only way of getting it to work.

Alvar

Answer 3

Not having actually tested this myself, according to the source code of the postgres package, you have to specify that you want a Unix socket:

connection = PostgreSQLConnection(
  ...
  isUnixSocket: true, //              <-- here
);

The default is false.

The host you pass is must also be valid. The docs say:

[host] must be a hostname, e.g. "foobar.com" or IP address. Do not include scheme or port.

Norby

Answer 4

I was struggling with the same issue.

The solution is to create a connection as follows:

  PostgreSQLConnection getProdConnection() {
    final String connectionName = Platform.environment['CLOUD_SQL_CONNECTION_NAME']!;
    final String databaseName = Platform.environment['DB_NAME']!;
    final String user = Platform.environment['DB_USER']!;
    final String password = Platform.environment['DB_PASS']!;
    final String socketPath = '/cloudsql/$connectionName/.s.PGSQL.5432';

    return PostgreSQLConnection(
      socketPath,
      5432,
      databaseName,
      username: user,
      password: password,
      isUnixSocket: true,
    );
  }

Then when you create a Cloud Run service, you need to define 'Enviroment variables' as follows.

You also need to select your sql instance in the 'connections' tab.

Then the last thing needed is to configure a Cloud Run service account.

Then the connection to instance should be successful and there should no longer be a need for a 0.0.0.0/0 connection.

However, if you try to run this connection locally on a Windows device during development the connection will not be allowed and you will be presented with this error message: 'Unix domain sockets are not available on this operating system.'

Therefore, I recommend that you open Google SQL networking to your public address and define a local environment using the 'Public IP address' of your SQL instance.

For more information on this topic, I can recommend these resources that have guided me to the right solution:

• https://cloud.google.com/sql/docs/postgres/connect-instance-cloud-run#console_5
• https://github.com/dart-lang/sdk/issues/47899

Oleaster