Help with authentication on Debain/Ubuntu installation

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).

//Magnus

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