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
