sqitch...ication(3pm)

📦
version 1.6.1 · 6 versions
, in ubuntu stonking

NAME 🌸

sqitch-authentication — a guide to using database authentication credentials with Sqitch. (You'll see it filed as sqitch-authentication(3pm) in the User Contributed Perl Documentation. That (3pm) at the top is just where it lives, bestie, don't overthink it. 💅)

DESCRIPTION 💖

Okay so here's the tea: some database engines require authentication before they'll let you in, and Sqitch is very prepared for that. It supports a whole bunch of credential-specification options, and it searches for them in a super specific order. 👑

That hunt happens in two separate parts: one search for a username, and one search for a password. Two whole quests. Let's get into it. 🦋

USERNAMES ✨

Sqitch looks for usernames one after another, and the first value it finds is the one it uses. Any of these can hand it a username, and here's the exact order:

  1. In the $SQITCH_USERNAME environment variable
  2. Via the --db-username option
  3. In the deploy target URI — and ngl, this one's the preferred option 💅
  4. In an engine-specific environment variable or configuration

Obviously that last one is gonna vary by database engine. The deets are as follows:

  • PostgreSQL, YugabyteDB, CockroachDB — the Postgres, Yugabyte, and Cockroach engines use the PGUSER environment variable, if it's set. Otherwise, they use 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. Like, none. Homie's on its own. 🙈
  • Vertica — the Vertica engine uses the VSQL_USER environment variable, if it's set. Otherwise, it uses the system username.
  • Firebird — the Firebird engine uses the ISC_USER environment variable, if it's set.
  • Exasol — Exasol provides no default to search for a username.
  • Snowflake — the Snowflake engine uses the SNOWSQL_USER environment variable, if it's set. Next, it looks in the ~/.snowsql/config file (https://docs.snowflake.com/en/user-guide/snowsql-start.html#snowsql-config-file) and uses the default connections.username value. Otherwise, it uses the system username.
  • ClickHouse — the ClickHouse engine uses the CLICKHOUSE_USER environment variable, if it's set.

PASSWORDS 🔐

So you may have noticed something iconic and slightly unhinged: Sqitch has no --password option. That is 100% intentional, bestie. Putting a password on the command line is generally not a great idea — from there it gets logged straight into your command history, and it's easy for anyone with access to your system to extract it. Yikes. 😭 So you're probably wondering: how do I give Sqitch a password so it can actually successfully deploy to a database that demands one?

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 one gets a full breakdown in the sections below. 🫶

Don't use Passwords 🚫

Of course, the best way to protect your passwords is to not use them at all. If your database engine can do passwordless authentication, it is sooo worth the time to make it work — especially on your production database systems. Some examples:

db:exasol://sys:exasol@localhost:8563/?Driver=Exasol&AUTHMETHOD=refreshtoken
  • Snowflake — Snowflake does not support password-less authentication, but it 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, because Sqitch uses ODBC to maintain its registry and SnowSQL to execute change scripts. Both of them. No skipping, bestie. 😭

  • ClickHouse — ClickHouse does not support password-less authentication, but it 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 genuinely must use password authentication with your database server, you may be able to use a protected password file. That's a file with access limited only to the current user, that the server client library can read in. Because of that, the format is specified by the database vendor, and not all database servers even offer the feature. Here's how the database engines Sqitch supports 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

okay that's part one, bestie — more incoming 💖

MySQL

okay so 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. slay. 💅

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. it's giving nope, bestie. 😭

Vertica

Vertica does not currently support a password file. short, sweet, no password file energy.

Firebird

Firebird does not currently support a password file. same vibe, different bird. 🦋

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”. iykyk. ✨

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

literally obsessed with not leaking creds. 🔥

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"

not the deprecated one, bestie. we don't do that anymore. 🙈

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. the way that port just changes on you… iconic behavior. 🌈

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.

warning, bestie: this is lowkey risky. non-root users might see your env vars in ps. protect the password, protect the peace. 🫶

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). fr fr, last resort only. 💀

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. yes, there are two spaces there. we keep the details exact, bestie. 😌

See Also

  • sqitch-environment
  • sqitch-configuration
  • sqitch-target

Sqitch

Part of the sqitch suite. main character energy, obviously. 👑

and that’s a wrap on this part, bestie! 💖 it was perl v5.40.1, dated 2026-07-26, and it belongs to the sqitch suite. periodt. ✨💅

📦 where this page comes from