Re: Re: Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!! (nearly fixed)

On Sat, Aug 3, 2013 at 7:16 AM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:

> They are cluster specific, as the roles are stored in the database. If you
> switch between different data directories, that means you're switching the
> available roles as well. And their details, such as passwords. You're also
> switching between configurations, such as pg_hba.conf.
>
> Where it gets confusing a bit here is that there's usually also an OS
> postgres user, but that user is used to run the postgres server/service
> with limited credentials and not to log into the database. On Windows,
> apparently postgres is run under a standard network service account
> instead, which serves the same purpose.
>
> Now, if you connect to the database without specifying a role-name to
> connect as, the standard tools (psql, pg_dump, etc. Don't know about
> pgAdmin) take your user account name and try to use that for the database
> login role, making it look like there's a relation between OS users and
> database users, but that's not actually the case (although there's an
> authentication option in pg_hba.conf to require such a relation).
>

Sounds about right. I can definitely confirm that it's run as NETWORK
SERVICE and that psql, by default, tries to connect as the current username.

Anyhow, I'm betting you will need to reset the password in your existing
data directory, Mr. Brearly. First of all, make sure you have an entire
back up of the data directory you want to get data from. (Hopefully, you
have this already from before you were making all these changes.) Then
start PostgreSQL with the data directory with your data. (If this is what
you've configured the service to do within the registry, that's fine.)
Next, follow the instructions in the FAQ:
http://wiki.postgresql.org/wiki/FAQ#I_lost_the_database_password._What_can_I_do_to_recover_it.3F.
(Also see this DBA StackExchange question for some Windows specific advice
on reloading the config:
http://dba.stackexchange.com/questions/19643/how-do-i-reset-the-postgres-password-for-postgresql-on-windows.)
See the documentation<http://www.postgresql.org/docs/9.2/static/auth-pg-hba-conf.html>on
configuring the pg_hba.conf file; this file is inside your data
directory somewhere. I suspect the best way to do this is to add a line of
the form "local *database user auth-method*" with *auth-method* as
trust, of course. (Could someone confirm?) Also, do your password reset
from the command line using psql; this eliminates some complexities and
uncertainty regarding PgAdmin in case it doesn't work.

If this works as expected, you'll be able to access your data. Good luck.