Re: Help with authentication on Debain/Ubuntu installation

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