Using Role-Based Access Control in N1QL

In Couchbase 4.5, data was secured bucket by bucket. Each bucket had a password, and in order to access a bucket through a N1QL query, the user had to include the bucket password with the query. Passwordless buckets were possible, but not recommended for production use.

This scheme offered basic protection, but it was very coarse-grained: a user could either do everything with a bucket, or nothing at all. Also, queries accessing many buckets required passing in a password for every bucket, which was inconvenient, particularly if passwords changed.

To allow our users to avoid these problems, we introduced role-based access control (RBAC) in Couchbase Server 5.0. Access to data is now controlled through fine-grained roles on buckets, and every user can be assigned roles that fit what they should actually be doing in the database.

In this article I will walk you through the main functionality offered by RBAC in Couchbase Server 5.0. Hands-on exercises will let you try out the new features for yourself.

Setup

For the hands-on exercises, you will need to install Couchbase Server 5.0, Enterprise Edition. During installation, configure it to have an administrator named “Administrator” with password “password”. This is not recommended practice in a production environment, of course.

Log in to the administration console (at http://localhost:8091) and go to the Buckets screen. There create two buckets, “testbucket1” and “testbucket2”, each with a memory quota of 100 MB to avoid running out of memory.

Then go to the Query screen and create primary indexes on the two buckets you have just created. The primary indexes let you run queries on the buckets.

1

2

CREATE PRIMARY INDEX ON testbucket1

CREATE PRIMARY INDEX ON testbucket2

You can go the Indexes screen to verify that the indexes were actually created. Both will be named “#primary”.

Main Roles

There are four main roles that control who can execute what N1QL queries. The roles query_select, query_insert, query_update, and query_delete each let you execute N1QL queries of the corresponding type. There are also three more specialized roles (query_manage_index, query_system_catalog, and query_external_access) that will be covered in the next section, Important Additional Roles.

But there’s more to it. These roles are parameterized by the bucket they apply to. A user can’t just have role query_select; we need to know what bucket the role is on, such as query_select[testbucket1], which lets the user run a SELECT query against testbucket1. It is also possible to have this role on all buckets, indicated by query_select[*].

To try this yourself, go into the Couchbase console and in the Security screen create a new user user1 with password “password” and role query_select[testbucket1].

The role is found under “Query Roles” in the user creation menu.

Then do the same for “user2”, but give that user query_select[testbucket2].

Now let’s try running a query against these buckets using the roles. I’ll use the CBQ shell tool that comes with Couchbase.

1

cbq-ehttp://localhost:8093/ -s "select * from testbucket1"

That get us this response:

1

2

3

4

5

6

7

8

9

{

"requestID":"d838da35-24a3-415e-b9e1-69ac02a9820b",

"signature":{"*":"*"},

"results":[

],

"errors":[{"code":13014,"msg":"User does not have credentials to run SELECT queries on the testbucket1 bucket. Add role query_select on testbucket1 to allow the query to run."}],

Try the query yourself with user2, who has the right role but on the wrong bucket. The request will fail.

Now let’s try a more complex query that touches two buckets. Start by running this query:

1

2

3

cbq-ehttp://localhost:8093/

-s"INSERT INTO testbucket2 (KEY source.c_id, VALUE source.customer)

SELECT * FROM testbucket1 source WHERE source.new = true"

The system tells us it needs role query_insert[testbucket2]:

1

2

"errors":[{"code":13014,"msg":"User does not have credentials to run INSERT queries

on the testbucket2 bucket. Add role query_insert on testbucket2 to allow the query to run."}],

Create a new user “user3” with the required role, and try again:

1

2

"errors":[{"code":13014,"msg":"User does not have credentials to run SELECT queries

on the testbucket1 bucket. Add role query_select on testbucket1 to allow the query to run."}],

We are still getting an error, but it is a different error than before. The query we are trying to run has two parts; it selects from testbucket1 and inserts into testbucket2. We have provided enough credentials for the insertion but not for the selection. Go into the console, add query_select[testbucket2] to user3 and try again. This time the query works.

"errors":[{"code":13014,"msg":"User does not have credentials to run SELECT queries

on the testbucket2 bucket. Add role query_select on testbucket2 to allow the query to run."}],

What’s going on here is that the RETURNING clause is being treated as a SELECT on the bucket we inserted into (testbucket2), occurring just after the insertion. The user does not have the query_select[testbucket2] role and is therefore being refused.

Add role query_select[testbucket2] to user3 and rerun the query. It will now work.

query_system_catalog In earlier versions of Couchbase, the system tables were fully accessible to anyone. They are now more restricted in various ways that will be described later in this document, but there is a useful role, query_system_catalog, that allows for access to the system tables. This is particularly useful for staff who need to be able to debug problems with queries or the system, but shouldn’t be given full administrator privileges.

If we query system:keyspaces as administrator, we see both of the bucket we have created in the system:

Create a new user “user5” with role query_system_catalog, and rerun the query. You will see the same results as when we ran the query as Administrator.

Query_external_access

The new CURL() function makes it possible to query REST services from within N1QL. To use the function, we need to do some configuration, and run the query using the new role query_external_access.

Let’s try the query first:

1

2

3

cbq-ehttp://localhost:8093/query/service

-s'SELECT CURL("https://maps.googleapis.com/maps/api/geocode/json",

{"data":"address=Half+Moon+Bay", "get":true}) GEO'

The query is refused because appropriate permissions are not supplied:

1

2

"errors":[{"code":13014,"msg":"User does not have credentials to run queries using

the CURL() function. Add role query_external_access to allow the query to run."}]

Create a user “user6” with role query_external_access and rerun the query using that user. This time the query refuses to run because we have not enabled CURL(); for security reasons it is disabled by default.

Create a file “curl_whitelist.json” with the following contents:

1

{"all_access":true}

Place it under the couchbase directory. On a Mac, the exact location is “/Applications/Couchbase Server.app/Contents/Resources/couchbase-core/var/lib/couchbase/n1qlcerts/curl_whitelist.json”. On other systems, the location is slightly different. Try the query again, which should run correctly, downloading data like this:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

"address_components":[

{

"long_name":"Half Moon Bay",

"short_name":"Half Moon Bay",

"types":["locality","political"]

},

{

"long_name":"San Mateo County",

"short_name":"San Mateo County",

"types":["administrative_area_level_2","political"]

},

{

"long_name":"California",

"short_name":"CA",

"types":["administrative_area_level_1","political"]

},

{

"long_name":"United States",

"short_name":"US",

"types":["country","political"]

}

The full list of N1QL roles is as follows:

Role

Permits

GRANT Syntax

Query Select

SELECT Statements

GRANT select ON testbucket TO myuser

Query Insert

INSERT Statements

GRANT insert ON testbucket TO myuser

Query Update

UPDATE Statements

GRANT update ON testbucket TO myuser

Query Delete

DELETE Statements

GRANT delete ON testbucket to myuser

Query System Catalog

Access to system keyspaces

GRANT query_system_catalog TO myuser

Query Manage Index

Index Operations

GRANT query_manage_index ON testbucket TO myuser

Query External Access

CURL() Function

GRANT query_external_access TO myuser

EXPLAIN and PREPARE

Of course, we can do things with queries other than just running them. We can use EXPLAIN to understand how the query engine computes a query. And we can use PREPARE to set up a query for repeated execution.

In Couchbase 5.0, the permissions for EXPLAIN and PREPARE are simple. They are the same as the permissions required for the underlying statement. That’s all there is to it.

To test this, let’s try to EXPLAIN a simple SELECT statement, the same one we used in the earlier “Primary Roles” section:

1

cbq-ehttp://localhost:8093/ -s "explain select * from testbucket1"

In response we get an error message asking for a specific role:

1

2

"errors":[{"code":13014,"msg":"User does not have credentials to run SELECT queries

on the testbucket1 bucket. Add role query_select on testbucket1 to allow the query to run."}],

Comparison with Oracle

Role-based access control in Couchbase 5.0 has been designed to be familiar to professionals who have used access control in other current database systems. By now, you should have recognized familiar concepts such as insert/select/update/delete permissions being applicable to buckets, which are sort of like tables. Also, the notion of administrator users with broad permissions should be familiar. Role-based access control in Couchbase 5.0 has been designed to be familiar to professionals who have used access control in other current database systems. By now, you should have recognized familiar concepts such as insert/select/update/delete permissions being applicable to buckets, which are sort of like tables. Also, the notion of administrator users with broad permissions should be familiar.

For example, this GRANT query will work on both Couchbase and Oracle:

1

GRANT select,insert ON defaultTOjlarson,pedwards

One conceptual difference between Couchbase and other systems such as Oracle is that we have a binary model of privilege: users and roles. They typically have a trinary model, with users, roles, and privileges. Accordingly, giving users permissions in Oracle is about granting privileges on objects to users. In Couchbase, it’s about granting parameterized roles to users.

Roles are static in 5.0. There is no equivalent of statements to CREATE/DROP/ALTER ROLE.

Couchbase permissions are either on the system as a whole or on buckets. We have no notion of permissions on tables, columns, or collections.

In Couchbase GRANT and REVOKE are only possible by Admin users. There is no such thing as an ADMIN OPTION or a GRANT OPTION. Changing permissions requires you to be an Administrator.

Finally, we have no equivalent of SET ROLE. SET ROLE is a session-level command, which makes no sense in Couchbase because we have no sessions, only individual RESTful requests.

New System Keyspaces

Couchbase 5.0 introduces three new system keyspaces related to users. The first of these is system:user_info. This keyspace lists the users configured in the system, including their roles. Let’s try a query:

At the other end of security are system:user_info and system:applicable_roles, which contain the list of users and their roles. These are accessible only by Admin and Read Only Admin users. The system:my_user_info table is similar, but only shows the relevant information for the user whose credentials are presented with the query. We have already explored these tables, above.

The next four system keyspaces contain a variety of runtime and configuration information.

System:prepareds

System:completed_requests

System:active_requests

System:nodes

Since the information in them is not as sensitive as user information, they are more widely viewable. They can be accessed by most admins, even minor ones: Admin, Read Only Admin, Cluster Admin, Query System Catalog, Replication Admin, Bucket Admin(any bucket), or Views Admin(any bucket).

Let’s try looking at system:prepareds as user5, the user we created with Query System Catalog permission, above.

The system:keyspaces keyspace shows the list of buckets configured in the system. This information is somewhat sensitive, since it gives an attacker information about what a system is used for. Accordingly, users with high-level roles (Admin, Read Only Admin, Cluster Admin) see everything in this keyspace. Users with the Query System Catalog role also see everything, since this role is intended for debugging query problems. For other users, the view is filtered; the user sees only those keyspaces for which they have query_select permission.

For example, a query against system:keyspaces with no credentials will succeed but will show no entries:

1

cbq-ehttp://localhost:8093/ -s "select * from system:keyspaces"

1

2

3

"results":[

],

Add credentials for user5, who has the Query System Catalog role, and all entries are shown:

The system:indexes keyspace is handled much like system:keyspaces: high-level roles allow complete access, while the query_select role allows filtered access. The real difference is that the query_manage_index role allows filtered access, like query_select.

Protection for Admin APIs

The N1QL query engine has RESTful APIs corresponding to three of the system keyspaces: system:prepareds, system:completed_requests, and system:active_requests. These used to be open in Couchbase 4.6, but are now secured. They require the same credentials as the the keyspaces do.

1

curl http://localhost:8093/admin/prepareds

1

2

3

4

{"caller":"auth:268",

"code":13014,"key":"datastore.couchbase.insufficient_credentiasl",

"message":"User does not have credentials to run queries accessing the system tables.

Add role query_system_catalog to allow the query to run."}

If we try again with user5, who has the required role, it works.

1

curl http://localhost:8093/admin/prepareds -u user5:password

Access to Migrated Passwordless Buckets

When migrating from 4.6 to 5.0, it is useful to preserve existing permissions patterns, to allow legacy applications to keep working as they did before. This is a bit tricky in this case, because in 4.6 access was controlled using passwords on buckets themselves, whereas in 5.0 access control is based on users with various roles.

To preserve legacy access patterns, during the upgrade process we create users with names and passwords matching the original bucket. This lets existing applications keep doing what they were doing, even in the case of access without any password at all.

Seeing this in action is going to take some work. Get comfortable.

Begin by installing Couchbase 4.6. Then create two buckets, “openbucket” with no password and “closedbucket” with password “password”.

Go to the Query tab and add primary indexes for both buckets to allow N1QL queries to run.Go to the Query tab and add primary indexes for both buckets to allow N1QL queries to run.

This is the pattern of access we want to preserve as we upgrade to 5.0. Now shut down Couchbase, and upgrade to 5.0, while preserving the underlying data we have just created. Then go to the Security screen of the Couchbase console. There you will see two new users created by the upgrade process.

The “closedbucket” user has the same name as the “closedbucket” bucket and the password that was assigned to the bucket in 4.6. It allows us to preserve legacy access to closedbucket. The “openbucket” user does the same, but for openbucket.

Summary

Access control in in Couchbase 4.6 was using passwords for buckets. In 5.0 it is using roles assigned to users.

The primary roles used to control a user’s access to N1QL are query_select/query_insert/query_update/query_delete. All of them are parameterized by bucket.

Important more specialized roles include query_manage_index, query_system_catalog, and query_external_access.

Explaining or preparing a query requires the same roles as executing it would. There are new statements that let you GRANT and REVOKE roles.

The system of roles should be familiar to users familiar with other major databases. The main difference is that Couchbase has no notion of permissions outside of roles.

There are new system keyspaces for querying users and the roles assigned to them. System keyspaces are controlled for security. The behavior varies from keyspace to keyspace depending on the sensitivity of the data therein.

Existing access patterns, even for passwordless buckets, should still work after migration from 4.6 to 5.0.