Database

Setting Up Your Database on Supabase Cloud

A Supabase project in your own account, set up in five steps the setup checks as you go: the project, its publishable key, the setup SQL, the access token hook, then connect.

6 min read · Updated

Supabase runs the database; you own the project. It is the quickest of the three homes, which makes it the one to try Pental with, and you can move to PostgreSQL in your own cloud or a self-hosted stack later with everything in it. This guide follows the Database step of your setup on pental.io, which shows the same five steps, opens the right page of your own project at each one, and checks each step from your browser before the next.

Pros and cons

Pros

  • Nothing to install, patch or keep running: Supabase runs the database, and its dashboard is where you look after it.
  • The quickest of the three to set up, and every step is checked from your browser as you go.
  • On a paid Supabase plan it is backed up every day, in the region you chose when you created it.

Cons

  • Updates are one paste rather than automatic: when a release changes the schema, an administrator copies the SQL into the project’s SQL editor and runs it.
  • The free tier is for trying it out: Supabase keeps no restorable daily backups on it, so a firm working on it for real wants a paid plan.
  • The data sits with Supabase as your hosting provider, in your own account. A client who needs it in your own cloud or on your own servers may prefer one of the other two.

Before you start

  • A Supabase account. Signing up is free, and the free tier is enough to try Pental on; a firm working on it for real wants a paid plan, for its daily backups.
  • Your pental.io account, on the Database step of the setup (or later, on your dashboard under Portal Configuration, then Database).

Create a Supabase project

  1. Open Supabase and sign in, or create an account.
  2. Press New project. Name it (Pental portal, say) and set a database password. Keep the password in your password manager: Pental never needs it, and nobody at Pental can recover it.
  3. For Region, pick a specific region, such as West Europe (London), rather than a general area such as Europe: a specific region keeps the data in the one place you chose. The setup names the one nearest you, from your time zone.
  4. Press Create new project and let Supabase start it.
  5. Copy the address from your browser’s address bar while you are on the project (any page of it) and paste it into Your project’s address. The project ID on its own works too.

From then on, every link in the setup opens that project’s own page: its API keys, a new query in its SQL editor, and its auth hooks.

Copy its publishable key

On the project’s API Keys page, copy the key labelled Publishable; it starts sb_publishable_. Paste it into Publishable key and the setup asks your project at once whether it accepts it. It is the only key of the project Pental keeps.

The secret key (it starts sb_secret_), and the legacy service_role key, read past every security policy. The setup refuses either, and Pental never holds one. The publishable key is safe precisely because the portal’s data sits behind the policies the next step installs.

Run the setup SQL

  1. Press Copy the setup SQL. If your browser will not copy it, or download it saves the same SQL as a file.
  2. Press Open the SQL editor, which opens a new query in your project.
  3. Paste the SQL in and press Run. It ends with “Success. No rows returned.”

If Supabase asks whether to run it with or without RLS, choose Run with RLS. The SQL is written for it.

It makes every table, security policy and function the portal needs, inside your project, and nobody at Pental runs it for you. What you copy is compressed, only so it fits: Supabase’s SQL editor refuses a query over about 1 MB, and the setup is larger. Its first lines are plain SQL that unpack it, check it is exactly the readable setup and run it, so anything else is refused. To check it yourself, open Why it is compressed, and how to check it under the step: it gives the readable setup’s SHA-256, which is the hash in the paste’s check line, and or download it saves that readable setup. The setup watches for it and ticks the step by itself once it is in.

Turn on the access token hook

The hook copies the time of each person’s last second-factor check into their sign-in, so the portal is not asking for the second factor every time a session refreshes. The portal works without it, but asks far more often.

  1. Press Open Auth Hooks, then Add hook, and choose Customize Access Token (JWT) Claims.
  2. Set Enabled on, Hook type to Postgres, Postgres schema to public and Postgres function to custom_access_token_hook.
  3. Press Create hook, then tick I have turned it on in the setup.

Supabase shows this setting to nobody but you, so the setup cannot check it and asks you to tick it instead.

Connect your portal

Press Check and connect. Pental checks every service the portal uses (tables, sign-in, files and live updates), then connects your portal to the project. Only the publishable key is kept.

Keeping it updated

When a release changes the schema, the portal shows Database update available to the people who can change settings, and your pental.io dashboard says the same under Database. Press Copy SQL, then Open SQL editor, paste and Run. It is the same file you ran at setup: it only adds what is missing and never touches your data, and until it runs the portal carries on as it was, with only the new features waiting.

The setup guide records it being done: watch the update, from 4:29.

If something is not right

What you seeWhat to do
Your project does not accept this keyIt is not this project’s publishable key. Copy the one labelled Publishable from the same project’s API Keys page.
It did not answerA new project takes a moment to start. The setup keeps asking by itself; press Check again if it has stopped.
An older setup SQLThe project holds an earlier version. Copy the SQL and run it again: it only adds what is missing.
The hook’s function is not in the listThe setup SQL has not finished. Run it, check it ends with “Success. No rows returned.”, then reload the Auth Hooks page.

Moving later

If a client asks for the data on your own servers, or you simply change your mind, move it to PostgreSQL in your cloud or self-hosted Supabase, with everything in it. For a move from your browser you need the project’s legacy service key: API Keys, then Legacy API keys, then service_role (it starts eyJ). Supabase refuses its newer sb_secret_ keys from a browser, so a project with only those moves from your computer instead, as the move guide shows.


Set It Up in the Trial

The Database step walks through these five steps, opens the right page of your own project at each, and checks each one from your browser.

Free trial  ·  No card required

Only need the reporting? Our reporting tool is free and open source, on your own server.


Also Worth Reading

Setup guide

Prefer to watch it?

This step is in the recording as “Your database on Supabase Cloud”, from 2:38. Every other step is a chapter away.

  • 2:38 · Your database on Supabase Cloud
  • 3:15 · Email from your own address
  • 4:04 · Branding, and finishing setup
  • +5 more