xof@thebuild.com wrote:
tgl@sss.pgh.pa.us wrote:
Perhaps you'd already revoked from public in this database?
Very possible!
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...