Re: confusion about user paring with pg_hba and pg_ident

On Wed, Oct 12, 2016 at 3:41 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 10/12/2016 02:07 AM, arnaud gaboury wrote:
>
> >
>
> >
>
> > On Tue, Oct 11, 2016 at 4:20 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
>
> > <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> >
>
> > arnaud gaboury <arnaud(dot)gaboury(at)gmail(dot)com
>
> > <mailto:arnaud(dot)gaboury(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com
>
>