Limit global default function execution privileges

Greetings,

* David G. Johnston (david.g.johnston@gmail.com) wrote:

Since we are discussing locking down our defaults is revoking the global
function execution privilege granted to PUBLIC - instead limiting it to
just the pg_catalog schema - on the table?

I'm not sure how strongly I feel toward the proposal but it does come up on
these lists; and the fact that it doesn't distinguish between security
definer and security invoker is a trap for the unaware.

I wouldn't limit it to the pg_catalog schema, I'd just explicitly mark
the functions in pg_catalog which should have EXECUTE rights available
to PUBLIC.

I'm afraid this would cause a lot of work for people who use a lot of
pl/pgsql, but it might be a good thing in the end. Environments could
configure ALTER DEFAULT PRIVILEGES to automatically install the GRANT
back if they wanted it, and pg_dump would just pull through whatever the
privileges actually were on old systems into the new systems.

This definitely comes up regularly when introducing new people to
PostgreSQL.

Thanks!

Stephen