CODEWITHSUNDEEP
postgresqlfabricpostgresql-9.1
Feanor
Feanor

Reputation: 3690

fabric postgres password in command

I have a fabric script that dumps database on server. And I can use it on multiple servers with the PostgreSQL database. The command is simple: 

sudo("su postgres -c \"PGPASSWORD=%s pg_dump %s > /tmp/telemedia_newdb\""
% (HOST_SOURCE_DB_UPASS,HOST_SOURCE_DB))

But sometimes, Postgres does not ask for a password at all ...

Will this command fail without a password prompting from Postgres? (Or I know that it will not prompt and HOST_SOURCE_DB_UPASS=''). I want THIS code to work with or without password.

Upvotes: 0

Views: 1847

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658122

It all depends on how you set up access to your database in pg_hba.conf. There is a separate config file per database cluster (effectively per port) and settings can be different from database to database.

So, yes, if you have set it up that way, then the system user postgres will have password-less access to some databases but is prompted to enter a password for others. The default is that the system user postgres has password-less access to every database as database user of the same name (postgres).

If you provide a password in the command with the environment variable PGPASSWORD, but no password is needed, it will be ignored silently.

However, I quote the manual here:

PGPASSWORD (...) Use of this environment variable is not recommended for security reasons.

You can use a password file to provide passwords automatically (.pgpass on Unix systems). pg_dump will use it.

Finally, consider the command line options:

--no-password
--password

to force pg_dump to either prompt or not prompt for a password. If a password is required but disabled by --no-password, pg_dump will fail.

I would enable password-less access for the system user postgres to every database in the config file pg_hba.conf. Use peer or ident authentication methods. Then you don't have to provide a password and the script will always work:

local    all         postgres             ident

Your script would be simplified to (untested):

sudo("su postgres -c \"pg_dump %s > /tmp/telemedia_newdb\"" % (HOST_SOURCE_DB))

Upvotes: 1

Related Questions