Connect a Supabase database to Dataki

02·Set it up

4 steps, about 10 minutes

1. 01

   Create a read-only role

   Supabase asks you never to give the postgres password to a third-party service you do not absolutely trust, and to create a role for each service instead. Open the project's SQL editor, which runs as postgres, and run this with a long password of your own. The last two lines make the role read tables you create later, and refuse any write even if a grant slips through.

   CREATE ROLE dataki_reader WITH LOGIN PASSWORD 'choose-a-long-password';
   GRANT CONNECT ON DATABASE postgres TO dataki_reader;
   GRANT USAGE ON SCHEMA public TO dataki_reader;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO dataki_reader;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dataki_reader;
   ALTER ROLE dataki_reader SET default_transaction_read_only = on;

   • Through the shared pooler, the role's user name carries the project's reference: dataki_reader.<project-ref>. The reference is what follows postgres. in the user Dataki fills in.
2. 02

   Let the role past row level security

   Row level security holds dataki_reader like any role that does not own the table: where it is on and no policy names the role, the role sees no rows at all. Tables created in the Supabase dashboard have it on by default, and Supabase asks for it on every table its Data API serves.

   This gives dataki_reader a read policy on every such table. It skips tables that already have one, so run it again after adding tables. Run it as postgres, which owns the tables you create in the dashboard.

   DO $$
   DECLARE t text;
   BEGIN
     FOR t IN
       SELECT tablename FROM pg_tables
       WHERE schemaname = 'public' AND rowsecurity
         AND tablename NOT IN (
           SELECT tablename FROM pg_policies
           WHERE schemaname = 'public' AND policyname = 'dataki_reader_select')
     LOOP
       EXECUTE format('CREATE POLICY dataki_reader_select ON public.%I'
                      || ' FOR SELECT TO dataki_reader USING (true)', t);
     END LOOP;
   END $$;

   • Each policy lets dataki_reader read, and nothing else. It opens nothing to the anon and authenticated roles your app uses.
   • Supabase also documents alter role ... with bypassrls, which skips every policy on every table at once, and says never to share the credentials of a role that has it.
3. 03

   Let Dataki in

   Supabase accepts connections from any address until you turn on network restrictions. If you have, add 34.89.253.13/32 under Database Settings › Network Restrictions. They apply to the pooler as well as to direct connections, and changing them needs Owner or Admin in the organization.

   • Enforce SSL on incoming connections, under Database Settings › SSL Configuration, can stay on, or be turned on: Dataki offers TLS before anything else. Changing it restarts the database briefly.
4. 04

   Connect Supabase in Dataki

   Open app.dataki.ai/connect and, under Database Connections, choose Connect Supabase. Sign in to Supabase and allow Dataki access to the organization that holds the project. Back in Dataki, the dialog lists your projects with the host, port, database and user of each. Pick the project, and Dataki opens the connection form with PostgreSQL chosen, the connection named after the project, and these filled in:

   • Host

     Dataki fills in

     The shared pooler, a host ending in pooler.supabase.com.

     What to do

     Keep it.

   • Port

     Dataki fills in

     The pooler's port as Supabase reports it, usually 6543, its transaction mode.

     What to do

     Keep it.

   • Database Name

     Dataki fills in

     postgres.

     What to do

     Keep it.

   • Username

     Dataki fills in

     postgres.<project-ref>.

     What to do

     Change postgres to dataki_reader, keeping the reference.

   • Password

     Dataki fills in

     Nothing.

     What to do

     The role's password, typed as it is. It is its own field, not part of a connection string, so it needs no percent-encoding.

   Field	Dataki fills in	What to do
   Host	The shared pooler, a host ending in pooler.supabase.com.	Keep it.
   Port	The pooler's port as Supabase reports it, usually 6543, its transaction mode.	Keep it.
   Database Name	postgres.	Keep it.
   Username	postgres.<project-ref>.	Change postgres to dataki_reader, keeping the reference.
   Password	Nothing.	The role's password, typed as it is. It is its own field, not part of a connection string, so it needs no percent-encoding.

   • Then Test connection and Save Connection. Dataki encrypts the password with Google Cloud KMS before storing it, and never sends it back to the browser.
   • If Host shows db.<project-ref>.supabase.co instead, Dataki could not read the pooler's settings, and that host is out of its reach (see below). Copy the host and port of the Session pooler, which Supabase suggests for third-party tools, from the project's Connect panel in Supabase, and use the user dataki_reader.<project-ref>.
   • Dataki uses the Supabase sign-in only to list your projects and read each one's pooler settings. Disconnect, in the dialog's menu, makes it forget the sign-in; connections you saved keep working, because they use the password.