162 lines
6.7 KiB
SQL
162 lines
6.7 KiB
SQL
ALTER TABLE br_case ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE deadline ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE task ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE document_item ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE comment_item ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE notification_item ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE app_user ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE meeting ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE decision ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE template_item ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE committee ENABLE ROW LEVEL SECURITY;
|
|
|
|
ALTER TABLE br_case FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE deadline FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE task FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE document_item FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE comment_item FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE notification_item FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE app_user FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE meeting FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE decision FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE template_item FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE committee FORCE ROW LEVEL SECURITY;
|
|
|
|
DROP POLICY IF EXISTS committee_select ON committee;
|
|
DROP POLICY IF EXISTS committee_modify ON committee;
|
|
CREATE POLICY committee_select ON committee FOR SELECT USING (TRUE);
|
|
CREATE POLICY committee_modify ON committee FOR ALL USING (app_role() IN ('admin','chair')) WITH CHECK (app_role() IN ('admin','chair'));
|
|
|
|
DROP POLICY IF EXISTS user_select ON app_user;
|
|
DROP POLICY IF EXISTS user_modify ON app_user;
|
|
CREATE POLICY user_select ON app_user FOR SELECT USING (
|
|
app_role() IN ('admin','chair') OR id = app_user_id()
|
|
);
|
|
CREATE POLICY user_modify ON app_user FOR ALL USING (
|
|
app_role() = 'admin' OR id = app_user_id()
|
|
) WITH CHECK (
|
|
app_role() = 'admin' OR id = app_user_id()
|
|
);
|
|
|
|
DROP POLICY IF EXISTS br_case_select_policy ON br_case;
|
|
DROP POLICY IF EXISTS br_case_modify_policy ON br_case;
|
|
CREATE POLICY br_case_select_policy ON br_case FOR SELECT USING (
|
|
deleted_at IS NULL AND can_read_conf_level(confidentiality_level, owner_user_id, committee_id)
|
|
);
|
|
CREATE POLICY br_case_modify_policy ON br_case FOR ALL USING (
|
|
deleted_at IS NULL AND (
|
|
app_role() = 'admin'
|
|
OR owner_user_id = app_user_id()
|
|
OR (committee_id = app_committee_id() AND app_role() IN ('chair', 'secretary'))
|
|
)
|
|
) WITH CHECK (can_read_conf_level(confidentiality_level, owner_user_id, committee_id));
|
|
|
|
DROP POLICY IF EXISTS deadline_select_policy ON deadline;
|
|
DROP POLICY IF EXISTS deadline_modify_policy ON deadline;
|
|
CREATE POLICY deadline_select_policy ON deadline FOR SELECT USING (
|
|
deleted_at IS NULL AND EXISTS (
|
|
SELECT 1 FROM br_case c WHERE c.id = deadline.case_id
|
|
AND can_read_conf_level(c.confidentiality_level, c.owner_user_id, c.committee_id)
|
|
)
|
|
);
|
|
CREATE POLICY deadline_modify_policy ON deadline FOR ALL USING (
|
|
deleted_at IS NULL AND EXISTS (
|
|
SELECT 1 FROM br_case c WHERE c.id = deadline.case_id
|
|
AND (
|
|
app_role() = 'admin'
|
|
OR c.owner_user_id = app_user_id()
|
|
OR (c.committee_id = app_committee_id() AND app_role() IN ('chair', 'secretary'))
|
|
)
|
|
)
|
|
) WITH CHECK (TRUE);
|
|
|
|
DROP POLICY IF EXISTS meeting_select_policy ON meeting;
|
|
DROP POLICY IF EXISTS meeting_modify_policy ON meeting;
|
|
CREATE POLICY meeting_select_policy ON meeting FOR SELECT USING (
|
|
committee_id IS NULL OR committee_id = app_committee_id() OR app_role() IN ('admin','chair','secretary')
|
|
);
|
|
CREATE POLICY meeting_modify_policy ON meeting FOR ALL USING (
|
|
app_role() IN ('admin','chair','secretary')
|
|
) WITH CHECK (app_role() IN ('admin','chair','secretary'));
|
|
|
|
DROP POLICY IF EXISTS decision_select_policy ON decision;
|
|
DROP POLICY IF EXISTS decision_modify_policy ON decision;
|
|
CREATE POLICY decision_select_policy ON decision FOR SELECT USING (
|
|
case_id IS NULL OR EXISTS (
|
|
SELECT 1 FROM br_case c WHERE c.id = decision.case_id
|
|
AND can_read_conf_level(c.confidentiality_level, c.owner_user_id, c.committee_id)
|
|
) OR app_role() IN ('admin','chair','secretary')
|
|
);
|
|
CREATE POLICY decision_modify_policy ON decision FOR ALL USING (
|
|
app_role() IN ('admin','chair','secretary') OR responsible_user_id = app_user_id()
|
|
) WITH CHECK (TRUE);
|
|
|
|
DROP POLICY IF EXISTS task_select_policy ON task;
|
|
DROP POLICY IF EXISTS task_modify_policy ON task;
|
|
CREATE POLICY task_select_policy ON task FOR SELECT USING (
|
|
deleted_at IS NULL AND (
|
|
assigned_user_id = app_user_id()
|
|
OR backup_user_id = app_user_id()
|
|
OR app_role() IN ('admin','chair','secretary')
|
|
)
|
|
);
|
|
CREATE POLICY task_modify_policy ON task FOR ALL USING (
|
|
deleted_at IS NULL AND (
|
|
app_role() IN ('admin','chair','secretary')
|
|
OR assigned_user_id = app_user_id()
|
|
OR created_by = app_user_id()
|
|
)
|
|
) WITH CHECK (TRUE);
|
|
|
|
DROP POLICY IF EXISTS document_select_policy ON document_item;
|
|
DROP POLICY IF EXISTS document_modify_policy ON document_item;
|
|
CREATE POLICY document_select_policy ON document_item FOR SELECT USING (
|
|
deleted_at IS NULL
|
|
AND scan_status = 'clean'
|
|
AND (
|
|
app_role() = 'admin'
|
|
OR uploaded_by = app_user_id()
|
|
OR EXISTS (
|
|
SELECT 1 FROM br_case c
|
|
WHERE c.id = document_item.case_id
|
|
AND can_read_conf_level(document_item.confidentiality_level, c.owner_user_id, c.committee_id)
|
|
)
|
|
OR case_id IS NULL
|
|
)
|
|
);
|
|
CREATE POLICY document_modify_policy ON document_item FOR ALL USING (
|
|
deleted_at IS NULL AND (
|
|
app_role() IN ('admin','chair','secretary')
|
|
OR uploaded_by = app_user_id()
|
|
)
|
|
) WITH CHECK (TRUE);
|
|
|
|
DROP POLICY IF EXISTS comment_select_policy ON comment_item;
|
|
DROP POLICY IF EXISTS comment_modify_policy ON comment_item;
|
|
CREATE POLICY comment_select_policy ON comment_item FOR SELECT USING (
|
|
deleted_at IS NULL AND (
|
|
visibility = 'intern'
|
|
OR (visibility = 'vertraulich' AND app_role() IN ('admin','chair','secretary'))
|
|
)
|
|
);
|
|
CREATE POLICY comment_modify_policy ON comment_item FOR ALL USING (
|
|
deleted_at IS NULL AND (
|
|
author_user_id = app_user_id()
|
|
OR app_role() IN ('admin','chair','secretary')
|
|
)
|
|
) WITH CHECK (TRUE);
|
|
|
|
DROP POLICY IF EXISTS template_select_policy ON template_item;
|
|
DROP POLICY IF EXISTS template_modify_policy ON template_item;
|
|
CREATE POLICY template_select_policy ON template_item FOR SELECT USING (TRUE);
|
|
CREATE POLICY template_modify_policy ON template_item FOR ALL USING (app_role() IN ('admin','chair','secretary')) WITH CHECK (app_role() IN ('admin','chair','secretary'));
|
|
|
|
DROP POLICY IF EXISTS notification_select_policy ON notification_item;
|
|
DROP POLICY IF EXISTS notification_modify_policy ON notification_item;
|
|
CREATE POLICY notification_select_policy ON notification_item FOR SELECT USING (
|
|
user_id = app_user_id() OR app_role() IN ('admin','chair','secretary')
|
|
);
|
|
CREATE POLICY notification_modify_policy ON notification_item FOR ALL USING (
|
|
user_id = app_user_id() OR app_role() IN ('admin','chair','secretary')
|
|
) WITH CHECK (TRUE);
|