Prompts for password for user postgres

[jcandan]

Problem/Motivation

Using drush with postgres repeatedly prompts for database password. It seems #63 has become an issue again.

I believe the culprit is Drush 9.0.0, because it only just became an issue for me when drupal-composer updated to Drush 9.0.0 a couple days ago.

To reproduce this problem:

composer create-project drupal-composer/drupal-project:8.x-dev my_app --stability dev --no-interaction
cd my_app/web
../vendor/bin/drush si standard --db-url=pgsql://postgres:root@127.0.0.1:5432/my_app -y

Proposed resolution

Not sure if the fix in #695 could address this again, but basically that fix was to set the PGPASSWORD environment variable to the settings.php database password.

[geerlingguy]

I'm suddenly getting this as well, starting last night, on my Drupal VM automated builds, e.g.: https://travis-ci.org/geerlingguy/drupal-vm/jobs/333562827

TASK [geerlingguy.drupal : Install Drupal with drush.] *************************
fatal: [localhost]: FAILED! => {"changed": true, "cmd": ["/var/www/drupalvm/drupal/vendor/drush/drush/drush", "site-install", "standard", "-y", "--root=/var/www/drupalvm/drupal/web", "--site-name=Drupal", "--account-name=admin", "--account-pass=admin", "--db-url=pgsql://drupal:drupal@localhost/drupal"], "delta": "0:00:00.408622", "end": "2018-01-26 04:27:38.036190", "msg": "non-zero return code", "rc": 1, "start": "2018-01-26 04:27:37.627568", "stderr": " [error]  Failed to create database: Password for user drupal:psql: fe_sendauth: no password supplied ", "stderr_lines": [" [error]  Failed to create database: Password for user drupal:psql: fe_sendauth: no password supplied "], "stdout": "\n // You are about to DROP all tables in your 'drupal' database. Do you want to  \n // continue?: yes.                                                             ", "stdout_lines": ["", " // You are about to DROP all tables in your 'drupal' database. Do you want to  ", " // continue?: yes.                                                             "]}

[geerlingguy]

From https://www.drupal.org/node/2146131, I've also found:

You might want to try setting the environment variable PGPASSWORD and then passing a flag into your drush command line.

Assume Linux/OSX
export PGPASSWORD=password;
drush --extra=-w commands

the flag -w tells drush to take the password from the environment variable PGPASSWORD and > passes it to postgres

it works for the 'drush --extra=-w sqlq' command , so it might also work for your other drush command when using a postgres database. Just have to make sure that PGPASSWORD is set.

Seems a bit heavy-handed, but I might have to do a hack like this for now, since it's breaking my precious builds!

[darrenoh]

I can reproduce this with Drush 9.2.1.

[iVegas]

Does it related?
https://github.com/drush-ops/drush/blob/9.2.1/src/Sql/SqlPgsql.php#L72

[darrenoh]

@iVegas, that does look like the reason.

[iVegas]

Maybe it is the issue with the temporary $password_file?

As a workaround https://www.drupal.org/project/drush/issues/438828#comment-5020060

[iVegas]

I'm using docker4drupal compose env.
I had created .pgpass file inside php container and this helped me.
touch ~/.pgpass
And the content of the file is:
postgres:5432:drupal:drupal:drupal

[steinmb]

Also noticed this, moving from 8.x to 9.x when my env. moved from PHP 7.0 to 7.1.

[steinmb]

And yes, as mention above. Adding a .pgpass to your home directory work. I use a slightly different one: postgres:5432:*:<user name>:<password>

Do not forget to alter the .pgpass permissions after you created. It do/might not work without this (did not for me): chmod 0600 ~/.pgpass

[nbertram]

This is affecting us too. While a .pgpass file works (because psql will look for it if a password isn't supplied any other way), this is a hassle to keep in sync with the one in the settings file.

Drush 9 doesn't exhibit this behaviour with MySQL (where it'll supply the password from config still), so I can't imagine this was an intentional change as implied.

Rather, it looks like it might be a typo in the createPasswordFile() method, where it only writes a password file if there already is one, which seems reversed.

This patch reverses that logic and makes it work again, but I'm not sure it's the right way. Judging by other commits in this file, like #3099, it might've been working in its current form:

diff --git a/src/Sql/SqlPgsql.php b/src/Sql/SqlPgsql.php
index f8c805c..aa01b63 100644
--- a/src/Sql/SqlPgsql.php
+++ b/src/Sql/SqlPgsql.php
@@ -17,7 +17,7 @@ class SqlPgsql extends SqlBase
     {
         $password_file = null;
         $dbSpec = $this->getDbSpec();
-        if (null !== ($this->getPasswordFile()) && isset($dbSpec['password'])) {
+        if (null == ($this->getPasswordFile()) && isset($dbSpec['password'])) {
             $pgpass_parts = [
             empty($dbSpec['host']) ? 'localhost' : $dbSpec['host'],
             empty($dbSpec['port']) ? '5432' : $dbSpec['port'],

I'll look into this more next week, and if it seems correct I'll make a more official PR then.

[nbertram]

I've since confirmed that this regressed in 2d98048 under the auspices of a refactor, and have submitted PR #3480 to fix it.

Can others affected by this please try the patch from the PR to see if it solves your issue (and doesn't break anything else...). If your Drush is installed with composer, you can add the patch in composer.json's extra section and re-run composer install:

"extra": {
    ...
    "patches": {
        "drush/drush": {
            "PR#3480 PGPASSFILE support": "https://github.com/drush-ops/drush/pull/3480.patch"
        }
    }
}

[darrenoh]

The patch works. Thank you, @nbertram, for your thorough investigation. Anyone who has trouble applying the patch, please read https://docs.acquia.com/article/fixing-failing-composer-patches-updating-gnu-patch.

[RoSk0]

Tested patch works perfectly. Thansk @nbertram

[weitzman]

Fixed in #3480.

[kalpaitch]

Can I just point out that this is only half fixed, as I discovered while opening #4023. See line 161 https://github.com/drush-ops/drush/pull/4023/files#diff-b45a8244b8e5a42fb3f08899b3ef1765R161

If using structure tables key pg_dump will still password prompt or fail as I have discovered the hard way :)