drush keeps on asking the password on PostgreSQL site-install

[ricardoamaro]

ping @weitzman, since this is quite important to make the postgresql testbot on D7 to work.

Operation 7 [install]... 
Bootstrap to phase 0. [0 sec, 1.88 MB]                               [bootstrap]
Drush bootstrap phase : _drush_bootstrap_drush() [0 sec, 1.89 MB]    [bootstrap]
Cache MISS cid: 7.0-dev-commandfiles-0-7c3538320ee94c32854c70c64dc02899 [0.01 sec, 1.94 MB]                   [debug]
Cache SET cid: 7.0-dev-commandfiles-0-7c3538320ee94c32854c70c64dc02899 [0.01 sec, 1.96 MB]                    [debug]
Bootstrap to phase 0. [0.04 sec, 5.42 MB]                                                                 [bootstrap]
Bootstrap to phase 1. [0.06 sec, 5.42 MB]                                                                 [bootstrap]
Drush bootstrap phase : _drush_bootstrap_drupal_root() [0.06 sec, 5.43 MB]                                [bootstrap]
Initialized Drupal 7.26 root directory at /var/www [0.07 sec, 6.29 MB]                                       [notice]
Found command: site-install (commandfile=site_install) [0.07 sec, 6.29 MB]                                [bootstrap]
Calling hook drush_core_site_install_validate [0.11 sec, 6.32 MB]                                             [debug]
Returned from hook drush_core_site_install_validate [0.11 sec, 6.32 MB]                                       [debug]
Calling hook drush_core_pre_site_install [0.11 sec, 6.33 MB]                                                  [debug]
Declaration of Drush\Sql\Sqlpgsql::creds() should be compatible with                                         [notice]
Drush\Sql\SqlBase::creds($hide_password = true) Sqlpgsql.php:5 [0.11 sec, 6.43 MB]
Cache MISS cid: 7.0-dev-alias-path--8bf32223596c4e7624f626b4fa56d306 [0.11 sec, 6.43 MB]                      [debug]
Executing: psql -q --dbname=template1 --host=172.17.0.76 --port=5432 --username=drupaltestbot --no-align --field-separator='    ' --pset footer=off -t -c 'SELECT 1 AS result FROM pg_database WHERE datname='\''build_2014_06_25_230815'\'''
Password for user drupaltestbot: 

[ricardoamaro]

Tried a proposed solution from https://drupal.org/node/2146131

On the latest version on drush i changed, but it still doesn't work:

diff --git a/lib/Drush/Sql/Sqlpgsql.php b/lib/Drush/Sql/Sqlpgsql.php
index 5c8dcf6..acc9b6e 100644
--- a/lib/Drush/Sql/Sqlpgsql.php
+++ b/lib/Drush/Sql/Sqlpgsql.php
@@ -4,7 +4,7 @@ namespace Drush\Sql;

class Sqlpgsql extends SqlBase {

- public $query_extra = "--no-align --field-separator='\t' --pset footer=off";
+ public $query_extra = "--no-align --field-separator=\"\t\" --pset footer=off";

public $query_file = "--file";

[ricardoamaro]

I would be happy to know if there is a way to test postgresql without install, also. Since that is possible with mysql.

[ricardoamaro]

ping @greg-1-anderson

[greg-1-anderson]

Hm, seems I have used D7 site install on postgres before, but if it is not working for you, I guess it must have broken. Will add it to my large backlog of things to look at, but I've got a lot of balls in the air right now. Will see what I can do -- I like postgres testing on Drupal. :)

[greg-1-anderson]

Sorry, I didn't read the whole description, and missed what your problem was! Sorry.

The thing about postgres and Drush is that there isn't any way to get the psql client to suppress the password prompt via commandline parameters. You have to write a .pgpass file in your home directory. Mine looks like this:

localhost:5432:*:www-data:SECRETPASSWORDHERE

I don't think that there is a way to tell psql to use a different .pgpass file, but we could potentially fake that by setting the HOME environment variable to some directory that contained one, just for each invocation of psql. If writing a .pgpass file is not an option for the testbot, let me know, and I will investigate making Drush write one using that technique.

[greg-1-anderson]

I created a PR #695 that should do the trick, at least for site-install.

[greg-1-anderson]

Fixed by the above-mentioned PR.

[joejoseph00]

There's likely a much easier way.

All you need to do is set the environment variable PGPASSWORD
and pass in the flag "-w" using the "--extra="
example (Assume Linux or OSX)

export PGPASSWORD=password_for_postgres ;


drush --extra=-w sqlq "select * from users where uid = 1" ;

this runs a sqlq command against a postgres db (configured in your settings.php file) without a password prompt

The same thing can probably be done in your other drush commands.

If no flags are passed in then you will for sure be prompted for a password

[greg-1-anderson]

Users may continue to use .pgpass or the PGPASSWORD environment variable if they wish; this is, in fact, easier for users to set up.

However, if Drush is going to supply the password to a postgres database using the password that is configured in the settings.php file, using PGPASSWORD would expose the password on the process list. This is no worse than what Drush does for mysql, where the password appears in the commandline arguments, and is therefore exposed in the process list. However, since we have the capability to put the password in a file, and specify a path to the file in an environment variable, doing it that way is more secure.

I suppose that Drush could do a setenv('PGPASSWORD', 'password_for_postgres'); followed by a psql command. I initially discounted this as being a critical section, but since each Drush process will have its own set of environment variables (and since we do not multithread Drush commands), this isn't really an issue. I think I still favor PGPASSFILE, as was done in #695.