-- db/grants.sql: rights for the application's database user. Safe to run again, and it MUST be run
-- again after every migration (scripts/migrate.sh does this), and after a hosting panel such as cPanel
-- adds a user to the database, because those panels can hand out broad rights on existing tables.
--
-- The name of the application user comes from the setting pos.app_role (default: pos_app):
--   SET LOCAL pos.app_role = 'cpuser_posapp';
--
-- Layers of protection for the ledger and audit log:
--   1. Triggers refuse UPDATE, DELETE and TRUNCATE for everyone (migration 0001).
--   2. The application user is not even allowed to try (this file), so an API bug cannot edit them.
DO $$
DECLARE
  app_role text := COALESCE(NULLIF(current_setting('pos.app_role', true), ''), 'pos_app');
  fn record;
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = app_role) THEN
    RAISE NOTICE 'database user "%" does not exist: no rights were granted', app_role;
    RETURN;
  END IF;

  IF NOT has_schema_privilege(app_role, 'public', 'USAGE') THEN
    EXECUTE format('GRANT USAGE ON SCHEMA public TO %I', app_role);
  END IF;
  EXECUTE format('GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO %I', app_role);
  EXECUTE format('REVOKE UPDATE, DELETE, TRUNCATE ON stock_movements, audit_log FROM %I', app_role);
  EXECUTE format('GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO %I', app_role);
  -- Only our own functions; functions that belong to the server or an extension are already usable.
  FOR fn IN SELECT p.oid::regprocedure AS signature
              FROM pg_proc p
              JOIN pg_namespace n ON n.oid = p.pronamespace
             WHERE n.nspname = 'public' AND p.proowner = (SELECT oid FROM pg_roles WHERE rolname = current_user)
  LOOP
    EXECUTE format('GRANT EXECUTE ON FUNCTION %s TO %I', fn.signature, app_role);
  END LOOP;
  IF to_regclass('public.schema_migrations') IS NOT NULL THEN
    EXECUTE format('REVOKE ALL ON schema_migrations FROM %I', app_role);
  END IF;
END $$;