Thread: Help with authentication on Debain/Ubuntu installation

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?


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

On Thu, Nov 21, 2019 at 02:05:09PM +0100, Magnus Hagander wrote:
> On Thu, Nov 21, 2019 at 1:46 PM stan <stanb@panix.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@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.

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

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@panix.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@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

On 11/21/19 6:35 AM, stan wrote:
> 

>> 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?

Yes.

The question is whether you really need a password connection to a local 
socket or not?

If so then you will need to make the change and deal with any scripts 
that are expecting a password free socket connection.

If not leave it alone. If you want to test password connection then use 
a -h localhost to get a host connection.


> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 11/21/19 8:12 AM, stan wrote:
Please reply to list also
Ccing list.
> On Thu, Nov 21, 2019 at 07:56:10AM -0800, Adrian Klaver wrote:
>> On 11/21/19 6:35 AM, stan wrote:

>>> 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?
>>
>> Yes.
>>
>> The question is whether you really need a password connection to a local
>> socket or not?
>>
>> If so then you will need to make the change and deal with any scripts that
>> are expecting a password free socket connection.
>>
>> If not leave it alone. If you want to test password connection then use a -h
>> localhost to get a host connection.
>>
>>
> 
> Ugly. Thanks though I think I can skin this cat with a dfferent tool. I am
> going to try setting te needed users as the posgres db superuser.

I would avoid that if at all possible, a superuser can wreak a lot of 
havoc intentionally or unintentionally.

It would help if you could spell out what you want to achieve, as I am 
sure it has been done before by multiple people on this list.

> 
> Thnaks.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Nov 21, 2019 at 08:18:08AM -0800, Adrian Klaver wrote:
> On 11/21/19 8:12 AM, stan wrote:
> Please reply to list also
> Ccing list.
> > On Thu, Nov 21, 2019 at 07:56:10AM -0800, Adrian Klaver wrote:
> > > On 11/21/19 6:35 AM, stan wrote:
> 
> > > > 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?
> > > 
> > > Yes.
> > > 
> > > The question is whether you really need a password connection to a local
> > > socket or not?
> > > 
> > > If so then you will need to make the change and deal with any scripts that
> > > are expecting a password free socket connection.
> > > 
> > > If not leave it alone. If you want to test password connection then use a -h
> > > localhost to get a host connection.
> > > 
> > > 
> > 
> > Ugly. Thanks though I think I can skin this cat with a dfferent tool. I am
> > going to try setting te needed users as the posgres db superuser.
> 
> I would avoid that if at all possible, a superuser can wreak a lot of havoc
> intentionally or unintentionally.
> 
> It would help if you could spell out what you want to achieve, as I am sure
> it has been done before by multiple people on this list.
> 

Can do.

At the moment, I have 2 instances one production and one sandbox. I want to
be  able to run pg_dump -d "production", take the resultant file to the
sandbox and recreate a duplicate instance to do testing against. I would
prefer that this not involve changing UNIX users to the postgres user, just
to do this.
-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

On 11/21/19 11:09 AM, stan wrote:
> 

>> It would help if you could spell out what you want to achieve, as I am sure
>> it has been done before by multiple people on this list.
>>
> 
> Can do.
> 
> At the moment, I have 2 instances one production and one sandbox. I want to
> be  able to run pg_dump -d "production", take the resultant file to the
> sandbox and recreate a duplicate instance to do testing against. I would
> prefer that this not involve changing UNIX users to the postgres user, just
> to do this.

You don't. Seems to be my day to go through this:):

1) There are the Postgres database users, postgres, stan for instance.

2) There are the system users, postgres, stan for instance.

3) Even though they have the same username they are not the same user.

4) When you run pg_dump/pg_restore -U points to the database user not 
the system user. So any user can run pg_dump -d some_db -U postgres or 
the pg_restore version. If pg_hba.conf requires a password then they 
will need to know that, though it also possible to use .pgpass or env 
variables to work around that.

