Re: Lost password to user postgres

2 years ago, I installed postgresql on my desktop and used it for a while for learning purposes. Then, I stopped using it. I forgot the password to the user postgres

Now, I am doing a lot with Microsoft Access, and participate a lot at utteraccess.com

Last night I installed MySQL, and am using it for some learning exercises. I would very much like to connect to my existing installation of PostgresQL, but I have lost the password. One of the admin programs is still working, and connects to the server, but of course the password is concealed.

HERE IS WHAT I WANT TO DO. I dont care about the old data in the server. I just want to have total access to it again as admin. I DO NOT want to uninstall and reinstall, if possible. Is there ANY WAY to recover my password, or reset the server, without going through an uninstall/reinstall process.

IF it is the case that I MUST uninstall and then reinstall, are there any caveats.

I saw one post in google from someone who lost his password, and had all sorts of hassels uninstalling and reinstalling.

Also, if it is the case that I must reinstall, should I use the install download that I still have from 2 years ago. I remember going through SHEAR TORTURE plowing through all the websites, with all the obscure technical jargon, to find the proper windows install to download. Perhaps things have improved in two years. The MySQL install was effortless, up and working in 30 minutes, with no reference to instructions or help.

Anyway, I would appreciate your advice.

If you are Access users, hope you can stop by http://utteraccess.com some time and say hello.

Thanks

---------------------------------
Want to start your own business? Learn how on Yahoo! Small Business.

On Sat, 2006-12-02 at 11:44 -0800, Philippe Salama wrote:
> 2 years ago, I installed postgresql on my desktop and used it for a
> while for learning purposes. Then, I stopped using it. I forgot the
> password to the user postgres

You don't have to reinstall. you've got three basic options:

1. Change the password in single user mode
2. Edit pg_hba.conf and set it to trust mode, change your password,
change back to md5
3. Re-initdb your cluster.

For 1:
su - postgres (from root if you have to)
pg_ctl stop
postgres template1 (or some other database you know exists)
alter user postgres password 'newpassword';

For 2:
su - postgres
cd $PGDATA
vi pg_hba.conf
// change entries at bottom to trust
pg_ctl reload
psql template1
alter user postgres password 'newpassword';

For 3:
su - postgres
pg_ctl stop
echo $PGDATA // make sure this is set to something like /var/lib/pgsql
rm -rf $PGDATA/*
initdb // with whatever options you need.

I prefer option 2, as you don't have to take down your database to do
it.

Regarding point 1, Scott, the user is on Windows.

Windows binaries are available at www.postgresql.org and a lot has
changed in the last 2 years, so if you do go down the re-install route,
I'd recommend upgrading, although you'll have to dump your old data out
of the server first, then restore it in to the new version.

Regarding passwords, are you talking about the service account for the
PostgreSQL database, or the postgres user in the database server?
You can change the service account using the "Local Users & Groups"
control panel (run "lusrmgr.msc") but you'll also need to change the
logon user in the Services control panel for the PostgreSQL Database
Server service.

If you're talking about the postgres user, edit your pg_hba.conf file
(Start > Programs > PostgreSQL [version] > Edit pg_hba.conf (or
there-abouts.)
Add (or change if it already exists) a line (if it doesn't exist, add it
before any other "host...." lines)

host all all 127.0.0.1/32 trust

And restart the PostgreSQL service.
Then when you connect to 'localhost', you'll always be granted access.
Login, change the postgres user password, then edit your pg_hba.conf
file again, to:

host all all 127.0.0.1/32 md5

And restart the service, then you should be able to login with that
password.

Andy.

Scott Marlowe wrote:
> On Sat, 2006-12-02 at 11:44 -0800, Philippe Salama wrote:
>
>> 2 years ago, I installed postgresql on my desktop and used it for a
>> while for learning purposes. Then, I stopped using it. I forgot the
>> password to the user postgres
>>
>
> You don't have to reinstall. you've got three basic options:
>
> 1. Change the password in single user mode
> 2. Edit pg_hba.conf and set it to trust mode, change your password,
> change back to md5
> 3. Re-initdb your cluster.
>
> For 1:
> su - postgres (from root if you have to)
> pg_ctl stop
> postgres template1 (or some other database you know exists)
> alter user postgres password 'newpassword';
>
> For 2:
> su - postgres
> cd $PGDATA
> vi pg_hba.conf
> // change entries at bottom to trust
> pg_ctl reload
> psql template1
> alter user postgres password 'newpassword';
>
> For 3:
> su - postgres
> pg_ctl stop
> echo $PGDATA // make sure this is set to something like /var/lib/pgsql
> rm -rf $PGDATA/*
> initdb // with whatever options you need.
>
> I prefer option 2, as you don't have to take down your database to do
> it.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
> !DSPAM:37,45720b9840411059761709!
>
>
>

--
Andy Shellam
NetServe Support Team

the Mail Network
"an alternative in a standardised world"

p: +44 (0) 121 288 0832/0839
m: +44 (0) 7818 000834