Supabase•2mo ago

Using SQL editor to create prisma user fails due to not being a superuser

To give a little bit context to this, I have done this successfully before as I have had prisma working in my project for a while. However recently after making a lot of changes to the database structure I was trying to pull those changes to prisma schemas as follows:

npx prisma db pull

npx prisma db pull

I run into following error:

Error: FATAL: Authentication error, reason: "Unsupported or invalid secret format"

Error: FATAL: Authentication error, reason: "Unsupported or invalid secret format"

After trying to figure it out for a while I reasoned that maybe the prisma user I created in the past is missing. Not sure if it should show up in the auth/users, but at least there I can't see it. It's possible I would have accidentally deleted it while testing things. So I figured that let's create a new one. I go to SQL Editor in the project and I already had saved snippet there to run from last time. It's essentially step 1 from supabase's own docs copied. Only diff being the password. https://supabase.com/docs/guides/database/prisma When trying to run the query I get following error however

ERROR:  42501: must be superuser to create bypassrls users

ERROR:  42501: must be superuser to create bypassrls users

I tried googling this and searching for this error here. Didn't really find the answer to this. Partly what adds to my confusion is next to when pressing run for snippet there is source and role selectors. For source I have primary database and for role I have postgres, which has following description

The default Postgres/superuser role. This has admin privileges.
It will bypass Row Level Security (RLS) policies.

The default Postgres/superuser role. This has admin privileges.
It will bypass Row Level Security (RLS) policies.

To me this sounds like I ran the snippet as a superuser, yet it complains that I'm not one. Any help would be welcome. My current theory is that I don't have prisma user anymore for whatever reason (should it show up at auth/users or is there other way to check?) and I can't seem to create one for reason I can't quite understand as I seem to be superuser and running the snippet as superuser yet getting error complaining not being one. Here's picture of the situation:

Prisma | Supabase Docs

Prisma Quickstart

21 Replies

silentworks•2mo ago

Supabase removed the permissions to allow you to create a superuser as it was very destructive and folks were deleting stuff they shouldn't and it was becoming an issue for Supabase. With Prisma you don't require a superuser to perform db pull and so forth, your issue is likely related to something else as we have others on here using Prisma without this issue.

ATLOP•2mo ago

Okay, so I'm a bit confused by this response. Appreciate it regardless though! You are saying that prisma doesn't require superuser to perform db pull, however I assume it still does require prisma user, right? To clarify the issue for me seems to be that following the docs of supabase: https://supabase.com/docs/guides/database/prisma 1. Step 1 in the docs is to create the prisma user, for that they give the sql snippet. 2. However when trying to run this snippet to create the prisma user I get the error "must be superuser to create bypassrls users". So the issue isn't that I'm trying to create a superuser, right. It's that to create "bypassrls user", which supabase tells prisma user should be, requires me to be superuser. 3. Not sure if I'm missing something here, but as I highlighted in the picture it seems to me that the role I have selected when running the snippet would be superuser or have those permissions. I don't think I'm trying to create a superuser (unless "bypassrls user" is the same as superuser). I'm happy to hear others are using Prisma without this issue, but I guess I'm just wondering how would those people if starting to use Prisma today would create the prisma user. Are they following the method in the supabase docs or are they changing it in some way? (Like is the bypassrls not needed anymore or something?) Do they manage to use that snippet and not get the error I had (in which case are they using different role or something?)

Prisma | Supabase Docs

Prisma Quickstart

silentworks•2mo ago

You are correct about it not creating a superuser. I just tested the guide and created the prisma user and didn't get any error. Can you check that the role set in your SQL editor is postgres?

silentworks•2mo ago

ATLOP•2mo ago

Should be as I see there circled in red. I assume when you tested you used the bypassrls as well.

silentworks•2mo ago

Yeah mine is the same

silentworks•2mo ago

This is in a project that was created a week or so ago, so it's new enough

ATLOP•2mo ago

However when navigating to the place you had picture from I find following. Correct me if I'm wrong here. It seems I do have already a prisma user? Or is prisma database role different from user we are trying to create? Also seems that postgres is not superuser itself, so now it makes sense why I couldn't run the codesnippet. Is your postgres superuser there or what?

silentworks•2mo ago

Yes you already have the prisma user setup then. No there are no superuser roles available to us in Supabase anymore.

silentworks•2mo ago

Mine is the same as yours

ATLOP•2mo ago

First of all, I really appreciate you helping with this! At least being able to confirm it's not the prisma user issue is a big step forwards for me. I will show my setup on code side in case you can spot something there that seems off or if you have any advice, but otherwise I'll look for more help in prisma's discord I suppose. Here's the full error I get

PS C:\Main Data\Dev\app-name> npx prisma db pull
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": PostgreSQL database "postgres", schema "public" at "aws-0-eu-north-1.pooler.supabase.com:5432"

✖ Introspecting based on datasource defined in prisma\schema.prisma

Error: FATAL: Authentication error, reason: "Unsupported or invalid secret format"

PS C:\Main Data\Dev\app-name> npx prisma db pull
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": PostgreSQL database "postgres", schema "public" at "aws-0-eu-north-1.pooler.supabase.com:5432"

✖ Introspecting based on datasource defined in prisma\schema.prisma

Error: FATAL: Authentication error, reason: "Unsupported or invalid secret format"

In prisma/schema.prisma the relevant part

datasource db {
  provider  = "postgresql"
  url       = env("SUPABASE_CONNECTION")
  directUrl = env("DIRECT_URL")
}

datasource db {
  provider  = "postgresql"
  url       = env("SUPABASE_CONNECTION")
  directUrl = env("DIRECT_URL")
}

In env

