Do you want BuboFlash to help you learning these things? Or do you want to add or correct something? Click here to log in or create user.



So. Postgres user handling is Bloody Fucking Weird, and very VERY archaic in some places.

To begin with, Postgres users are not the same users as your computer. Except when you use peer authentication, which is default for "local" connections (in-machine).

With peer authentication, you need to have the same username inside the database as outside. So root cannot connect as postgres into the database.

Postgres also supports other kinds of authentication, for example trust, which basically says yessir, you're ok no matter who you say you are (not very good).

It also supports the ancient and untrustworthy ident protocol, that noone in their sane mind would ever use, and which was designed in the 80s/90s.

Then comes the slightly more modern authentications, md5.

md5 is basically a username & password combination for your database, where both the username and password are stored in the database system. Then it no longer matters who you are connecting as on the computer ( be it root, postgres, joedope).

This is the sane method, and you should use it.

By default, postgres might use it if you give it a hostname of localhost. ( -h localhost).

Then comes the pg_hba.conf file, which is an amazing hairy construct. It's basically a firewall file for your database system. It tells you which users & databases are allowed to connect from which systems, including your local machine.

Then, because ident wasn't stupid enough, they re-envisoned ident as a config file, allowing you to override the username and other combinations in a file on your machine. Don't use it. Really don't.

What you want to do is this:
- Ignore peer authentication for anything but the postgres user.
- Create a new admin user (createuser) with password.
- Add your new admin user to pg_hba.conf like this:

local all mysuperuser md5 
    If you want to change selection, open document below and click on "Move attachment"

    Cannot get into shell using psql command. : PostgreSQL
    Your? Do you enter your shell account password? Do you even have "user" account *in database *? permalink embed save parent give gold [–]Darkmere 1 point2 points3 points 1 year ago (0 children) <span>So. Postgres user handling is Bloody Fucking Weird, and very VERY archaic in some places. To begin with, Postgres users are not the same users as your computer. Except when you use peer authentication, which is default for "local" connections (in-machine). With peer authentication, you need to have the same username inside the database as outside. So root cannot connect as postgres into the database. Postgres also supports other kinds of authentication, for example trust, which basically says yessir, you're ok no matter who you say you are (not very good). It also supports the ancient and untrustworthy ident protocol, that noone in their sane mind would ever use, and which was designed in the 80s/90s. Then comes the slightly more modern authentications, md5. md5 is basically a username & password combination for your database, where both the username and password are stored in the database system. Then it no longer matters who you are connecting as on the computer ( be it root, postgres, joedope). This is the sane method, and you should use it. By default, postgres might use it if you give it a hostname of localhost. ( -h localhost). Then comes the pg_hba.conf file, which is an amazing hairy construct. It's basically a firewall file for your database system. It tells you which users & databases are allowed to connect from which systems, including your local machine. Then, because ident wasn't stupid enough, they re-envisoned ident as a config file, allowing you to override the username and other combinations in a file on your machine. Don't use it. Really don't. What you want to do is this: - Ignore peer authentication for anything but the postgres user. - Create a new admin user (createuser) with password. - Add your new admin user to pg_hba.conf like this: local all mysuperuser md5 permalink embed save give gold about blog about advertising careers help site rules Reddit help center wiki reddiquette mod guidelines contact us apps & tools Reddit for iPhone Redd


    Summary

    statusnot read reprioritisations
    last reprioritisation on suggested re-reading day
    started reading on finished reading on

    Details



    Discussion

    Do you want to join discussion? Click here to log in or create user.