Re: user name and password woes

Charles Mortell wrote:
> The dawg that originally set up postgres 8.0 on one of our Windows 2003
> servers has left the company, and I have taken it over.
> I need to use pg_dump on that server. When I try to, I get ‘user
> "Administrator" does not exist.’
> Next I try ‘createuser –U postgres –W administrator –P’. The response is
> ‘ERROR: user "administrator" already exists.’

Hmm - is it the capital "A" here? I notice the second attempt has "a".

Anyway, pgAdmin can display roles/users, and if not "SELECT * FROM
pg_roles" will do so.

> I know PG requires a user with the same name as the Windows user and that
> should be ‘administrator’ on this server.

No, the database users are completely separate from OS users. By
convention, "postgres" is the OS user of the process and superuser for
the DB, but you could change it if you wanted to.

> Oddly I can connect to PG on this server through PGAdmin (through
> postgres user?).

OK, so you've either got the postgres user password, or none set. That
should let you do everything you want - dump, restore, create users,
reset passwords etc.

> Ultimately I want to create a database with a new name from
> an existing database; I’ve done this from the command line on other servers.
> Can’t, or don’t know how to, do it through PGAdmin.

Well, with SQL you can do CREATE DATABASE <newdb> TEMPLATE=<olddb>
You should be able to run that from PgAdmin, but there's probably a
dialogue-box that will do it too.

> I’ve also tried to delete and recreate the administrator user from PGAdmin
> but I still can’t use pg_dump.

Use postgres as the user for the moment. Once you've figured out which
users you have use ALTER USER or ALTER ROLE to reset their passwords.
See manual for syntax.

> Tried all the passwords my colleagues can think of. I’ve searched the
> forums and tried various things like setting pg_hba.conf to trust
> temporarily and restarting.
> Any suggestions greatly appreciated! Thanks in advance.

Oh, there's a file called ".pgpass" that you can use to hold passwords
on unix-like systems. I think it's called "pgpass.txt" on Windows but
check the manuals for details. It will be readable only by the OS user
that owns it.

--
Richard Huxton
Archonet Ltd