Errors when running migrations

[hami-sh]

Hello there! Thanks for making this package. When trying to run your script, I get the following output. Note that some things fail, like

psql:sql/01_database_schema.sql:159: ERROR: must be member of role "supabase_admin"

Wondering if you could take a look? This happened when trying to run from both the SQL Editor on Supabase, and from psql

~/Developer/flutter_supabase_chat_core/example/utils (main*) » ./prepare.sh -h XXXXXXXXXXX.pooler.supabase.com -p 5432 -d postgres -U postgres.XXXXXXXXXXXXx

Password for user postgres.XXXXXXXXXXXXXX:
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
REVOKE
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
psql:sql/01_database_schema.sql:148: ERROR:  must be member of role "supabase_admin"
psql:sql/01_database_schema.sql:149: ERROR:  must be member of role "supabase_admin"
psql:sql/01_database_schema.sql:150: ERROR:  must be member of role "supabase_admin"
psql:sql/01_database_schema.sql:151: ERROR:  must be member of role "supabase_admin"
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
psql:sql/01_database_schema.sql:158: ERROR:  must be member of role "supabase_admin"
psql:sql/01_database_schema.sql:159: ERROR:  must be member of role "supabase_admin"
psql:sql/01_database_schema.sql:160: ERROR:  must be member of role "supabase_admin"
psql:sql/01_database_schema.sql:161: ERROR:  must be member of role "supabase_admin"
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
psql:sql/01_database_schema.sql:168: ERROR:  must be member of role "supabase_admin"
psql:sql/01_database_schema.sql:169: ERROR:  must be member of role "supabase_admin"
psql:sql/01_database_schema.sql:170: ERROR:  must be member of role "supabase_admin"
psql:sql/01_database_schema.sql:171: ERROR:  must be member of role "supabase_admin"
ALTER PUBLICATION
ALTER PUBLICATION
ALTER PUBLICATION
Password for user postgres.XXXXXXXXXXXXXXXXXXX:
psql:sql/02_database_trigger.sql:1: NOTICE:  trigger "on_auth_user_created" for relation "auth.users" does not exist, skipping
DROP TRIGGER
psql:sql/02_database_trigger.sql:2: NOTICE:  function chats.handle_new_user() does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
CREATE TRIGGER
CREATE FUNCTION
CREATE TRIGGER
CREATE FUNCTION
CREATE TRIGGER
Password for user postgres.XXXXXXXXXXXXXXXXXXX:
psql:sql/03_database_policy.sql:1: NOTICE:  policy "chats.users_grant_create" for relation "chats.users" does not exist, skipping
DROP POLICY
psql:sql/03_database_policy.sql:2: NOTICE:  policy "chats.users_grant_read" for relation "chats.users" does not exist, skipping
DROP POLICY
psql:sql/03_database_policy.sql:3: NOTICE:  policy "chats.users_grant_update" for relation "chats.users" does not exist, skipping
DROP POLICY
psql:sql/03_database_policy.sql:4: NOTICE:  policy "chats.users_grant_delete" for relation "chats.users" does not exist, skipping
DROP POLICY
psql:sql/03_database_policy.sql:6: NOTICE:  policy "chats.rooms_grant_create" for relation "chats.rooms" does not exist, skipping
DROP POLICY
psql:sql/03_database_policy.sql:7: NOTICE:  policy "chats.rooms_grant_read" for relation "chats.rooms" does not exist, skipping
DROP POLICY
psql:sql/03_database_policy.sql:8: NOTICE:  policy "chats.rooms_grant_update" for relation "chats.rooms" does not exist, skipping
DROP POLICY
psql:sql/03_database_policy.sql:9: NOTICE:  policy "chats.rooms_grant_delete" for relation "chats.rooms" does not exist, skipping
DROP POLICY
psql:sql/03_database_policy.sql:11: NOTICE:  policy "chats.messages_grant_create" for relation "chats.messages" does not exist, skipping
DROP POLICY
psql:sql/03_database_policy.sql:12: NOTICE:  policy "chats.messages_grant_read" for relation "chats.messages" does not exist, skipping
DROP POLICY
psql:sql/03_database_policy.sql:13: NOTICE:  policy "chats.messages_grant_update" for relation "chats.messages" does not exist, skipping
DROP POLICY
psql:sql/03_database_policy.sql:14: NOTICE:  policy "chats.messages_grant_delete" for relation "chats.messages" does not exist, skipping
DROP POLICY
psql:sql/03_database_policy.sql:16: NOTICE:  function chats.is_auth() does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
psql:sql/03_database_policy.sql:28: NOTICE:  function chats.is_owner() does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
psql:sql/03_database_policy.sql:40: NOTICE:  function chats.is_member() does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
psql:sql/03_database_policy.sql:52: NOTICE:  function chats.is_chat_member() does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
CREATE POLICY
Password for user postgres.XXXXXXXXXXXXXXXXXXX:
INSERT 0 2
psql:sql/04_storage.sql:8: NOTICE:  policy "storage.object_grant_create_auth_chats_assets" for relation "storage.objects" does not exist, skipping
DROP POLICY
CREATE POLICY
psql:sql/04_storage.sql:17: NOTICE:  policy "storage.object_grant_read_auth_chats_assets" for relation "storage.objects" does not exist, skipping
DROP POLICY
CREATE POLICY
psql:sql/04_storage.sql:26: NOTICE:  policy "storage.object_grant_update_auth_chats_assets" for relation "storage.objects" does not exist, skipping
DROP POLICY
CREATE POLICY
psql:sql/04_storage.sql:39: NOTICE:  policy "storage.object_grant_delete_auth_chats_assets" for relation "storage.objects" does not exist, skipping
DROP POLICY
CREATE POLICY
psql:sql/04_storage.sql:48: NOTICE:  policy "storage.object_grant_create_auth_chats_user_avatar" for relation "storage.objects" does not exist, skipping
DROP POLICY
CREATE POLICY
psql:sql/04_storage.sql:57: NOTICE:  policy "storage.object_grant_read_auth_chats_user_avatar" for relation "storage.objects" does not exist, skipping
DROP POLICY
CREATE POLICY
psql:sql/04_storage.sql:66: NOTICE:  policy "storage.object_grant_update_auth_chats_user_avatar" for relation "storage.objects" does not exist, skipping
DROP POLICY
CREATE POLICY
psql:sql/04_storage.sql:79: NOTICE:  policy "storage.object_grant_delete_auth_chats_user_avatar" for relation "storage.objects" does not exist, skipping
DROP POLICY
CREATE POLICY