SUPABASE_CONNECTION="postgresql://prisma.[project-id]:[prisma-user-password]@aws-0-eu-north-1.pooler.supabase.com:5432/postgres"


# Direct connection to the database. Used for migrations
DIRECT_URL="postgresql://prisma.[project-id]:[prisma-user-password]@aws-0-eu-north-1.pooler.supabase.com:5432/postgres"

SUPABASE_CONNECTION="postgresql://prisma.[project-id]:[prisma-user-password]@aws-0-eu-north-1.pooler.supabase.com:5432/postgres"


# Direct connection to the database. Used for migrations
DIRECT_URL="postgresql://prisma.[project-id]:[prisma-user-password]@aws-0-eu-north-1.pooler.supabase.com:5432/postgres"

Not sure if those should be the same, but so far those had worked I think. Any ideas what I should look towards?

silentworks•2mo ago

The first thing that came to mind from your error message is that your password is using a special character like an @ in it. ~~You also have postgresql:// whilst the docs seem to state postgres://~~ ignore this, the docs seem wrong here.

ATLOP•2mo ago

Unfortunately not, password is mix of capitalized and non capitalized normal letters and numbers. Similar to this I created with keepass just now "zNOJjzRX6QEr3arleY87hj5Zo" I guess one thing I could test is to delete the prisma user in supabase and try to create another one. Just to make sure that part works. However I would be bit nervous about that I suppose since the error I got when trying to create one didn't seem to come from that user already existing.

silentworks•2mo ago

Yeah it should have said user already exists as that's what I get when I try to create that same user again.

ATLOP•2mo ago

Oh, okay, that's relieving!

silentworks•2mo ago

I've found this on the GitHub discussion from someone who had the same issue https://github.com/orgs/supabase/discussions/34447

ATLOP•2mo ago

Will trying changing the password regardless I think. Seems to have worked for some people and nothing lost on giving it a go. Figured I would let you know. This solved it for me. Similar to the poster in that discussion, I simply altered my password by changing it to one that it already was (so should have been no change). Not sure why that worked as it makes no sense to me. It's the same password that was in my create prisma user snippet and in my env file. It's almost like my password changed by itself at some point or something. Maybe some of the recent changes require you to reset your prisma user password etc? Not sure. But solved. Once again appreciate your patience, this was huge help for me!

silentworks•2mo ago

Wait are you saying using the default new_password worked? and changing it to anything else makes it fail?

ATLOP•2mo ago

Not exactly. Basically here are my last 2 sql snippets run in the project: Custom User Creation and Privileges Setup (Ran this first time 1-2 months ago when installing prisma to the project, then ran copy of it yesterday/today bunch of times after getting the error with db pull)

-- Create custom user
create user "prisma" with password 'zNOJjzRX6QEr3arleY87hj5Zo' bypassrls createdb;
-- extend prisma's privileges to postgres (necessary to view changes in Dashboard)
grant "prisma" to "postgres";
-- Grant it necessary permissions over the relevant schemas (public)
grant usage on schema public to prisma;
grant create on schema public to prisma;
grant all on all tables in schema public to prisma;
grant all on all routines in schema public to prisma;
grant all on all sequences in schema public to prisma;
alter default privileges for role postgres in schema public grant all on tables to prisma;
alter default privileges for role postgres in schema public grant all on routines to prisma;
alter default privileges for role postgres in schema public grant all on sequences to prisma;

-- Create custom user
create user "prisma" with password 'zNOJjzRX6QEr3arleY87hj5Zo' bypassrls createdb;
-- extend prisma's privileges to postgres (necessary to view changes in Dashboard)
grant "prisma" to "postgres";
-- Grant it necessary permissions over the relevant schemas (public)
grant usage on schema public to prisma;
grant create on schema public to prisma;
grant all on all tables in schema public to prisma;
grant all on all routines in schema public to prisma;
grant all on all sequences in schema public to prisma;
alter default privileges for role postgres in schema public grant all on tables to prisma;
alter default privileges for role postgres in schema public grant all on routines to prisma;
alter default privileges for role postgres in schema public grant all on sequences to prisma;

Password Update for Pirsma User (Ran just now)

alter user "prisma" with password 'zNOJjzRX6QEr3arleY87hj5Zo';

alter user "prisma" with password 'zNOJjzRX6QEr3arleY87hj5Zo';

The actual password replaced by similar one I just generated. Basically what I'm saying is when I originally created prisma user (month or two ago) I didn't use any default password. I generated one similar to what you see in queries above with keepass as I do with all my passwords. Then things worked fine with db pull etc. Then I didn't touch prisma for a month or so and worked on other things. Yesterday I finished a lot of changes to supabase tables. Removing and adding columns etc. Tried to db pull and got the auth error. Now "changing" the password back to what it already was (or should have been at least) fixed everything. From my point of view I created the prisma user month ago with password similar to 'zNOJjzRX6QEr3arleY87hj5Zo'. Then wait a month and get error while trying to db pull. Then now change password to same one it should already be and suddenly db pull works. I know it sounds crazy.

silentworks•2mo ago

Yeah this does sound crazy. Let's see in a few months time if this changes again. If it does you should open an issue with Supabase support at that point.

ATLOP•2mo ago

Yeah, I'll keep the eye on it. Some part of me wonders if they did recently change something regarding superuser rights etc and that could have messed with it. Like set it to null, requiring password reset regardless etc. I'm quite sure it was the same password since I can quite literally see the same password that I changed it to now in create prisma user sql query and in the env file connections. Either way if it happens again I will notice as especially going forward I do need to regularly update prisma schemas on the project.

Gaming

Programming

Using SQL editor to create prisma user fails due to not being a superuser

Did you find this page helpful?