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
