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.
Best Answer
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!