Hi,
PostgreSQL version: 13.2
Operating system: FreeBSD 13-STABLE
I've founded that pg_dump (and pg_dumpall) incorrectlty exports subsequent default privileges.
Ex.:
> ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE ALL ON FUNCTIONS FROM PUBLIC;
> ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA bar GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
> bug=# \ddp
> Default access privileges
> Owner | Schema | Type | Access privileges
> ----------+--------+----------+---------------------
> postgres | bar | function | =X/postgres
> postgres | | function | postgres=X/postgres
> -- I can execute newly created functions on scheme 'bar' after that.
will be exported as:
> ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA bar REVOKE ALL ON FUNCTIONS FROM postgres;
> ALTER DEFAULT PRIVILEGES FOR ROLE postgres REVOKE ALL ON FUNCTIONS FROM PUBLIC;
> bug=# \ddp
> Default access privileges
> Owner | Schema | Type | Access privileges
> ----------+--------+----------+---------------------
> postgres | | function | postgres=X/postgres
> -- I can NOT execute newly created functions on scheme 'bar' after that.
After restoring the dump I cannot execute newly created functions on scheme 'bar'.
I've attached the shell-script that create the environment.
I create a function after dump or restore procedures (ex. backup purposes):
> CREATE FUNCTION bar.new() RETURNS bool
> IMMUTABLE
> LANGUAGE sql
> AS 'SELECT TRUE;';
The function bar.new() cannot be executed by 'bug' user if created after the restore procedure.
I've got an error: ERROR: permission denied for function new.
WBR,
Boris