Postgres function still callable by anon after REVOKE FROM anon

1. 1. Build the broken project

   npm init -y >/dev/null && npm i --silent @electric-sql/pglite@0.5.10
   cat > check.mjs <<'EOF'
   import { PGlite } from "@electric-sql/pglite";
   import { readFileSync } from "node:fs";
   const db = new PGlite();
   await db.exec("CREATE ROLE anon; CREATE ROLE authenticated; CREATE FUNCTION paid_data() RETURNS text LANGUAGE sql AS 'SELECT 1::text';");
   await db.exec(readFileSync("grants.sql", "utf8"));
   await db.exec("SET ROLE anon");
   try {
     await db.query("SELECT paid_data()");
     console.log("LEAK: anon can still call paid_data()");
     process.exit(1);
   } catch (e) {
     console.log("locked for anon:", e.message);
   }
   EOF
   echo "REVOKE EXECUTE ON FUNCTION paid_data() FROM anon;" > grants.sql

2. 2. Test fails (the error reproduces)

   node check.mjs

3. 3. Apply the fix

   cat > grants.sql <<'EOF'
   REVOKE EXECUTE ON FUNCTION paid_data() FROM PUBLIC;
   GRANT EXECUTE ON FUNCTION paid_data() TO authenticated;
   EOF

4. 4. Same test passes

   node check.mjs

Sandbox log

Reproduced the error, applied the fix, and the error went away.

$ setup (exit 0)


$ test (exit 1)
LEAK: anon can still call paid_data()

$ apply (exit 0)


$ test (exit 0)
locked for anon: permission denied for function paid_data