75 lines
4.2 KiB
PL/PgSQL
75 lines
4.2 KiB
PL/PgSQL
-- SPDX-License-Identifier: LicenseRef-ConusAI-Template-1.0
|
|
-- An exact lookup through a platform-only role, never SELECT on auth tables.
|
|
-- This is a read side, not a decision, action lease or App user declaration.
|
|
CREATE FUNCTION public.conusai_app_approval_session_v1(p_handle_hash text,p_app_tenant uuid)
|
|
RETURNS jsonb LANGUAGE plpgsql STABLE SECURITY DEFINER
|
|
SET search_path=pg_catalog SET row_security=on AS $$
|
|
DECLARE observed jsonb; matches integer;
|
|
BEGIN
|
|
IF session_user::text<>current_database()||'_approval' OR p_handle_hash !~ '^[0-9a-f]{64}$' OR p_app_tenant IS NULL
|
|
OR p_app_tenant='00000000-0000-0000-0000-000000000000'::uuid THEN
|
|
RAISE EXCEPTION 'app approval session refused' USING ERRCODE='42501';
|
|
END IF;
|
|
IF NOT EXISTS(SELECT FROM pg_roles WHERE rolname=session_user
|
|
AND NOT rolsuper AND NOT rolbypassrls AND NOT rolcreatedb AND NOT rolcreaterole AND NOT rolreplication AND rolcanlogin)
|
|
OR EXISTS(SELECT FROM pg_auth_members WHERE member=session_user::regrole)
|
|
OR EXISTS(SELECT FROM pg_class WHERE relnamespace='public'::regnamespace AND relowner=session_user::regrole)
|
|
OR EXISTS(SELECT FROM pg_database WHERE datname=current_database() AND datdba=session_user::regrole)
|
|
THEN RAISE EXCEPTION 'app approval session refused' USING ERRCODE='42501'; END IF;
|
|
PERFORM pg_catalog.set_config('app.org_id',p_app_tenant::text,true);
|
|
SELECT count(*),min(x.record::text)::jsonb INTO matches,observed FROM (
|
|
SELECT jsonb_build_object(
|
|
'schema_version',1,'session_id',s.id,'session_version',s.version,
|
|
'member_id',u.id,'identity_id',a.id,'identity_version',a.version,
|
|
'issuer',s.issuer,'provider_subject',s.subject,'sealed_tokens',s.tokens,
|
|
'created_at',s.created_at,'last_seen_at',s.last_seen_at,
|
|
'absolute_expires_at',s.absolute_expires_at,
|
|
'access_token_expires_at',s.access_token_expires_at,
|
|
'app_tenant',o.id,'membership_id',m.id,'membership_version',m.version,
|
|
'membership_role',m.role,'membership_role_key',m.role_key,
|
|
'organization_version',o.version
|
|
) AS record FROM public.auth_sessions s
|
|
JOIN public.app_user_identity a ON a.issuer=s.issuer AND a.subject=s.subject AND a.user_id=s.user_id
|
|
JOIN public.users u ON u.id=a.user_id
|
|
JOIN public.org_memberships m ON m.user_id=u.id AND m.org_id=p_app_tenant
|
|
JOIN public.organizations o ON o.id=m.org_id
|
|
WHERE s.handle_hash=p_handle_hash AND s.revoked_at IS NULL
|
|
AND o.status='active'
|
|
LIMIT 2
|
|
) x;
|
|
IF matches<>1 THEN RETURN NULL; END IF;
|
|
RETURN observed;
|
|
END
|
|
$$;
|
|
REVOKE ALL ON FUNCTION public.conusai_app_approval_session_v1(text,uuid) FROM PUBLIC;
|
|
|
|
-- DS21 gives the ordinary runtime EXECUTE by owner default privileges. This
|
|
-- narrow private function must explicitly remove those inherited ACL entries.
|
|
DO $$
|
|
DECLARE role_name text;
|
|
BEGIN
|
|
FOREACH role_name IN ARRAY ARRAY[current_database()||'_app',current_database()||'_reader'] LOOP
|
|
IF octet_length(role_name)<=63 AND EXISTS(SELECT FROM pg_roles WHERE rolname=role_name) THEN
|
|
EXECUTE format('REVOKE ALL ON FUNCTION public.conusai_app_approval_session_v1(text,uuid) FROM %I',role_name);
|
|
END IF;
|
|
END LOOP;
|
|
END
|
|
$$;
|
|
|
|
-- The managed provider creates this platform-only role. Local/self-managed
|
|
-- installations without it gain no authority and receive no table grant.
|
|
DO $$
|
|
DECLARE approval_role text:=current_database()||'_approval'; safe boolean;
|
|
BEGIN
|
|
IF octet_length(approval_role)<=63 AND EXISTS(SELECT FROM pg_roles WHERE rolname=approval_role) THEN
|
|
SELECT NOT rolsuper AND NOT rolbypassrls AND NOT rolcreatedb AND NOT rolcreaterole
|
|
AND NOT rolreplication AND rolcanlogin INTO safe FROM pg_roles WHERE rolname=approval_role;
|
|
IF NOT safe OR EXISTS(SELECT FROM pg_auth_members WHERE member=(SELECT oid FROM pg_roles WHERE rolname=approval_role)) THEN
|
|
RAISE EXCEPTION 'app approval role refused' USING ERRCODE='42501';
|
|
END IF;
|
|
EXECUTE format('GRANT USAGE ON SCHEMA public TO %I',approval_role);
|
|
EXECUTE format('GRANT EXECUTE ON FUNCTION public.conusai_app_approval_session_v1(text,uuid) TO %I',approval_role);
|
|
END IF;
|
|
END
|
|
$$;
|