Hey everyone, after many debugs and tries, I can't figure it out if it is possible to listen to a database realtime change with for example:

js
this.channel.on('postgres_changes', { event: '*', schema: '*' }, data => console.log('RECV', data));

After I subscribe to a channel. Because it works well when I listen to a

presence

and a

broadcast

but not

postgres_changes

. Is it normal? Or there is something wrong with my code?

Hi, For an application I would like to monitor the number of subscriptions on my tables through the realtime.subscription table. However, any attempt so far resulted in the following APIerror:

permission denied for schema realtime

I already added an endpoint for the realtime schema through the interface. I did not find any option to give access to the schema through queries. I also tried to give access rights through postgresql commands

GRANT USAGE ON SCHEMA realtime TO postgres, anon, authenticated, service_role, dashboard_user, authenticator, supabase_auth_admin, supabase_admin,

This did not seem to help. How to continue?

I have the private user table. I also have a public table called Profile. Once a new user is created, I need to automatically pass the id from users, and create a basic row in Profile. The ID is foreign link. How is this done in Supabase? I'm guessing there is something on the backend I can configure to do this?

I need to allow write access to a RLS table triggered by a external webhook. I don't have access to the Cors headers. What is the standard practice for this?

This is my first time using supabase and I am following the nextjs quick start guide (https://supabase.com/docs/guides/getting-started/quickstarts/nextjs). I am just trying to get some data for my database to display on the page but I am getting returned an empty array. I create the supabase client in my supabase.js file:

js
import { createClient } from '@supabase/supabase-js';

export const supabase = createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL,
    process.env.NEXT_PUBLIC_SUPABASE_KEY
)

And then I try to display the data on the page in my index.js file:

js
import { supabase } from '../utils/supabase'

export default function Home({ lessons }) {
  console.log({lessons})
  return (
    <div className="flex flex-col items-center justify-center min-h-screen py-2">
      {lessons.map(lesson => (
        <p key={lesson.id}>{lesson.title}</p>
      ))}
    </div>
  );
}

export const getStaticProps = async () => {
  const {data: lessons} = await supabase.from('lesson').select('*');

  return {
    props: {
      lessons,
    },
  };
};

I attached an image of the empty array I get logged. In my database I have a "lesson" table with two rows. Are there some permissions you need to turn on in the settings? I am just trying to display the two rows in my database. Any advice helps. Thanks.

I'm hitting RLS inside of

getServerSideProps

when using

createServerSupabaseClient()

. I was under the impression that the server client used the service role key and would bypass RLS. Has this changed?

I am getting a 500 error after I deploy my Nuxt app to vercel:

{
  "url": "/api/",
  "statusCode": 500,
  "statusMessage": "",
  "message": "main.createClient is not a function",
  "stack": ""
}

From this code:

import { createClient } from '@supabase/supabase-js'

