Full disclosure, new to postgres and new to linux. I am having trouble understanding the user management side of postgresql. I installed on a Ubuntu 19.04 based os using
sudo apt update
sudo apt install postgresql postgresql-contrib
Ive been able to switch to the default user postgres and can successfully call psql amongst other commands. When trying to connect via pgadminIII i am prompted for a password when using the same user name. A couple searches reveal that you should be able to leave this blank. I get an error stating no password supplied.
Additionally, I would like to make my own user name an admin and use my linux password. I suppose this would be similiar to windows authentiaction on microsoft sql. After searching the internet it appeared that all i need to do is createuser *MyNameHere* and declare it a superuser. However when atempting to connect form command line it acts like there should also be a database named the same as my user. Secondly when using the pgadmin it actively rejects my credentials.
I guess my question is this:
Can someone explain to me the basic concepts of roles/usernames and how they relate to the linux user names. And secondly, what is going wrong in the above.
Best Answer
pgAdminIII is probably connecting over TCP (as opposed to unix domain sockets) and so is subject to the rules of a 'host*' line from pg_hba.conf, rather than a 'local' line. If that line says a password is needed, then it will ask for a password--even if no password is actually assigned to the user. There should be a message in the server log describing the situation in more detail. The details are not sent to the (failed) client, to avoid leaking sensitive information.
It sounds like you might want PAM authentication (or maybe GSSAPI or LDAP). But to use those, you have to change your linux log-on system so that it also uses one of them, as I don't think any of them are configured to do that by default. You can also use peer authentication, which doesn't require your password explicitly, it just uses the fact that you are logged on as the local Linux user (which presumably did require the password) to authenticate to PostgreSQL. Or you could take the easy way out, and just set your PostgreSQL password to be the same thing as your linux password, and then manually keep them in sync.
Yes, that is what happens if you don't specify a database name--it assumes you want to connect to a database with the same name as the user. If you want to connect to a different database, then just specify it.