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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

.

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

/messages/by-id/CAK1hC9uLhsyn4g8Fc1FwhnDQzNx9k115GkK9iFKHepfjeMc+eg@mail.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

/messages/by-id/CAK1hC9uLhsyn4g8Fc1FwhnDQzNx9k115GkK9iFKHepfjeMc+eg@mail.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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general