40

I have just install Postgres 9.3 on Windows 7. The installation completed successfully. It has never asked me to provide the password for postgres user.

The service postgresql-x64-9.3 is up and running. However, I cannot connect: I do not not know the password. I've found the following answer, but it did not help:

similar question on Ubuntu

Community
  • 1
  • 1
AlexC
  • 2,143
  • 3
  • 21
  • 33

3 Answers3

54

WARNING: trust means exactly that. Anyone who can connect to the PostgreSQL server can control it. If you set trust mode that allows superusers like user postgres (or all users) to connect, they get total control of your PostgreSQL and can probably run shell commands too. You should usually only use it to change the password then restore the configuration back to the auth mode you were using before.

If you used an unattended installer script, the password will be in the script or associated config file.

Otherwise, treat it the same as if you lost/forgot the password rather than never knowing it:

  • Edit pg_hba.conf, setting the auth mode to trust instead of the default md5
  • In the Services control panel restart the PostgreSQL service
  • Connect with psql or PgAdmin or whatever
  • ALTER USER postgres PASSWORD 'mynewpassword';
  • Edit pg_hba.conf again and set the auth mode back to md5
  • Restart PostgreSQL again

pg_hba.conf is in your data directory. By default it'll be %PROGRAMFILES%\PostgreSQL\9.3\data.

To edit it you'll have to use the security tab to give yourself read/write permissions (via a UAC prompt). This might require you to set yourself as the owner of the file.

On unix systems it's more secure to prepend a

local all all peer

line to pg_hba.conf and then sudo -u postgres psql (assuming your PostgreSQL server runs as user postgres) to get an interactive psql session without using a password. That way you don't have to use trust.

Craig Ringer
  • 259,831
  • 56
  • 584
  • 684
  • This doesn't work anymore on newer versions of PostgreSQL. I'm running PostgreSQL 11 on MacOS, the auth mode in `pg_hba.conf` is `trust`, but still I get the authentication error. – ataravati Nov 27 '19 at 18:20
  • 1
    @ataravati Yes, it does. Possibilities: You have edited the wrong `pg_hba.conf` (you have more than one postgres data dir). You forgot to reload the PostgreSQL configuration or restart the PostgreSQL server after editing the config file. There are lines before the entry/entries you edited/added that match so your `trust` entries never get matched. You are connecting over unix socket (`local`) or TCP/IP (`host`) mode, and only edited the config for the other auth mode, try the other connection mode and check both are edited. – Craig Ringer Dec 06 '19 at 04:13
2

Through trial and error I found that the password for Postgre SQL 10 for the username postgres is "admin". I kept typing in different password until I reached that password. I am using pgAdmin 4 to test out my SQL Statements, POSTGRE SQL 10 is the first server connection set up using localhost.

Demetre Phipps
  • 59
  • 3
0

go to control >> computer management >> Locaol users and group >> users >> right click on openpgsvc >> set password. after that now you can access with this password on openpgsvc

lalit bhadouria
  • 47
  • 1
  • 14