Provided by: sqitch_1.2.1-1_all 
Name
sqitch-authentication - Guide to using database authentication credentials with Sqitch
Description
For database engines that require authentication, Sqitch supports a number of credential-
specification options, and searches for them in a specific sequence. These searches are
performed in two parts: a search for a username and a search for a password.
Usernames
Sqitch searches for usernames sequentially, using the first value it finds. Any of these
approaches may be used to specify a username, in this order:
1. In the $SQITCH_USERNAME environment variable
2. Via the "--db-username" option
3. In the deploy target URI; this is the preferred option
4. In an engine-specific environment variable or configuration
Naturally, this last option varies by database engine. The details are as follows:
PostgreSQL
The Postgres engine uses the "PGUSER" environment variable, if set. Otherwise, it
uses the system username.
MySQL
For MySQL, if the MySQL::Config module is installed, usernames and passwords can be
specified in the /etc/my.cnf and ~/.my.cnf files
<https://dev.mysql.com/doc/refman/5.7/en/password-security-user.html>. These files
must limit access only to the current user (0600). Sqitch will look for a username and
password under the "[client]" and "[mysql]" sections, in that order.
Oracle
Oracle provides no default to search for a username.
Vertica
The Vertica engine uses the "VSQL_USER" environment variable, if set. Otherwise, it
uses the system username.
Firebird
The Firebird engine uses the "ISC_USER" environment variable, if set.
Exasol
Exasol provides no default to search for a username.
Snowflake
The Snowflake engine uses the "SNOWSQL_USER" environment variable, if set. Next, it
looks in the ~/.snowsql/config file <https://docs.snowflake.com/en/user-guide/snowsql-
start.html#snowsql-config-file> and use the default "connections.username" value.
Otherwise, it uses the system username.
Passwords
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
<https://www.postgresql.org/docs/current/static/auth-methods.html>, including the
passwordless SSL certificate <https://www.postgresql.org/docs/current/static/auth-
methods.html#AUTH-CERT>, GSSAPI <https://www.postgresql.org/docs/current/static/auth-
methods.html#GSSAPI-AUTH>, and, for local connections, peer authentication
<https://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-PEER>.
MySQL
MySQL supports a number of authentication methods
<https://dev.mysql.com/doc/internals/en/authentication-method.html>, plus SSL
authentication <https://dev.mysql.com/doc/internals/en/ssl.html>.
Oracle
Oracle supports a number of authentication methods
<https://docs.oracle.com/cd/B19306_01/network.102/b14266/authmeth.htm#BABCGGEB>,
including SSL authentication
<https://docs.oracle.com/cd/B19306_01/network.102/b14266/authmeth.htm#i1009722>,
third-party authentication
<https://docs.oracle.com/cd/B19306_01/network.102/b14266/authmeth.htm#i1009853>, and,
for local connections, OS authentication
<https://docs.oracle.com/cd/B19306_01/network.102/b14266/authmeth.htm#i1007520>.
Vertica
Vertica supports a number of authentication methods
<https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Security/ClientAuth/SupportedClientAuthenticationMethods.htm>
including the passwordless TLS authentication
<https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Security/ClientAuth/ConfiguringTLSAuthentication.htm>,
GSS authentication
<https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Security/ClientAuth/Kerberos/ImplementingKerberosAuthentication.htm>,
and, for local connections, ident authentication
<https://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
<https://www.firebirdsql.org/manual/qsg2-config.html> for local connections.
Exasol
Exasol doesn't seem to support password-less authentication at this time, though there
is support for Authentication using OpenID
<https://docs.exasol.com/sql/create_user.htm#Authenti4>. To use it with Sqitch,
include "AUTHMETHOD=refreshtoken" in the target URI query string, e.g.,
db:exasol://sys:exasol@localhost:8563/?Driver=Exasol&AUTHMETHOD=refreshtoken
Snowflake
Snowflake does not support password-less authentication, but does support key-pair
authentication. Follow the instructions <https://docs.snowflake.com/en/user-
guide/snowsql-start.html#using-key-pair-authentication> to create a key pair, then set
the following variables in the ~/.snowsql/config file:
authenticator = SNOWFLAKE_JWT
private_key_path = "path/to/privatekey.p8"
To connect, set the $SNOWSQL_PRIVATE_KEY_PASSPHRASE environment variable to the
passphrase for the private key, and add these parameters to the query part of your
connection URI:
· "authenticator=SNOWFLAKE_JWT"
· "uid=$username"
· "priv_key_file=path/to/privatekey.p8"
· "priv_key_file_pwd=$private_key_password"
For example:
db:snowflake://movera@example.snowflakecomputing.com/flipr?Driver=Snowflake;warehouse=sqitch;authenticator=SNOWFLAKE_JWT;uid=movera;priv_key_file=path/to/privatekey.p8;priv_key_file_pwd=s0up3rs3cre7
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
<https://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, usernames and passwords can be
specified in the /etc/my.cnf and ~/.my.cnf files
<https://dev.mysql.com/doc/refman/5.7/en/password-security-user.html>. These files
must limit access only to the current user (0600). Sqitch will look for a username and
password under the "[client]" and "[mysql]" sections, in that order.
Oracle
Oracle supports password file
<https://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 <https://stackoverflow.com/q/7183513/79202> into a tnsnames.ora
<https://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.
Exasol
Exasol allows configuring connection profiles for the 'exaplus' client:
> exaplus -u sys -p exasol -c localhost:8563 -wp flipr_test
EXAplus 6.0.4 (c) EXASOL AG
Profile flipr_test is saved.
> exaplus -profile flipr_test -q -sql "select current_timestamp;"
CURRENT_TIMESTAMP
--------------------------
2017-11-02 13:35:48.360000
These profiles are stored in ~/.exasol/profiles.xml, readable only to the user by
default. See the documentation
<https://www.exasol.com/portal/display/DOC/Database+User+Manual> for more information
on connection profiles, specifically the EXAplus section in the chapter on "Clients
and interfaces".
For ODBC connections from Sqitch, we can use connection settings in ~/.odbc.ini:
[flipr_test]
DRIVER = Exasol
EXAHOST = localhost:8563
EXAUID = sys
EXAPWD = exasol
AUTHMETHOD = refreshtoken
When combining the above, Sqitch doesn't need to know any credentials; they are stored
somewhat safely in ~/.exasol/profiles.xml and ~/.odbc.ini:
> sqitch status db:exasol:flipr_test
# On database db:exasol:flipr_test
# Project: flipr
# ...
#
Nothing to deploy (up-to-date)
> sqitch rebase --onto '@HEAD^' -y db:exasol:flipr_test
Reverting changes to hashtags @v1.0.0-dev2 from db:exasol:flipr_test
- userflips .. ok
Deploying changes to db:exasol:flipr_test
+ userflips .. ok
Snowflake
For Snowflake, Sqitch will read the ~/.snowsql/config file
<https://docs.snowflake.com/en/user-guide/snowsql-start.html#snowsql-config-file> and
use the default connections settings; named connections are not supported. An
example:
[connections]
accountname = myaccount.us-east-1
warehousename = compute
username = frank
password = fistula postmark bag
rolename = ACCOUNTADMIN
dbname = reporting
The variables that Sqitch currently reads are:
"connections.accountname"
"connections.username"
"connections.password"
"connections.rolename"
"connections.region" (Deprecated by Snowflake)
"connections.warehousename"
"connections.dbname"
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, as is the
complementary $SQITCH_USERNAME environment variable. 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
Snowflake
$SNOWSQL_PWD
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.