PostgreSQLのRow Level Security紹介

PostgreSQLで下記のようで、Functionを定義することができます。

CREATE OR REPLACE FUNCTION public.is_superuser(user_id uuid)
RETURNS boolean AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM public.user_profiles
        WHERE id = user_id
        AND user_type = 'superuser'
    );
END;

クエリする際SQL関数のように使えます：

-- Find all orders from superusers
SELECT * FROM orders o
WHERE public.is_superuser(o.user_id);

-- Example in a stored procedure or DO block
DO $$
DECLARE
    v_user_id uuid := '123e4567-e89b-12d3-a456-426614174000';
BEGIN
    IF public.is_superuser(v_user_id) THEN
        RAISE NOTICE 'User is a superuser';
        -- Perform superuser-specific actions
    ELSE
        RAISE NOTICE 'User is not a superuser';
        -- Perform regular user actions
    END IF;
END $$;

-- Example: Only allow certain operations for superusers
CREATE OR REPLACE FUNCTION delete_sensitive_data(user_id uuid, record_id integer)
RETURNS void AS $$
BEGIN
    IF NOT public.is_superuser(user_id) THEN
        RAISE EXCEPTION 'Only superusers can delete sensitive data';
    END IF;
    
    -- Proceed with deletion
    DELETE FROM sensitive_table WHERE id = record_id;
END;
$$ LANGUAGE plpgsql;