Re: Help with authentication on Debain/Ubuntu installation

On Thu, Nov 21, 2019 at 09:15:02AM -0500, stan wrote:
> On Thu, Nov 21, 2019 at 02:05:09PM +0100, Magnus Hagander wrote:
> > On Thu, Nov 21, 2019 at 1:46 PM stan <stanb(at)panix(dot)com> wrote:
> >
> > > I am trying to set up to do some work with pg_dump, and I would like to be
> > > able to connect from my normal user to do this. This is on a Ubunt 18.04
> > > installation. I have added the follwing to pg_hba.conf:
> > >
> > > host all all 0.0.0.0/0 md5
> > > hostssl all all 0.0.0.0/0 md5
> > >
> > > But when I try to login like this:
> > >
> > > psql stan postgres -W
> > >
> > > I get the following error, and yes, I set postgres'es password to something
> > > I know:
> > >
> > > tan(at)stantest:~$ psql stan postgres -W
> > > Password:
> > > psql: error: could not connect to server: FATAL: Peer authentication
> > > failed for user "postgres"
> > > stan(at)stantest:~$
> > >
> > > What am I doing wrong?
> > >
> > >
> > By default, Debian has a row in pg_hba.conf that enforces peer specifically
> > for the postgres superuser when connecting over unix sockets (which is a
> > very good thing from a security perspective). You will have to find that
> > row further up in the file and comment it out (since the first row that
> > matches will be used).
>
> Thanks, found it, I believe:
>
> local all postgres
>
> Good to know first match wins, that is different than some other systems I
> am used to.

OK, there appears to be a bit more than I thought to this. Here is the line I
believe is causing me issues:

# Database administrative login by Unix domain socket
local all postgres peer

There is a comment saying that removing this is a bad idea, so ...

If the 1st rule matches, I am thinking this will override any rule I put in
after such as:

local all postgres md5

Is my understanding of this correct?

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin