398 lines
14 KiB
PL/PgSQL
398 lines
14 KiB
PL/PgSQL
-- c77_rbac--1.0.sql: PostgreSQL extension for role-based access control (RBAC)
|
|
-- Requires PostgreSQL 14 or later
|
|
-- All objects in public schema with c77_rbac_ prefix
|
|
\echo Use "CREATE EXTENSION c77_rbac" to load this file. \quit
|
|
|
|
-- Tables
|
|
CREATE TABLE public.c77_rbac_subjects (
|
|
subject_id BIGSERIAL PRIMARY KEY,
|
|
external_id TEXT UNIQUE NOT NULL
|
|
);
|
|
|
|
CREATE TABLE public.c77_rbac_roles (
|
|
role_id BIGSERIAL PRIMARY KEY,
|
|
name TEXT UNIQUE NOT NULL
|
|
);
|
|
|
|
CREATE TABLE public.c77_rbac_features (
|
|
feature_id BIGSERIAL PRIMARY KEY,
|
|
name TEXT UNIQUE NOT NULL
|
|
);
|
|
|
|
CREATE TABLE public.c77_rbac_subject_roles (
|
|
subject_id BIGINT REFERENCES public.c77_rbac_subjects(subject_id) ON DELETE CASCADE,
|
|
role_id BIGINT REFERENCES public.c77_rbac_roles(role_id) ON DELETE CASCADE,
|
|
scope_type TEXT NOT NULL,
|
|
scope_id TEXT NOT NULL,
|
|
PRIMARY KEY (subject_id, role_id, scope_type, scope_id)
|
|
);
|
|
|
|
CREATE TABLE public.c77_rbac_role_features (
|
|
role_id BIGINT REFERENCES public.c77_rbac_roles(role_id) ON DELETE CASCADE,
|
|
feature_id BIGINT REFERENCES public.c77_rbac_features(feature_id) ON DELETE CASCADE,
|
|
PRIMARY KEY (role_id, feature_id)
|
|
);
|
|
|
|
-- Create indexes for performance
|
|
CREATE INDEX idx_c77_rbac_subjects_external_id ON public.c77_rbac_subjects(external_id);
|
|
CREATE INDEX idx_c77_rbac_roles_name ON public.c77_rbac_roles(name);
|
|
CREATE INDEX idx_c77_rbac_features_name ON public.c77_rbac_features(name);
|
|
CREATE INDEX idx_c77_rbac_subject_roles_subject_id ON public.c77_rbac_subject_roles(subject_id);
|
|
CREATE INDEX idx_c77_rbac_subject_roles_role_id ON public.c77_rbac_subject_roles(role_id);
|
|
CREATE INDEX idx_c77_rbac_subject_roles_scope ON public.c77_rbac_subject_roles(scope_type, scope_id);
|
|
|
|
-- Function: c77_rbac_assign_subject
|
|
CREATE OR REPLACE FUNCTION public.c77_rbac_assign_subject(
|
|
p_external_id TEXT,
|
|
p_role_name TEXT,
|
|
p_scope_type TEXT,
|
|
p_scope_id TEXT
|
|
) RETURNS VOID AS $$
|
|
DECLARE
|
|
v_subject_id BIGINT;
|
|
v_role_id BIGINT;
|
|
BEGIN
|
|
IF p_external_id IS NULL THEN
|
|
RAISE EXCEPTION 'external_id cannot be NULL';
|
|
END IF;
|
|
|
|
IF p_role_name IS NULL THEN
|
|
RAISE EXCEPTION 'role_name cannot be NULL';
|
|
END IF;
|
|
|
|
IF p_scope_type IS NULL THEN
|
|
RAISE EXCEPTION 'scope_type cannot be NULL';
|
|
END IF;
|
|
|
|
IF p_scope_id IS NULL THEN
|
|
RAISE EXCEPTION 'scope_id cannot be NULL';
|
|
END IF;
|
|
|
|
-- Insert or get subject
|
|
INSERT INTO public.c77_rbac_subjects (external_id)
|
|
VALUES (p_external_id)
|
|
ON CONFLICT (external_id) DO NOTHING
|
|
RETURNING subject_id INTO v_subject_id;
|
|
|
|
IF v_subject_id IS NULL THEN
|
|
SELECT subject_id INTO v_subject_id
|
|
FROM public.c77_rbac_subjects
|
|
WHERE external_id = p_external_id;
|
|
END IF;
|
|
|
|
-- Insert or get role
|
|
INSERT INTO public.c77_rbac_roles (name)
|
|
VALUES (p_role_name)
|
|
ON CONFLICT (name) DO NOTHING
|
|
RETURNING role_id INTO v_role_id;
|
|
|
|
IF v_role_id IS NULL THEN
|
|
SELECT role_id INTO v_role_id
|
|
FROM public.c77_rbac_roles
|
|
WHERE name = p_role_name;
|
|
END IF;
|
|
|
|
-- Assign role to subject with scope
|
|
INSERT INTO public.c77_rbac_subject_roles (subject_id, role_id, scope_type, scope_id)
|
|
VALUES (v_subject_id, v_role_id, p_scope_type, p_scope_id)
|
|
ON CONFLICT (subject_id, role_id, scope_type, scope_id) DO NOTHING;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Function: c77_rbac_grant_feature
|
|
CREATE OR REPLACE FUNCTION public.c77_rbac_grant_feature(
|
|
p_role_name TEXT,
|
|
p_feature_name TEXT
|
|
) RETURNS VOID AS $$
|
|
DECLARE
|
|
v_role_id BIGINT;
|
|
v_feature_id BIGINT;
|
|
BEGIN
|
|
IF p_role_name IS NULL THEN
|
|
RAISE EXCEPTION 'role_name cannot be NULL';
|
|
END IF;
|
|
|
|
IF p_feature_name IS NULL THEN
|
|
RAISE EXCEPTION 'feature_name cannot be NULL';
|
|
END IF;
|
|
|
|
-- Insert or get role
|
|
INSERT INTO public.c77_rbac_roles (name)
|
|
VALUES (p_role_name)
|
|
ON CONFLICT (name) DO NOTHING
|
|
RETURNING role_id INTO v_role_id;
|
|
|
|
IF v_role_id IS NULL THEN
|
|
SELECT role_id INTO v_role_id
|
|
FROM public.c77_rbac_roles
|
|
WHERE name = p_role_name;
|
|
END IF;
|
|
|
|
-- Insert or get feature
|
|
INSERT INTO public.c77_rbac_features (name)
|
|
VALUES (p_feature_name)
|
|
ON CONFLICT (name) DO NOTHING
|
|
RETURNING feature_id INTO v_feature_id;
|
|
|
|
IF v_feature_id IS NULL THEN
|
|
SELECT feature_id INTO v_feature_id
|
|
FROM public.c77_rbac_features
|
|
WHERE name = p_feature_name;
|
|
END IF;
|
|
|
|
-- Grant feature to role
|
|
INSERT INTO public.c77_rbac_role_features (role_id, feature_id)
|
|
VALUES (v_role_id, v_feature_id)
|
|
ON CONFLICT (role_id, feature_id) DO NOTHING;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Function: c77_rbac_can_access
|
|
CREATE OR REPLACE FUNCTION public.c77_rbac_can_access(
|
|
p_feature_name TEXT,
|
|
p_external_id TEXT,
|
|
p_scope_type TEXT,
|
|
p_scope_id TEXT
|
|
) RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
IF p_external_id IS NULL THEN
|
|
RETURN FALSE; -- Changed from exception to false for RLS usage
|
|
END IF;
|
|
|
|
-- Check for global admin access (global/all scope)
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM public.c77_rbac_subjects s
|
|
JOIN public.c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id
|
|
JOIN public.c77_rbac_roles r ON sr.role_id = r.role_id
|
|
JOIN public.c77_rbac_role_features rf ON r.role_id = rf.role_id
|
|
JOIN public.c77_rbac_features f ON rf.feature_id = f.feature_id -- Fixed: was f.id
|
|
WHERE s.external_id = p_external_id
|
|
AND f.name = p_feature_name
|
|
AND sr.scope_type = 'global'
|
|
AND sr.scope_id = 'all'
|
|
) THEN
|
|
RETURN TRUE;
|
|
END IF;
|
|
|
|
-- Check regular access with exact scope match
|
|
RETURN EXISTS (
|
|
SELECT 1
|
|
FROM public.c77_rbac_subjects s
|
|
JOIN public.c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id
|
|
JOIN public.c77_rbac_roles r ON sr.role_id = r.role_id
|
|
JOIN public.c77_rbac_role_features rf ON r.role_id = rf.role_id
|
|
JOIN public.c77_rbac_features f ON rf.feature_id = f.feature_id -- Fixed: was f.id
|
|
WHERE s.external_id = p_external_id
|
|
AND f.name = p_feature_name
|
|
AND sr.scope_type = p_scope_type
|
|
AND sr.scope_id = p_scope_id
|
|
);
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE;
|
|
|
|
-- Function: c77_rbac_apply_policy (simplified version)
|
|
CREATE OR REPLACE FUNCTION public.c77_rbac_apply_policy(
|
|
p_table_name TEXT,
|
|
p_feature_name TEXT,
|
|
p_scope_type TEXT,
|
|
p_scope_column TEXT
|
|
) RETURNS VOID AS $$
|
|
DECLARE
|
|
v_schema_name TEXT;
|
|
v_table_name TEXT;
|
|
v_policy_exists BOOLEAN;
|
|
BEGIN
|
|
-- Split schema and table name
|
|
IF position('.' IN p_table_name) > 0 THEN
|
|
v_schema_name := split_part(p_table_name, '.', 1);
|
|
v_table_name := split_part(p_table_name, '.', 2);
|
|
ELSE
|
|
v_schema_name := 'public';
|
|
v_table_name := p_table_name;
|
|
END IF;
|
|
|
|
-- Check if policy exists
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM pg_policies
|
|
WHERE schemaname = v_schema_name
|
|
AND tablename = v_table_name
|
|
AND policyname = 'c77_rbac_policy'
|
|
) INTO v_policy_exists;
|
|
|
|
-- Drop existing policy if it exists
|
|
IF v_policy_exists THEN
|
|
EXECUTE format('DROP POLICY c77_rbac_policy ON %I.%I', v_schema_name, v_table_name);
|
|
END IF;
|
|
|
|
-- Create the new policy - simplified column reference
|
|
EXECUTE format(
|
|
'CREATE POLICY c77_rbac_policy ON %I.%I FOR ALL TO PUBLIC USING (
|
|
public.c77_rbac_can_access(
|
|
%L,
|
|
current_setting(''c77_rbac.external_id'', true),
|
|
%L,
|
|
%I
|
|
)
|
|
)',
|
|
v_schema_name, v_table_name, p_feature_name, p_scope_type, p_scope_column
|
|
);
|
|
|
|
-- Enable and force RLS
|
|
EXECUTE format('ALTER TABLE %I.%I ENABLE ROW LEVEL SECURITY', v_schema_name, v_table_name);
|
|
EXECUTE format('ALTER TABLE %I.%I FORCE ROW LEVEL SECURITY', v_schema_name, v_table_name);
|
|
|
|
RAISE NOTICE 'Applied RLS policy to %.%', v_schema_name, v_table_name;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Grant necessary permissions
|
|
GRANT USAGE ON SCHEMA public TO PUBLIC;
|
|
GRANT EXECUTE ON FUNCTION
|
|
public.c77_rbac_assign_subject(TEXT, TEXT, TEXT, TEXT),
|
|
public.c77_rbac_grant_feature(TEXT, TEXT),
|
|
public.c77_rbac_can_access(TEXT, TEXT, TEXT, TEXT),
|
|
public.c77_rbac_apply_policy(TEXT, TEXT, TEXT, TEXT)
|
|
TO PUBLIC;
|
|
|
|
-- Function to remove all RLS policies and prepare for extension removal
|
|
CREATE OR REPLACE FUNCTION public.c77_rbac_remove_all_policies()
|
|
RETURNS void AS $$
|
|
DECLARE
|
|
policy_record RECORD;
|
|
table_count INTEGER := 0;
|
|
BEGIN
|
|
-- Find and remove all c77_rbac policies
|
|
FOR policy_record IN
|
|
SELECT schemaname, tablename, policyname
|
|
FROM pg_policies
|
|
WHERE policyname = 'c77_rbac_policy'
|
|
LOOP
|
|
EXECUTE format('DROP POLICY IF EXISTS %I ON %I.%I',
|
|
policy_record.policyname,
|
|
policy_record.schemaname,
|
|
policy_record.tablename);
|
|
|
|
-- Optionally disable RLS on the table
|
|
EXECUTE format('ALTER TABLE %I.%I DISABLE ROW LEVEL SECURITY',
|
|
policy_record.schemaname,
|
|
policy_record.tablename);
|
|
|
|
table_count := table_count + 1;
|
|
RAISE NOTICE 'Removed policy from %.%', policy_record.schemaname, policy_record.tablename;
|
|
END LOOP;
|
|
|
|
RAISE NOTICE 'Removed policies from % tables', table_count;
|
|
RAISE NOTICE 'You can now run: DROP EXTENSION c77_rbac CASCADE;';
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- More comprehensive cleanup function that also clears data
|
|
CREATE OR REPLACE FUNCTION public.c77_rbac_cleanup_for_removal(
|
|
p_remove_data BOOLEAN DEFAULT false
|
|
)
|
|
RETURNS void AS $$
|
|
DECLARE
|
|
policy_record RECORD;
|
|
table_count INTEGER := 0;
|
|
BEGIN
|
|
-- First remove all policies
|
|
PERFORM public.c77_rbac_remove_all_policies();
|
|
|
|
-- Optionally clear all RBAC data
|
|
IF p_remove_data THEN
|
|
-- Clear in correct order due to foreign keys
|
|
DELETE FROM public.c77_rbac_subject_roles;
|
|
DELETE FROM public.c77_rbac_role_features;
|
|
DELETE FROM public.c77_rbac_subjects;
|
|
DELETE FROM public.c77_rbac_roles;
|
|
DELETE FROM public.c77_rbac_features;
|
|
|
|
RAISE NOTICE 'Cleared all RBAC data';
|
|
END IF;
|
|
|
|
RAISE NOTICE 'Cleanup complete. You can now run: DROP EXTENSION c77_rbac CASCADE;';
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Grant execute permissions
|
|
GRANT EXECUTE ON FUNCTION
|
|
public.c77_rbac_remove_all_policies(),
|
|
public.c77_rbac_cleanup_for_removal(BOOLEAN)
|
|
TO PUBLIC;
|
|
|
|
-- Grant table permissions with more restrictions
|
|
GRANT SELECT ON
|
|
public.c77_rbac_subjects,
|
|
public.c77_rbac_roles,
|
|
public.c77_rbac_features,
|
|
public.c77_rbac_subject_roles,
|
|
public.c77_rbac_role_features
|
|
TO PUBLIC;
|
|
|
|
-- Only allow modifications through functions
|
|
REVOKE INSERT, UPDATE, DELETE ON
|
|
public.c77_rbac_subjects,
|
|
public.c77_rbac_roles,
|
|
public.c77_rbac_features,
|
|
public.c77_rbac_subject_roles,
|
|
public.c77_rbac_role_features
|
|
FROM PUBLIC;
|
|
|
|
-- Function to show what depends on c77_rbac
|
|
-- Fixed version of the dependencies function
|
|
CREATE OR REPLACE FUNCTION public.c77_rbac_show_dependencies()
|
|
RETURNS TABLE(
|
|
dependency_type TEXT,
|
|
schema_name TEXT,
|
|
object_name TEXT,
|
|
details TEXT
|
|
) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT * FROM (
|
|
-- Find policies
|
|
SELECT
|
|
'POLICY'::TEXT as dependency_type,
|
|
schemaname::TEXT as schema_name,
|
|
tablename::TEXT as object_name,
|
|
policyname::TEXT as details
|
|
FROM pg_policies
|
|
WHERE policyname = 'c77_rbac_policy'
|
|
|
|
UNION ALL
|
|
|
|
-- Find tables with RLS enabled
|
|
SELECT
|
|
'RLS_ENABLED'::TEXT as dependency_type,
|
|
schemaname::TEXT as schema_name,
|
|
tablename::TEXT as object_name,
|
|
'Row Level Security is enabled'::TEXT as details
|
|
FROM pg_tables
|
|
WHERE rowsecurity = true
|
|
AND (schemaname, tablename) IN (
|
|
SELECT schemaname, tablename
|
|
FROM pg_policies
|
|
WHERE policyname = 'c77_rbac_policy'
|
|
)
|
|
|
|
UNION ALL
|
|
|
|
-- Find stored procedures that might use c77_rbac functions
|
|
SELECT DISTINCT
|
|
'FUNCTION'::TEXT as dependency_type,
|
|
n.nspname::TEXT as schema_name,
|
|
p.proname::TEXT as object_name,
|
|
'May reference c77_rbac functions'::TEXT as details
|
|
FROM pg_proc p
|
|
JOIN pg_namespace n ON p.pronamespace = n.oid
|
|
WHERE p.prosrc LIKE '%c77_rbac%'
|
|
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
|
|
AND p.proname NOT LIKE 'c77_rbac%'
|
|
) AS dependencies
|
|
ORDER BY dependency_type, schema_name, object_name;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
GRANT EXECUTE ON FUNCTION public.c77_rbac_show_dependencies() TO PUBLIC;
|