PostgreSQL 17 on Supabase Free: Catalog Visibility, Session Pooler, TLS, and Extension Compatibility

[ianjomar]

Hi Supabase Community!

I'm developing MyBLlist, a Next.js application using PostgreSQL 17 on Supabase Free as a staging database.

Before running our custom database migrations, I want to make sure our setup is compatible with Supabase's managed PostgreSQL environment.

Our application schema has already passed offline testing on PostgreSQL 17.11, but we still need clarification about several Supabase-specific requirements.

1. PostgreSQL Catalog Visibility

Is it possible to obtain a complete PostgreSQL catalog inventory through the Supabase SQL Editor or an authorized database role?

Specifically, we need to inspect provider-managed objects, schemas, ownership, ACLs, and dependencies.

Are there catalog visibility restrictions or provider-managed objects that cannot be fully inspected?

2. Session Pooler and Advisory Locks

Our migration runner uses a dedicated PostgreSQL session and session-level advisory locks.

Does Supabase's Session Pooler on port 5432 preserve the same PostgreSQL backend throughout the client session, including across transaction boundaries?

Is it suitable for custom migration runners that depend on session-level advisory locks?

3. Verified TLS Connections

What is the recommended configuration for connecting securely to the Session Pooler using Node.js with the postgres (postgres.js) library?

We require certificate-chain validation and hostname verification.

We previously encountered a timeout during an initial read-only identity query. Are there known Session Pooler configuration considerations or recommended troubleshooting steps for this setup?

4. PostgreSQL Extensions

Our staging checks currently show:

• PostgreSQL 17.11
• uuid-ossp 1.1 installed in extensions
• plpgsql 1.0 installed in pg_catalog
• pg_trgm not installed

Can pg_trgm 1.6 be installed into the extensions schema on the Free plan?

Are there special permission requirements or recommended practices for this?

5. Restricted Application Roles

Can we create a separate restricted PostgreSQL runtime role while keeping migration/ownership privileges assigned to another role?

Are there Supabase-specific restrictions or recommended privilege configurations for this approach?

Additional context

We are using Supabase only as a managed PostgreSQL database, not Supabase Auth or Storage.

Our staging database has not been bootstrapped, and we want to avoid modifying provider-managed objects.

I'm particularly interested in official documentation or experiences from developers running custom PostgreSQL migrations on Supabase.

Any guidance would be greatly appreciated.

Thank you!

[Chhunsour]

For the migration runner, Supavisor session mode on the shared pooler’s port 5432 is the IPv4 alternative to a direct connection. It supports session-level advisory locks across transactions while the connection remains open. Transaction mode on port 6543 is unsuitable for that dependency. Supabase documents this in connection modes and session-state limitations.

There is also a client-side requirement: reserve one Postgres.js connection and run the lock, migration statements, and unlock through that reserved client. Using the general sql pool can select different sessions. sql.reserve() provides this. If the connection is lost, stop the migration attempt and reconcile what committed before retrying; a session lock does not survive reconnection.

For your initial identity check with TLS verification enabled:

import postgres from 'postgres'

// Copy the Session pooler URL from Dashboard > Connect.
// Keep it server-side. Use the actual pooler hostname and role.project-ref user.
// Omit URL sslmode parameters; configure TLS explicitly below.
const databaseUrl = process.env.DATABASE_URL
const host = new URL(databaseUrl).hostname
const sql = postgres(databaseUrl, {
  max: 1,
  connect_timeout: 15,
  ssl: { rejectUnauthorized: true, servername: host },
})

try {
  const connection = await sql.reserve()
  try {
    const rows = await connection`
      select current_user, current_database(), pg_backend_pid()
    `
    console.log(rows)
  } finally {
    await connection.release()
  }
} finally {
  await sql.end()
}

This uses Node's default trusted CAs and checks the pooler hostname. If a custom CA is necessary, add the CA appropriate to that endpoint via the TLS ca option. Do not disable verification to hide a certificate error. Postgres.js's ssl: 'require' disables certificate verification; it is insufficient for your stated requirement. See its TLS implementation and Node TLS options. A timeout alone does not establish a TLS failure: preserve the error code, and check DNS, TCP reachability, and the exact Connect-dialog hostname/username first.

For the remaining preflight questions:

• Catalog visibility: inspect pg_namespace, pg_class, pg_proc, pg_depend, and pg_roles, but do not assume a superuser-equivalent inventory. Supabase's postgres role is not a superuser, and protected fields such as role password material are not an ordinary catalog-inspection entitlement.

• pg_trgm 1.6: check your actual instance before pinning a version:

  select name, version, installed
  from pg_available_extension_versions
  where name = 'pg_trgm';

  Supabase lists pg_trgm among its extensions. If version 1.6 is available and the extension is not already installed, enable it through the Dashboard or a reviewed migration using CREATE EXTENSION pg_trgm WITH SCHEMA extensions VERSION '1.6';. CREATE EXTENSION does not upgrade an already-installed older version. PostgreSQL documents pg_trgm as a trusted extension, but your executing role still needs the relevant database privileges.

• Separate runtime role: Supabase supports custom Postgres roles. Give the runtime role only the schema/table/function privileges it needs, and keep application-object ownership and migration privileges separate. Check effective inherited privileges too; a REVOKE on the role does not cancel a grant inherited through PUBLIC or another role. For a precise hosted-role lifecycle beyond those documented capabilities, maintainers need to confirm supportability.

Prepared with AI assistance. The catalog query and reserved-connection/session-lock behavior were checked on an isolated PostgreSQL 17 instance. The hosted pooler and its certificate were not tested against your project.

[ianjomar]

Thank you very much for the detailed response. This was genuinely helpful and clarified several of the remaining questions in our staging preflight.

In particular, your explanation of using the Supavisor Session Pooler on port 5432 together with a reserved Postgres.js connection (sql.reserve()) gives us a clear path for our migration runner. Our runner depends on a session-level advisory lock, so we’ll continue to avoid transaction mode on port 6543.

The TLS guidance is also very useful. We’ll keep certificate and hostname verification enabled and test the actual Session Pooler endpoint separately rather than treating a timeout as proof of a TLS problem or disabling verification.
We have now enabled pg_trgm on our Supabase staging project as well. Our next step will be to verify its installed version/schema and continue with a small, read-only staging preflight.

The clarification about catalog visibility and the Supabase postgres role not being a true superuser was particularly important for us. We’ll keep our provider-object checks conservative rather than assuming that our catalog view represents unrestricted superuser visibility. We’ll also keep the application runtime role separate from migration/ownership privileges and check inherited privileges.
This gives us enough information to move forward with the next part of our staging-readiness verification without weakening the safety checks we already have in place.

Thank you again for taking the time to provide the references, example, and the limitations of what was tested. It helped us narrow down the remaining work considerably.