Supabase Permissions

[briancao]

Some users are reporting issues with Prisma migrations and a lack of permissions on Supabase.

9:12:48 PM: Error: P3018
9:12:48 PM: 
9:12:48 PM: A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve
9:12:48 PM: 
9:12:48 PM: Migration name: 02_add_event_data
9:12:48 PM: 
9:12:48 PM: Database error code: 42501
9:12:48 PM: 
9:12:48 PM: Database error:
9:12:48 PM: ERROR: must be owner of table event
9:12:48 PM: 
9:12:48 PM: DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E42501), message: "must be owner of table event", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("aclchk.c"), line: Some(3583), routine: Some("aclcheck_error") }
9:12:48 PM: 
9:12:48 PM: ✗ Command failed: prisma migrate deploy

If you see this error, there have been two reported ways to solve it.

Prisma's Solution

The first comes directly from Prisma's documentation. Those commands will grant your postgres user full privledges on all tables.

https://supabase.com/docs/guides/integrations/prisma#troubleshooting

CREATE SCHEMA IF NOT EXISTS "auth";
CREATE SCHEMA IF NOT EXISTS "extensions";
create extension if not exists "uuid-ossp"      with schema extensions;
create extension if not exists pgcrypto         with schema extensions;
create extension if not exists pgjwt            with schema extensions;

grant usage on schema public to postgres, anon, authenticated, service_role;
grant usage on schema extensions to postgres, anon, authenticated, service_role;
alter user supabase_admin SET search_path TO public, extensions; -- don't include the "auth" schema

grant all privileges on all tables in schema public to postgres, anon, authenticated, service_role, supabase_admin;
grant all privileges on all functions in schema public to postgres, anon, authenticated, service_role, supabase_admin;
grant all privileges on all sequences in schema public to postgres, anon, authenticated, service_role, supabase_admin;

alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to postgres, anon, authenticated, service_role;

alter default privileges for user supabase_admin in schema public grant all on sequences to postgres, anon, authenticated, service_role;
alter default privileges for user supabase_admin in schema public grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges for user supabase_admin in schema public grant all on functions to postgres, anon, authenticated, service_role;

alter role anon set statement_timeout = '3s';
alter role authenticated set statement_timeout = '8s';

rohandebsarkar's Solution

The second comes from an Umami user @rohandebsarkar

comment: #1486 (reply in thread)

ALTER TABLE account OWNER TO postgres;
ALTER TABLE website OWNER TO postgres;
ALTER TABLE session OWNER TO postgres;
ALTER TABLE pageview OWNER TO postgres;
ALTER TABLE event OWNER TO postgres;
ALTER TABLE event_data OWNER TO postgres;

[candideu]

Thanks for this! I'm sharing what I did here as well (from #1519) in case anyone has the same issue as me:

I ran both rohandebsarkar's Solution and Prisma's Solution in Supabase's SQL Editor. I then ran into the error described here: #1486 (reply in thread).

To solve this, I did the following:

1. Forked the umami GitHub project
2. Cloned my fork locally using GitHub Desktop
3. In my local folder, I created a file called .env. In this file, I typed in DATABASE_URL= and pasted the postgres URL from Supabase (Connection Pooling > Connection String) along with ?pgbouncer=true
4. Once that was done, I ran the following commands in my command prompt (with the directory set to my local cloned umami fork):

yarn install
yarn build
yarn prisma migrate resolve --applied "02_add_event_data"

Once that was done, I redeployed on Vercel, and it now works.