-- Create dedicated role for pg_background
CREATE ROLE pgbackground_role NOLOGIN INHERIT;

-- Grant to application users (NOT superusers for production)
GRANT pgbackground_role TO app_user;

-- Revoke from specific user
REVOKE pgbackground_role FROM app_user;

-- Or use helper function
SELECT revoke_pg_background_privileges('app_user', true);

-- ❌ DANGEROUS: Don't do this!
GRANT pgbackground_role TO PUBLIC;

-- ✅ SAFE: Grant only to specific roles
GRANT pgbackground_role TO trusted_app_role;

-- ❌ DANGEROUS: Untrusted input in dynamic SQL
DO $$
DECLARE
  user_input text := get_user_input();  -- Could be malicious
BEGIN
  PERFORM pg_background_launch_v2(
    'SELECT * FROM users WHERE id = ' || user_input  -- SQL INJECTION!
  );
END;
$$;

-- ✅ SAFE: Use format() with proper quoting
DO $$
DECLARE
  user_input text := get_user_input();
BEGIN
  PERFORM pg_background_launch_v2(
    format('SELECT * FROM users WHERE id = %L', user_input)  -- Safely quoted
  );
END;
$$;

-- 1. Set max_worker_processes appropriately
ALTER SYSTEM SET max_worker_processes = 32;  -- Not unlimited!

-- 2. Monitor active workers
CREATE OR REPLACE FUNCTION check_worker_limit()
RETURNS boolean AS $$
DECLARE
  active_count int;
BEGIN
  SELECT count(*) INTO active_count
  FROM pg_background_list_v2() AS (
    pid int4, cookie int8, launched_at timestamptz, user_id oid,
    queue_size int4, state text, sql_preview text, last_error text, consumed bool
  )
  WHERE state = 'running';
  
  RETURN active_count < 20;  -- Application-specific limit
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 3. Use before launching workers
DO $$
BEGIN
  IF NOT check_worker_limit() THEN
    RAISE EXCEPTION 'Too many active workers';
  END IF;
  
  PERFORM pg_background_launch_v2('...');
END;
$$;

-- Set timeout to prevent runaway queries
SET statement_timeout = '5min';

-- Worker inherits this setting
SELECT pg_background_launch_v2('slow_query()');

-- ⚠️ IMPORTANT: Worker commits are independent
BEGIN;
  -- Launch background worker
  SELECT * FROM pg_background_launch_v2(
    'INSERT INTO audit_log VALUES (now(), ''user_login'')'
  ) AS h \gset;
  
  -- Main transaction work
  UPDATE users SET last_login = now() WHERE id = 123;
  
  -- If we ROLLBACK here, audit_log INSERT still commits!
ROLLBACK;

-- ✅ GOOD: Audit logging (should commit regardless)
SELECT pg_background_submit_v2(
  format('INSERT INTO audit_log VALUES (now(), %L)', action)
);

-- ✅ GOOD: Async notification
SELECT pg_background_submit_v2(
  format('SELECT pg_notify(''channel'', %L)', message)
);

-- ❌ BAD: Interdependent data modifications
BEGIN;
  INSERT INTO orders VALUES (...);
  
  -- Don't do this! Order INSERT might rollback, but payment won't
  SELECT pg_background_submit_v2('INSERT INTO payments VALUES (...)');
COMMIT;

-- ❌ VULNERABLE: PID might be reused after hours/days
SELECT pg_background_launch('...') AS pid \gset
-- ... session lives for weeks ...
SELECT pg_background_result(:pid);  -- Might attach to WRONG worker!

-- ✅ SAFE: Cookie prevents PID reuse confusion
SELECT * FROM pg_background_launch_v2('...') AS h \gset
-- ... time passes ...
SELECT pg_background_result_v2(:'h.pid', :'h.cookie');  -- Cookie validated

-- last_error in list_v2() might show:
-- "duplicate key value violates unique constraint: value (secret_data)"

-- Protect with VIEW + RLS
CREATE VIEW safe_worker_list AS
SELECT pid, cookie, state, consumed
FROM pg_background_list_v2() AS (...);
-- Omit sql_preview and last_error from public view

-- Application-level rate limiting
CREATE TABLE user_worker_quota (
  user_id oid PRIMARY KEY,
  workers_launched int DEFAULT 0,
  last_reset timestamptz DEFAULT now()
);

-- Check quota before launch
CREATE OR REPLACE FUNCTION launch_with_quota(sql text)
RETURNS pg_background_handle AS $$
DECLARE
  h pg_background_handle;
BEGIN
  -- Rate limit: 10 workers per minute
  UPDATE user_worker_quota
  SET workers_launched = CASE
    WHEN now() - last_reset > interval '1 minute' THEN 1
    ELSE workers_launched + 1
  END,
  last_reset = CASE
    WHEN now() - last_reset > interval '1 minute' THEN now()
    ELSE last_reset
  END
  WHERE user_id = current_user::regrole::oid;
  
  IF (SELECT workers_launched FROM user_worker_quota WHERE user_id = current_user::regrole::oid) > 10 THEN
    RAISE EXCEPTION 'Worker quota exceeded';
  END IF;
  
  SELECT * INTO h FROM pg_background_launch_v2(sql);
  RETURN h;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Always set statement_timeout on Windows
ALTER DATABASE mydb SET statement_timeout = '10min';

-- Or per-session:
SET statement_timeout = '5min';
SELECT pg_background_launch_v2('potentially_slow_query()');