I'm using PostgreSQL 9.6.2 on Windows 7 and when I start psql from the command prompt with:

psql -U myusername 

it asks for a password just fine and logs in. However if I do:

psql -U postgres

it's gonna log in directly to the main database with superuser rights without asking for any kind of password. If I type in:

psql -U postgres -W 

then it's gonna ask for the su password which is by the way the one that I set during the one-click installation.

I've checked the pg_hba.conf and it's set to md5 for all users, all databases. I've even changed the password from the db with: postgres=# ALTER USER postgres PASSWORD 'myPassword';, to no avail.

The question is why when I log as a normal user it asks for a password and doesn't when i log as superuser? It's not a major issue as I can access my databases in either case, but it just doesn't seem safe at all. Any workaround would be very much appreciated.

  • 1
    Do you have a pgpass.conf? postgresql.org/docs/current/static/libpq-pgpass.html – a_horse_with_no_name Feb 24 '17 at 6:54
  • Can you please show the full pg_hba.conf? I mean all lines that are not commented out. – dezso Feb 24 '17 at 7:35
  • I had a look at the pgpass.conf and the postgres password was indeed stored there in plain text. I commented the lines out and now psql prompts for password when I login as postgres. – pazeltov Feb 24 '17 at 15:04

why when I log as a normal user it asks for a password and doesn't when i log as superuser?

it just doesn't seem safe at all.

The small but important detail that you're missing is that nobody but you (or your DBA Team) should be allowed anywhere near the database server and absolutely should not have the postgres account credentials. Without these, "logging in as postgres, with or without a password" simply won't be a option for them.

Remember: As the DBA, you'll have to cleanup the "mess" that other people make, so always keep the biggest and best tools for yourself!

| improve this answer | |

As per @a_horse_with_no_name's comment, have a look in your pgpass.conf (.pgpass on Linux) file if you have one, and see if there's an entry for the postgres user:

The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.