Thread: confusion about user paring with pg_hba and pg_ident

confusion about user paring with pg_hba and pg_ident

From
arnaud gaboury
Date:
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?

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?

Thank you for help.



Re: confusion about user paring with pg_hba and pg_ident

From
Adrian Klaver
Date:
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


Re: confusion about user paring with pg_hba and pg_ident

From
arnaud gaboury
Date:


On Tue, Oct 11, 2016 at 3:45 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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?

YES I did it this time



>

> Thank you for help.

>

>

>





--

Adrian Klaver

adrian.klaver@aklaver.com

Re: confusion about user paring with pg_hba and pg_ident

From
Adrian Klaver
Date:
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


Re: confusion about user paring with pg_hba and pg_ident

From
Tom Lane
Date:
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


Re: confusion about user paring with pg_hba and pg_ident

From
arnaud gaboury
Date:


On Tue, Oct 11, 2016 at 4:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.

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.

.



                        regards, tom lane

Re: confusion about user paring with pg_hba and pg_ident

From
arnaud gaboury
Date:


On Tue, Oct 11, 2016 at 4:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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

Then, if my files are correct, why can't I connect ?

 % psql --dbname=mattermost --username=mmuser
psql: FATAL:  Peer authentication failed for user "mmuser"

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.


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

Re: confusion about user paring with pg_hba and pg_ident

From
Adrian Klaver
Date:
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


Re: confusion about user paring with pg_hba and pg_ident

From
Adrian Klaver
Date:
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


Re: confusion about user paring with pg_hba and pg_ident

From
arnaud gaboury
Date:


On Wed, Oct 12, 2016 at 3:41 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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?

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 ?

TY for your time.

 




>

>

>                             regards, tom lane

>





--

Adrian Klaver

adrian.klaver@aklaver.com

Re: confusion about user paring with pg_hba and pg_ident

From
Adrian Klaver
Date:
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