Re: Is it possible to stop sessions killing eachother when they all authorize as the same role? >> CASE CLOSED

xof(at)thebuild(dot)com wrote:
>
>> tgl(at)sss(dot)pgh(dot)pa(dot)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...