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.klaver@aklaver.com