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 <[email protected]> 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@stantest:~$ psql stan postgres -W
> > > Password:
> > > psql: error: could not connect to server: FATAL:  Peer authentication
> > > failed for user "postgres"
> > > stan@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