Re: [HACKERS] big problem
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: >> I deleted valuable data from wrong table :) pretty common problem i >> think. Guy on #postgresql at freenode told me that my data is still >> there, but tricky part is how to undo my delete. I'm using pg 7.4.7 on >> fbsd, i dont' use any special config and pg_xlog is fine. I hope there >> is a solution :) > That guy on IRC is probably me. I told him to take a copy of his data > dir for safekeeping, and not to vacuum. I don't know how to get a look > at old row versions, however. It's new data with no backup, and no PITR > running. The easiest avenue might be to back up the XID counter with pg_resetxlog. You'd have to dump and restore the table of course, else the rows will go missing again as soon as the XID climbs up past the transaction that deleted 'em. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] big problem
I deleted valuable data from wrong table :) pretty common problem i think. Guy on #postgresql at freenode told me that my data is still there, but tricky part is how to undo my delete. I'm using pg 7.4.7 on fbsd, i dont' use any special config and pg_xlog is fine. I hope there is a solution :) That guy on IRC is probably me. I told him to take a copy of his data dir for safekeeping, and not to vacuum. I don't know how to get a look at old row versions, however. It's new data with no backup, and no PITR running. Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] big problem
Hi all, I couldn't find anything related to my problem on web or irc, so i'm posting here. I deleted valuable data from wrong table :) pretty common problem i think. Guy on #postgresql at freenode told me that my data is still there, but tricky part is how to undo my delete. I'm using pg 7.4.7 on fbsd, i dont' use any special config and pg_xlog is fine. I hope there is a solution :) Thanks for answer and best regards, Bostjan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Big problem
On Mon, 2004-05-24 at 16:03, Christopher Kings-Lynne wrote: > > Isn't it just enough to prevent the user with userid 1 from losing the > > superuser status. If one want to allow it one could prevent it just when > > doing the ALTER USER stuff and allow it when editing pg_shadow directly. > > Or maybe have some guc variable that write locks the user with id 1. > > That gets my vote - can't take superuser off id 1... Gets my vote too, postgres user can't take superuser off. > > > Given that it was so "simple" to restore I'm not sure if it's worth it or > > not, but restricting just user 1 does not give any of the problems you > > wrote about. > > Well, sergio sure wasn't very happy... yes I wasn't but "Stop postmaster and start a standalone backend. Now you are asuperuser, and you can create a new superuser, or just go in and UPDATE pg_shadow to make your original user super again. Exit standalone backend, restart postmaster, have a beer." worked ! thanks , > > And if I ever get around to my patch that separates out superuser and > catalog modification privileges, superusers will no longer necessarily > be able to 'delete from pg_proc'; > > Chris -- Sérgio M. B. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Big problem
IMHO we (that is Christopher, me and others maintaining easy to (mis)use tools) should warn the users about what they're going to do. Yes, I'm going to have to modify phpPgAdmin methinks. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Big problem
Christopher Kings-Lynne wrote: The mistake has only come up two or three times that I can remember, which doesn't elevate it to the category of stuff that I want to install a lot of mechanism to prevent. Especially not mechanism that would get in the way of reasonable uses. I think it's sufficient to have a recovery procedure. Hmmm - I agree it's difficult, but somehow I think it's something we should do. Just imagine if some major user of postgres did it - they'd be screaming blue murder... IMHO we (that is Christopher, me and others maintaining easy to (mis)use tools) should warn the users about what they're going to do. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Big problem
Tom Lane wrote: Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: Hmmm - I agree it's difficult, but somehow I think it's something we should do. Just imagine if some major user of postgres did it - they'd be screaming blue murder... Shrug. Superusers can *always* shoot themselves in the foot in Postgres. Try "delete from pg_proc", for instance. This sounds right up there with the notion of preventing a Unix superuser from doing "rm -rf /". Why not simply add a flag "undeleteable" applicable only to super user? In this way is enough in the initdb fase create the postgres user as undeleateable. I think this is resonable. Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Big problem
On Mon, May 24, 2004 at 11:23:09AM -0700, Joe Conway wrote: > Tom Lane wrote: > >Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > >>Hmmm - I agree it's difficult, but somehow I think it's something we > >>should do. Just imagine if some major user of postgres did it - they'd > >>be screaming blue murder... > > > >Shrug. Superusers can *always* shoot themselves in the foot in Postgres. > >Try "delete from pg_proc", for instance. This sounds right up there > >with the notion of preventing a Unix superuser from doing "rm -rf /". > > FWIW, I've seen a unix superuser do a recursive chmod 777 on /, and I've > seen a Windows server admin recursively deny EVERYTHING from EVERYBODY > starting at c:\. In both cases, we found that's why we keep regular > backups ;-) I've personally done rm -fr /, but this doesn't mean we couldn't do better than imitate Unix permission scheme. In fact, latest efforts are trying to get rid of a all-powerful superuser by using more granular "capabilities". Maybe we don't need to exclusive-lock the entire ALTER USER operation; perhaps a lock escalation method could be used. OTOH I agree this particular problem may not need a solution. -- Alvaro Herrera () "La grandeza es una experiencia transitoria. Nunca es consistente. Depende en gran parte de la imaginación humana creadora de mitos" (Irulan) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Big problem
Tom Lane wrote: Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: Hmmm - I agree it's difficult, but somehow I think it's something we should do. Just imagine if some major user of postgres did it - they'd be screaming blue murder... Shrug. Superusers can *always* shoot themselves in the foot in Postgres. Try "delete from pg_proc", for instance. This sounds right up there with the notion of preventing a Unix superuser from doing "rm -rf /". I have to agree. FWIW, I've seen a unix superuser do a recursive chmod 777 on /, and I've seen a Windows server admin recursively deny EVERYTHING from EVERYBODY starting at c:\. In both cases, we found that's why we keep regular backups ;-) Joe ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Big problem
Isn't it just enough to prevent the user with userid 1 from losing the superuser status. If one want to allow it one could prevent it just when doing the ALTER USER stuff and allow it when editing pg_shadow directly. Or maybe have some guc variable that write locks the user with id 1. That gets my vote - can't take superuser off id 1... Given that it was so "simple" to restore I'm not sure if it's worth it or not, but restricting just user 1 does not give any of the problems you wrote about. Well, sergio sure wasn't very happy... And if I ever get around to my patch that separates out superuser and catalog modification privileges, superusers will no longer necessarily be able to 'delete from pg_proc'; Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Big problem
On Mon, 24 May 2004, Tom Lane wrote: > I think the cure would probably be worse than the disease. To make any > serious attempt at preventing remove-the-last-superuser, we'd have to > make operations on pg_shadow grab exclusive lock. For instance, you > couldn't allow two backends to DROP USER in parallel; they might be > dropping the last two superusers, but neither one would think it was > creating a problem. And while we could theoretically make > CREATE/ALTER/DROP USER take such locks, I dunno how you make a straight > "DELETE FROM pg_shadow" do so. Isn't it just enough to prevent the user with userid 1 from losing the superuser status. If one want to allow it one could prevent it just when doing the ALTER USER stuff and allow it when editing pg_shadow directly. Or maybe have some guc variable that write locks the user with id 1. Given that it was so "simple" to restore I'm not sure if it's worth it or not, but restricting just user 1 does not give any of the problems you wrote about. -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Big problem
The mistake has only come up two or three times that I can remember, which doesn't elevate it to the category of stuff that I want to install a lot of mechanism to prevent. Especially not mechanism that would get in the way of reasonable uses. I think it's sufficient to have a recovery procedure. Hmmm - I agree it's difficult, but somehow I think it's something we should do. Just imagine if some major user of postgres did it - they'd be screaming blue murder... We could always implement it without locks, thereby taking care of 99.9% of the times it might happen, with still the availability of a cure even if they manage to get through that... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Big problem
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > Hmmm - I agree it's difficult, but somehow I think it's something we > should do. Just imagine if some major user of postgres did it - they'd > be screaming blue murder... Shrug. Superusers can *always* shoot themselves in the foot in Postgres. Try "delete from pg_proc", for instance. This sounds right up there with the notion of preventing a Unix superuser from doing "rm -rf /". regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Big problem
Ð ÐÐÐ, 24.05.2004, Ð 16:12, Tom Lane ÐÐÑÐÑ: > Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > > A guy on the IRC channel managed to accidentally click the wrong thing > > in phpPgAdmin and managed to remove superuser privileges from his only > > superuser. > > No sweat; we've seen this one before. > > Stop postmaster and start a standalone backend. Now you are a > superuser, and you can create a new superuser, or just go in and UPDATE > pg_shadow to make your original user super again. Exit standalone > backend, restart postmaster, have a beer. The question whether we should prevent this from happening stands; I think we should. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Big problem
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: >> No sweat; we've seen this one before. > Should this situation be prevented though? I think the cure would probably be worse than the disease. To make any serious attempt at preventing remove-the-last-superuser, we'd have to make operations on pg_shadow grab exclusive lock. For instance, you couldn't allow two backends to DROP USER in parallel; they might be dropping the last two superusers, but neither one would think it was creating a problem. And while we could theoretically make CREATE/ALTER/DROP USER take such locks, I dunno how you make a straight "DELETE FROM pg_shadow" do so. The mistake has only come up two or three times that I can remember, which doesn't elevate it to the category of stuff that I want to install a lot of mechanism to prevent. Especially not mechanism that would get in the way of reasonable uses. I think it's sufficient to have a recovery procedure. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Big problem
No sweat; we've seen this one before. Should this situation be prevented though? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Big problem
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > A guy on the IRC channel managed to accidentally click the wrong thing > in phpPgAdmin and managed to remove superuser privileges from his only > superuser. No sweat; we've seen this one before. Stop postmaster and start a standalone backend. Now you are a superuser, and you can create a new superuser, or just go in and UPDATE pg_shadow to make your original user super again. Exit standalone backend, restart postmaster, have a beer. The REINDEX man page is worth reading if you've never used a standalone backend before; it covers some of the gory details. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Big problem
On Mon, May 24, 2004 at 09:54:20PM +0800, Christopher Kings-Lynne wrote: > in phpPgAdmin and managed to remove superuser privileges from his only > superuser. > > We thought and though but it seems that there is no way to recover from > this situation except a re-init and reload. And what user is he even > going to dump as? Hmm ... I'm not sure but maybe with a standalone backend it can be recovered? If not, I'd suggest compiling a hacked backend with the permission check for the ALTER USER ripped out, use that to correct the problem, and then erase it (and the patch). -- Alvaro Herrera () One man's impedance mismatch is another man's layer of abstraction. (Lincoln Yeoh) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Big problem
Hi guys, A guy on the IRC channel managed to accidentally click the wrong thing in phpPgAdmin and managed to remove superuser privileges from his only superuser. We thought and though but it seems that there is no way to recover from this situation except a re-init and reload. And what user is he even going to dump as? Is there anything he can do? Also, shouldn't we prevent this situation from ever occurring? Please reply to all as he isn't subscribed. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] BIG problem !!:fatal 1:set user id user admin is not in eg shadow
We have postgres running on a linux machine and we connect with 15 winnt 4.0 machines running ACCESS2000 When we change from access 97 to access 2000 we get every 15 minutes following problem after a process query - fatal 1:set user id user admin is not in eg shadow and also after we select --- waiting... have sombody already have this problem on how to solve??? VERY URGENT ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
