I have patched drush sql to allow it to work with Postgres. Note that this patch introduces a new SQL authentication policy for drush sql, as the Postgres command-line tools work a little bit differently than the mysql tools. With mysql, you can pass the db connection username and password on the command line, but Postgres does not allow you to specify the password on the command line. Were drush to call psql the same way that it called mysql (as the web server user), you would have to enter the Postgres db password for every database command; that would be extremely tedious. Therefore, I added a new option 'sudo-user'; if set (e.g. to "postgres"), then drush sql will add a "sudo -u postgres" in front of every psql command. If you set sudo-user to the special value '*', then drush sql will omit the user specification to psql commands (useful if you are already logged in as a postgres superuser). If sudo-user is unset, then drush sql will call psql the same way that it calls mysql.


The attached patch is up-to-date with respect to the recent 19 April changes checked into the head of the drush sql branch just a few hours ago.


This patch should not impact mysql users. The code in the msql branches is for the most part the same as it was; the one thing I changed for mysql was in the drush sql load command, where I switched from input redirection to a pipe for consistency with the psql code branch.


Please contact me if you have any questions about this patch.

CommentFileSizeAuthor
#6 drush-HEAD-2009_04_20.3.patch10.26 KBgreg.1.anderson
#5 drush-HEAD-2009_04_20.2.patch11.04 KBgreg.1.anderson
#3 drush-HEAD-2009_04_20.patch10.48 KBgreg.1.anderson
drush-HEAD-2009_04_19.patch10.76 KBgreg.1.anderson

Comments

moshe weitzman’s picture

moshe weitzman commented

Minor correction - drush commands run as the logged in user not the webserver user. There is no webserver in CLI PHP.

It isn't quite as nice, but perhaps you could add an bash alias called drushp which does a sudo -u postgres [path]/[to]/[drush.php]. then use that when you use sql commands. I'm not sure drush should do what you propose in this patch.

I'd like to commit this patch without the sudo stuff. Also, please revert the pipe change in sql load. We used to do it that way and ran into memory problems. Hope you can reroll with those changes.

sqlite, anyone?

greg.1.anderson’s picture

greg.1.anderson commented

I have a clarification to your correction. I know that drush runs as the logged in user, but it passes the webserver user to the database as part of the get credentials routine. (Sorry for the confusion; I used the wrong words when I described the patch the first time). I changed get credentials routine to key off of the existence of the 'sudo-user' routine and made it subvert the user-setting parameter when set. This part of the code probably isn't necessary, though, as the credentials will probably be accepted by postgres w/out a password if you run su-ed to the postgres super user (e.g. via the alias you suggested).

In any event, I will yank the sudo stuff and revert the pipe change (and avoid pipes in the pgsql code as well) and resubmit ASAP. I don't know if I'll have time to do it today, but I'll try to do it early in the week.

Thanks for the review,

- Greg

greg.1.anderson’s picture

greg.1.anderson commented
StatusFileSize
new drush-HEAD-2009_04_20.patch10.48 KB

Included is a new patch file that replaces the previous one submitted above. The sudo-related changes have been backed out, as have all use of pipes (both mysql and pgsql code branches now use file redirection).

This patch also includes a comment pointing out a potential bug; search for "// ga:", delete the comment and take appropriate action (no action if code is correct, or perhaps move the line below the comment up above the previous line if my suspicion is correct).

Thanks,

- Greg

p.s. As an interim measure, I invoke drush with the following shell script:

#!/bin/bash

DRUSH=/srv/www/drupal/sites/all/modules/drush/drush.php

if [ "x$1" == "xsql" ] ; then
        sudo -u postgres $DRUSH "$@"
else
        $DRUSH "$@"
fi

This is a little more convenient than 'drushp', but has the side effect of being destructive to Drupal installations that use mysql. Those who have both Postgres and mysql databases on the same machine should favor the drushp alias suggestion above, or just configure 'root' as a Postgres superuser.

moshe weitzman’s picture

moshe weitzman commented
Status: Needs review » Needs work

Much better.

- Please fix some indentation problems in the new code. Also remove some tabs that snuck in.
- $extra has to go before the file redirection. It is for optional extra params to the mysql/psql commands
- remove a drush_print($exec). thats probably your debug code. FYI, I use the --simulate flag for this.
- I think the comment for '// If you are calling drush as the postgres superuser,' can be improved. Lets be more terse about describing the problem and more verbose about how to solve it. Perhaps just add a link to this issue.

Hope you can refine this for one more patch. Thanks.

greg.1.anderson’s picture

greg.1.anderson commented
StatusFileSize
new drush-HEAD-2009_04_20.2.patch11.04 KB

I spent some time fiddling with Postgres authentication, and couldn't get the .pgpass file to work right. I limited my comment to a hint on how to set up ident authentication in pg_hba.conf, and added a pointer to this node in case anyone cared to continue the discussion on Postgres authentication options wrt drush here.

I made the other changes as you suggested.

- Greg

greg.1.anderson’s picture

greg.1.anderson commented
StatusFileSize
new drush-HEAD-2009_04_20.3.patch10.26 KB

Patch version 3 is the same as version 2 above, except I forgot to remove the 'sudo-user' from the example rc file in version 2.

moshe weitzman’s picture

moshe weitzman commented
Status: Needs work » Fixed

Committed. Thanks a lot.

