Thread: I slipped up so that no existing role allows connection. Is rescue possible?
I slipped up so that no existing role allows connection. Is rescue possible?
From
Bryn Llewellyn
Date:
*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.
Re: I slipped up so that no existing role allows connection. Is rescue possible?
From
Tom Lane
Date:
Bryn Llewellyn <bryn@yugabyte.com> writes:
> Is rescue possible? Or must I simply remove my cluster and use "pg_ctl" to create a new one within my existing PG
14.5software env?
Stop the cluster, start a single-user session ("postgres --single"),
re-grant superuser to the postgres user and/or whatever else you
wish you could take back, end that session, restart the cluster.
(You're not the first to mess up like this.)
regards, tom lane
Re: I slipped up so that no existing role allows connection. Is rescue possible?
From
"David G. Johnston"
Date:
On Monday, September 19, 2022, Bryn Llewellyn <bryn@yugabyte.com> wrote:
*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?
Running the postgres executable in single user mode should provide an avenue I believe.
David J.
Re: I slipped up so that no existing role allows connection. Is rescue possible?
From
Bryn Llewellyn
Date:
tgl@sss.pgh.pa.us wrote:bryn@yugabyte.com writes: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?
Stop the cluster, start a single-user session ("postgres --single"), re-grant superuser to the postgres user and/or whatever else you wish you could take back, end that session, restart the cluster.
So nice to find this waiting for me when I got back to the keyboard after a late lunch. Thanks, Tom. And thanks to David, too who said much the same. I'll note how I spelled the magic—as a little memo for me:
postgres --single -D /usr/local/var/postgres postgres
The world that the "backend> " prompt opened up for me was rather basic. But even so, "alter user postgres with superuser" worked fine. (This was the only rescue that I needed.) And, after a normal restart, everything looks normal again now from the psql prompt.
This leads to some follow-up questions. But I'll start a new thread.
Re: I slipped up so that no existing role allows connection. Is rescue possible?
From
Adrian Klaver
Date:
On 9/19/22 16:15, Bryn Llewellyn wrote: > // >> /tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us> wrote: >> / > So nice to find this waiting for me when I got back to the keyboard > after a late lunch. Thanks, Tom. And thanks to David, too who said much > the same. I'll note how I spelled the magic—as a little memo for me: > > postgres --single -D /usr/local/var/postgres postgres > > The world that the "backend> " prompt opened up for me was rather basic. Which is documented here: https://www.postgresql.org/docs/current/app-postgres.html#APP-POSTGRES-SINGLE-USER -- Adrian Klaver adrian.klaver@aklaver.com
Re: I slipped up so that no existing role allows connection. Is rescue possible?
From
Bryn Llewellyn
Date:
> adrian.klaver@aklaver.com wrote: > >> bryn@yugabyte.com wrote: >> >>> tgl@sss.pgh.pa.us wrote: >>> >>> ... >> >> So nice to find this waiting for me when I got back to the keyboard after a late lunch. Thanks, Tom. And thanks to David,too who said much the same. I'll note how I spelled the magic—as a little memo for me: >> >> postgres --single -D /usr/local/var/postgres postgres >> >> The world that the "backend> " prompt opened up for me was rather basic. > > Which is documented here: > > www.postgresql.org/docs/current/app-postgres.html#APP-POSTGRES-SINGLE-USER Yes, after Tom's hint, a search for "single-user" took me to that page. But, beginner as I am, I didn't know that single-usermode was the thing that I needed. I need a remedial class. Something like "PostgreSQL for those whose mental modelhas been conditioned by decades of working with Oracle Database".
Aw: Re: I slipped up so that no existing role allows connection. Is rescue possible?
From
Karsten Hilbert
Date:
> Yes, after Tom's hint, a search for "single-user" took me to that page. But, beginner as I am, > I didn't know that single-user mode was the thing that I needed. I need a remedial class. > Something like "PostgreSQL for those whose mental model has been conditioned by decades of working with Oracle Database". I think it's normal to not know the Ins and Outs of a ... new software. And that's the point why that class should be an easy one: Drop the idea that PG works like Oracle 101. Then, read the manual, back to cover. Yes, one will forget most of what's written there. However, a coarse structure of a new mental model will form. Karsten
Re: I slipped up so that no existing role allows connection. Is rescue possible?
From
Mladen Gogala
Date:
On 9/19/22 18:15, Tom Lane wrote:
Bryn Llewellyn <bryn@yugabyte.com> writes: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?Stop the cluster, start a single-user session ("postgres --single"), re-grant superuser to the postgres user and/or whatever else you wish you could take back, end that session, restart the cluster. (You're not the first to mess up like this.) regards, tom lane
Tom, your knowledge is vast and your advice is extremely useful. Have you ever considered creating a dedicated page for beginners? Something like AskTom.postgresql.org would probably be appropriate.
Regards
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: I slipped up so that no existing role allows connection. Is rescue possible?
From
"Theodore M Rolle, Jr."
Date:
.
.
.
And Tom’s English is excellent!
On Tue, Sep 20, 2022, 18:29 Mladen Gogala <gogala.mladen@gmail.com> wrote:
On 9/19/22 18:15, Tom Lane wrote:Bryn Llewellyn <bryn@yugabyte.com> writes: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?Stop the cluster, start a single-user session ("postgres --single"), re-grant superuser to the postgres user and/or whatever else you wish you could take back, end that session, restart the cluster. (You're not the first to mess up like this.) regards, tom laneTom, your knowledge is vast and your advice is extremely useful. Have you ever considered creating a dedicated page for beginners? Something like AskTom.postgresql.org would probably be appropriate.
Regards
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: I slipped up so that no existing role allows connection. Is rescue possible?
From
Rob Sargent
Date:
On Sep 20, 2022, at 4:54 PM, Theodore M Rolle, Jr. <stercor@gmail.com> wrote:
...And Tom’s English is excellent!
That’s what this is! With the bonus of AK,DJ and the gang - and you Mladen. (Maybe not as searchable as one might like but that makes one pay attention. )
Re: I slipped up so that no existing role allows connection. Is rescue possible?
From
Mladen Gogala
Date:
On 9/20/22 18:54, Theodore M Rolle, Jr. wrote:
And Tom’s English is excellent!
As opposed to mine?
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com