*Summary*
Is rescue possible? Or must I simply remove my cluster and use "pg_ctl" to create a new one within my existing PG 14.5 software env?
*Detail*
This is a sandbox PostgreSQL 14.5 cluster on my MacBook and it contains nothing of value. I was doing some empirical destructive tests with a view to clarifying my mental model. In the belief that a superuser is unstoppable, I had set all the options like "createdb" and "createrole" for the "postgres" role to their "no" mode. And I couldn't detect any problems. However, I'd left the "login" option in its "yes" mode.
The rationale here was informed by tests with superusers created (and then dropped) ad hoc. I found that setting "nologin" trumped the otherwise unstoppability of a superuser. This was a surprise.
<aside>This was also nice because I haven't yet seen a use case that needs more than one superuser in the whole cluster. Yet I'm stuck with a second superuser, in addition to "postgres", with the name of the macOS user, "Bllewell" (with init cap) that owns the installation. And it has to exist because it owns the "pg_catalog" schema (and its cousins) an every database. So I set "nologin" for "Bllewell".</aside>
I tried both "drop role postgres" and "drop database postgres". They both failed with errors to the effect that they are needed by the system. Then came the test whose outcome was to lock me out totally. At this point, "\du" without the "S" qualifier listed only "postgres" and "Bllewell". I did this:
alter user postgres with nosuperuser;
I expected an error—just as I'd got on attempting to drop the "postgres" role or the "postgres" database. But it quietly succeeded. And then I hit a wrong key and exited my "psql" session. Now I can't start a psql session. Trying with one of the two available roles gets me this:
role "Bllewell" is not permitted to log in
And trying with the other gets me this:
permission denied for database "postgres"… User does not have CONNECT privilege… permission denied for database "postgres"
Neither error is a lie. The first reflects my intention. And the second reflects the fact that, while "postgres" was a superuser, it didn't need an explicit "connect" privilege on any database.
My "hba" file says "trust"—and, before locking myself out, I was happily able to start sessions without a password challenge.
With Oracle Database, the roughly equivalent user, called "SYS", is what it is by virtue of its intrinsic immutable hard-coded identity. And a person who can authorize as the O/S user that owns the installation can always start a session. This is regarded as the last ditch rescue mechanism. But I'm already authorised as the O/S user that owns the PG installation. And I'm locked out.