3

I've got a postgres database that I want to backup regularly. For that, I'd like to use the automated backup script here

Usually I to proceed admin tasks I use these command:

> sudo -i -u postgres
> psql

No password is requested and I can dump manually my database. It's not too bad.

Using the automated script I face a password issue. It asks me for postgres password. I've never set it up and I thought I shouldn't need to set it up as the example given above showed.

Here is how is configured my pg_hba.conf.

# Database administrative login by Unix domain socket
local   all             postgres                                peer
TYPE  DATABASE        USER            ADDRESS                 METHOD

"local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Do I really need to set a postgres password for running my script?

2
  • As what user are you executing the backup script and how are you executing it? Nov 5, 2015 at 13:26
  • Actually it's now working. I will answer to myself to keep trace of how it works. I execute the script as a system user who's not a postgres user. Nov 5, 2015 at 13:55

2 Answers 2

1

If you are running script as root:

sudo -u postgres psql

If you are running script as other system user, add sudo permission for this user with visudo before executing above command.

 <Systemuser> ALL=(postgres) NOPASSWD: /usr/bin/psql
0

So what I found out in order to have this script running from a system user.

  1. I wasn't able to have it running without setting a password for postgres

    $ sudo -i -u postgres

    $ psql

    $ ALTER USER postgres WITH PASSWORD 'new_password';

  2. Then create a .pgpass file in the user system home who's running the script.

    $ echo '*:*:*:postgres:new_password' > ~/.pgpass

    $ chmod 0600 ~/.pgpass

  3. That's it.

My errors was:

  • to think that I could handle it without setting the a postgres password.
  • trying to place the .pgpass in /var/lib/postgresql (postgres home) instead of user system home.

Hope this help!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .