view release on metacpan or  search on metacpan

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

=encoding UTF-8

=head1 Name

sqitch-authentication - Guide to using database authentication credentials with Sqitch

=head1 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.

=head1 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:

=over

=item 1. In the C<$SQITCH_USERNAME> environment variable

=item 2. Via the C<--db-username> option

=item 3. In the deploy target URI; this is the preferred option

=item 4. In an engine-specific environment variable or configuration

=back

Naturally, this last option varies by database engine. The details are as
follows:

=over

=item PostgreSQL, YugabyteDB, CockroachDB

The Postgres, Yugabyte, and Cockroach engines use the C<PGUSER> environment
variable, if set. Otherwise, it uses the system username.

=item MySQL

For MySQL, if the L<MySQL::Config> module is installed, usernames and
passwords can be specified in the
L<F</etc/my.cnf> and F<~/.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 (C<0600>). Sqitch will
look for a username and password under the C<[client]> and C<[mysql]>
sections, in that order.

=item Oracle

Oracle provides no default to search for a username.

=item Vertica

The Vertica engine uses the C<VSQL_USER> environment variable, if set.
Otherwise, it uses the system username.

=item Firebird

The Firebird engine uses the C<ISC_USER> environment variable, if set.

=item Exasol

Exasol provides no default to search for a username.

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

contains lines specify authentication rules as follows:

  hostname:port:database:username:password

=item MySQL

For MySQL, if the L<MySQL::Config> module is installed, usernames and
passwords can be specified in the
L<F</etc/my.cnf> and F<~/.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 (C<0600>). Sqitch will
look for a username and password under the C<[client]> and C<[mysql]>
sections, in that order.

=item Oracle

Oracle supports
L<password file|https://docs.oracle.com/cd/B28359_01/server.111/b28310/dba007.htm#ADMIN10241>
created with the C<ORAPWD> utility to authenticate C<SYSDBA> and C<SYSOPER>
users, but B<Sqitch is unable to take advantage of this functionality.> Neither can
one L<embed a username and password|https://stackoverflow.com/q/7183513/79202>
into a
L<F<tnsnames.ora>|https://docs.oracle.com/cd/B28359_01/network.111/b28317/tnsnames.htm#NETRF007>
file.

=item Vertica

Vertica does not currently support a password file.

=item Firebird

Firebird does not currently support a password file.

=item 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 F<~/.exasol/profiles.xml>, readable only to the user
by default. See the L<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 F<~/.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 F<~/.exasol/profiles.xml> and F<~/.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

=item Snowflake

For Snowflake, Sqitch will read the
L<F<~/.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:

=over

=item C<connections.accountname>

=item C<connections.username>

=item C<connections.password>

=item C<connections.rolename>

=item C<connections.region> (Deprecated by Snowflake)

=item C<connections.warehousename>

=item C<connections.dbname>

=back

=back

=head2 Use C<$SQITCH_PASSWORD>

The C<$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 C<ps>.

The behavior of C<$SQITCH_PASSWORD> is consistent across all supported
engines, as is the complementary C<$SQITCH_USERNAME> environment variable.