Thread: user name and password woes

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.’
   I know PG requires a user with the same name as the Windows user and that
should be ‘administrator’ on this server.
   Oddly I can connect to PG on this server through PGAdmin (through
postgres user?). 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.
    I’ve also tried to delete and recreate the administrator user from PGAdmin
but I still can’t use pg_dump.
   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.

Charles Mortell
Advanced Planning Technologies, Inc.
Mississippi River port of La Crosse, WI
cmortell at apt-cafm dot com

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

Richard Huxton wrote:
> 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".
>
> ...
>
> 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.
>

I've a feeling that Charles is invoking pg_dump without specifying the
user. The default will assume the current *OS* user. Thus,
"Administrator" and the "does not exist" error. Try doing:

pg_dump -S administrator ...

(using the postgres-specific administrator user).

brian