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 Variable ybsql Option Description Example
YBDATABASE -d or --dbname Destination database name. Default: yellowbrick

See also SQL Identifiers.

YBHOST -h or --host Destination server host name. Default: localhost
YBPASSWORD -W or --password Interactive prompt for the database user's password. No default.
YBPORT Destination server port number. Default: 5432
YBSSLMODE See SSL Modes. Determines whether or not SSL/TLS is used when connecting to the server and, if so, how.
YBUSER -U or --username Database login username. No default.

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 several modes. For details, see SSL Modes.
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;
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.