view release on metacpan or  search on metacpan

lib/sqitch-authentication.pod  view on Meta::CPAN

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:

=over

=item PostgreSQL

PostgreSQL supports a number of
L<authentication methods|https://www.postgresql.org/docs/current/static/auth-methods.html>,
including the passwordless L<SSL certificate|https://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-CERT>, L<GSSAPI|https://www.postgresql.org/docs/current/static/auth-methods.html#GSSAPI-AUTH>, and, for local connections,
L<peer authentication|https://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-PEER>.

=item MySQL

MySQL supports a number of
L<authentication methods|https://dev.mysql.com/doc/internals/en/authentication-method.html>,
plus L<SSL authentication|https://dev.mysql.com/doc/internals/en/ssl.html>.

=item Oracle

Oracle supports a number of
L<authentication methods|https://docs.oracle.com/cd/B19306_01/network.102/b14266/authmeth.htm#BABCGGEB>,
including
L<SSL authentication|https://docs.oracle.com/cd/B19306_01/network.102/b14266/authmeth.htm#i1009722>,
L<third-party authentication|https://docs.oracle.com/cd/B19306_01/network.102/b14266/authmeth.htm#i1009853>,
and, for local connections,
L<OS authentication|https://docs.oracle.com/cd/B19306_01/network.102/b14266/authmeth.htm#i1007520>.

=item Vertica

Vertica supports a number of
L<authentication methods|https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Security/ClientAuth/SupportedClientAuthenticationMethods.htm>
including the passwordless L<TLS authentication|https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Security/ClientAuth/ConfiguringTLSAuthentication.htm>,
L<GSS authentication|https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Security/ClientAuth/Kerberos/ImplementingKerberosAuthentication.htm>,
and, for local connections,
L<ident authentication|https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Security/ClientAuth/ConfiguringIdentAuthentication.htm>.

=item Firebird

Firebird supports passwordless authentication only via
L<trusted authentication|https://www.firebirdsql.org/manual/qsg2-config.html>
for local connections.

=item Exasol

Exasol doesn't seem to support password-less authentication at this time,
though there is support for
L<Authentication using OpenID|https://docs.exasol.com/sql/create_user.htm#Authenti4>.
To use it with Sqitch, include C<AUTHMETHOD=refreshtoken> in the target URI
query string, e.g.,

  db:exasol://sys:exasol@localhost:8563/?Driver=Exasol&AUTHMETHOD=refreshtoken

=item Snowflake

Snowflake does not support password-less authentication, but does support
key-pair authentication. Follow
L<the instructions|https://docs.snowflake.com/en/user-guide/key-pair-auth>
to create a key pair, then set the C<private_key_path> in the F<~/.snowsql/config>
to point to the private key file:

  private_key_path = "<path>/rsa_key.p8"

To connect, set the C<$SNOWSQL_PRIVATE_KEY_PASSPHRASE> environment variable to
the passphrase for the private key, and add these parameters under the
configuration for your DSN in F</etc/odbc.ini> or F<~/.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 C<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 C<priv_key_file_pwd> in F<odbc.ini>):

=over

=item * C<authenticator=SNOWFLAKE_JWT>

=item * C<uid=$username>

=item * C<priv_key_file=path/to/privatekey.p8>

=item * C<priv_key_file_pwd=$private_key_password>

=back

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 C<SNOWSQL_PRIVATE_KEY_PASSPHRASE> environment variable and
the C<priv_key_file_pwd> ODBC parameter must be set, as Sqitch uses ODBC to
maintain its registry and SnowSQL to execute change scripts.

=item ClickHouse

ClickHouse does not support password-less authentication, but does support
L<TLS Client authentication| https://clickhouse.com/docs/guides/sre/ssl-user-auth>.
Sqitch reads the C<$.openSSL.client> section of the
C<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
C<ODBC parameters https://github.com/clickHouse/clickhouse-odbc?tab=readme-ov-file#configuration>
as follows:

=over

=item C<privateKeyFile>: C<PrivateKeyFile>

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

=item C<certificateFile>: C<CertificateFile>

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

=item C<caConfig>: C<CALocation>

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

=item C<secure>, C<port>, C<host>, C<verificationMode>: C<SSLMode>

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

   verificationMode | SSLMode
  ------------------|-----------
   none             | [not set]