sql-def_priv_bug.sh

#!/bin/sh

PSQL="psql -v ON_ERROR_STOP=1"

export PGUSER="postgres"
export PGHOST="127.0.0.1"
export PGPORT="5432"
export PGSSLMODE="disable"

export PGDATABASE="template1"
export PGUSER="postgres"

${PSQL} << E*O*F || exit 1
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'bug'
  AND pid <> pg_backend_pid();

DROP DATABASE IF EXISTS "bug";

DROP ROLE IF EXISTS "bug";
CREATE ROLE "bug" NOINHERIT LOGIN BYPASSRLS PASSWORD '123456';

CREATE DATABASE "bug";
E*O*F

export PGDATABASE="bug"
export PGUSER="postgres"

${PSQL} -q << E*O*F || exit 1
ALTER DEFAULT PRIVILEGES REVOKE ALL ON FUNCTIONS FROM PUBLIC;

CREATE SCHEMA foo;
GRANT USAGE ON SCHEMA foo TO "bug";
CREATE FUNCTION foo.foo() RETURNS bool
    IMMUTABLE
    LANGUAGE sql
AS
\$_\$
SELECT TRUE;
\$_\$;

CREATE SCHEMA bar;
GRANT USAGE ON SCHEMA bar TO "bug";
ALTER DEFAULT PRIVILEGES IN SCHEMA bar GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
CREATE FUNCTION bar.bar() RETURNS bool
    IMMUTABLE
    LANGUAGE sql
AS
\$_\$
SELECT FALSE;
\$_\$;
E*O*F