xof@xxxxxxxxxxxx wrote:
You all forgot to tell me to put this aside and go out for a walk. I just told myself to do that. And it struck me then. Tom just said it here—albeit parenthetically with his "in this database". I had suppressed what I did understand well enough: that the whole suite of infrastructure objects—the catalog tables and views and the built-in functions (or at least as covers for whatever lies beneath them in C) is installed afresh in each newly-created database. So when I did "revoke execute on function pg_catalog.pg_terminate_backend(int, bigint) from public", my current database was "postgres". But when I invoked "pg_catalog.pg_terminate_backend()", my current database was "play". So with a trivial typing effort to re-order things, it all works fine now: Good for the lifetime of the "psql" CLI session. */; \set ECHO None \set QUIET On \set VERBOSITY Default -------------------------------------------------- /* Global actions for the entire cluster. */; \c postgres postgres set client_min_messages = warning; drop database if exists play; drop role if exists r1; create database play owner postgres; revoke all on database play from public; create role r1 with login password 'p'; grant connect on database play to r1; -------------------------------------------------- /* Local actions - limited in scope to the "play" database. */; \c play postgres set client_min_messages = warning; revoke execute on function pg_terminate_backend from public; \c play r1 set client_min_messages = warning; select pg_terminate_backend(42); The final "select" now gets the "permission denied for function pg_terminate_backend" error that you all have been seeing all along. Thanks to all for keeping me honest here. I feel rather embarrassed—but not enough to hold me back from asking the next stupid question... |