[rickypid]

Hi @hami-sh,

The script is definitely improvable to get better diagnostics. Try modified the prepare.sh script and the filel 01_database_schema.sql as below:

prepare.sh

#!/bin/bash

while getopts h:p:d:U: flag
do
    case "${flag}" in
        h) hostname=${OPTARG};;
        p) port=${OPTARG};;
        d) database=${OPTARG};;
        U) user=${OPTARG};;
    esac
done

psql -U $user -h $hostname -p $port -d $database -f ./sql/01_database_schema.sql
psql -U $user -h $hostname -p $port -d $database -f ./sql/02_database_trigger.sql
psql -U $user -h $hostname -p $port -d $database -f ./sql/03_database_policy.sql
psql -U $user -h $hostname -p $port -d $database -f ./sql/04_storage.sql

01_database_schema.sql

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

CREATE SCHEMA chats;

ALTER SCHEMA chats OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

CREATE TABLE chats.messages (
    id bigint NOT NULL,
    "createdAt" bigint,
    metadata jsonb,
    duration bigint,
    "mimeType" text,
    name text,
    "remoteId" text,
    "repliedMessage" jsonb,
    "roomId" bigint NOT NULL,
    "showStatus" boolean,
    size bigint,
    status text,
    type text,
    "updatedAt" bigint,
    uri text,
    "waveForm" jsonb,
    "isLoading" boolean,
    height double precision,
    width double precision,
    "previewData" jsonb,
    "authorId" uuid NOT NULL,
    text text
);

ALTER TABLE chats.messages OWNER TO postgres;

