0

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.

migrated from stackoverflow.com Aug 18 at 21:06

This question came from our site for professional and enthusiast programmers.

1

When trying to connect via pgadminIII i am prompted for a password when using the same user name.

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.

Additionally, I would like to make my own user name an admin and use my linux password

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.

However when atempting to connect form command line it acts like there should also be a database named the same as my user.

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.

1

Users, roles, and permissions can get kind of confusing in PostgreSQL. To control who can try to connect to the database, Postgres uses the pg_hba.conf file. In Ubuntu, it's buried in /etc/postgres/<#>/main.

From inside the data base there are roles which control permissions each user has inside the database.

An easy, but probably insecure way to connect is to set up trust from localhost connections in pg_hba.conf.

0

"windows authentication, microsoft sql != postgres, linux"

Postgres manages permissions through the concept of "roles". Roles are different from traditional Unix-style permissions in that there is no distinction between users and groups. Roles can be manipulated to resemble both of these conventions, but they are also more flexible...

Please see following post/answer -> Can someone explain how Postgresql roles, Postgresql users and Linux users relate to each other?

Use the following as your guide (steps 3,4,5) -> https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04

If you would like to authenticate with password you will need to change postgres Authentication Method from Ident or Trust to MD5 by editing the pg_hba.conf file

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.