477 lines
14 KiB
PL/PgSQL
477 lines
14 KiB
PL/PgSQL
-- =====================================================
|
|
-- BotKonzept - Installer JSON API (Supabase Auth)
|
|
-- =====================================================
|
|
-- Secure API using Supabase Auth JWT tokens
|
|
-- NO Service Role Key in Frontend - EVER!
|
|
|
|
-- =====================================================
|
|
-- Step 1: Add installer_json column to instances table
|
|
-- =====================================================
|
|
|
|
ALTER TABLE instances
|
|
ADD COLUMN IF NOT EXISTS installer_json JSONB DEFAULT '{}'::jsonb;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_instances_installer_json ON instances USING gin(installer_json);
|
|
|
|
COMMENT ON COLUMN instances.installer_json IS 'Complete installer JSON output from install.sh (includes secrets - use api.get_my_instance_config() for safe access)';
|
|
|
|
-- =====================================================
|
|
-- Step 2: Link instances to Supabase Auth users
|
|
-- =====================================================
|
|
|
|
-- Add owner_user_id column to link instance to Supabase Auth user
|
|
ALTER TABLE instances
|
|
ADD COLUMN IF NOT EXISTS owner_user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL;
|
|
|
|
-- Create index for faster lookups
|
|
CREATE INDEX IF NOT EXISTS idx_instances_owner_user_id ON instances(owner_user_id);
|
|
|
|
COMMENT ON COLUMN instances.owner_user_id IS 'Supabase Auth user ID of the instance owner';
|
|
|
|
-- =====================================================
|
|
-- Step 3: Create safe API view (NON-SECRET data only)
|
|
-- =====================================================
|
|
|
|
CREATE SCHEMA IF NOT EXISTS api;
|
|
GRANT USAGE ON SCHEMA api TO anon, authenticated, service_role;
|
|
|
|
-- View that exposes only safe (non-secret) installer data
|
|
CREATE OR REPLACE VIEW api.instance_config AS
|
|
SELECT
|
|
i.id,
|
|
i.customer_id,
|
|
i.owner_user_id,
|
|
i.lxc_id as ctid,
|
|
i.hostname,
|
|
i.fqdn,
|
|
i.ip,
|
|
i.vlan,
|
|
i.status,
|
|
i.created_at,
|
|
-- Extract safe URLs from installer_json (NO SECRETS)
|
|
jsonb_build_object(
|
|
'n8n_internal', i.installer_json->'urls'->>'n8n_internal',
|
|
'n8n_external', i.installer_json->'urls'->>'n8n_external',
|
|
'postgrest', i.installer_json->'urls'->>'postgrest',
|
|
'chat_webhook', i.installer_json->'urls'->>'chat_webhook',
|
|
'chat_internal', i.installer_json->'urls'->>'chat_internal',
|
|
'upload_form', i.installer_json->'urls'->>'upload_form',
|
|
'upload_form_internal', i.installer_json->'urls'->>'upload_form_internal'
|
|
) as urls,
|
|
-- Extract safe Supabase data (NO service_role_key, NO jwt_secret)
|
|
jsonb_build_object(
|
|
'url_external', i.installer_json->'supabase'->>'url_external',
|
|
'anon_key', i.installer_json->'supabase'->>'anon_key'
|
|
) as supabase,
|
|
-- Extract Ollama URL (safe)
|
|
jsonb_build_object(
|
|
'url', i.installer_json->'ollama'->>'url',
|
|
'model', i.installer_json->'ollama'->>'model',
|
|
'embedding_model', i.installer_json->'ollama'->>'embedding_model'
|
|
) as ollama,
|
|
-- Customer info (joined)
|
|
c.email as customer_email,
|
|
c.first_name,
|
|
c.last_name,
|
|
c.company,
|
|
c.status as customer_status
|
|
FROM instances i
|
|
JOIN customers c ON i.customer_id = c.id
|
|
WHERE i.status = 'active' AND i.deleted_at IS NULL;
|
|
|
|
COMMENT ON VIEW api.instance_config IS 'Safe API view - exposes only non-secret data from installer JSON';
|
|
|
|
-- =====================================================
|
|
-- Step 4: Row Level Security (RLS) Policies
|
|
-- =====================================================
|
|
|
|
-- Enable RLS on instances table (if not already enabled)
|
|
ALTER TABLE instances ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Drop old policy if exists
|
|
DROP POLICY IF EXISTS instance_config_select_own ON instances;
|
|
|
|
-- Policy: Users can only see their own instances
|
|
CREATE POLICY instances_select_own ON instances
|
|
FOR SELECT
|
|
USING (
|
|
-- Allow if owner_user_id matches authenticated user
|
|
owner_user_id = auth.uid()
|
|
OR
|
|
-- Allow service_role to see all (for n8n workflows)
|
|
auth.jwt()->>'role' = 'service_role'
|
|
);
|
|
|
|
-- Grant SELECT on api.instance_config view
|
|
GRANT SELECT ON api.instance_config TO authenticated, service_role;
|
|
|
|
-- =====================================================
|
|
-- Step 5: Function to get MY instance config (Auth required)
|
|
-- =====================================================
|
|
|
|
-- Function to get instance config for authenticated user
|
|
-- Uses auth.uid() - NO email parameter (more secure)
|
|
CREATE OR REPLACE FUNCTION api.get_my_instance_config()
|
|
RETURNS TABLE (
|
|
id UUID,
|
|
customer_id UUID,
|
|
owner_user_id UUID,
|
|
ctid BIGINT,
|
|
hostname VARCHAR,
|
|
fqdn VARCHAR,
|
|
ip VARCHAR,
|
|
vlan INTEGER,
|
|
status VARCHAR,
|
|
created_at TIMESTAMPTZ,
|
|
urls JSONB,
|
|
supabase JSONB,
|
|
ollama JSONB,
|
|
customer_email VARCHAR,
|
|
first_name VARCHAR,
|
|
last_name VARCHAR,
|
|
company VARCHAR,
|
|
customer_status VARCHAR
|
|
)
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
BEGIN
|
|
-- Check if user is authenticated
|
|
IF auth.uid() IS NULL THEN
|
|
RAISE EXCEPTION 'Not authenticated';
|
|
END IF;
|
|
|
|
-- Return instance config for authenticated user
|
|
RETURN QUERY
|
|
SELECT
|
|
ic.id,
|
|
ic.customer_id,
|
|
ic.owner_user_id,
|
|
ic.ctid,
|
|
ic.hostname,
|
|
ic.fqdn,
|
|
ic.ip,
|
|
ic.vlan,
|
|
ic.status,
|
|
ic.created_at,
|
|
ic.urls,
|
|
ic.supabase,
|
|
ic.ollama,
|
|
ic.customer_email,
|
|
ic.first_name,
|
|
ic.last_name,
|
|
ic.company,
|
|
ic.customer_status
|
|
FROM api.instance_config ic
|
|
WHERE ic.owner_user_id = auth.uid()
|
|
LIMIT 1;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
GRANT EXECUTE ON FUNCTION api.get_my_instance_config() TO authenticated;
|
|
|
|
COMMENT ON FUNCTION api.get_my_instance_config IS 'Get instance configuration for authenticated user - uses auth.uid() for security';
|
|
|
|
-- =====================================================
|
|
-- Step 6: Function to get config by CTID (Service Role ONLY)
|
|
-- =====================================================
|
|
|
|
CREATE OR REPLACE FUNCTION api.get_instance_config_by_ctid(ctid_param BIGINT)
|
|
RETURNS TABLE (
|
|
id UUID,
|
|
customer_id UUID,
|
|
owner_user_id UUID,
|
|
ctid BIGINT,
|
|
hostname VARCHAR,
|
|
fqdn VARCHAR,
|
|
ip VARCHAR,
|
|
vlan INTEGER,
|
|
status VARCHAR,
|
|
created_at TIMESTAMPTZ,
|
|
urls JSONB,
|
|
supabase JSONB,
|
|
ollama JSONB,
|
|
customer_email VARCHAR,
|
|
first_name VARCHAR,
|
|
last_name VARCHAR,
|
|
company VARCHAR,
|
|
customer_status VARCHAR
|
|
)
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
BEGIN
|
|
-- Only service_role can call this
|
|
IF auth.jwt()->>'role' != 'service_role' THEN
|
|
RAISE EXCEPTION 'Forbidden: service_role required';
|
|
END IF;
|
|
|
|
RETURN QUERY
|
|
SELECT
|
|
ic.id,
|
|
ic.customer_id,
|
|
ic.owner_user_id,
|
|
ic.ctid,
|
|
ic.hostname,
|
|
ic.fqdn,
|
|
ic.ip,
|
|
ic.vlan,
|
|
ic.status,
|
|
ic.created_at,
|
|
ic.urls,
|
|
ic.supabase,
|
|
ic.ollama,
|
|
ic.customer_email,
|
|
ic.first_name,
|
|
ic.last_name,
|
|
ic.company,
|
|
ic.customer_status
|
|
FROM api.instance_config ic
|
|
WHERE ic.ctid = ctid_param
|
|
LIMIT 1;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
GRANT EXECUTE ON FUNCTION api.get_instance_config_by_ctid(BIGINT) TO service_role;
|
|
|
|
COMMENT ON FUNCTION api.get_instance_config_by_ctid IS 'Get instance configuration by CTID - service_role only';
|
|
|
|
-- =====================================================
|
|
-- Step 7: Public config endpoint (NO auth required)
|
|
-- =====================================================
|
|
|
|
CREATE OR REPLACE FUNCTION api.get_public_config()
|
|
RETURNS TABLE (
|
|
registration_webhook_url TEXT,
|
|
api_base_url TEXT
|
|
)
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
'https://api.botkonzept.de/webhook/botkonzept-registration'::TEXT as registration_webhook_url,
|
|
'https://api.botkonzept.de'::TEXT as api_base_url;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
GRANT EXECUTE ON FUNCTION api.get_public_config() TO anon, authenticated, service_role;
|
|
|
|
COMMENT ON FUNCTION api.get_public_config IS 'Get public configuration for website (registration webhook URL)';
|
|
|
|
-- =====================================================
|
|
-- Step 8: Store installer JSON (Service Role ONLY)
|
|
-- =====================================================
|
|
|
|
CREATE OR REPLACE FUNCTION api.store_installer_json(
|
|
customer_email_param TEXT,
|
|
lxc_id_param BIGINT,
|
|
installer_json_param JSONB
|
|
)
|
|
RETURNS JSONB
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
DECLARE
|
|
instance_record RECORD;
|
|
result JSONB;
|
|
BEGIN
|
|
-- Only service_role can call this
|
|
IF auth.jwt()->>'role' != 'service_role' THEN
|
|
RAISE EXCEPTION 'Forbidden: service_role required';
|
|
END IF;
|
|
|
|
-- Find the instance by customer email and lxc_id
|
|
SELECT i.id, i.customer_id, c.id as auth_user_id INTO instance_record
|
|
FROM instances i
|
|
JOIN customers c ON i.customer_id = c.id
|
|
WHERE c.email = customer_email_param
|
|
AND i.lxc_id = lxc_id_param
|
|
LIMIT 1;
|
|
|
|
IF NOT FOUND THEN
|
|
RETURN jsonb_build_object(
|
|
'success', false,
|
|
'error', 'Instance not found for customer email and LXC ID'
|
|
);
|
|
END IF;
|
|
|
|
-- Update the installer_json column
|
|
UPDATE instances
|
|
SET installer_json = installer_json_param,
|
|
updated_at = NOW()
|
|
WHERE id = instance_record.id;
|
|
|
|
-- Return success
|
|
result := jsonb_build_object(
|
|
'success', true,
|
|
'instance_id', instance_record.id,
|
|
'customer_id', instance_record.customer_id,
|
|
'message', 'Installer JSON stored successfully'
|
|
);
|
|
|
|
RETURN result;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
GRANT EXECUTE ON FUNCTION api.store_installer_json(TEXT, BIGINT, JSONB) TO service_role;
|
|
|
|
COMMENT ON FUNCTION api.store_installer_json IS 'Store installer JSON after instance creation - service_role only';
|
|
|
|
-- =====================================================
|
|
-- Step 9: Link customer to Supabase Auth user
|
|
-- =====================================================
|
|
|
|
-- Function to link customer to Supabase Auth user (called during registration)
|
|
CREATE OR REPLACE FUNCTION api.link_customer_to_auth_user(
|
|
customer_email_param TEXT,
|
|
auth_user_id_param UUID
|
|
)
|
|
RETURNS JSONB
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
DECLARE
|
|
customer_record RECORD;
|
|
instance_record RECORD;
|
|
result JSONB;
|
|
BEGIN
|
|
-- Only service_role can call this
|
|
IF auth.jwt()->>'role' != 'service_role' THEN
|
|
RAISE EXCEPTION 'Forbidden: service_role required';
|
|
END IF;
|
|
|
|
-- Find customer by email
|
|
SELECT id INTO customer_record
|
|
FROM customers
|
|
WHERE email = customer_email_param
|
|
LIMIT 1;
|
|
|
|
IF NOT FOUND THEN
|
|
RETURN jsonb_build_object(
|
|
'success', false,
|
|
'error', 'Customer not found'
|
|
);
|
|
END IF;
|
|
|
|
-- Update all instances for this customer with owner_user_id
|
|
UPDATE instances
|
|
SET owner_user_id = auth_user_id_param,
|
|
updated_at = NOW()
|
|
WHERE customer_id = customer_record.id;
|
|
|
|
-- Return success
|
|
result := jsonb_build_object(
|
|
'success', true,
|
|
'customer_id', customer_record.id,
|
|
'auth_user_id', auth_user_id_param,
|
|
'message', 'Customer linked to auth user successfully'
|
|
);
|
|
|
|
RETURN result;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
GRANT EXECUTE ON FUNCTION api.link_customer_to_auth_user(TEXT, UUID) TO service_role;
|
|
|
|
COMMENT ON FUNCTION api.link_customer_to_auth_user IS 'Link customer to Supabase Auth user - service_role only';
|
|
|
|
-- =====================================================
|
|
-- Step 10: Audit logging
|
|
-- =====================================================
|
|
|
|
CREATE OR REPLACE FUNCTION api.log_config_access(
|
|
access_type TEXT,
|
|
ip_address_param INET DEFAULT NULL
|
|
)
|
|
RETURNS VOID
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
BEGIN
|
|
-- Log access for authenticated user
|
|
IF auth.uid() IS NOT NULL THEN
|
|
INSERT INTO audit_log (
|
|
customer_id,
|
|
action,
|
|
entity_type,
|
|
performed_by,
|
|
ip_address,
|
|
metadata
|
|
)
|
|
SELECT
|
|
i.customer_id,
|
|
'api_config_access',
|
|
'instance_config',
|
|
auth.uid()::text,
|
|
ip_address_param,
|
|
jsonb_build_object('access_type', access_type)
|
|
FROM instances i
|
|
WHERE i.owner_user_id = auth.uid()
|
|
LIMIT 1;
|
|
END IF;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
GRANT EXECUTE ON FUNCTION api.log_config_access(TEXT, INET) TO authenticated, service_role;
|
|
|
|
-- =====================================================
|
|
-- Step 11: PostgREST API Routes
|
|
-- =====================================================
|
|
|
|
-- Available routes:
|
|
--
|
|
-- 1. POST /rpc/get_my_instance_config
|
|
-- - Body: {}
|
|
-- - Returns instance config for authenticated user
|
|
-- - Requires: Supabase Auth JWT token
|
|
-- - Response: Single instance config object (or empty if not found)
|
|
--
|
|
-- 2. POST /rpc/get_public_config
|
|
-- - Body: {}
|
|
-- - Returns public configuration (registration webhook URL)
|
|
-- - Requires: No authentication
|
|
--
|
|
-- 3. POST /rpc/get_instance_config_by_ctid
|
|
-- - Body: {"ctid_param": 769697636}
|
|
-- - Returns instance config for specific CTID
|
|
-- - Requires: Service Role Key (backend only)
|
|
--
|
|
-- 4. POST /rpc/store_installer_json
|
|
-- - Body: {"customer_email_param": "...", "lxc_id_param": 123, "installer_json_param": {...}}
|
|
-- - Stores installer JSON after instance creation
|
|
-- - Requires: Service Role Key (backend only)
|
|
--
|
|
-- 5. POST /rpc/link_customer_to_auth_user
|
|
-- - Body: {"customer_email_param": "...", "auth_user_id_param": "..."}
|
|
-- - Links customer to Supabase Auth user
|
|
-- - Requires: Service Role Key (backend only)
|
|
|
|
-- =====================================================
|
|
-- Example Usage
|
|
-- =====================================================
|
|
|
|
-- Example 1: Get my instance config (authenticated user)
|
|
-- POST /rpc/get_my_instance_config
|
|
-- Headers: Authorization: Bearer <USER_JWT_TOKEN>
|
|
-- Body: {}
|
|
|
|
-- Example 2: Get public config (no auth)
|
|
-- POST /rpc/get_public_config
|
|
-- Body: {}
|
|
|
|
-- Example 3: Store installer JSON (service role)
|
|
-- POST /rpc/store_installer_json
|
|
-- Headers: Authorization: Bearer <SERVICE_ROLE_KEY>
|
|
-- Body: {"customer_email_param": "max@beispiel.de", "lxc_id_param": 769697636, "installer_json_param": {...}}
|
|
|
|
-- Example 4: Link customer to auth user (service role)
|
|
-- POST /rpc/link_customer_to_auth_user
|
|
-- Headers: Authorization: Bearer <SERVICE_ROLE_KEY>
|
|
-- Body: {"customer_email_param": "max@beispiel.de", "auth_user_id_param": "550e8400-e29b-41d4-a716-446655440000"}
|
|
|
|
-- =====================================================
|
|
-- End of Supabase Auth API
|
|
-- =====================================================
|