Thread: Postgres user password
Hello,
I have setup my postgres server with postgres as a super user with password. Everytimg when i connect to postgress as super user i have to login with password. My question is how can I automate the scripts for back up and restore that will require password while taking backup or doing restore. We do not want to store the password in the script due to security concerns. Is there a way to do ?
Hi,
can you check if you can use the pgpass file.
Thanks,
Chiru
On Wed, Feb 21, 2018 at 9:27 PM, Azimuddin Mohammed <azimeiu@gmail.com> wrote:
Hello,I have setup my postgres server with postgres as a super user with password. Everytimg when i connect to postgress as super user i have to login with password. My question is how can I automate the scripts for back up and restore that will require password while taking backup or doing restore. We do not want to store the password in the script due to security concerns. Is there a way to do ?
There are some possible solutions:
- .pgpass file, which permissions can be changed so only owner can read it
- PGPASS environment variable. Don't discard it right away, because don't have to set this variable pn the script itself; you can set it into your .bash_profile, and change its permissions so only owner can read it. You can even use some openssl commands so that it is never stored in clear text.
- Playing a bit with pg_hba.conf . You can use "trust" authentication for UNIX socket connections, or create a user intended only for backup(read only access) with "trust" authentication for the specific database for UNIX socket or localhost connections. With this, you won't have to use a password.
- Further on the pg_hba.conf tricks, you can create a postgres user with the same name as the linux user running your tasks, and add a line for such user with "ident" authentication, so the user will not require password only if the task is running as such linux user and using UNIX socket connection.
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
Sent from my Sony Xperia™ smartphone
---- Azimuddin Mohammed wrote ----
- .pgpass file, which permissions can be changed so only owner can read it
- PGPASS environment variable. Don't discard it right away, because don't have to set this variable pn the script itself; you can set it into your .bash_profile, and change its permissions so only owner can read it. You can even use some openssl commands so that it is never stored in clear text.
- Playing a bit with pg_hba.conf . You can use "trust" authentication for UNIX socket connections, or create a user intended only for backup(read only access) with "trust" authentication for the specific database for UNIX socket or localhost connections. With this, you won't have to use a password.
- Further on the pg_hba.conf tricks, you can create a postgres user with the same name as the linux user running your tasks, and add a line for such user with "ident" authentication, so the user will not require password only if the task is running as such linux user and using UNIX socket connection.
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
Sent from my Sony Xperia™ smartphone
---- Azimuddin Mohammed wrote ----
Hello,
I have setup my postgres server with postgres as a super user with password. Everytimg when i connect to postgress as super user i have to login with password. My question is how can I automate the scripts for back up and restore that will require password while taking backup or doing restore. We do not want to store the password in the script due to security concerns. Is there a way to do ?
If you can use SSH to transfer your backup maybe you could authenticate with ssh keys. you should have to generate postgres ssh keys being login as 'postgres' with ssh-keygen program, then you copy you public key on your backup server and the authentication will hapend between the 2 servers by comparing the keys ... Le 22/02/2018 à 03:27, Azimuddin Mohammed a écrit : > Hello, > I have setup my postgres server with postgres as a super user with > password. Everytimg when i connect to postgress as super user i have > to login with password. My question is how can I automate the scripts > for back up and restore that will require password while taking backup > or doing restore. We do not want to store the password in the script > due to security concerns. Is there a way to do ? -- Joan Luc Labòrda 05.5684.3013 laborde@crpp-bordeaux.cnrs.fr jean-luc.laborde@u-bordeaux.fr