bionic (3) sqitch-passwords.3pm.gz
Name
sqitch-passwords - Guide to using database passwords with Sqitch
Description
You may have noticed that Sqitch has no "--password" option. This is intentional. It's generally not a
great idea to specify a password on the command-line: from there, it gets logged to your command history
and is easy to extract by anyone with access to your system. So you might wonder how to specify passwords
so that Sqitch an successfully deploy to databases that require passwords. There are four approaches, in
order from most- to least-recommended:
1. Avoid using a password at all
2. Use a database engine-specific password file
3. Use the $SQITCH_PASSWORD environment variable
4. Include the password in the deploy target URI
Each is covered in detail in the sections below.
Don't use Passwords
Of course, the best way to protect your passwords is not to use them at all. If your database engine is
able to do passwordless authentication, it's worth taking the time to make it work, especially on your
production database systems. Some examples:
PostgreSQL
PostgreSQL supports a number of authentication methods
<http://www.postgresql.org/docs/current/static/auth-methods.html>, including the passwordless SSL
certificate <http://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-CERT>, GSSAPI
<http://www.postgresql.org/docs/current/static/auth-methods.html#GSSAPI-AUTH>, and, for local
connections, peer authentication <http://www.postgresql.org/docs/current/static/auth-
methods.html#AUTH-PEER>.
MySQL
MySQL supports a number of authentication methods
<http://dev.mysql.com/doc/internals/en/authentication-method.html>, plus SSL authentication
<http://dev.mysql.com/doc/internals/en/ssl.html>.
Oracle
Oracle supports a number of authentication methods
<http://docs.oracle.com/cd/B19306_01/network.102/b14266/authmeth.htm#BABCGGEB>, including SSL
authentication <http://docs.oracle.com/cd/B19306_01/network.102/b14266/authmeth.htm#i1009722>, third-
party authentication <http://docs.oracle.com/cd/B19306_01/network.102/b14266/authmeth.htm#i1009853>,
and, for local connections, OS authentication
<http://docs.oracle.com/cd/B19306_01/network.102/b14266/authmeth.htm#i1007520>.
Vertica
Vertica supports a number of authentication methods
<http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Security/ClientAuth/SupportedClientAuthenticationMethods.htm>
including the passwordless TLS authentication
<http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Security/ClientAuth/ConfiguringTLSAuthentication.htm>,
GSS authentication
<http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Security/ClientAuth/Kerberos/ImplementingKerberosAuthentication.htm>,
and, for local connections, ident authentication
<http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Security/ClientAuth/ConfiguringIdentAuthentication.htm>.
Firebird
Firebird supports passwordless authentication only via trusted authentication
<http://www.firebirdsql.org/manual/qsg2-config.html> for local connections.
Use a Password File
If you must use password authentication with your database server, you may be able to use a protected
password file. This is file with access limited only to the current user that the server client library
can read in. As such, the format is specified by the database vendor, and not all database servers offer
the feature. Here's how the database engines supported by Sqitch shake out:
PostgreSQL
PostgreSQL will use a .pgpass file <http://www.postgresql.org/docs/current/static/libpq-pgpass.html>
in the user's home directory to or referenced by the $PGPASSFILE environment variable. This file must
limit access only to the current user (0600) and contains lines specify authentication rules as
follows:
hostname:port:database:username:password
MySQL
For MySQL, if the MySQL::Config module is installed, passwords can be specified in the /etc/my.cnf
and ~/.my.cnf files <http://dev.mysql.com/doc/refman/5.1/en/password-security-
user.html#idm139947650158560>. These files must limit access only to the current user (0600). Sqitch
will look for a password under the "[client]" and "[mysql]" sections, in that order.
Oracle
Oracle supports password file
<http://docs.oracle.com/cd/B28359_01/server.111/b28310/dba007.htm#ADMIN10241> created with the
"ORAPWD" utility to authenticate "SYSDBA" and "SYSOPER" users, but Sqitch is unable to take advantage
of this functionality. Neither can one embed a username and password
<http://stackoverflow.com/q/7183513/79202> into a tnsnames.ora
<http://docs.oracle.com/cd/B28359_01/network.111/b28317/tnsnames.htm#NETRF007> file.
Vertica
Vertica does not currently support a password file.
Firebird
Firebird does not currently support a password file.
Use $SQITCH_PASSWORD
The $SQITCH_PASSWORD environment variable can be used to specify the password for any supported database
engine. However 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".
The behavior of $SQITCH_PASSWORD is consistent across all supported engines. Some database engines
support their own password environment variables, which you may wish to use instead. However, their
behaviors may not be consistent:
PostgreSQL
$PGPASSWORD
MySQL
$MYSQL_PWD
Vertica
$VSQL_PASSWORD
Firebird
$ISC_PASSWORD
Use Target URIs
Passwords may also be specified in target URIs. This is not generally recommended, since such URIs are
either specified via the command-line (and therefore visible in "ps" and your shell history) or stored in
the configuration, the project instance of which is generally pushed to your source code repository. But
it's provided here as an absolute last resort (and because web URLs support it, though it's heavily
frowned upon there, too).
Such URIs can either be specified on the command-line:
sqitch deploy db:pg://fred:s3cr3t@db.example.com/widgets
Or stored as named targets in the project configuration file:
sqitch target add wigets db:pg://fred:s3cr3t@db.example.com/widgets
After which the target is available by its name:
sqitch deploy widgets
See sqitch-targets and "sqitch-configuration" for details on target configuration.
See Also
• sqitch-environment
• sqitch-configuration
• sqitch-target
Sqitch
Part of the sqitch suite.