5) Now in your case you have peer auth(first in the list) for local 
socket connections which means a user can only connect on the local 
socket as the db postgres user if they are also the os postgres user.
You can work around that by having other users connect to the database 
using a -h(host) connection that requires a password.



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Nov 21, 2019 at 12:14:16PM -0800, Adrian Klaver wrote:
> On 11/21/19 11:09 AM, stan wrote:
> > 
> 
> > > It would help if you could spell out what you want to achieve, as I am sure
> > > it has been done before by multiple people on this list.
> > > 
> > 
> > Can do.
> > 
> > At the moment, I have 2 instances one production and one sandbox. I want to
> > be  able to run pg_dump -d "production", take the resultant file to the
> > sandbox and recreate a duplicate instance to do testing against. I would
> > prefer that this not involve changing UNIX users to the postgres user, just
> > to do this.
> 
> You don't. Seems to be my day to go through this:):
> 
> 1) There are the Postgres database users, postgres, stan for instance.

Right, I knew that.
> 
> 2) There are the system users, postgres, stan for instance.

Same.
> 
> 3) Even though they have the same username they are not the same user.
Yes.
> 
> 4) When you run pg_dump/pg_restore -U points to the database user not the
> system user. So any user can run pg_dump -d some_db -U postgres or the
> pg_restore version. If pg_hba.conf requires a password then they will need
> to know that, though it also possible to use .pgpass or env variables to
> work around that.

Understodd.
> 
> 5) Now in your case you have peer auth(first in the list) for local socket
> connections which means a user can only connect on the local socket as the
> db postgres user if they are also the os postgres user.
> You can work around that by having other users connect to the database using
> a -h(host) connection that requires a password.

So, to implement it this way. I would

* set teh postgres database user password to one I know.
* run tasks that need posgres superuser access with -h
<external_ip_address> -U postgress -W

* enter the password.

Correct?

I should  have thought of this technique, I have used it in the past when I
had issues with pg_hba.conf file. Something abou IPV6 entries, or lack of
them ?

Thanks for the help.

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

On 11/21/19 1:52 PM, stan wrote:
> 
> On Thu, Nov 21, 2019 at 12:14:16PM -0800, Adrian Klaver wrote:

>> 5) Now in your case you have peer auth(first in the list) for local socket
>> connections which means a user can only connect on the local socket as the
>> db postgres user if they are also the os postgres user.
>> You can work around that by having other users connect to the database using
>> a -h(host) connection that requires a password.
> 

I will answer your questions below, but I would suggest you spend some 
time going over:

https://www.postgresql.org/docs/11/auth-pg-hba-conf.html

It will answer a lot of questions.

> So, to implement it this way. I would
> 
> * set teh postgres database user password to one I know.
> * run tasks that need posgres superuser access with -h
> <external_ip_address> -U postgress -W
> 
> * enter the password.
> 
> Correct?

Yes and no.

If you are running tasks as the OS user postgres and connecting to the 
local socket(no -h) then this:

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

will apply and you will not need a password.

If you are not running as the OS user postgres or are not using the 
local socket, then another connection line in pg_hba.conf will come into 
play. In that case you probably want something like:

host   all             postgres                                md5

In the above case you can connect as an OS user other then 
postgres(using -U postgres) and you will be need to supply a password. 
Also if you connect as OS user postgres(using -h) then you will a 
password. The password can be supplied manually or it can come from .pgpass:

https://www.postgresql.org/docs/11/libpq-pgpass.html

or an env variable:

https://www.postgresql.org/docs/11/libpq-envars.html


> 
> I should  have thought of this technique, I have used it in the past when I
> had issues with pg_hba.conf file. Something abou IPV6 entries, or lack of
> them ?

Possibly, you can force a IPV4 connection by doing for instance:

-h 127.0.0.1 instead of -h localhost

in the case where localhost is pointing to ::1

> 
> Thanks for the help.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com