How to get psql to prompt for a password with the superuser postgres?

Question

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.

• user1822: Do you have a pgpass.conf? postgresql.org/docs/current/static/libpq-pgpass.html
• András Váczi: Can you please show the full pg_hba.conf? I mean all lines that are not commented out.
• pazeltov: 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.

Answer 1

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!

• Merlin: I did not find this helpful to understanding the credentials issue.
• Phill W.: Look at pg_hba.conf. "Local" connections, on the database server itself, will probably be "trusted". These don't need passwords. Anything coming in from any other "host" will use different authentication methods, commonly MD5. These do need passwords and will prompt users for it. My point is that the /only/ place where this "trust" applies is on your highly secured database server, which "normal users" should never get anywhere near.

Answer 2

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).

• Randall: Can't find the comment you refer to
• Randall: Where did the comment from @a_horse_with_no_name go? I expected it somewhere in this question's or answer's comments.