Re: Help with authentication on Debain/Ubuntu installation

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.

2) There are the system users, postgres, stan for instance.

3) Even though they have the same username they are not the same user.

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.

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.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com