Automatically obtain database password from db-specific external source if not explicitly specified

[ToBeReplaced]

I'd like to use the PGPASSWORD environment variable to pass the password through to Postgresql, as allowed by psql. That way, I don't end up with a password visible to other users.

To reproduce, instead of using flyway migrate -password=example, use PGPASSWORD=example flyway migrate.

Great tool, thanks for the no-nonsense SQL-based migrations.

[ToBeReplaced]

I should have said more -- This is very low priority; The flyway.properties file is sufficient.

[axelfontaine]

Thanks for the suggestion and the nice comments :-)

I am still undecided about this. I basically see 3 options:

• keep things as is and close the issue
• add PGPASSWORD support, most convenient for PG users, doesn't help others
• add environment variable replacement support to the config file parser, not quite as convenient for PG users, but helps everyone else too

I'll leave this opne for now and give it some thought post 3.2

[cbandy]

@ToBeReplaced if security is your concern, the PGPASSWORD environment variable may not be what you want.

http://www.postgresql.org/docs/current/static/libpq-envars.html

Use of this environment variable is not recommended for security reasons, as some operating systems allow non-root users to see process environment variables via ps; instead consider using the ~/.pgpass file.

[ToBeReplaced]

I'm using flyway inside of a docker container, and I don't believe the
environment is visible to any users if it's passed in with an envfile.

On Mon, 2015-03-16 at 09:22 -0700, Chris Bandy wrote:

@ToBeReplaced if security is your concern, the PGPASSWORD environment variable may not be what you want.

http://www.postgresql.org/docs/current/static/libpq-envars.html

Use of this environment variable is not recommended for security reasons, as some operating systems allow non-root users to see process environment variables via ps; instead consider using the ~/.pgpass file.

---

Reply to this email directly or view it on GitHub:
#960 (comment)

[tuukkamustonen]

Just bumped into this. It doesn't sound technically difficult to resort to ~/.pgpass if -password <pass> is not given. But, I assume ~/.pgpass is for psql CLI only and JDBC driver is completely another beast...?

Would be nice feature, still.

[eepstein]

Would be helpful to check the .pgpass file.

[axelfontaine]

@cajnoj This sounds interesting. Yes, let's discuss!

[kn327]

This is a bit old, but I was able to script around this to extract the appropriate password from the desired .pgpass file within a shell script.

Posting this in case anyone else is looking for a way not to store sensitive passwords inside their scripts.

The password is stored in the standard .pgpass format under the current directory, though that could easily be changed based on your individual needs

pretty configurable, you could even read the list of hosts, databases and users from a config file to make this script 100% generic.

#!/usr/bin/env bash

ENV=local
if [ ! -z "$1" ]; then
    ENV=$1
fi

case "$ENV" in
prd|prod|production)
  ENV="production"
# insert multiple hosts separated by spaces
  HOSTS="host1 host2"
# insert your database name here
  DATABASE="database_name"
# insert your username here.
  USER="user_name"
  ;;
*)
  err "$ENV not found"
  exit
  ;;
esac

# insert your port number here, or within the switch if it changes
PORT=port_number

# we assume the .pgpass file exists under the current tree.
PGPASSFILE=$PWD/config/*.pgpass

# go through all of the hosts and upgrade each one
for HOST in ${HOSTS}; do
    # .pgpass file is stored in the format host:port:database:user:password
    SEARCH="$HOST:$PORT:$DATABASE:$USER:"
    # find the first matching line
    LINE=$(grep -m1 $SEARCH $PGPASSFILE)
    # extract our password from the line
    PASSWORD="${LINE:${#SEARCH}}"

   # make sure a password was found
    if [ -z "$PASSWORD" ]; then
        echo "Unable to find password for $USER in $HOST:$PORT/$DATABASE"
        continue
    fi

    # execute upgrade.
    ./flyway-6.1.0/flyway.cmd migrate \
        -url="jdbc:redshift://$HOST:$PORT/$DATABASE" \
        -installedBy="$USERNAME" \
        -user="$USER" \
        -password="$PASSWORD"
done

echo "Done."

[juliahayward]

This is (loosely) related to #1194 and we'll be revisiting authentication more generally in v7