Skip to main content

Connecting Supabase to Bicycle

Connect to your Supabase Postgres database. Bicycle queries Supabase tables and views using pushdown, so analytics run against your database: Bicycle connects through Supabase's connection pooler (Supavisor) over TLS.

Requirements​

To follow the steps in this guide, you'll need:

  • Permission to create connectors in Bicycle
  • Access to a Supabase project and its SQL Editor
  • Supabase permissions that allow you to create database users and grant schema and table access (the project's postgres user can)

Work with your Supabase or database administrator to provision access. In most organizations, a platform or data team runs the SQL in the sections below; an analyst or engineer then completes the connection in Bicycle.

ItemDescription
Project ReferenceThe project ID in your project URL, https://<project-ref>.supabase.co
RegionRegion of your project, for example eu-north-1
Pooler Mode (optional)Session (default, port 5432) or Transaction (port 6543)
Database (optional)Defaults to postgres
Schemas (optional)Limit discovery to these schemas; omit to discover all accessible schemas except Supabase's platform schemas
Host / Port (optional)Override the pooler endpoint; needed only when your pooler host does not start with aws-0-
AuthenticationUsername and password only

Prepare Supabase access​

Create a dedicated database user for Bicycle. Do not use the postgres user, service_role, or personal credentials for production connections.

Retrieve Supabase connection details

In the Supabase dashboard, open your project:

  1. Go to Project Settings → General. The Project ID is your project reference, for example abcdefghijklmnop.
  2. Click Connect at the top of the project page and choose the Session pooler connection string. Its host has the form aws-0-<region>.pooler.supabase.com; the part after aws-0- is your region, for example aws-0-eu-north-1.pooler.supabase.com → eu-north-1.

If the pooler host starts with anything other than aws-0- (for example aws-1-), copy the whole host as well: you will enter it in Host in Bicycle.

Create a user and grant read access

Open the SQL Editor and run the following. Replace placeholders with values from your environment, and repeat the grants for every schema Bicycle should query.

Create Bicycle user and grant read access
CREATE ROLE bicycle_user WITH LOGIN PASSWORD '<secure_password>';

GRANT USAGE ON SCHEMA <schema_name> TO bicycle_user;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO bicycle_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name>
GRANT SELECT ON TABLES TO bicycle_user;

SELECT ON ALL TABLES covers both tables and views. ALTER DEFAULT PRIVILEGES covers tables and views created later by the user that runs it; if another user (for example a migration user) creates them, add FOR ROLE <owner_user>.

If the views Bicycle reads call functions, the Bicycle user also needs EXECUTE on those functions. Postgres checks functions called inside a view against the user querying the view, not the view's owner, so a SELECT grant on the view is not enough. Supabase projects often revoke the default EXECUTE grant, especially on SECURITY DEFINER functions:

Allow the Bicycle user to call functions used by views
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA <schema_name> TO bicycle_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name>
GRANT EXECUTE ON FUNCTIONS TO bicycle_user;

To grant only specific functions, use GRANT EXECUTE ON FUNCTION <schema_name>.<function_name>(<argument_types>) TO bicycle_user;. A SECURITY DEFINER function reads its tables with its owner's privileges, so EXECUTE is all it needs; a SECURITY INVOKER function (the default) also needs SELECT on the tables it reads.

note

Use naming conventions your organization already follows for service accounts. The example user (bicycle_user) is a placeholder.

Read-only by default

Bicycle needs only read access to query your data, and it runs every query in a read-only transaction. If Bicycle should only ever read, also make the user read-only on the server:

ALTER ROLE bicycle_user SET default_transaction_read_only = on;

Grant other privileges only for what you want Bicycle to do beyond querying.

Allow the user to see rows

Supabase enables Row Level Security (RLS) on tables created in the dashboard. A user that no policy covers sees zero rows: queries succeed but return nothing. For each table Bicycle reads, either add a read policy:

Allow the Bicycle user to read a table
CREATE POLICY bicycle_user_select ON <schema_name>.<table_name>
FOR SELECT TO bicycle_user USING (true);

or, if Bicycle may read every row of the tables it can access, let the user bypass RLS:

Let the Bicycle user bypass Row Level Security
ALTER ROLE bicycle_user BYPASSRLS;

If Bicycle reads views: a view normally reads its tables with the privileges of the view's owner, so granting SELECT on the view is enough. If a view is created with security_invoker = on, the Bicycle user also needs SELECT on the tables underneath it, and RLS on those tables applies to the Bicycle user.


Setup in Bicycle​

Open Connectors and select Supabase

In Bicycle, go to Connectors. Find Supabase and click + Connect.

Choose Username + Password

Enter a connection name that identifies the environment, such as Supabase Production. Select Username + Password as the Connector method, then click Next.

Add Supabase connection details

On the Add details step, fill in the Supabase connection fields.

Project ReferenceRequired

Project ID from Project Settings → General, lowercase letters and digits

RegionRequired

Region of the project, for example eu-north-1

Pooler ModeDefault: Session

Session (port 5432) or Transaction (port 6543). See Session or transaction pooler

DatabaseDefault: postgres

Database Bicycle should connect to

Schemas

Schemas to discover, for example analytics. If omitted, Bicycle discovers all schemas the user can access, except Supabase's platform schemas (auth, storage, realtime, extensions, graphql, vault and similar)

Host

Pooler host override, needed only when your pooler host does not start with aws-0-, for example aws-1-eu-north-1.pooler.supabase.com. The project reference is still required

UsernameRequired

Dedicated Supabase database user, such as bicycle_user. Enter the plain user name, never <user>.<project-ref>; Bicycle adds .<project-ref> when it connects through the pooler

PasswordRequired

Password for the Supabase database user

Example values:

  • Project Reference: abcdefghijklmnop
  • Region: eu-north-1
  • Pooler Mode: Session
  • Schemas: analytics
  • Username: bicycle_user

Test the connection

Click Test connection. Bicycle validates the credentials, confirms each configured schema exists, and checks that the user has USAGE on it. When the test succeeds, you will see Ready to add this connection.

Add the connection

Click Add connection. When the connection is created successfully, you will see a confirmation with the connection name, ID, and method (Username + Password). Click Close to return to the Connectors page.

Session or transaction pooler​

ModePortWhen to use
Session (default)5432Full Postgres session behaviour. Use this unless your project runs out of pooler connections.
Transaction6543The pooler shares database connections between clients per transaction. Bicycle turns off prepared statements on these connections automatically, as transaction pooling requires.

In both modes, Bicycle runs every query in its own read-only transaction rather than marking the whole database session read-only, because the pooler hands the same database connection to other clients after Bicycle disconnects.

Bicycle keeps a small pool of connections open to your project. If your project has little compute, or other applications already use most of its pooler connections, choose Transaction mode, or raise the pool size under Project Settings → Database.

Troubleshooting​

If Test connection fails or no tables appear after connecting, check the following.

Error or symptomLikely causeFix
A valid project reference is required (lowercase letters and digits)Project Reference is empty or not the project IDCopy the Project ID from Project Settings → General
A valid region is required (e.g. ap-south-1)Region is empty or malformedTake the region from the pooler host, for example eu-north-1
Username/password authentication is requiredUsername or Password is missingEnter the Supabase database user and its password
Failed to connect to Supabase: ... password authentication failedWrong password, or the username includes .<project-ref>Enter the plain user name and check the password
Failed to connect to Supabase: ... Tenant or user not foundThe project is not on the derived aws-0- pooler, or the project reference or region is wrongCopy the pooler host from Connect into Host, and check the project reference and region
Schema '<name>' does not exist in database '<db>'A configured schema name is wrongCheck the schema name, or clear Schemas to discover all accessible schemas
User lacks USAGE privilege on schema '<name>'The user was not granted the schemaRun the GRANT USAGE and GRANT SELECT statements for that schema
Unsupported sslmode. Allowed: requireAn unsupported TLS option was enteredRemove the sslmode option; connections are always encrypted
permission denied for function <name>A view Bicycle reads calls a function the user cannot executeRun GRANT EXECUTE ON FUNCTION <schema>.<name>(<argument_types>) TO bicycle_user, or grant EXECUTE ON ALL FUNCTIONS IN SCHEMA
Connection succeeds but no tables are discoveredMissing SELECT grants, or Schemas lists a schema with no tables or viewsRun the grant SQL for each schema and check the schema names
Tables are discovered but queries return no rowsRow Level Security hides the rows from the userAdd a read policy for the user, or grant BYPASSRLS
New tables or views do not appearDefault privileges were not set for the user that creates themRun ALTER DEFAULT PRIVILEGES ... FOR ROLE <owner_user>, or grant SELECT again
tip

To verify access outside Bicycle, connect to the pooler as the Bicycle user (for example with psql) and run a simple SELECT against the target tables.

References​