Our team is considering moving our firestore application to supabase. However, we currently use the onSnapshot functionality(https://firebase.google.com/docs/firestore/query-data/listen) extensively throughout our application. We use the firebase web-sdk to listen to real time changes on data.. Every time the app boots up, we start up listeners that listen to collections for changes, allowing our app to basically have a real-time sync of data for all users in the web-browser. I know that supabase has a "changes API", but this looks like it isn't exactly what we need, because when a user loads up the browser, they need to have an "initial snapshot" of the data and then start listening to changes from that snapshot going forward. The changes api appears to only give you changes, but not an initial starting snapshot of the data. Is there a way to do this behavior with supabase? I'm happy to explain further if the above explanation is not helpful. Thanks! -Michael

I am using next-auth in supabase , I have user table and Idea table as one-many relationship , I tried to create policy for delete own user idea but I couldn't here is my condition for deleting next_auth.uid() = userId

I have a site with discord social auth enabled, but I need to get the referenced user_id (from

auth.users

via

auth.identities

), is it possible to get the user_id? Help

Here are my questions: - does upsert require the id (primary key)? What if it is new data and it still does not have a primary key? - should the rows in onConflict be unique or can I specify any row?

This is my RLS policy but it doesn’t work (when I submit while in a session of that specific email - UID). My form doesn't submit anything relate to auth.uid (hmm I only submit the content). Idk if this is the reason why it doesn't work?

Currently Im querying data on each page which is may slow the website and take more Database size. So is there a way to do this, like when you first open the website (any pages) and then it query data from supabase table. When you open other pages of the same web, it will use the data that is queried before. (Like for example: if (!tableData) {const { data: data, error } = await supabase.from('main').select('*'); tableData = data } return tableData;) I’m thinking about using this script for +layout.server.ts but not really sure if this will query multiple times or not Sorry for my bad english :((

How do I edit the users table within the schema.auth table? I am trying to be able to grab the information from the schema.auth table and tie it into my schema.public table so I can use the Id from the schema.auth to tie that to say products so that this specific user has products...I hope that makes sense. any help would be great

I am not sure why this is happening. I (and my test users) login via Supabase's Google oauth connection - but the first time they get redirected back to the base URL, and then they do the flow again and it works the second time. Has anyone else had this problem and know how to fix it by any chance? Here is the code snippet:

export async function signInWithGoogle() {
  const { session, user, error } = await supabase.auth.signInWithOAuth({
    provider: "google",
    options: {
      redirectTo: "https://www.website.com/dashboard",
      queryParams: {
        access_type: "offline",
        prompt: "consent",
        scopes: ["https://www.googleapis.com/auth/calendar.events"],
      },
    },
  });

  if (error) {
    console.log("Error: ", error);
  }
}

Hello there! I basically have a question about how, after the OAuth2 with Discord, we can get users guild maps for dashboard features?

I'm thinking about using migrations only for tables, and then using a script that diffs everything else to local files - if the view, function, trigger etc differs from the file, drop it and recreate it. So, on deploy we'd run prisma migrate, and then our own script that synchronizes non-table items. This would allow us to treat all non-table objects as checked in source code - a single source of truth. Migrations for these kinds of objects just drop and recreate them anyway. Is this a reasonable approach? Are there any tools that work like this that I could use so I don't have to write my own?

Hi, I would like to setup a call with someone there at Supabase to discuss solutions for a new and ever expanding project. Can I set this up please?

Hey, I want to make public buckets so that it's easier to store links to files in the DB, but I am not sure if I can restrict access to view/open those links via RLS. Can someone confirm?

Firstly I created a user, then for testing purposes I deleted this user from console, and create user with same mail again from my app. This is where it going weird, After creating user again, I get user object and there seems no error:

{
  id: 'bcede112-7d3e-4c49-9ab6-5101842f2a15',
  aud: 'authenticated',
  role: 'authenticated',
  email: 'someEmail@gmail.com',
  email_confirmed_at: '2023-05-20T21:21:03.322059064Z',
  phone: '',
  app_metadata: {
    has_active_subscription: false,
    provider: 'email',
    providers: [ 'email' ]
  },
  user_metadata: {},
  identities: [
    {
      id: 'bcede112-7d3e-4c49-9ab6-5101842f2a15',
      user_id: 'bcede112-7d3e-4c49-9ab6-5101842f2a15',
      identity_data: [Object],
      provider: 'email',
      last_sign_in_at: '2023-05-20T21:21:03.319379632Z',
      created_at: '2023-05-20T21:21:03.319427Z',
      updated_at: '2023-05-20T21:21:03.319427Z'
    }
  ],
  created_at: '2023-05-20T21:21:03.317314Z',
  updated_at: '2023-05-20T21:21:03.322296Z'
}

but when I try to log this user, boom there is no user. Because its never created, doesn't appear on console too.

const {data: {user}, error} = await supabase.auth.admin.createUser({
    email: userEmail,
    password: userPassword,
    email_confirm: true,
    app_metadata: {
      has_active_subscription: false,
    },
  })

this is how I create user on server side

Hi there, At sign-up I add a

tenant_id

field (which is an integer) to the new user's

app_metadata

, and I need to use this in row-level security policy. I can't figure out the syntax for accessing this in the policy statement. I thought it would be something like this:

SQL
BEGIN;
  ALTER POLICY "Enable tenant-based access" ON "public"."test" USING (tenant_id = (auth.jwt().app_metadata -> 'tenant_id'::integer));
COMMIT;

But I get a syntax error. Any ideas? Thanks!

this would also be a good discussion for here - https://github.com/orgs/supabase/discussions

I have a node server that directly connects to the Postgres Database on Superbase. And I know the logged in user and it's token (jwt). There is Policy to only allow reading for the logged in user

sql
CREATE POLICY "Enable select for users based on user_id" ON "public"."rlt-test"
AS PERMISSIVE FOR SELECT
TO public
USING (auth.uid() = user_id)

Data looks something like this (with user_id simplified)

| id  | name        | user_id |
| --- | ----------- | ------- |
| 1   | John Doe    | 1       |
| 2   | Jane Doe    | 1       |
| 3   | Susan Smith | 2       |

so now if I do a select via the api with the user_id 1 logged in I get - John - Jane if I do a

select * from test

then I get - John - Jane - Susan Can I execute an sql query that will give me the same result as the API? e.g. maybe somehow set the values that are used within auth.uid() an idea

BEGIN;
SET LOCAL auth.jwt = '...';

SELECT * from test;
COMMIT;

it sadly does not work as I don't know which values to set... and if it can even be set that way 😅 anyone got any ideas?

Is there a reliable use of Supabase for large services compared to AWS Aurora Serverless? I'm going to introduce Supabase to the developers around me, and they suspect that this project may have a problem with scaling. Of course, I'm already reading Showcase or Made With Supabase, but I don't have the exact size, so I'm wondering if there's any information that the team has collected and released.

This is my first time messing with triggers and functions, I wanted to have in my profiles table additional information that the user provide in the registration form, so I copied a simple function and created a trigger on auth after insert, "begin insert into public.profiles (id) values (new.id); return new; end;" But I'm getting an error: "failed to close prepared statement: ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02): ERROR: column \"id\" is of type bigint but expression is of type uuid (SQLSTATE 42804)" Shouldn't new.id be of the correct type already? This line is copied from https://supabase.com/docs/guides/auth/managing-user-data so it should work, similar line works in another project I've seen and no one is getting such an error. I must've skipped an important step I guess? I cant fix it and I'd be very thankful for providing any direction in solving this problem.

I am in the process of enabling SAML authentication for my self-hosted environment. However, I'm encountering an issue when creating a private certificate (for the environment variable in Gotrue => GOTRUE_SAML_PRIVATE_KEY). I keep getting the error message Failed to load configuration: SAML private key not in PKCS#1 format. In the source code, it fails at the following function x509.ParsePKCS1PrivateKey(bytes). Does anyone happen to have an idea? I created the certificate as follows: openssl genrsa -out private-key.pem 4096 openssl rsa -in private-key.pem -outform DER -out private-key.der base64 -w 0 private-key.der > private-key-base64.der

which repo contains the server APIs such as one for generating the typescript types I am trying to fix an issue raised https://github.com/supabase/supabase/issues/14556 as per my exploration, I came to know that these types are being generated on the server side so can someone let me know about the server-side APIs repo I am new to this project and exploring it

I want to setup an autogenerated ID for each of my table rows, however I'd like them to be in a certain format. Something like

FEXXXXXXXXXX

where X is an alphanumerical character. Is there a way I can put this as an expression in the Default Value?

Good morning. I am looking for some help getting my local project up to speed. I am nearing v1, and that means no more hail mary database changes in the web console.I had decided to ignore the cli for everything but edge functions, now I am paying for it.

supabase remote commit --debug

gives me error

...
2023/05/25 09:05:57 PG Send: {"Type":"StartupMessage","ProtocolVersion":196608,"Parameters":{"database":"postgres","user":"postgres"}}
2023/05/25 09:05:57 PG Recv: {"Type":"AuthenticationSASL","AuthMechanisms":["SCRAM-SHA-256"]}
2023/05/25 09:05:57 PG Send: {"Type":"SASLInitialResponse","AuthMechanism":"SCRAM-SHA-256","Data":"n,,n=,r=du1hqGfLEFgx4XrpnRKsj+9Z"}
2023/05/25 09:05:57 PG Recv: {"Type":"AuthenticationSASLContinue","Data":"r=du1hqGfLEFgx4XrpnRKsj+9ZkUQNMBV3doWl3ycFRUbqtkJ9,s=vxLvuGMsKfKIEl1JRBbsiQ==,i=4096"}
2023/05/25 09:05:57 PG Send: {"Type":"SASLResponse","Data":"c=biws,r=du1hqGfLEFgx4XrpnRKsj+9ZkUQNMBV3doWl3ycFRUbqtkJ9,p=LnAY4FOG73/n5u+lFzU222OCWSmkUn7R1elrIxSYpSk="}
2023/05/25 09:05:58 PG Recv: {"Type":"AuthenticationSASLFinal","Data":"v=vXxebxj0pYh0v/8EySC0zCUEp1XREkpDXHaILk4YfsI="}
2023/05/25 09:05:58 PG Recv: {"Type":"AuthenticationOK"}
2023/05/25 09:05:58 PG Recv: {"Type":"ParameterStatus","Name":"default_transaction_read_only","Value":"off"}
.

i can't use

supabase db push

because the remote is the important one

I'm optimizing images on a codebase. Currently the image pulled from the supabase bucket is returning a blob. For example, when I go to open the image in a new tab in the browser, this is the url: blob:http://localhost:5173/11b8b903-9584-43b6-8d95-a7f45e4c8d3f and the image is in the format the user uploaded it in - jpeg or png Is it possible to instead return the images as a webp file? If so, how do I do that? (We are using SvelteKit in this app). fyi I'm a junior dev. This is the function in our api

/**
 *
 * @param url <string> - the Supabase URL string of the image to fetch
 * @returns <string> - a URL object string that can be used in an <img src={getImageSm('someImage')}> tag
 *
 * TODO: string validation
 *       better return object error handling
 */
export const getPostImage = async (url: string) => {
    const { data, error: downloadError } = await imageBucket('images').download(url, {
        transform: {
            width: 384
        }
    });
    if (downloadError) {
        throw downloadError;
    }
    return URL.createObjectURL(data);
};

I have created this table which links users to communities. These users have specific fields for communities, like whether they are admin, which are in the same table as well.

Hello everyone! I am building a small authenticator app to integrate with a 3rd party service. The app is built as a very simple React single page application and uses react-router for url management and the Auth UI component. The app is hosted on

https://example.org

, and initially prompts the user to sign in with supabase. When he does so, he is redirected to

https://example.org/redirect

, an internal route which first runs an edge function, then redirects again the user to the 3rd party service. It works fine with email sign-in, but not with oauth providers. After signing in with google, for example, the redirect is invoked... but without the

/redirect

part. After some troubleshooting I found out the following: - The allowed URLs in my supabase control panel are correct - If I manually browse to

https://example.org/redirect

, the page is opened correctly and the app works as intended - If I change the redirect to another whitelisted static page (so NOT a React SPA), the

/redirect

part is appended correctly - I tested a different hosting provider for the app, and the result did not change I am thinking it could be because I am implementing this flow as a single page application, but isn't it an extremely common scenario to have with supabase? What might be wrong? Is it a bug? Thanks for any assistance 🙂

I have always enjoyed using the cli's diff tool to create migrations, recently; however, it is behaving very strangely. I have updated to the newest version of the cli and this is still happening. Maybe I am missing something obvious? I have made a very small change to my local database (I am hoping to push this live), namely the addition of one column "Title" to my "Section" table. Yet, when I run ...diff... I get an 87 line migration starting with "CREATE INDEX refresh_token_session_id ON auth.refresh_tokens USING btree (session_id);" and absolutely no reference to the new column I added on my "Section" Table. Has anyone ever seen something like this?

Im using https://supabase.nuxtjs.org/get-started , so im getting that message some time when im using my project how could i refresh token or auto refresh token?:

hello there after discord auth why the session not showing user guilds?

I have a user that is authenticated but can only insert data into a table when the RLS expression is set to true and the target roles are all public. Changing either of these results in an error. The same table works with other clients that have been registered for months. The difference is they are running on supabase python sdk==0.5.3 and the one I am currently trying to get to work is on the newest python one==1.0.3. I can also send the code but it is identical between the two versions. There are policies for inserting updating and selecting, I am also using returning='minimal' and have tried every iteration possible. It seems like the user can't access auth.uid() and doesn't count as authenticated but when getting the session it says it is and checking through the dashboard also proves it is. I have no more ideas how to debug this so any help would be appreciated.

I'm looking for a reliable way to query my MAU counts. The reason for this is that it's a B2B/B2C hybrid SaaS app that I'm building and one of the utilisation metrics I want to bill my clients for is their proportion of my MAUs I'm perfectly happy joining

auth.users

to my own account table and joining this to my organisation_users and organisation tables etc – I've got all that and I can attribute what I think is an MAU to an organisation. Below is a massively simplified version of the query which cuts out all the organisation/user joins and just joins

auth.users

to

auth.refresh_tokens

select 
  count(distinct auth.users.email)
from auth.users
  inner join auth.refresh_tokens on auth.refresh_tokens.user_id::uuid = auth.users.id
where auth.refresh_tokens.created_at > (current_timestamp - interval '1 months')

You might be asking why I'm joining to

refresh_tokens

and not just using

auth.users.last_sign_in_at

? Well I noticed that

auth.users.last_sign_in_at

is literally timestamping the last actual sign in and not updating for a returning user like I personally expected (for example, I have a user who was definitely active this month but

auth.users.last_sign_in_at

is showing a date in April. The only thing I could find to test if a user was effectively an MAU this month was to see if they had a refresh token created in the last month... Is this reliable and a good way of doing this? How does Supabase actually calculate an MAU? As an aside, is anyone else's MAU reporting in the dashboard under settings > usage showing zero MAUs this month? (I'm meaning to open an ticket)