On this page... (hide)

More psql follies

Change/reset postgresql user password on windows 7
how to reset password

Let's say you have trouble logging in to postgres.

  1. Find your installation, and find the pg_hba.conf file inside of the data folder
  2. Change the Method from md5 to trust
  3. Save the file
  4. update the password
    1. psql -U postgres (or whatever the username name is)
    2. ALTER USER postgres with password 'secure-password';
  5. Change the Method from trust back to md5
  6. RESTART THE SERVICE from services.msc
    1. command-line attempts result in:
λ postgres restart
Execution of PostgreSQL by a user with administrative permissions is not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the documentation for
more information on how to properly start the server.

Now, my password update still failed:

λ psql -U postgres
psql (9.4.1)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'password'
postgres-# \q


λ psql -U postgres
Password for user postgres:
psql: FATAL: password authentication failed for user "postgres"
NOTE: I attempted the password update with both single- and double-quotes.

Results the same (auth failure) in both cases.

OKAY I GOT IT TO WORK.

Somehow.

I don't think I did anything differently... perhaps single-quotes with trailing semi-colon?

postgres=# ALTER USER postgres WITH PASSWORD 'password';
ALTER ROLE
postgres=# \q

after that, reset .conf to use md5, reset the service (all steps I had done before), but this time it worked.

I'm thinking it's the semi-colon.....

NOTE: when I couldn't get the password-change to work, I left .conf at trust and connected in-code without a password:
 pgconn: 'postgres://postgres@localhost:5432/postgres'