Supabase with SSL

[BananaMasterz]

Hi, it's my first time with postgres and since drift does support it I decided to use it with it since im familiar with drift. Can someone help me connect to a supabase database using ssl? I tried the following but I get Severity.fatal Bad server certificate. but idk where to set my .crt file. Is it some global variable or something?

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final pgDatabase = PgDatabase(
      endpoint: Endpoint(
        host: 'db.yyyy.supabase.co',
        port: 3333,
        database: 'postgres',
        username: 'postgres',
        password: 'password',
      ),
      settings: const PoolSettings(sslMode: SslMode.verifyFull),
    );

    return pgDatabase;
  });
}

[simolus3]

I haven't used Supabase before, but if the server is using a self-signed certificate you can't use SslMode.verifyFull by default. You could use SslMode.require, which requires SSL but accepts every certificate. IMO that provides no real security at all and I tried lobbying for an API in package:postgres that would allow only some self-signed certificates, but at the moment we only have these two options.

With isoos/postgresql-dart#280, you would be able to set a custom security context containing only the expected certificate.

[BananaMasterz]

got it, thanks!

[simolus3]

By the way, postgres: ^3.0.6 has just been released and allows supplying a SecurityContext in ConnectionSettings. You can create a SecurityContext, add your crt with setTrustedCertificates and pass that to PoolSettings. Then you can use SslMode.verifyFull as well.

[BananaMasterz]

Great timing! Thanks for letting me know!

[BananaMasterz]

@simolus3 could you also possibly help me understand this:

the connection string is of this form:
psql -h db.my_db.supabase.co -p 5432 -d postgres -U postgres

and supabase wants to make it of this form:
postgres://[user].my_db:[password]@aws-0-eu-central-1.pooler.supabase.com:6543/[db-name]

is there a way to connect with connection pooling

I am currently using this:

final pgDatabase = PgDatabase(
  endpoint: Endpoint(
    host: 'db.my_db.supabase.co',
    port: 5555,
    database: 'postgres',
    username: 'postgres',
    password: 'pw',
  ),
  settings: const PoolSettings(sslMode: SslMode.require),
);

but there's nowhere to set aws-0-eu-central-1.pooler.supabase.com for example as seen in the above connection string

[simolus3]

Since your database connection will be kept open for a while, I think using a direct connection is not unreasonable. The warning only mentions IPv4 access going away, so if your server has IPv6 connectivity you should still be able to connect.

postgres://[user].my_db:[password]@aws-0-eu-central-1.pooler.supabase.com:6543/[db-name]

The endpoint settings for that would be:

final pgDatabase = PgDatabase(
  endpoint: Endpoint(
    host: 'aws-0-eu-central-1.pooler.supabase.com',
    port: 6543,
    database: '[db-name]',
    username: '[user]',
    password: '[password]',
  ),
  settings: const PoolSettings(sslMode: SslMode.require),
);

[BananaMasterz]

ah got it, thanks!