Product Documentation

Appearance

All platforms

ybsql Connections ​

This section describes options for connecting to a database with ybsql. See also Setting Up a Database Connection.

Syntax Summary ​

ybsql [ connection_options ] [ other 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 ]
[ --cluster clustername ]
[ -U | --username username ] 
[ -W | --password ]
[ -w | --no-password ]
-h, --host

Host name of the data warehouse instance.

-p, --port

Port number for the connection (5432 by default).

-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
--cluster

Name of the compute cluster for the connection.

-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

If you do not specify a user, the default user is the current OS user on the client system.

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

Security Options ​

See also the discussion of SSL modes in Secure Connections for ODBC/JDBC Clients and ybsql.

--cacert STRING

Customize trust with secured communication. Enter the file name of a custom PEM-encoded certificate or the file name and password for a Java KeyStore (JKS).

For PEM format, the file must be named with a .pem, .cert, .cer, .crt, or .key extension. For example:

--cacert cacert.pem

For JKS format, files are always password-protected. Use the following format:

--cacert yellowbrick.jks:changeit

where the : character separates the file name from the password of the keystore.

See also Verifying SSL/TLS Encryption.

--disable-trust

Disable SSL/TLS trust when using secured communications. Trust is enabled by default. See also Verifying SSL/TLS Encryption.

Important: This option is not supported for use on production systems and is only recommended for testing purposes. It may be useful to disable trust during testing, then enable it when a formal signed certificate is installed on the cluster.

External Authentication Options ​

--auth-browser

Authenticate the connection by opening a browser window on the client machine and entering a password. -U username is required when you use this option. You may need to make sure that your client system is configured to open a default browser. If a browser is not available (ie. restricted console, ssh), fallback to device login if the authorization endpoint supports it.

--auth-device

Authenticate the connection by device login flow, utilizing a browser URL. You may use this option on a restricted console, copy the URL given and code, and proceed to authenticate with a separate browser or device that has a browser.

--auth-direct

Authenticate the connection directly from the ybtools command (as opposed to opening a browser). The -U username option and YBPASSWORD, --password, or -W are required when you use direct authentication.

--auth-issuer

Specify the URL of the OAuth/OIDC issuer. This URL belongs to the identity provider (IDP) that issues JWT tokens for user connections. The IDP checks that the user exists in the specified realm, then returns a JWT token, which is a short-lived password for authentication for that user. Alternatively, you can set the YBAUTHISSUER environment variable.

This option must be specified for any kind of OAuth/OIDC connection.

For example:

--auth-issuer https://cn.beta.yellowbrickcloud.com/auth/realms/mycompany.com

where cn.beta.yellowbrickcloud.com/auth is the Yellowbrick IDP, which has some number of realms, such as mycompany.com.

--auth-scopes

Supply override for scopes requested when authenticating. Some IDPs such as Azure AD require this to be specified as <client_id>/.default, which returns an access token that can be validated by an external resource owner, such as Yellowbrick Database. Alternatively, you can set the YBAUTHSCOPES environment variable.

--auth-token AUTH_TOKEN

Supply a JWT string in the command line explicitly. Alternatively, you can set the YBAUTHTOKEN environment variable.

--client-id

Specify the Yellowbrick Data Warehouse client ID, which is ybauth. Alternatively, you can set the YBCLIENTID environment variable.

This option is required for OAuth/OIDC authentication.

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.

ybsql URI Syntax ​

ybsql supports an alternative PostgreSQL connection method that uses a connection string of the following form:

ybsql postgresql://password:user@host/dbname

The keywords postgresql and postgres are both supported at the beginning of the connection string.

For example:

% ybsql postgres://bobrum:trebor@ybbob-azure.testue1.az.yellowbrickcloud.com/premdb
ybsql (6.1.12)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type:  \h for help with SQL commands
      \? for help with ybsql commands
      \g or terminate with semicolon to execute query
      \q to quit

In this example:

  • User = bobrum
  • Password = trebor
  • Host = ybbob-azure.testue1.az.yellowbrickcloud.com (where ybbob-azure is the data warehouse instance name)
  • Database name= premdb

You can use this connection string in combination with environment variables that are already set. For example, if you have YBPASSWORD set:

% ybsql postgresql://bobrum@ybbob-azure.testue1.az.yellowbrickcloud.com/premdb

For example, if you have YBPASSWORD and YBUSER set:

% ybsql postgresql://ybbob-azure.testue1.az.yellowbrickcloud.com/premdb

Configuration parameter variables can be set at the end of the connection string. Depending on your shell, you may need to use quotes around the connection string when you specify variables. For example:

% ybsql 'postgresql://bobrum:bobrum@yb-demo-azure.produe1.az.yellowbrickcloud.com/premdb?connect_timeout=5&application_name=YBSQLMAC'

Additional keywords may be specified, as documented for PostgreSQL (psql); however, some of these keywords may not be meaningful for a ybsql connection.

Keyword/Value Connection Strings ​

A similar connection method to the URI syntax uses key/value pairs. The same keywords may be used. For example:

% ./ybsql 'host=ybbob-azure.testue1.az.yellowbrickcloud.com user=bobrum password=bobrum dbname=premdb'