Обсуждение: confusion about user paring with pg_hba and pg_ident

On 10/11/2016 04:25 AM, arnaud gaboury wrote:
> I am a little confused about some of my settings when it comes to map
> linux/psql users.
>
> I have two databases: mattermost and thetradinghall
> I have two linux users: mattermost and dovecot. mattermost  is the one
> who want to talk to mattermost and dovecot  the one who want to talk to
> thetradinghall.
> I have two postresql users: mmuser and mailman.
>
> Here are the relevant part of my config files:
>
> pg_hba.conf
> -------------------
>  local   thetradinghall      mailman                     peer
> map=mailmap
>  local   mattermost          mmuser                      peer
> map=mattermap
>
> pg_ident.conf
> ----------------------
>   mailmap        dovecot                 mailman
>   mattermap      mattermost              mmuser
>
> * question: can I use same mapname for my both DB, or using two mapnames
> like I did is the correct way?

Yes:

https://www.postgresql.org/docs/9.5/static/auth-username-maps.html

"Since different mappings might be needed for different connections, the
name of the map to be used is specified in the map-name parameter in
pg_hba.conf to indicate which map to use for each individual connection."


>
> Now testing:
> -----------------------------
> bash-4.3$ whoami
> mattermost
> bash-4.3$ psql postgres:///mattermost?
> psql: FATAL:  no pg_hba.conf entry for host "[local]", user
> "mattermost", database "mattermost", SSL off
> -----------------------------------
>
> I thought my settings told postgres that linux user mattermost was
> mapped by psql user mmuser (which of course holds the mattermost DB),
> but it seems it is not the case.
> What do I do wrong?

Did you remember to reload Postgres?

>
> Thank you for help.
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 10/11/2016 06:47 AM, arnaud gaboury wrote:
>
>

>
>
>
>     Did you remember to reload Postgres?
>
>
> YES I did it this time

My mistake, forget to look at your connection string:

psql postgres:///mattermost

You have not told Postgres what user you want mattermost to connect as.
It worked for you before, because you had this:

psql postgresql://mmuser:XXXYYYY@/mattermost?


>
>
>
>
>     >
>
>     > Thank you for help.
>
>     >
>
>     >
>
>     >
>
>
>
>
>
>     --
>
>     Adrian Klaver
>
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

arnaud gaboury <arnaud.gaboury@gmail.com> writes:
> I am a little confused about some of my settings when it comes to map
> linux/psql users.

I think you're misunderstanding what the user-mapping stuff does.
It does not silently translate the username in the connection request
to something else; rather, it checks whether a user having the given
external name is allowed to log in as a particular Postgres user.
So there's nothing particularly wrong with your config files, but your
expectation about how your Linux users should log in to the database is
mistaken.  dovecot needs to specify that it wants to log in as mailman,
and likewise mattermost needs to specify mmuser.

If it's not practical to make the client applications send non-default
user names, you'll need to rename the Postgres roles to match the
external user names.

            regards, tom lane

On 10/12/2016 01:30 AM, arnaud gaboury wrote:
>
>
> On Tue, Oct 11, 2016 at 4:20 PM Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
>     arnaud gaboury <arnaud.gaboury@gmail.com
>     <mailto:arnaud.gaboury@gmail.com>> writes:
>
>     > I am a little confused about some of my settings when it comes to map
>
>     > linux/psql users.
>
>
>
>     I think you're misunderstanding what the user-mapping stuff does.
>
>     It does not silently translate the username in the connection request
>
>     to something else; rather, it checks whether a user having the given
>
>     external name is allowed to log in as a particular Postgres user.
>
>     So there's nothing particularly wrong with your config files, but your
>
>     expectation about how your Linux users should log in to the database is
>
>     mistaken.  dovecot needs to specify that it wants to log in as mailman,
>
>     and likewise mattermost needs to specify mmuser.
>
>
>
>     If it's not practical to make the client applications send non-default
>
>     user names, you'll need to rename the Postgres roles to match the
>     external user names.
>
>
> Tom,
>
> thank you for this point. I am not sure to understand the last part:
> <rename the Postgres roles to match the external user names>. Do you
> mean best would be for dovecot to log in as dovecot Postgres user, and
> mattermost as mattermost Postgres user ?
> Thank you for precising.

Yes, that was Tom was getting at. Create dovecot and mattermost
roles(users) in Postgres.

>
> .
>
>
>
>
>                             regards, tom lane
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 10/12/2016 02:07 AM, arnaud gaboury wrote:
>
>
> On Tue, Oct 11, 2016 at 4:20 PM Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
>     arnaud gaboury <arnaud.gaboury@gmail.com
>     <mailto:arnaud.gaboury@gmail.com>> writes:
>
>     > I am a little confused about some of my settings when it comes to map
>
>     > linux/psql users.
>
>
>
>     I think you're misunderstanding what the user-mapping stuff does.
>
>     It does not silently translate the username in the connection request
>
>     to something else; rather, it checks whether a user having the given
>
>     external name is allowed to log in as a particular Postgres user.
>
>     So there's nothing particularly wrong with your config files, but your
>
>
> Then, if my files are correct, why can't I connect ?
>
>  % psql --dbname=mattermost --username=mmuser
> psql: FATAL:  Peer authentication failed for user "mmuser"

What system user are you doing the above as?

>
> login with postgres is OK (pg_hba.conf settings: local
> all                 postgres       trust):
>  % psql --dbname=mattermost --username=postgres
> psql (9.5.4)
> Type "help" for help.
>
> mattermost=# \q
>
> I can't see why I can't connect as Postgresuser mmuser.
>
>

Common issues:

1) You have more then one Postgres cluster and you are not connecting to
the one you think you are.

2) pg_hba.conf works on first match wins, so you have another line that
matches the criteria but is not pointing at the correct map.


You had it working here:

https://www.postgresql.org/message-id/CAK1hC9uLhsyn4g8Fc1FwhnDQzNx9k115GkK9iFKHepfjeMc%2Beg%40mail.gmail.com

So other then adding the mapping for the dovecot user, did anything else
change?


>
>
>                             regards, tom lane
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 10/12/2016 08:57 AM, arnaud gaboury wrote:
>
>
> On Wed, Oct 12, 2016 at 3:41 PM Adrian Klaver <adrian.klaver@aklaver.com

>
>
>
>     So other then adding the mapping for the dovecot user, did anything else
>
>     change?
>
> after a little bit of cleaning and a change in my Postgres username (now
> postgres username == unix user), the various commands to connect are
> working.
>
> Now I want to be sure to have correctly understood the mapping story.
> Say root is running myApp, and at one point, myApp is poling a
> postgresql DB as user myUser.
> Run myApp as root:
> # myApp
>
> Do I have to add an entry in pg_ident to map linux user root to Postgres
> myUser ? Or the command above will be enough with no entries in pg_ident
> or pg_hba ?


The answer depends on what result you are trying to achieve.

Are you trying to restrict access to a database by Postgres user only,
regardless of who they logged into the system as?

Or do you want to restrict access based on their system user login as well?

If not the above, what are your criteria for determining who can log in
to the database?

>
> TY for your time.
>

--
Adrian Klaver
adrian.klaver@aklaver.com