mirror of
https://github.com/jordienr/zenblog.git
synced 2026-08-28 18:24:26 -05:00
262 lines
10 KiB
PL/PgSQL
262 lines
10 KiB
PL/PgSQL
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 EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium";
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "pg_graphql" WITH SCHEMA "graphql";
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "extensions";
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA "extensions";
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "pgjwt" WITH SCHEMA "extensions";
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "supabase_vault" WITH SCHEMA "vault";
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA "extensions";
|
|
|
|
CREATE FUNCTION "public"."generate_slug"("title" "text") RETURNS "text"
|
|
LANGUAGE "plpgsql"
|
|
AS $$
|
|
DECLARE
|
|
slug_text text;
|
|
normalized_title text;
|
|
BEGIN
|
|
-- Normalize the title by removing special characters and converting to lowercase
|
|
normalized_title := lower(regexp_replace(title, '[^\w\s]', '', 'g'));
|
|
|
|
-- Replace spaces with dashes to create the slug
|
|
slug_text := regexp_replace(normalized_title, '\s+', '-', 'g');
|
|
|
|
-- Ensure the slug is unique in the articles table
|
|
WHILE EXISTS(SELECT 1 FROM articles WHERE slug = slug_text) LOOP
|
|
slug_text := slug_text || '-' || substring(md5(random()::text), 1, 4);
|
|
END LOOP;
|
|
|
|
RETURN slug_text;
|
|
END;
|
|
$$;
|
|
|
|
ALTER FUNCTION "public"."generate_slug"("title" "text") OWNER TO "postgres";
|
|
|
|
CREATE FUNCTION "public"."requesting_user_id"() RETURNS "text"
|
|
LANGUAGE "sql" STABLE
|
|
AS $$
|
|
select nullif(current_setting('request.jwt.claims', true)::json->>'sub', '')::text;
|
|
$$;
|
|
|
|
ALTER FUNCTION "public"."requesting_user_id"() OWNER TO "postgres";
|
|
|
|
SET default_tablespace = '';
|
|
|
|
SET default_table_access_method = "heap";
|
|
|
|
CREATE TABLE "public"."blogs" (
|
|
"id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
|
|
"title" "text" NOT NULL,
|
|
"emoji" "text" NOT NULL,
|
|
"user_id" "text" DEFAULT "public"."requesting_user_id"() NOT NULL,
|
|
"description" "text",
|
|
"public_id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL
|
|
);
|
|
|
|
ALTER TABLE "public"."blogs" OWNER TO "postgres";
|
|
|
|
CREATE TABLE "public"."homepage_signup" (
|
|
"id" bigint NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT "now"(),
|
|
"name" "text" NOT NULL,
|
|
"email" "text" NOT NULL
|
|
);
|
|
|
|
ALTER TABLE "public"."homepage_signup" OWNER TO "postgres";
|
|
|
|
ALTER TABLE "public"."homepage_signup" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
|
|
SEQUENCE NAME "public"."homepage_signup_id_seq"
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1
|
|
);
|
|
|
|
CREATE TABLE "public"."invitations" (
|
|
"created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
|
|
"blog_id" "uuid" NOT NULL,
|
|
"email" "text" NOT NULL,
|
|
"name" "text" NOT NULL,
|
|
"id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL
|
|
);
|
|
|
|
ALTER TABLE "public"."invitations" OWNER TO "postgres";
|
|
|
|
CREATE TABLE "public"."members" (
|
|
"id" bigint NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
|
|
"user_id" "text" NOT NULL,
|
|
"blog_id" "uuid" NOT NULL
|
|
);
|
|
|
|
ALTER TABLE "public"."members" OWNER TO "postgres";
|
|
|
|
ALTER TABLE "public"."members" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
|
|
SEQUENCE NAME "public"."members_id_seq"
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1
|
|
);
|
|
|
|
CREATE TABLE "public"."posts" (
|
|
"created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
|
|
"user_id" "text" DEFAULT "public"."requesting_user_id"() NOT NULL,
|
|
"blog_id" "uuid" NOT NULL,
|
|
"title" "text" NOT NULL,
|
|
"published" boolean DEFAULT false NOT NULL,
|
|
"content" "jsonb" DEFAULT '{}'::"jsonb" NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT "now"() NOT NULL,
|
|
"slug" "text" NOT NULL,
|
|
"id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
|
|
"cover_image" "text"
|
|
);
|
|
|
|
ALTER TABLE "public"."posts" OWNER TO "postgres";
|
|
|
|
ALTER TABLE ONLY "public"."blogs"
|
|
ADD CONSTRAINT "blogs_pkey" PRIMARY KEY ("id");
|
|
|
|
ALTER TABLE ONLY "public"."homepage_signup"
|
|
ADD CONSTRAINT "homepage_signup_pkey" PRIMARY KEY ("id");
|
|
|
|
ALTER TABLE ONLY "public"."invitations"
|
|
ADD CONSTRAINT "invitations_pkey" PRIMARY KEY ("id");
|
|
|
|
ALTER TABLE ONLY "public"."members"
|
|
ADD CONSTRAINT "members_pkey" PRIMARY KEY ("id");
|
|
|
|
ALTER TABLE ONLY "public"."posts"
|
|
ADD CONSTRAINT "posts_pkey" PRIMARY KEY ("id");
|
|
|
|
ALTER TABLE ONLY "public"."posts"
|
|
ADD CONSTRAINT "unique_slug_per_user_post_constraint" UNIQUE ("slug", "user_id", "blog_id");
|
|
|
|
ALTER TABLE ONLY "public"."invitations"
|
|
ADD CONSTRAINT "invitations_blog_id_fkey" FOREIGN KEY ("blog_id") REFERENCES "public"."blogs"("id");
|
|
|
|
ALTER TABLE ONLY "public"."members"
|
|
ADD CONSTRAINT "members_blog_id_fkey" FOREIGN KEY ("blog_id") REFERENCES "public"."blogs"("id");
|
|
|
|
ALTER TABLE ONLY "public"."posts"
|
|
ADD CONSTRAINT "posts_blog_id_fkey" FOREIGN KEY ("blog_id") REFERENCES "public"."blogs"("id") ON DELETE CASCADE;
|
|
|
|
CREATE POLICY "Allow AUTHED users to query ALL their POSTS" ON "public"."posts" FOR SELECT TO "authenticated" USING ((("auth"."uid"())::"text" = "user_id"));
|
|
|
|
CREATE POLICY "Enable INSERT access for all users" ON "public"."homepage_signup" FOR INSERT TO "anon" WITH CHECK (true);
|
|
|
|
CREATE POLICY "Enable delete for users based on user_id" ON "public"."posts" FOR DELETE USING ((("auth"."uid"())::"text" = "user_id"));
|
|
|
|
CREATE POLICY "Enable insert for authenticated users only" ON "public"."posts" FOR INSERT TO "authenticated" WITH CHECK ((("auth"."uid"())::"text" = "user_id"));
|
|
|
|
CREATE POLICY "Enable read access for all users if published" ON "public"."posts" FOR SELECT TO "anon" USING (("published" = true));
|
|
|
|
CREATE POLICY "Enable update for users based on user_id" ON "public"."posts" FOR UPDATE USING ((("auth"."uid"())::"text" = "user_id"));
|
|
|
|
ALTER TABLE "public"."blogs" ENABLE ROW LEVEL SECURITY;
|
|
|
|
ALTER TABLE "public"."homepage_signup" ENABLE ROW LEVEL SECURITY;
|
|
|
|
ALTER TABLE "public"."invitations" ENABLE ROW LEVEL SECURITY;
|
|
|
|
ALTER TABLE "public"."members" ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "owners of a blog can create invitations" ON "public"."invitations" FOR INSERT TO "authenticated" WITH CHECK ((EXISTS ( SELECT 1
|
|
FROM "public"."blogs"
|
|
WHERE (("blogs"."id" = "invitations"."blog_id") AND ("blogs"."user_id" = "public"."requesting_user_id"())))));
|
|
|
|
CREATE POLICY "owners of a blog can delete invitations" ON "public"."invitations" FOR DELETE TO "authenticated" USING ((EXISTS ( SELECT 1
|
|
FROM "public"."blogs"
|
|
WHERE (("blogs"."id" = "invitations"."blog_id") AND ("blogs"."user_id" = "public"."requesting_user_id"())))));
|
|
|
|
CREATE POLICY "owners of the blog can see invitations" ON "public"."invitations" FOR SELECT TO "authenticated" USING ((EXISTS ( SELECT "blogs"."id",
|
|
"blogs"."created_at",
|
|
"blogs"."title",
|
|
"blogs"."emoji",
|
|
"blogs"."user_id",
|
|
"blogs"."description",
|
|
"blogs"."public_id"
|
|
FROM "public"."blogs"
|
|
WHERE (("blogs"."id" = "invitations"."blog_id") AND ("blogs"."user_id" = "public"."requesting_user_id"())))));
|
|
|
|
ALTER TABLE "public"."posts" ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "users can crud their blogs" ON "public"."blogs" TO "authenticated" USING ((("auth"."uid"())::"text" = "user_id"));
|
|
|
|
REVOKE USAGE ON SCHEMA "public" FROM PUBLIC;
|
|
GRANT USAGE ON SCHEMA "public" TO "postgres";
|
|
GRANT USAGE ON SCHEMA "public" TO "anon";
|
|
GRANT USAGE ON SCHEMA "public" TO "authenticated";
|
|
GRANT USAGE ON SCHEMA "public" TO "service_role";
|
|
|
|
GRANT ALL ON FUNCTION "public"."generate_slug"("title" "text") TO "anon";
|
|
GRANT ALL ON FUNCTION "public"."generate_slug"("title" "text") TO "authenticated";
|
|
GRANT ALL ON FUNCTION "public"."generate_slug"("title" "text") TO "service_role";
|
|
|
|
GRANT ALL ON FUNCTION "public"."requesting_user_id"() TO "anon";
|
|
GRANT ALL ON FUNCTION "public"."requesting_user_id"() TO "authenticated";
|
|
GRANT ALL ON FUNCTION "public"."requesting_user_id"() TO "service_role";
|
|
|
|
GRANT ALL ON TABLE "public"."blogs" TO "anon";
|
|
GRANT ALL ON TABLE "public"."blogs" TO "authenticated";
|
|
GRANT ALL ON TABLE "public"."blogs" TO "service_role";
|
|
|
|
GRANT ALL ON TABLE "public"."homepage_signup" TO "anon";
|
|
GRANT ALL ON TABLE "public"."homepage_signup" TO "authenticated";
|
|
GRANT ALL ON TABLE "public"."homepage_signup" TO "service_role";
|
|
|
|
GRANT ALL ON SEQUENCE "public"."homepage_signup_id_seq" TO "anon";
|
|
GRANT ALL ON SEQUENCE "public"."homepage_signup_id_seq" TO "authenticated";
|
|
GRANT ALL ON SEQUENCE "public"."homepage_signup_id_seq" TO "service_role";
|
|
|
|
GRANT ALL ON TABLE "public"."invitations" TO "anon";
|
|
GRANT ALL ON TABLE "public"."invitations" TO "authenticated";
|
|
GRANT ALL ON TABLE "public"."invitations" TO "service_role";
|
|
|
|
GRANT ALL ON TABLE "public"."members" TO "anon";
|
|
GRANT ALL ON TABLE "public"."members" TO "authenticated";
|
|
GRANT ALL ON TABLE "public"."members" TO "service_role";
|
|
|
|
GRANT ALL ON SEQUENCE "public"."members_id_seq" TO "anon";
|
|
GRANT ALL ON SEQUENCE "public"."members_id_seq" TO "authenticated";
|
|
GRANT ALL ON SEQUENCE "public"."members_id_seq" TO "service_role";
|
|
|
|
GRANT ALL ON TABLE "public"."posts" TO "anon";
|
|
GRANT ALL ON TABLE "public"."posts" TO "authenticated";
|
|
GRANT ALL ON TABLE "public"."posts" TO "service_role";
|
|
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "postgres";
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "anon";
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "authenticated";
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "service_role";
|
|
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "postgres";
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "anon";
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "authenticated";
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "service_role";
|
|
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "postgres";
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "anon";
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "authenticated";
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "service_role";
|
|
|
|
RESET ALL;
|