pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRegaining superuser access
7+ messages / 5 participants
[nested] [flat]
* Regaining superuser access
@ 2008-09-18 11:13 Bernt Drange <badrange@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bernt Drange @ 2008-09-18 11:13 UTC (permalink / raw)
To: pgsql-admin
I have a user with a problem on his PostgreSQL 8.1 server running
Windows:
The server only has one superuser, and for some reason he decided to
remove the logon privilege on this user in PgAdmin III. So now he has
lost superuser access to the database server.
Is there some way to override this setting? Or some way to trick
PostgreSQL into creating a new superuser without superuser access?
I can log on to the database with other users, but even if I add
"trust" to pg_hba.conf PostgreSQL will not let me access the database
as the superuser.
I have remote access to the server, which is now on a ship outside of
Madagascar.
The problem we have seems to be the same as this user reports (but got
no reply):
http://www.nabble.com/Fatal-error%3A-role-%22postgres%22-no-login%21-tp14537100p14541715.html
I have looked in the documentation, but don't seem to be able to find
any solutions. Pointers are welcome!
Kind regards,
Bernt
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Regaining superuser access
@ 2008-09-18 12:35 Glyn Astill <glynastill@yahoo.co.uk>
parent: Bernt Drange <badrange@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Glyn Astill @ 2008-09-18 12:35 UTC (permalink / raw)
To: pgsql-admin@postgresql.org, Bernt Drange <badrange@gmail.com>
> The server only has one superuser, and for some reason he
> decided to
> remove the logon privilege on this user in PgAdmin III. So
> now he has
> lost superuser access to the database server.
>
> Is there some way to override this setting? Or some way to
> trick
> PostgreSQL into creating a new superuser without superuser
> access?
Start it up in single user mode?
http://www.postgresql.org/docs/8.3/static/app-postgres.html
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Regaining superuser access
@ 2008-09-18 16:01 Bernt Drange <badrange@gmail.com>
parent: Glyn Astill <glynastill@yahoo.co.uk>
0 siblings, 1 reply; 7+ messages in thread
From: Bernt Drange @ 2008-09-18 16:01 UTC (permalink / raw)
To: pgsql-admin
On Sep 18, 2:35 pm, glynast...@yahoo.co.uk (Glyn Astill) wrote:
> > The server only has one superuser, and for some reason he
> > decided to
> > remove the logon privilege on this user in PgAdmin III. So
> > now he has
> > lost superuser access to the database server.
>
> > Is there some way to override this setting? Or some way to
> > trick
> > PostgreSQL into creating a new superuser without superuser
> > access?
>
> Start it up in single user mode?
>
> http://www.postgresql.org/docs/8.3/static/app-postgres.html
Thanks a bunch!
After a lot of fiddling with being able to enter single user mode on a
windows machine (I had to figure out how to run the command line as
the correct user, then for some reason -D didn't work, but SET
PGDATA=xxx worked), I finally managed to fix my problem.
You saved my user's day (of course *I* would never do something that
stupid)!
Bernt
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Regaining superuser access
@ 2008-09-18 17:03 Alvaro Herrera <alvherre@commandprompt.com>
parent: Bernt Drange <badrange@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Alvaro Herrera @ 2008-09-18 17:03 UTC (permalink / raw)
To: Bernt Drange <badrange@gmail.com>; +Cc: pgsql-admin@postgresql.org, Pg Hackers <pgsql-hackers@postgresql.org>
Bernt Drange escribió:
> After a lot of fiddling with being able to enter single user mode on a
> windows machine (I had to figure out how to run the command line as
> the correct user, then for some reason -D didn't work, but SET
> PGDATA=xxx worked), I finally managed to fix my problem.
Hmm, the -D thing not working should probably be studied -- perhaps
we're missing escaping something somewhere. Does the PGDATA path
contain spaces or weird chars?
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Regaining superuser access
@ 2008-09-19 08:26 Bernt Drange <badrange@gmail.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bernt Drange @ 2008-09-19 08:26 UTC (permalink / raw)
To: pgsql-admin
On Sep 18, 7:03 pm, alvhe...@commandprompt.com (Alvaro Herrera) wrote:
> Bernt Drange escribió:
>
> > After a lot of fiddling with being able to enter single user mode on a
> > windows machine (I had to figure out how to run the command line as
> > the correct user, then for some reason -D didn't work, but SET
> > PGDATA=xxx worked), I finally managed to fix my problem.
>
> Hmm, the -D thing not working should probably be studied -- perhaps
> we're missing escaping something somewhere. Does the PGDATA path
> contain spaces or weird chars?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Regaining superuser access
@ 2008-09-19 14:39 Scott Marlowe <scott.marlowe@gmail.com>
parent: Bernt Drange <badrange@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Scott Marlowe @ 2008-09-19 14:39 UTC (permalink / raw)
To: Bernt Drange <badrange@gmail.com>; +Cc: pgsql-admin
On Fri, Sep 19, 2008 at 2:26 AM, Bernt Drange <badrange@gmail.com> wrote:
> On Sep 18, 7:03 pm, alvhe...@commandprompt.com (Alvaro Herrera) wrote:
>> Bernt Drange escribió:
>>
>> > After a lot of fiddling with being able to enter single user mode on a
>> > windows machine (I had to figure out how to run the command line as
>> > the correct user, then for some reason -D didn't work, but SET
>> > PGDATA=xxx worked), I finally managed to fix my problem.
>>
>> Hmm, the -D thing not working should probably be studied -- perhaps
>> we're missing escaping something somewhere. Does the PGDATA path
>> contain spaces or weird chars?
>
> From memory the path was something like: F:\Postgresql Database\data.
> I quoted it with double quotes. Without -D postgres.exe complained
> about not finding the data path, with it postgres.exe complained about
> not finding the config file, stating that it looked in (from memory
> vague) F:\Postgresql Database\data\postgres\somethingmore. Adding the
> --config-file parameter didn't help.
>
> Is this enough information for you to start digging a bit more? If
> not, I might find the exact messages, but I'm reluctant to do it on
> this production database..
I'm pretty sure the problem is with the space between Postgresql and
Database. Not sure if it's fixed in later releases or not.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Regaining superuser access
@ 2008-09-19 16:22 Tim Bruce - Postgres <postgres@tbruce.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tim Bruce - Postgres @ 2008-09-19 16:22 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
On Fri, September 19, 2008 07:39, Scott Marlowe wrote:
> On Fri, Sep 19, 2008 at 2:26 AM, Bernt Drange <badrange@gmail.com> wrote:
>> On Sep 18, 7:03 pm, alvhe...@commandprompt.com (Alvaro Herrera) wrote:
>>> Bernt Drange escribió:
>>>
>>> > After a lot of fiddling with being able to enter single user mode on
>>> a
>>> > windows machine (I had to figure out how to run the command line as
>>> > the correct user, then for some reason -D didn't work, but SET
>>> > PGDATA=xxx worked), I finally managed to fix my problem.
>>>
>>> Hmm, the -D thing not working should probably be studied -- perhaps
>>> we're missing escaping something somewhere. Does the PGDATA path
>>> contain spaces or weird chars?
>>
>> From memory the path was something like: F:\Postgresql Database\data.
>> I quoted it with double quotes. Without -D postgres.exe complained
>> about not finding the data path, with it postgres.exe complained about
>> not finding the config file, stating that it looked in (from memory
>> vague) F:\Postgresql Database\data\postgres\somethingmore. Adding the
>> --config-file parameter didn't help.
>>
>> Is this enough information for you to start digging a bit more? If
>> not, I might find the exact messages, but I'm reluctant to do it on
>> this production database..
>
> I'm pretty sure the problem is with the space between Postgresql and
> Database. Not sure if it's fixed in later releases or not.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
Part of the problem may be the embedded space, as was already mentioned
(even though it shouldn't be an issue).
A good test would be to use the 8.3 directory naming convention which you
can get on Windows by using the "dir /x" command. On my system,
"C:\Program Files\" is shortened to "C:\PROGRA~1\". Obviously, you'd have
to look at every directory in the fully qualified filename to pull the 8.3
pathname.
And I'll go back to lurking and learning on the list.
Tim
KB0ODU
--
Timothy J. Bruce
visit my Website at: http://www.tbruce.com
Registered Linux User #325725
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-09-19 16:22 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-09-18 11:13 Regaining superuser access Bernt Drange <badrange@gmail.com>
2008-09-18 12:35 ` Glyn Astill <glynastill@yahoo.co.uk>
2008-09-18 16:01 ` Bernt Drange <badrange@gmail.com>
2008-09-18 17:03 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-09-19 08:26 ` Bernt Drange <badrange@gmail.com>
2008-09-19 14:39 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-09-19 16:22 ` Tim Bruce - Postgres <postgres@tbruce.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox