A real puzzler: ANY way to recover?

"David F. Skoll" <dfs@roaringpenguin.com> writes:

Supposing someone stupidly did this:
UPDATE pg_database SET datallowconn = false;
and then closed all the connections to the server.

Is there any way to recover short of nuking everything and
restoring from a backup dump? :-(

That would be a bad move.

I wonder whether we should allow a standalone backend (postgres)
to connect regardless of datallowconn. The tradeoff is that you
could break your template0 slightly more easily, but you can break
template0 anyway if you are determined.

If you have actually done that :-(, my recommendation would be to
build a modified backend with the check in
src/backend/utils/init/postinit.c diked out ...

regards, tom lane

while you weren't looking, David F. Skoll wrote:

Is there any way to recover short of nuking everything and
restoring from a backup dump? :-(

I don't have any ability to test this and see if it actually works,
but a priori, I'd suggest trying, as your postgres user:

$ createdb oopswow
$ psql oopswow
oopswow=# update pg_database set datallowconn = true; -- with optional
WHERE clause
oopswow=# \q

That should work. Unless the new database takes its datallowconn
attribute from the template database, which will have been set to
false by the errant UPDATE -- in which case, I've no idea.

/rls

--
:wq

On Thu, May 05, 2005 at 01:14:26PM -0400, David F. Skoll wrote:

Hi,

Supposing someone stupidly did this:

UPDATE pg_database SET datallowconn = false;

and then closed all the connections to the server.

Is there any way to recover short of nuking everything and
restoring from a backup dump? :-(

Connect in standalone mode?

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Ellos andaban todos desnudos como su madre los pari�, y tambi�n las mujeres,
aunque no vi m�s que una, harto moza, y todos los que yo vi eran todos
mancebos, que ninguno vi de edad de m�s de XXX a�os" (Crist�bal Col�n)

Alvaro Herrera wrote:

Connect in standalone mode?

Does not work. Tom Lane replied with the only thing that does work,
which we independently discovered about 30 seconds before hearing from
Tom. :-)

The solution is to modify the PostgreSQL source code to skip the check,
and run the modified binary in standalone mode just to reset the
datallowconn flag to true. Once we did that, we quit and started
the normal server to complete the maintenance work.

I recommend having a postgres single-user-mode command-line option
to disable the check, with a suitably stern warning in the man
page not to use it. :-)

Regards,

David.

Rosser Schwarz <rosser.schwarz@gmail.com> writes:

but a priori, I'd suggest trying, as your postgres user:

$ createdb oopswow

Doesn't work because createdb has to connect to something (typically
template1). If he's closed off *all* his databases, he's in deep
doo-doo.

This is reminiscent of the problem pointed out some time ago "what
do you do if you deleted all your superusers"? We fixed that by
allowing a standalone backend to connect (and to believe itself to
be a superuser) no matter what is in ... or not in ... pg_shadow.
I'm inclined to think the same answer should hold for this one.

regards, tom lane

"David F. Skoll" <dfs@roaringpenguin.com> writes:

Supposing someone stupidly did this:
UPDATE pg_database SET datallowconn = false;
and then closed all the connections to the server.

Is there any way to recover short of nuking everything and
restoring from a backup dump? :-(

BTW, am I right in guessing that you got into this fix by sloppy
application of the directions for fixing template0 for the recent
security issues?

Although I dislike last-minute fixes, I am strongly tempted to put in
the ignore-datallowconn-if-standalone change in all the releases we are
about to make. It'd be a one-liner that is very unlikely to break
anything, and I have this nasty feeling that you may not be the only
guys to make this mistake.

regards, tom lane

Tom Lane wrote:

Although I dislike last-minute fixes, I am strongly tempted to put in
the ignore-datallowconn-if-standalone change in all the releases we
are about to make.

That sounds very reasonable.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e@gmx.net> writes:

Tom Lane wrote:

Although I dislike last-minute fixes, I am strongly tempted to put in
the ignore-datallowconn-if-standalone change in all the releases we
are about to make.

That sounds very reasonable.

Done.

regards, tom lane

Peter Eisentraut wrote:

Tom Lane wrote:

Although I dislike last-minute fixes, I am strongly tempted to put in
the ignore-datallowconn-if-standalone change in all the releases we
are about to make.

I'm curious as to what the purpose of such a setting might be
(datallowconn = false)? Any functional use? (other than a tough
learning experience?)

--
Until later, Geoffrey

David F. Skoll wrote: