sqitch-authentication(3pm)

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, YugabyteDB, CockroachDB
    The Postgres, Yugabyte, and Cockroach engines use 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.

ClickHouse
    The ClickHouse engine uses the "CLICKHOUSE_USER" environment variable, if set.

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/key-pair-auth> to create a key pair, then set the "private_key_path" in the ~/.snowsql/config to point to the private key file:

      private_key_path = "<path>/rsa_key.p8"

    To connect, set the $SNOWSQL_PRIVATE_KEY_PASSPHRASE environment variable to the passphrase for the private key, and add these parameters under the configuration for your DSN in /etc/odbc.ini or ~/.odbc.ini:

      [sqitch]
      AUTHENTICATOR     = SNOWFLAKE_JWT
      UID               = <username>
      PRIV_KEY_FILE     = <path>/rsa_key.p8
      PRIV_KEY_FILE_PWD = <password>

    Then connect using the named DSN via the "DSN" query parameter:

      db:snowflake://movera@example.snowflakecomputing.com/flipr?warehouse=compute_wh;DSN=sqitch

    Or add the ODBC parameters directly to the query part of your connection URI (although it's safer to put "priv_key_file_pwd" in odbc.ini):

    •   "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

    Sadly, both the "SNOWSQL_PRIVATE_KEY_PASSPHRASE" environment variable and the "priv_key_file_pwd" ODBC parameter must be set, as Sqitch uses ODBC to maintain its registry and SnowSQL to execute change scripts.

ClickHouse
    ClickHouse does not support password-less authentication, but does support TLS Client authentication. Sqitch reads the "$.openSSL.client" section of the "Client configuration|https://clickhouse.com/docs/interfaces/cli#connection-credentials" to also configure is ODBC connection. For example, given this config:

       <config>
        <openSSL>
          <client>
            <certificateFile>my_cert_name.crt</certificateFile>
            <privateKeyFile>my_cert_name.key</privateKeyFile>
          </client>
        </openSSL>
      </config>

    It maps the configuration parameters to the "ODBC parameters https://github.com/clickHouse/clickhouse-odbc?tab=readme-ov-file#configuration" as follows:

    "privateKeyFile": "PrivateKeyFile"
        Path to private key file. Raises an error if both are set and not the same value.

    "certificateFile": "CertificateFile"
        Path to certificate file. Raises an error if both are set and not the same value.

    "caConfig": "CALocation"
        Path to the file or directory containing the CA/root certificates. Raises an error if both are set and not the same value.

    "secure", "port", "host", "verificationMode": "SSLMode"
        Sets the ODBC "SSLMode" parameter to "require" when the "secure" parameter from the configuration file is true or the port is 9440, or the host name from the configuration file or the target ends in ".clickhouse.cloud". If none of those are true but "verificationMode" is set, set the "SSLMode" query parameters as follows:

           verificationMode | SSLMode
          ------------------|-----------
           none             | [not set]
           relaxed          | allow
           strict           | require
           once             | require

    Unfortunately, these parameters cannot currently be specified exclusively via ODBC parameters, because the client lacks options or environment variables to match. It's best for now to omit them from target URIs and rely only on the client configuration.

  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, YugabyteDB, CockroachDB
    PostgreSQL, YugabyteDB, and CockroachDB 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"
ClickHouse
    ClickHouse supports password configuration via its client configuration file <https://clickhouse.com/docs/interfaces/cli#connection-credentials>. Sqitch will read the "host", "port", "database", and "password" parameters from either the root of the configuration file, as well as the "secure" and "openSSL.client" parameters, for example:

      <config>
        <user>username</user>
        <password>password</password>
        <secure>true</secure>
        <openSSL>
          <client>
            <caConfig>/etc/ssl/cert.pem</caConfig>
          </client>
        </openSSL>
      </config>

    All but the "openSSL.client" parameters may be set on a named connection basis, matched by the host name in the target URL (or the $CLICKHOUSE_HOSTNAME environment variable). All the parameters are the same except "host" becomes "hostname". For example, to set parameters for the host "click.house":

      <config>
        <connections_credentials>
          <connection>
            <name>click.house</name>
            <hostname>clickhouse.example.com</hostname>
            <port>9440</port>
            <secure>1</secure>
            <user>default</user>
            <password>password</password>
          </connection>
        </connections_credentials>
      </config>

    Note that setting "port" to 9440 will implicitly set the ODBC port to 8443 unless it's explicitly set to something else.

  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, YugabyteDB, CockroachDB
    $PGPASSWORD

MySQL
    $MYSQL_PWD

Vertica
    $VSQL_PASSWORD

Firebird
    $ISC_PASSWORD

Snowflake
    $SNOWSQL_PWD

ClickHouse
    $CLICKHOUSE_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.