9

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.

Improve this question
3
  • 1
    Do you have a pgpass.conf? postgresql.org/docs/current/static/libpq-pgpass.html
    – user1822
    Feb 24, 2017 at 6:54
  • Can you please show the full pg_hba.conf? I mean all lines that are not commented out.
    – dezso
    Feb 24, 2017 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, 2017 at 15:04

2 Answers 2

Reset to default
7

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
2
  • I did not find this helpful to understanding the credentials issue.
    – Merlin
    May 26, 2021 at 19:04
  • 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.
    – Phill W.
    Jun 8, 2021 at 15:10
1

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 and acknowledge that you have read and understand our privacy policy and code of conduct.

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