Product Documentation

Appearance

ybsql Connections ​

This section describes how to connect to a database with ybsql.

Syntax Summary ​

ybsql [ connection_options ] [ options ]

The order of the general options and connection options may be reversed in the command.

Connection Options ​

[ -h | --host hostname ] 
[ -p | --port portnumber ] 
[ -d | --dbname dbname [ -U | --username username ] ]
[ -W | --password ]
[ -w | --no-password ]
-d, --dbname

Name of the database for the connection. Alternatively, you can specify dbname by itself as the first argument on the command line. For example, all of the following syntax variations are valid, where premdb is the database name:

ybsql premdb
ybsql -d premdb
ybsql --dbname premdb
-U, --username

Name of the database user for the connection. Alternatively, you can specify username by itself as long as the database name precedes it. For example, all of the following syntax variations are valid, where bobr is the username:

ybsql -U bobr premdb
ybsql --username bobr premdb
ybsql premdb bobr
-W, --password

Prompt for the user's password before connecting. Regardless of this option, ybsql prompts for the password automatically when the server requires password authentication. This option remains in effect for the entire session and affects the use of any \connect commands.

-w, --no-password

Do not prompt for the user's password. If the server requires password authentication and a password is not set with the YBPASSWORD environment variable, the connection fails. This option may be useful for non-interactive batch jobs and scripts. It remains in effect for the entire session and affects the use of any \connect commands.

-h, --host

Host name of the appliance.

-p, --port

Port number for the connection (5432 by default).

Environment Variables ​

You can use the following environment variables to set connection parameters for ybsql connections. Note that the following examples are for Windows. For Linux, precede each example with export.

Environment Variableybsql OptionDescriptionExample
YBDATABASE-d or --dbnameDestination database name. Default: yellowbrick See also SQL Identifiers.


YBDATABASE=premdb


YBHOST-h or --hostDestination server host name. Default: localhost
YBHOST=test.ybsystem.io


YBPASSWORD-W or --passwordInteractive prompt for the database user's password. No default.
YBPASSWORD=********


YBPORTDestination server port number. Default: 5432
YBPORT=5433


YBSSLMODEFor a list of the YBSSLMODE options, see below.Determines whether or not SSL/TLS is used when connecting to the server and, if so, how.
YBUSER-U or --usernameDatabase login username. No default.
YBUSER=bobr


SSL/TLS Connection Modes ​

When SSL is enabled on the Yellowbrick appliance, the appropriate SSLMODE must be chosen:

  • By default, ybsql will not perform any server certificate verification, which can be used if a custom certificate has not been installed.
  • If you have chosen an SSLMODE requiring verification, the certificate(s) of one or more trusted CAs must be located in ~/.yellowbrick/root.cert for Linux or %APPDATA%\yellowbrick\root.crt for Windows.
  • These modes are used to prevent being a victim of server identity spoofing.

YBSSLMODE environment variable can be set to any of the following options:

ybsql OptionDescription
allowFirst, attempt a non-SSL connection. If this fails, attempt an SSL connection.
disableOnly attempt a non-SSL connection.
preferFirst attempt an SSL connection. If this fails, attempt a non-SSL connection. prefer is the default option.
requireOnly attempt an SSL connection. If a root CA file is present, verify the certificate as if verify-ca is specified.
verify-caAttempt an SSL connection and verify that the server certificate is issued by a trusted CA. Verify that the server is trustworthy by checking the certificate chain up to a trusted certificate authority (CA).
verify-fullOnly attempt an SSL connection. Verify that the server certificate is issued by a trusted CA and that the server hostname matches that in the certificate. Verify that the server host name matches its certificate's common name (CN) attribute. If the CN attribute starts with an asterisk (*), it is a wildcard and will match all characters except a dot (.). This means the certificate will not match subdomains and will not match if an IP address is used instead of a hostname. verify-full is recommended in most security-sensitive environments.

Important: If a YBSSLMODE environment variable is not set but an SSLMODE environment variable is, ybsql will use the SSLMODE environment variable.

Connecting with a Secure Password ​

You can use the ybsql \password command to reset a password for a user securely. Passwords set or changed with this command are not exposed in clear text or saved to any log files. For example:

premdb=# create user jamesbond;
CREATE ROLE
premdb=# \password jamesbond
Enter new password: 
Enter it again: 
...
premdb=# \c premdb jamesbond
Password for user jamesbond: 
You are now connected to database "premdb" as user "jamesbond".
...

CAUTION:

If you create and update unencrypted passwords with CREATE ROLE and ALTER ROLE commands, passwords are transmitted in clear text and may appear in log messages.

Parent topic:ybsql Reference