Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Mastering the Couchbase N1QL Shell: Connection Management and Security

DZone's Guide to

Mastering the Couchbase N1QL Shell: Connection Management and Security

Couchbase's cbq shell lets you write and run N1QL queries interactively. The shell also lets you securely interact with mixed nodes, among other handy tricks.

· Database Zone ·
Free Resource

MariaDB TX, proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.

This three-part series discusses the new and improved cbq shell. Cbq is the command line shell that helps you write and run N1QL queries and scripts interactively. The N1QL query engine parses, interprets, plans and executes each N1QL statement and returns the results in JSON. This response is available to the shell, which then displays the result. All shell commands start with a backslash, “ \ “. All other commands are treated as N1QL statements. It is important to note that all cbq commands are case insensitive. Command line options, on the other hand, are case sensitive.

The command line options and shell commands that help with scripting can be classified into multiple categories. In this first article of three, we shall talk about Connection Management and Security. The examples provided are specific to Couchbase Server 4.5. Future versions will introduce additional options, commands, and features.

Connection Management

Couchbase can be deployed in multiple configurations. You can have a single node with all the services enabled on that node, or have a multi-node cluster with each node running different services, such as data, query, and/or index. The N1QL shell, cbq, allows you to connect to either a cluster endpoint or a single query service instance running on a specific node. This can be achieved in three ways.

  • Command line option -e / --engine

  • Command line argument, ./cbq <url>(as the last argument to the shell. Details below)

  • Shell command \CONNECT when within a shell session (Details below)

Couchbase Server uses specific network ports for communication. Port 8091, the Administration Port, and 8093, the query service port, are defaults. In order to connect to the cluster, the url will contain port 8091. To connect to a specific query service in order to run a query on that node, the url will contain port 8093.

Screen Shot 2016-12-05 at 1.58.54 PM.png

Suppose you have a multi-node cluster with a mix of services among the nodes-data services, query services, and index services. The cbq shell can connect to any node in the cluster and automatically discover the query services across the cluster. As a user, you do not need to know or care about which nodes are running query services. If you connect to the cluster (details below), the cbq shell will round robin your queries among all the query services in the cluster. This allows you to load balance work among query nodes.

In the diagram above, we have a three node cluster. Node 1 (172.23.100.191) is configured as a data node, Node 2 (172.23.100.192) with all three services (Data, Index and Query) and Node 3 (172.23.100.193) with just Index and Query.

When we use the shell to connect to this cluster, we pass in the URL to a cluster endpoint, namely http://172.23.100.191:8091 as follows:

 ./cbq http://172.23.100.191:8091 

The shell will automatically connect and discover that Node 2 and Node 3 are running query services, and then round robin the queries across these nodes. This saves us the trouble of having to identify query services within a large cluster.

Remember that we can use any node in the cluster:

./cbq http://172.23.100.192:8091

./cbq http://172.23.100.193:8091 

Another advantage of the shell being cluster-aware is that when nodes are added or removed, the shell will continue to auto detect query nodes, and queries will automatically be redirected to the current query nodes in the cluster.

Suppose we have a single node Couchbase deployment that is provisioned with all three services - data, query, and index. This can be seen in the diagram below.

Screen Shot 2016-12-02 at 11.04.26 AM.png

When bringing up cbq, if we don't pass in an argument or a -e option, then we are automatically connected to the default cluster IP, namely localhost and port 8091 or "http://localhost:8091". These are all equivalent:

This being a single node deployment of Couchbase, the shell detects the local query service and queries against that. In the message below, the absence of credentials is not an error, since this server does not have any password-protected buckets. (Accessing such buckets will be discussed in the next section.)

Screen Shot 2016-12-01 at 7.07.52 AM.png

We can also explicitly connect to a specific query node. In the image below, we see a four node cluster.

Screen Shot 2016-12-02 at 11.50.27 AM.png

Say we want to connect to the query node on 172.23.100.192, then we can issue the shell command

./cbq -e=http://172.23.100.192:8093 

OR

cbq >  \CONNECT http://172.23.100.192:8093;

This will now allow us to run all statements against the query service on Node 172.23.100.192.

The URL that we connect to is made up of two components, the prefix and a port number. The prefix can be any valid IP or hostname. The full URL consists of a scheme followed by host and a port.

For the \CONNECT command, if the user is already connected, they are disconnected and connected to the new endpoint. We can close an existing connection without exiting the shell using the \DISCONNECT command. This also allows us to switch between different connections in a given session. If the shell is not connected to any endpoint, then \DISCONNECT throws an error that the shell is not connected.

