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.
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 <strong>default</strong> is that the system user
postgres has password-less access to every database as database user of the same name (
If you provide a password in the command with the <a href="http://www.postgresql.org/docs/current/interactive/libpq-envars.html" rel="nofollow">environment variable
PGPASSWORD</a>, but no password is needed, it will be ignored silently.
However, I quote the <a href="http://www.postgresql.org/docs/current/interactive/libpq-envars.html" rel="nofollow">manual here</a>:<blockquote>
PGPASSWORD (...) Use of this environment variable is not recommended for security reasons.</blockquote>
You can use a <a href="http://www.postgresql.org/docs/9.1/static/libpq-pgpass.html" rel="nofollow">password file</a> to provide passwords automatically (
.pgpass on Unix systems).
pg_dump will use it.
Finally, consider the <a href="http://www.postgresql.org/docs/9.1/static/app-pgdump.html" rel="nofollow">command line options</a>:
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 <strong>password-less access</strong> for the system user
postgres to every database in the config file
ident <a href="http://www.postgresql.org/docs/9.1/static/auth-methods.html#AUTH-IDENT" rel="nofollow">authentication methods</a>. 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))