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@xxxxxxxxxxx