One option when trying to run the same set of queries between multiple Couchbase clusters (made easy by the history and scripting features of the shell, which will be discussed in a later article in this series), is to bring up the shell without any connection, using the -ne or -no-engine option. Then we can use the \CONNECT command to switch between the different clusters.

Security

Couchbase Server provides security features that allow administrators to ensure secure deployment.

You can use these features with cbq shell in order to interact with Couchbase securely.

cbq provides a set of command line options in order to connect securely to any Couchbase cluster. (Refer to the table below to see all the available options.)

When using SSL secure transport (https:// URL) to connect from the shell, we need to take into account the two kinds of certificates that Couchbase supports – self-signed certificates and CA (Certificate Authority) signed certificates. Once we connect using SSL, we have two courses of action. Either we can skip verifying the certificates returned from the cluster or accept a certificate and verify it. In order to skip verification of the certificates, we use the --no-ssl-verify option. cbq will issue a warning " Disabling SSL verification means that cbq will be vulnerable to man-in-the-middle attacks."

./cbq --no-ssl-verify https://172.23.107.159:18091 

When using SSL-encrypted connections using a self-signed certificate, we need to specify this flag. If not, cbq will throw an error, since it assumes that the certificate is signed by an unknown authority (X509: certificate signed by unknown authority).

Screen Shot 2016-12-02 at 4.23.50 PM.png

An option to pass in certificates, –cacert, will be added to the shell in the future. This will allow the user to specify a CA certificate that will then be used to verify the identity of the server.

In general, even for unencrypted connections, there are two types of credentials defined by the N1QL REST API, bucket credentials and role-based access credentials (for example Administrator). Each of these are passed in differently. In order to pass in role-based credentials we use the -u or --user option along with the -p or --password option. If the password option is not specified on startup, then the user is prompted for the password.

Screen Shot 2016-12-01 at 9.11.22 AM.png




In order to pass in bucket credentials, we can use the --credentials option.

Screen Shot 2016-12-06 at 3.35.12 PM.png

We can also set the creds parameter within a session.

Screen Shot 2016-12-06 at 3.37.45 PM.png

Secure Connections to Protected Clusters With Mixed Configurations

Couchbase data is stored in buckets. A bucket can optionally be protected with a bucket-specific password. The Couchbase cluster could contain only password-protected buckets, a mix of protected and unprotected buckets, or only unprotected buckets. In the cases where we have protected buckets in our Couchbase cluster, we need to pass in role based access credentials and / or bucket-specific credentials.  Here is an example of role-based credentials.

Screen Shot 2016-12-01 at 9.11.52 AM.png




If the cluster deployed has a mix of protected and unprotected buckets, then we can pass in each buckets credentials individually as well. This can be done using the  -c or --credentials option. When passing in these credentials, the format is a comma separated set of username:password. It is important to remember that any credentials passed in are valid only for that session. In Couchbase, the username of each bucket is the bucket name itself.

 ./cbq -c=beer-sample:pass1,travel-sample:pass2

Screen Shot 2016-12-06 at 3.44.51 PM.png

This is very helpful when performing joins or writing subqueries, when the buckets accessed are password protected.

Suppose the user changes the password of a bucket, and wants to update the credentials that he/she has passed to the shell. This can easily be accomplished by using the N1QL REST API parameter creds. This value can be set using the \SET command.

 cbq> \SET -creds beer-sample:pass1,travel-sample:newpass ; 

But it is important to remember that the user needs to pass in the other credentials as well. Even if they haven't changed. This is because the value of the credentials is passed in for every query that is executed. Once we do this, we can now access the secure buckets as well.

In order to see what values have been set for the creds REST API parameter, we can use either the \ECHO command or the \SET command without any input arguments. However the value of the password is not displayed.

Using the \ECHO Command

cbq> \ECHO -creds;

beer-sample:*,travel-sample:* 

Using the \SET Command

This will display all the different parameter settings within the shell session.

cbq> \SET; 

Query Parameters : 

Parameter name : creds 

Value : [ "beer-sample:*, travel-sample:*" ] 

 

Summary

Cbq is a powerful tool. It can manage connections to both a query service and full cluster, and can auto discover the query services in a Couchbase cluster. Queries are executed in a round-robin fashion against all the available query services. The shell also provides secure transport and authentication, so that users can securely access any data to which they are entitled.

Connection Management

Command Line Options

Screen Shot 2016-11-30 at 11.40.19 PM.png






Shell Commands

Screen Shot 2016-11-30 at 11.40.05 PM.png








Security

Command Line Options

Screen Shot 2016-12-01 at 7.43.10 AM.png









MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.

Topics:
couchbase ,n1ql ,cbq shell ,database

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}