.\" Automatically generated by Pod::Man 4.11 (Pod::Simple 3.35) .\" .\" Standard preamble: .\" ======================================================================== .de Sp \" Vertical space (when we can't use .PP) .if t .sp .5v .if n .sp .. .de Vb \" Begin verbatim text .ft CW .nf .ne \\$1 .. .de Ve \" End verbatim text .ft R .fi .. .\" Set up some character translations and predefined strings. \*(-- will .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left .\" double quote, and \*(R" will give a right double quote. \*(C+ will .\" give a nicer C++. Capital omega is used to do unbreakable dashes and .\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff, .\" nothing in troff, for use with C<>. .tr \(*W- .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' .ie n \{\ . ds -- \(*W- . ds PI pi . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch . ds L" "" . ds R" "" . ds C` "" . ds C' "" 'br\} .el\{\ . ds -- \|\(em\| . ds PI \(*p . ds L" `` . ds R" '' . ds C` . ds C' 'br\} .\" .\" Escape single quotes in literal strings from groff's Unicode transform. .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" .\" If the F register is >0, we'll generate index entries on stderr for .\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index .\" entries marked with X<> in POD. Of course, you'll have to process the .\" output yourself in some meaningful fashion. .\" .\" Avoid warning from groff about undefined register 'F'. .de IX .. .nr rF 0 .if \n(.g .if rF .nr rF 1 .if (\n(rF:(\n(.g==0)) \{\ . if \nF \{\ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . if !\nF==2 \{\ . nr % 0 . nr F 2 . \} . \} .\} .rr rF .\" .\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2). .\" Fear. Run. Save yourself. No user-serviceable parts. . \" fudge factors for nroff and troff .if n \{\ . ds #H 0 . ds #V .8m . ds #F .3m . ds #[ \f1 . ds #] \fP .\} .if t \{\ . ds #H ((1u-(\\\\n(.fu%2u))*.13m) . ds #V .6m . ds #F 0 . ds #[ \& . ds #] \& .\} . \" simple accents for nroff and troff .if n \{\ . ds ' \& . ds ` \& . ds ^ \& . ds , \& . ds ~ ~ . ds / .\} .if t \{\ . ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u" . ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u' . ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u' . ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u' . ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u' . ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u' .\} . \" troff and (daisy-wheel) nroff accents .ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V' .ds 8 \h'\*(#H'\(*b\h'-\*(#H' .ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#] .ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H' .ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u' .ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#] .ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#] .ds ae a\h'-(\w'a'u*4/10)'e .ds Ae A\h'-(\w'A'u*4/10)'E . \" corrections for vroff .if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u' .if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u' . \" for low resolution devices (crt and lpr) .if \n(.H>23 .if \n(.V>19 \ \{\ . ds : e . ds 8 ss . ds o a . ds d- d\h'-1'\(ga . ds D- D\h'-1'\(hy . ds th \o'bp' . ds Th \o'LP' . ds ae ae . ds Ae AE .\} .rm #[ #] #H #V #F C .\" ======================================================================== .\" .IX Title "sqitch-authentication 3pm" .TH sqitch-authentication 3pm "2020-07-19" "perl v5.30.3" "User Contributed Perl Documentation" .\" For nroff, turn off justification. Always turn off hyphenation; it makes .\" way too many mistakes in technical documents. .if n .ad l .nh .SH "Name" .IX Header "Name" sqitch-authentication \- Guide to using database authentication credentials with Sqitch .SH "Description" .IX Header "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. .SH "Usernames" .IX Header "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: .ie n .IP "1. In the $SQITCH_USERNAME environment variable" 4 .el .IP "1. In the \f(CW$SQITCH_USERNAME\fR environment variable" 4 .IX Item "1. In the $SQITCH_USERNAME environment variable" .PD 0 .ie n .IP "2. Via the ""\-\-db\-username"" option" 4 .el .IP "2. Via the \f(CW\-\-db\-username\fR option" 4 .IX Item "2. Via the --db-username option" .IP "3. In the deploy target \s-1URI\s0; this is the preferred option" 4 .IX Item "3. In the deploy target URI; this is the preferred option" .IP "4. In an engine-specific environment variable or configuration" 4 .IX Item "4. In an engine-specific environment variable or configuration" .PD .PP Naturally, this last option varies by database engine. The details are as follows: .IP "PostgreSQL" 4 .IX Item "PostgreSQL" The Postgres engine uses the \f(CW\*(C`PGUSER\*(C'\fR environment variable, if set. Otherwise, it uses the system username. .IP "MySQL" 4 .IX Item "MySQL" For MySQL, if the MySQL::Config module is installed, usernames and passwords can be specified in the \&\fI/etc/my.cnf\fR and \fI~/.my.cnf\fR files . These files must limit access only to the current user (\f(CW0600\fR). Sqitch will look for a username and password under the \f(CW\*(C`[client]\*(C'\fR and \f(CW\*(C`[mysql]\*(C'\fR sections, in that order. .IP "Oracle" 4 .IX Item "Oracle" Oracle provides no default to search for a username. .IP "Vertica" 4 .IX Item "Vertica" The Vertica engine uses the \f(CW\*(C`VSQL_USER\*(C'\fR environment variable, if set. Otherwise, it uses the system username. .IP "Firebird" 4 .IX Item "Firebird" The Firebird engine uses the \f(CW\*(C`ISC_USER\*(C'\fR environment variable, if set. .IP "Exasol" 4 .IX Item "Exasol" Exasol provides no default to search for a username. .IP "Snowflake" 4 .IX Item "Snowflake" The Snowflake engine uses the \f(CW\*(C`SNOWSQL_USER\*(C'\fR environment variable, if set. Next, it looks in the \&\fI~/.snowsql/config\fR file and use the default \f(CW\*(C`connections.username\*(C'\fR value. Otherwise, it uses the system username. .SH "Passwords" .IX Header "Passwords" You may have noticed that Sqitch has no \f(CW\*(C`\-\-password\*(C'\fR 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: .IP "1. Avoid using a password at all" 4 .IX Item "1. Avoid using a password at all" .PD 0 .IP "2. Use a database engine-specific password file" 4 .IX Item "2. Use a database engine-specific password file" .ie n .IP "3. Use the $SQITCH_PASSWORD environment variable" 4 .el .IP "3. Use the \f(CW$SQITCH_PASSWORD\fR environment variable" 4 .IX Item "3. Use the $SQITCH_PASSWORD environment variable" .IP "4. Include the password in the deploy target \s-1URI\s0" 4 .IX Item "4. Include the password in the deploy target URI" .PD .PP Each is covered in detail in the sections below. .SS "Don't use Passwords" .IX Subsection "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: .IP "PostgreSQL" 4 .IX Item "PostgreSQL" PostgreSQL supports a number of authentication methods , including the passwordless \s-1SSL\s0 certificate , \s-1GSSAPI\s0 , and, for local connections, peer authentication . .IP "MySQL" 4 .IX Item "MySQL" MySQL supports a number of authentication methods , plus \s-1SSL\s0 authentication . .IP "Oracle" 4 .IX Item "Oracle" Oracle supports a number of authentication methods , including \&\s-1SSL\s0 authentication , third-party authentication , and, for local connections, \&\s-1OS\s0 authentication . .IP "Vertica" 4 .IX Item "Vertica" Vertica supports a number of authentication methods including the passwordless \s-1TLS\s0 authentication , \&\s-1GSS\s0 authentication , and, for local connections, ident authentication . .IP "Firebird" 4 .IX Item "Firebird" Firebird supports passwordless authentication only via trusted authentication for local connections. .IP "Exasol" 4 .IX Item "Exasol" Exasol doesn't seem to support password-less authentication at this time; for other options, see the documentation . .IP "Snowflake" 4 .IX Item "Snowflake" Snowflake does not support password-less authentication, but does support key-pair authentication. Follow the instructions to create a key pair, then set the following variables in the \fI~/.snowsql/config\fR file: .Sp .Vb 2 \& authenticator = SNOWFLAKE_JWT \& private_key_path = "path/to/privatekey.p8" .Ve .Sp To connect, set the \f(CW$SNOWSQL_PRIVATE_KEY_PASSPHRASE\fR environment variable to the passphrase for the private key, and add these parameters to the query part of your connection \s-1URI:\s0 .RS 4 .IP "\(bu" 4 \&\f(CW\*(C`authenticator=SNOWFLAKE_JWT\*(C'\fR .IP "\(bu" 4 \&\f(CW\*(C`uid=$username\*(C'\fR .IP "\(bu" 4 \&\f(CW\*(C`priv_key_file=path/to/privatekey.p8\*(C'\fR .IP "\(bu" 4 \&\f(CW\*(C`priv_key_file_pwd=$private_key_password\*(C'\fR .RE .RS 4 .Sp For example: .Sp .Vb 1 \& 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 .Ve .RE .SS "Use a Password File" .IX Subsection "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: .IP "PostgreSQL" 4 .IX Item "PostgreSQL" PostgreSQL will use a \&\fI.pgpass\fR file in the user's home directory to or referenced by the \f(CW$PGPASSFILE\fR environment variable. This file must limit access only to the current user (\f(CW0600\fR) and contains lines specify authentication rules as follows: .Sp .Vb 1 \& hostname:port:database:username:password .Ve .IP "MySQL" 4 .IX Item "MySQL" For MySQL, if the MySQL::Config module is installed, usernames and passwords can be specified in the \&\fI/etc/my.cnf\fR and \fI~/.my.cnf\fR files . These files must limit access only to the current user (\f(CW0600\fR). Sqitch will look for a username and password under the \f(CW\*(C`[client]\*(C'\fR and \f(CW\*(C`[mysql]\*(C'\fR sections, in that order. .IP "Oracle" 4 .IX Item "Oracle" Oracle supports password file created with the \f(CW\*(C`ORAPWD\*(C'\fR utility to authenticate \f(CW\*(C`SYSDBA\*(C'\fR and \f(CW\*(C`SYSOPER\*(C'\fR users, but \fBSqitch is unable to take advantage of this functionality.\fR Neither can one embed a username and password into a \&\fItnsnames.ora\fR file. .IP "Vertica" 4 .IX Item "Vertica" Vertica does not currently support a password file. .IP "Firebird" 4 .IX Item "Firebird" Firebird does not currently support a password file. .IP "Exasol" 4 .IX Item "Exasol" Exasol allows configuring connection profiles for the 'exaplus' client: .Sp .Vb 2 \& > 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 .Ve .Sp These profiles are stored in \fI~/.exasol/profiles.xml\fR, readable only to the user by default. See the documentation for more information on connection profiles, specifically the EXAplus section in the chapter on \*(L"Clients and interfaces\*(R". .Sp For \s-1ODBC\s0 connections from Sqitch, we can use connection settings in \fI~/.odbc.ini\fR: .Sp .Vb 5 \& [flipr_test] \& DRIVER = Exasol \& EXAHOST = localhost:8563 \& EXAUID = sys \& EXAPWD = exasol .Ve .Sp When combining the above, Sqitch doesn't need to know any credentials; they are stored somewhat safely in \fI~/.exasol/profiles.xml\fR and \fI~/.odbc.ini\fR: .Sp .Vb 11 \& > sqitch status db:exasol:flipr_test \& # On database db:exasol:flipr_test \& # Project: flipr \& # ... \& # \& Nothing to deploy (up\-to\-date) \& >\ sqitch rebase \-\-onto \*(Aq@HEAD^\*(Aq \-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 .Ve .IP "Snowflake" 4 .IX Item "Snowflake" For Snowflake, Sqitch will read the \&\fI~/.snowsql/config\fR file and use the default connections settings; named connections are not supported. An example: .Sp .Vb 8 \& [connections] \& accountname = myaccount \& region = us\-east\-1 \& warehousename = compute \& username = frank \& password = fistula postmark bag \& rolename = ACCOUNTADMIN \& dbname = reporting .Ve .Sp The variables that Sqitch currently reads are: .RS 4 .ie n .IP """connections.accountname""" 4 .el .IP "\f(CWconnections.accountname\fR" 4 .IX Item "connections.accountname" .PD 0 .ie n .IP """connections.username""" 4 .el .IP "\f(CWconnections.username\fR" 4 .IX Item "connections.username" .ie n .IP """connections.password""" 4 .el .IP "\f(CWconnections.password\fR" 4 .IX Item "connections.password" .ie n .IP """connections.rolename""" 4 .el .IP "\f(CWconnections.rolename\fR" 4 .IX Item "connections.rolename" .ie n .IP """connections.region""" 4 .el .IP "\f(CWconnections.region\fR" 4 .IX Item "connections.region" .ie n .IP """connections.warehousename""" 4 .el .IP "\f(CWconnections.warehousename\fR" 4 .IX Item "connections.warehousename" .ie n .IP """connections.dbname""" 4 .el .IP "\f(CWconnections.dbname\fR" 4 .IX Item "connections.dbname" .RE .RS 4 .RE .PD .ie n .SS "Use $SQITCH_PASSWORD" .el .SS "Use \f(CW$SQITCH_PASSWORD\fP" .IX Subsection "Use $SQITCH_PASSWORD" The \f(CW$SQITCH_PASSWORD\fR 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 \f(CW\*(C`ps\*(C'\fR. .PP The behavior of \f(CW$SQITCH_PASSWORD\fR is consistent across all supported engines, as is the complementary \f(CW$SQITCH_USERNAME\fR 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: .IP "PostgreSQL" 4 .IX Item "PostgreSQL" \&\f(CW$PGPASSWORD\fR .IP "MySQL" 4 .IX Item "MySQL" \&\f(CW$MYSQL_PWD\fR .IP "Vertica" 4 .IX Item "Vertica" \&\f(CW$VSQL_PASSWORD\fR .IP "Firebird" 4 .IX Item "Firebird" \&\f(CW$ISC_PASSWORD\fR .IP "Snowflake" 4 .IX Item "Snowflake" \&\f(CW$SNOWSQL_PWD\fR .SS "Use Target URIs" .IX Subsection "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 \f(CW\*(C`ps\*(C'\fR 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). .PP Such URIs can either be specified on the command-line: .PP .Vb 1 \& sqitch deploy db:pg://fred:s3cr3t@db.example.com/widgets .Ve .PP Or stored as named targets in the project configuration file: .PP .Vb 1 \& sqitch target add wigets db:pg://fred:s3cr3t@db.example.com/widgets .Ve .PP After which the target is available by its name: .PP .Vb 1 \& sqitch deploy widgets .Ve .PP See sqitch-targets and \f(CW\*(C`sqitch\-configuration\*(C'\fR for details on target configuration. .SH "See Also" .IX Header "See Also" .IP "\(bu" 4 sqitch-environment .IP "\(bu" 4 sqitch-configuration .IP "\(bu" 4 sqitch-target .SH "Sqitch" .IX Header "Sqitch" Part of the sqitch suite.