On Sat, Aug 3, 2013 at 7:16 AM, Alban Hertroys <haramrae@gmail.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 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.