Is there some good way for postgres to support the structure-tables option in sql dump?

adrian’s picture

adrian commented

Postgres doesn't allow a command line mechanism for specifying the password.

the 'correct' way would be to use proc_open to call the command, and then write the password to the stdin pipe of the child process once you get the 'password:' prompt on the stdout process.

greg.1.anderson’s picture

greg.1.anderson commented

Yes; it can be implemented the same way that the mysql branch works. I just had some blinders on and didn't notice that block of code, so I didn't port it. I'll provide another patch to include that shortly.

My next trick is going to be porting my "clone-drupal" script over to use drush. clone-drupal currently exists in prototype form as a bash script. It has a few functions, the main ones being "fork" and "merge". Fork is basically:

# drush sql load live base
# drush sql load live dev

Merge is a new function that analyzes the tables in the live, base and dev databases. If a table has changed in dev but not in live, then it is pushed from dev to base, and similarly tables that have changed in live but not in dev are also pushed to base. Any tables that have changed in both places are conflicts; conflicts are checked first, and prevent all changes. This process actually does work in practice, although there are some wrinkles (cron timestamps in variables table, user login time in users table) that have to be taken care of with some special code.

Would you support any of the following additions to drush core?

  • A new flag for drush sql dump (and load) that specified the tables to ignore? (Without this feature I could have the script write the ignore tables to a temporary drushrc.php file and run drush -c to select it.)
  • A new flag / parameter for drush sql dump that specifies a single table to dump?
  • A "drush sql tables" command to list all tables in a database?
  • A "drush sql diff a b" command to list tables with differences in two drupal sites (a and b specified as in "drush sql load a b")?
  • A "drush sql merge dev base live" command in drush core?
  • A "drush clone" command in core that will also create databases and set up dev / base sites as necessary?

"drush clone" could easily be an external drush module, and that one at least probably should be. Any of the other features above that you don't want in drush sql could be private features of drush clone, but I'm partial to making them available in drush sql because they are generally useful. If I write code in drush core I will support and test both mysql and postgres databases.

Thanks,

- Greg

greg.1.anderson’s picture

greg.1.anderson commented

Reply test: I thought #9 was a reply to #7. This post is a reply to #7.

[Edited note: looks like "reply" isn't working the way it used to...]

greg.1.anderson’s picture

greg.1.anderson commented
Title: Postgres fixes for drush sql (patch included) » [Committed] Postgres fixes for drush sql (patch included)
moshe weitzman’s picture

moshe weitzman commented

We don't usually put a final status in the title - the Status field carries this info. Edits like those can bore the many people who subscribe via email/rss.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Yoran’s picture

Yoran commented
Component: Code » SQL
Status: Closed (fixed) » Needs review

Sorry to reopen this but I disagree with #8 as postgreSQL DO "allow a command line mechanism for specifying the password" :-)

PGPASSWORD='your_password' pg_dump  database --host=localhost --port=5432 --username=your_user --clean

So in order to have correct password handling for drush sql-dump, you just have to change in sql.drush.inc:338 (drush 4.5)

       $exec = 'pg_dump ';

by

       $exec = "PGPASSWORD='{$db_spec['password']}' pg_dump ";

If $db_spec['password'] is empty you'll have an empty PGPASSWORD which is also working correctly, so you don't even have to make a special case for no-password db.

The same can be done for all psql calls modifying same file, line 199 :

      /* YB - Mot de passe pour postgresql {
      $command = 'psql';
      */
      $command = "PGPASSWORD='{$db_spec['password']}' psql";
      /* } YB */

And to make work "drush sqlc"

function drush_sql_cli() {
  /** YB - nécessaire pour pgsql { 
  proc_close(proc_open(_drush_sql_connect(), array(0 => STDIN, 1 => STDOUT, 2 => STDERR), $pipes));
  } YB */
  $db_spec = _drush_sql_get_db_spec();
  proc_close(proc_open(_drush_sql_connect($db_spec), array(0 => STDIN, 1 => STDOUT, 2 => STDERR), $pipes));
}

If someone interested I can make a patch but this is 3 simples modifications.

greg.1.anderson’s picture

greg.1.anderson commented
Title: [Committed] Postgres fixes for drush sql (patch included) » Postgres fixes for drush sql (patch included)
Status: Needs review » Active

While this enhancement could in fact be helpful and convenient in some cases, the big advantage that .pgpass has is that it cannot be observed in transit on the commandline, whereas the above technique sometimes can. For local commands, VAR=val command args will show only command args in the process list, which is good; however, for remote commands, the above expression will be passed as an argument to ssh, which might be observed by other users on the same machine. There was an issue about this for mysql somewhere; ah, here it is: #671906: mysql credentials leak in drush sqlc.

Something like #14 would be okay as long as it was turned on by an option, and off by default. Patches welcome.

Yoran’s picture

Yoran commented
Status: Active » Closed (fixed)

thanks for you answer Greg. I didn't figure out this kind of leak and so this solution become a dead end for me. I'll have a look at the .pgpass file which I wasn't aware of. Thank you for this tip.

PS:If anyone still need this option, I can provide a patch. In the mid-time this issue is closed for me.

greg.1.anderson’s picture

greg.1.anderson commented

Put in your .pgpass:

localhost:5432:*:WEBSERVERUSER:DBPASSWORD