Re: postgresql db account

Having run into the problem of setting the superuser
password and then several months later forgetting what
I set it to, I have found myself having to reset the
password many a time.

To do this on a Linux system:
1. Switch User to 'root' using the 'su -' command.
2. Edit the pg_hba.conf file (on Fedora systems with
RPM install, this is found in /var/lib/pgsql/data. Use
the 'find' command to find the file on your system.)
At the end of the file you'll find the line similar
to:
local sameuser all md5
After the last 'local' entry, add a fairly permissive
entry for 'template1' thusly:
local postgres template1 ident sameuser
This entry will allow the user 'postgres' (and only
the 'postgres' user) to connect to the template1
database without a password when logged in as the
system user 'postgres'.
3. While still logged in as 'root', restart the
PostgreSQL service. This is system dependent, but on
Fedora systems, the Postmaster can be restarted
through the init script:
/etc/init.d/postgresql restart
4. Switch user from root to postgres:
su - postgres
(the dash is important, don't leave it out.)
5. Connect to template1 database as postgres user:
psql -d template1
6. Once connected to template1, change the password
for the postgres user:
ALTER USER postgres WITH PASSWORD 'IForgotAgain';
7. Exit psql:
\q
8. log out postgres user:
exit
9. log out root user:
exit
10. connect to your original database (I'll assume
'foo' in this example):
psql -U postgres -h localhost -d foo

In order to log in to foo this way, you'll need to
have an entry in the above mentioned pg_hba.conf file
similar to:
host all all 127.0.0.1/32 md5

As a side note:
If you want to have a web app on another machine in
your local network be able to access any database, try
this:
host all all 192.168.0.0/16 md5

This will allow any machine within the local
192.168.x.x range to connect to any database as long
as the correct database user and password are
provided.

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ray Stell <stellr(at)cns(dot)vt(dot)edu> writes:
> > Thanks, again, someone said there was no pw on the
> account, but if I
> > set local md5 I am prompted for a pw. Enter
> doesn't let you in. Is
> > the default known?
>
> Yes: there isn't one. If you want to use pw
> authentication, you have to
> first set a password for the superuser account, then
> change the auth
> method. This is why pw auth is not the initial
> default.
>
> (Actually, if you do a manual initdb, you can
> specify a password at that
> time and choose pw auth as the default auth method
> too. But this
> approach is discouraged by most pre-packaged
> installations.)
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com