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