export default defineEventHandler( async (event) => {
  const runtimeConfig = useRuntimeConfig()
  const supabase = createClient(runtimeConfig.supabase_url, runtimeConfig.supabase_service_role)

PS The code lives in the /server/api folder

Hi, I was wondering if there was a way to invalidate storage cache for a specific file? I've seen using ?bust=Date.now(), but that seems much more inefficient than being able to tell supabase which cache to invalidate. Is there a way to do that?

Just want to make sure i'm understanding this correctly. I have a couple of form actions that add and delete "products" and "product images" from my db & bucket. So if I want them to be transactional, so that way i dont have orphaned images I create a function first in supabase using sql with params ( product ID and the image path) then in supabase-js client I call that rpc passing in those details as args ? pardon my sql if it's wrong (still learning)

sql
create or replace function delete_product (product_id integer, image_path text)
  returns boolean
  language plpgsql
  as
  $$
    begin
      delete from public.products where id = product_id
      delete from storage.public where image_path = image_path
      return true
    end;
  $$

and then in js eg.

js
const product = { id: 2, image_path: 'product_images/a7pk014t92u09pa681c93w4y'}

const { data, error } = await supabase.rpc('delete_product', { product_id: product.id, image_path: product.image_path })

does that mean all three statements will either success or fail like that?

Hi, I'm trying to access my database but in my fly.io platform I get the message that supabase.from() is not a function, which I don't understand why this is happening. My code is as follows:

await supabase
        .from('BotRex_Database_Client_Info')
        .select('language')
        .where({ phone_number: number })
        .eq(languages);

Import supabase in my config file:

const { createClient } = require("@supabase/supabase-js");

const SUPABASEURL = process.env.SUPABASE_URL;
const SUPABASE_KEY = process.env.SUPABASE_ANON_KEY`;
const supabaseClient = createClient(SUPABASEURL,SUPABASE_KEY);

module.exports = {
    supabaseClient
};

And call it in my file as follows:

const supabase = require("../config");

I don't know if I'm importing something wrong, but the connection works but the code stops at

supabase.from('BotRex_Database_Client_Info')

which maybe is an error on my part.

Hi, I’m working on an application that will require multiple environments. At least 2 for development and production. I read some posts on doing it before project creation, but I think it’s a little too late for that since I’m in a little deep now. What options do I have to create multiple environments? I was hoping to just use a backup of one DB and load it into another but doesn’t seem like I can much besides “restore” from the backups dashboard Thanks in advance!

Using SvelteKit. It works fine on Chromium, but I'm not able to log in on Safari. Anyone got any tips?

Does anyone know if it's possible to search based on a partial word? If I have a string saved called 'bullet', I want to be able to get the result just by typing 'bul' or 'bull'. Right now it's making me match the full word in order to return the correct search term. I setup the

fts

column and index like this:

sql
ALTER TABLE public.profiles ADD COLUMN fts tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(name,'') || ' ' || description)) stored;

CREATE INDEX IF NOT EXISTS profile_fts ON public.profiles USING GIN (fts);

https://supabase.com/docs/guides/database/full-text-search#match-any-search-words

Hi all, I'm currently looking to authenticate my user against an IdP (free plan) using FastAPI. I was already looking at Firebase and Auth0. Would it be possible with Supabase ? I haven't see much guides / documentations. Looking at the doc, Python Client Library come from Supabase community, and might not be enough mature ? (mote from https://github.com/anand2312/supa-fastapi)

I finally got around to installing the cli as I have a new project I want to start and it seems the perfect opportunity, however I cannot get the postgres password in order to access the database using datagrip or anything else. What is the recommended way to proceed, as changing the password for the postgres user is possible, it will make the database inaccessible from the supabase console. Anyone got any tips here>

So, for reasons I don't want to go too deeply into (I don't want this post to be about the pros/cons of doing this vs the actual question), I have been trying to integrate the email send -> verify flows from Auth into my own codebase. It's not been terribly bad - I already have an email provider and I used Prisma to fetch the auth schema so I can edit the

users

table directly. For resetting password, there's two columns.

recovery_token

and

recovery_sent_at

- I am generating the values for both of those in my code and sending the email with a link to my own web app. Then with the token they can set a new password, which is stored in the

encrypted_password

column. The problem I am having, is the GoTrue library has a method called`hashPassword`( https://github.com/supabase/gotrue/blob/d536ed5ae341e818d8e1d83e83d237ad3c556e4a/models/user.go#L189) that calls a bcrypt implementation written in Go: https://pkg.go.dev/golang.org/x/crypto/bcrypt#GenerateFromPassword that uses a "Cost" of 10. They don't expose a method for this in their JS library. The Node implementations of bcrypt don't use cost, they let you specify a number of salt rounds to use. So even though I am hashing the new password with bcrypt, it's incompatible with their login system. I really hope there's a way around this, because it's the only thing stopping me from integrating the full flow into my own application code.

Hi, i disabled PG_NET extension in supabase UI and wanted to enable it again and now i am getting: "Failed to toggle PG_NET: failed to create pg.extensions: schema net is not a member of extension "pg_net"" i assume PG_NET is pretty important for supabase webhooks right? So i need to get this running again.

The little console log-esque readout in the SQL editor, or even with actual console logged error objects like this:

{
  "code": "22P02",
  "details": "Array value must start with \"{\" or dimension information.",
  "hint": null,
  "message": "malformed array literal: \"\""
}

The function knows where it couldn't proceed but if it says "Oh well the issue is where the right parenthese is" in a function with twenty lines of right parentheses, I am not sure how to address that. I am calling an RPC using the JS client and I am supplying params the same way as I always do with every other RPC (which work just fine) and there actually aren't any errors when doing the "create or replace" so I am not sure what's going on.

I'm back again. Trying to have a function that listens to a specific table row and logs any updates to it to the console. I feel like this should be straightforward, but I keep getting errors stating either "from(...).eq" or "from(...).on" are not functions . Here is the code I am using: const subscription = supabase .from('testinfo') .eq('id',

b12d5cab-8d8b-43d6-b658-10812de0adbe

) .on('UPDATE', (data) => { console.log(data) }) .subscribe() This is slightly modified code from these two videos showing realtime subscriptions: (the table id is correct and that exact .eq works elsewhere in my code, and if I remove it and just subscribe to the whole table, that's when I get the "from(...).on" variation of the error message) Is there a different way I should be doing this?

Restored project and upon authentication request I get the error

"message": "An invalid response was received from the upstream server"

. Postgres Logs read :`cannot drop constraint users_email_key on table users because other objects depend on it` I had never come across this issue using authentication before - So I suspect it is due to the database restore. Any ideas ?

Hi all ! What do I do with this kind of error? Error: P4002 The schema of the introspected database was inconsistent: The schema of the introspected database was inconsistent: Cross schema references are only allowed when the target schema is listed in the schemas property of your datasource.

public.clients

points to

auth.users

in constraint

clients_id_fkey

. Please add

auth

to your

schemas

property and run this command again.

Title ^. I want to know if its automatically sanitized, or if we need to sanitize before inserting. Also I want to know how exactly you are able to validate the data we're submitting. Example making sure email is actually an email and password meets requirements

I manually setup a database thru the supabase dashboard and now I'd like to run it locally using postgres. I followed the steps to setup local development (https://supabase.com/docs/guides/cli/local-development) but it doesn't do quite what I need it to. What I need to do is clone the schema from my production database to a local script (probably .sql?) that any of our developers can run to setup a clone of the production database on a local postgres database. Building a Nextjs 13 React app with Typescript.

i have a query that is working in the supabse sql editor but not working in javascript - i can't figure out why for the life of me. the sql query is:

update rsvps set status = 'not attending' where id='b57b4ac0-de3a-4bb7-8511-ecb4c30d2d25'

and this is what i have in javascript: console.log(rsvp); console.log(rsvpStatus); let { data, error, status } = await supabase .from("rsvps") .update({ status: "not attending" }) .eq("id", rsvp); the console.logs before show that rsvp = b57b4ac0-de3a-4bb7-8511-ecb4c30d2d25 and i've tried replacing rsvp with that string and it still doesn't work. the query is returning a 204 status as expected. any ideas what's wrong or how to go about debugging this???

Hi, So I followed the example here: https://supabase.com/docs/guides/database/tables#schemas By doing the following in the SQL editor:

sql
create schema private;
create table salaries (
  id bigint generated by default as identity primary key,
  salary bigint not null,
  actor_id bigint not null references public.actors
);
create table private.salaries (
  id bigint generated by default as identity primary key,
  salary bigint not null
);

I attempt to run

supabase db dump --debug

but get the following error:

2023/02/02 20:50:56 Recv First Byte
pg_dump: error: query failed: ERROR:  permission denied for schema private
pg_dump: detail: Query was: LOCK TABLE "private"."salaries" IN ACCESS SHARE MODE
2023/02/02 20:50:58 Sent Header: Host [docker]
2023/02/02 20:50:58 Sent Header: User-Agent [Go-http-client/1.1]
2023/02/02 20:50:58 Send Done
2023/02/02 20:50:58 Recv First Byte
Error: Error running pg_dump on remote database: error running container: exit 1

I then attempted to do the same using pg_dump, but I also get this same error. Any suggestions on what I may be doing wrong?

Hi, I have several triggers that call public functions when an auth.user is deleted. It will go through and delete related records from other public tables. Should I be concerned that these public function are exposed to the API? Or should RLS keep me safe?

I have Next.js API routes that get triggered to send out emails. At the top of the API handler it first checks if a user exists and if they have a valid email address. Right now I'm using the service key to fetch this but anyone could call the api route and get the users data back. What's the best way to protect this? The emails are sent out based on user actions (no session usually). Here is an example of the user data check:

js
import type { NextApiRequest, NextApiResponse } from 'next';

import { getSupabaseServicePublic } from '@/utils/initSupabaseService';

export default async function handler(req: NextApiRequest, res: NextApiResponse) {
    const { user_id } = req.query as { user_id: string };

    const supabaseService = getSupabaseServicePublic();

    switch (req.method) {
        case 'GET':
            try {
                // Check is user exists in Supabase
                const { data, error } = await supabaseService
                    .from('users')
                    .select('id, email_address, email_verified')
                    .eq('id', user_id)
                    .single();

                if (error) {
                    throw new Error('User does not exist!');
                }

                return res.status(200).json({ user: data });
            } catch (error) {
                return res.status(400).json(null);
            }

        default:
            res.status(405).send({ message: 'Method not allowed' });
            break;
    }
}

how to integrate supabase with stripe for SAAS application?

I thought about maybe first creating a project that would represent the development environment. When everything is tested and set up properly, we could (?) copy that project into a new one? Then we could just import all the data from production to dev if there is a critical error in production. However, im not sure if this is the best way to approach this. Any suggestions, articles, examples on how to tackle this? Thank you

I have a +layout.svelte file where I try and load the user that from supabase, this works fine. In another svelte component I'm trying to retrieve the user onAuthStateChanged to load its user content but the AuthStateChanged never gets called. What am I doing wrong? layout.svelte:

js
onMount(async () => {
    const { data, error } = await supabase.auth.getSession()
    invalidateAll();
    user.set(data.session?.user);
    loading = false
    
    if (data.session?.user == undefined) {
      goto('/')
    }
});

User page:

js
onMount(() => {
    supabase.auth.onAuthStateChange(( _, session) => {
      user.set(session?.user);
      loading = false
      if(session?.user){
        loadAnomalies();
      }
    });
  })

Note that if I tab out of the page and back in, it suddenly works, the loader disappears and I see the user content