73 lines
2.1 KiB
PL/PgSQL
73 lines
2.1 KiB
PL/PgSQL
-- ALTER TABLE --
|
|
|
|
ALTER TABLE workspaces
|
|
ADD COLUMN image_path TEXT DEFAULT '' NOT NULL CHECK (char_length(image_path) <= 1000);
|
|
|
|
-- STORAGE --
|
|
|
|
INSERT INTO storage.buckets (id, name, public) VALUES ('workspace_images', 'workspace_images', false);
|
|
|
|
-- FUNCTIONS --
|
|
|
|
CREATE OR REPLACE FUNCTION delete_old_workspace_image()
|
|
RETURNS TRIGGER
|
|
LANGUAGE 'plpgsql'
|
|
SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
status INT;
|
|
content TEXT;
|
|
BEGIN
|
|
IF TG_OP = 'DELETE' THEN
|
|
SELECT
|
|
INTO status, content
|
|
result.status, result.content
|
|
FROM public.delete_storage_object_from_bucket('workspace_images', OLD.image_path) AS result;
|
|
IF status <> 200 THEN
|
|
RAISE WARNING 'Could not delete workspace image: % %', status, content;
|
|
END IF;
|
|
END IF;
|
|
IF TG_OP = 'DELETE' THEN
|
|
RETURN OLD;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
-- TRIGGERS --
|
|
|
|
CREATE TRIGGER delete_old_workspace_image
|
|
AFTER DELETE ON workspaces
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE delete_old_workspace_image();
|
|
|
|
-- POLICIES --
|
|
|
|
CREATE OR REPLACE FUNCTION public.non_private_workspace_exists(p_name text)
|
|
RETURNS boolean
|
|
LANGUAGE sql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
SELECT EXISTS (
|
|
SELECT 1
|
|
FROM workspaces
|
|
WHERE (id::text = (storage.filename(p_name))) AND sharing <> 'private'
|
|
);
|
|
$$;
|
|
|
|
CREATE POLICY "Allow public read access on non-private workspace images"
|
|
ON storage.objects FOR SELECT TO public
|
|
USING (bucket_id = 'workspace_images' AND public.non_private_workspace_exists(name));
|
|
|
|
CREATE POLICY "Allow insert access to own workspace images"
|
|
ON storage.objects FOR INSERT TO authenticated
|
|
WITH CHECK (bucket_id = 'workspace_images' AND (storage.foldername(name))[1] = auth.uid()::text);
|
|
|
|
CREATE POLICY "Allow update access to own workspace images"
|
|
ON storage.objects FOR UPDATE TO authenticated
|
|
USING (bucket_id = 'workspace_images' AND (storage.foldername(name))[1] = auth.uid()::text);
|
|
|
|
CREATE POLICY "Allow delete access to own workspace images"
|
|
ON storage.objects FOR DELETE TO authenticated
|
|
USING (bucket_id = 'workspace_images' AND (storage.foldername(name))[1] = auth.uid()::text);
|