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

Thanks Blade-of-Light (mysterious person)

As you’ll see from my later post, I managed to fix things, largely by understanding that there was a Postgres service I needed to stop before I could make the last change I needed. However, there is a lot of useful stuff in your post, which I will try for another time, should I manage to get stuck again!

Also clarifies some other issues.

Cheers,

Stephen

From: BladeOfLight16 [mailto:bladeoflight16(at)gmail(dot)com]
Sent: 05 August 2013 03:10
To: Alban Hertroys
Cc: Adrian Klaver; Stephen Brearley; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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.