ALTER TABLE chats.messages ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME chats.messages_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE chats.rooms (
    id bigint NOT NULL,
    "imageUrl" text,
    metadata jsonb,
    name text,
    type text,
    "userIds" uuid[] NOT NULL,
    "lastMessages" jsonb,
    "userRoles" jsonb,
    "createdAt" bigint NOT NULL,
    "updatedAt" bigint NOT NULL
);

ALTER TABLE chats.rooms OWNER TO postgres;

ALTER TABLE chats.rooms ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME chats.rooms_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE chats.users (
    "firstName" text,
    "imageUrl" text,
    "lastName" text,
    metadata jsonb,
    role text,
    id uuid NOT NULL,
    "createdAt" bigint NOT NULL,
    "updatedAt" bigint NOT NULL,
    "lastSeen" bigint NOT NULL
);

ALTER TABLE chats.users OWNER TO postgres;

ALTER TABLE ONLY chats.messages
    ADD CONSTRAINT messages_pkey PRIMARY KEY (id);

ALTER TABLE ONLY chats.rooms
    ADD CONSTRAINT rooms_pkey PRIMARY KEY (id);

ALTER TABLE ONLY chats.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);

ALTER TABLE ONLY chats.messages
    ADD CONSTRAINT "messages_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES auth.users(id) ON DELETE CASCADE;

ALTER TABLE ONLY chats.messages
    ADD CONSTRAINT "messages_roomId_fkey" FOREIGN KEY ("roomId") REFERENCES chats.rooms(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY chats.users
    ADD CONSTRAINT users_id_fkey FOREIGN KEY (id) REFERENCES auth.users(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE chats.messages ENABLE ROW LEVEL SECURITY;

ALTER TABLE chats.rooms ENABLE ROW LEVEL SECURITY;

ALTER TABLE chats.users ENABLE ROW LEVEL SECURITY;

REVOKE USAGE ON SCHEMA chats FROM PUBLIC;
GRANT USAGE ON SCHEMA chats TO anon;
GRANT USAGE ON SCHEMA chats TO authenticated;
GRANT USAGE ON SCHEMA chats TO service_role;

GRANT ALL ON TABLE chats.messages TO anon;
GRANT ALL ON TABLE chats.messages TO authenticated;
GRANT ALL ON TABLE chats.messages TO service_role;

GRANT ALL ON SEQUENCE chats.messages_id_seq TO anon;
GRANT ALL ON SEQUENCE chats.messages_id_seq TO authenticated;
GRANT ALL ON SEQUENCE chats.messages_id_seq TO service_role;

GRANT ALL ON TABLE chats.rooms TO anon;
GRANT ALL ON TABLE chats.rooms TO authenticated;
GRANT ALL ON TABLE chats.rooms TO service_role;

GRANT ALL ON SEQUENCE chats.rooms_id_seq TO anon;
GRANT ALL ON SEQUENCE chats.rooms_id_seq TO authenticated;
GRANT ALL ON SEQUENCE chats.rooms_id_seq TO service_role;

GRANT ALL ON TABLE chats.users TO anon;
GRANT ALL ON TABLE chats.users TO authenticated;
GRANT ALL ON TABLE chats.users TO service_role;

GRANT USAGE ON SCHEMA chats TO anon, authenticated, service_role;
GRANT ALL ON ALL TABLES IN SCHEMA chats TO anon, authenticated, service_role;
GRANT ALL ON ALL ROUTINES IN SCHEMA chats TO anon, authenticated, service_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA chats TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA chats GRANT ALL ON TABLES TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA chats GRANT ALL ON ROUTINES TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA chats GRANT ALL ON SEQUENCES TO anon, authenticated, service_role;

ALTER PUBLICATION supabase_realtime ADD TABLE ONLY chats.messages;

ALTER PUBLICATION supabase_realtime ADD TABLE ONLY chats.rooms;

ALTER PUBLICATION supabase_realtime ADD TABLE ONLY chats.users;

[hami-sh]

That ran without any errors, thanks! Will report back if I have any further issues

[rickypid]

@hami-sh thanks for the feedback, I have prepared the fix here, I will merge shortly