Re: Help with authentication on Debain/Ubuntu installation

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(dot)klaver(at)aklaver(dot)com