CockroachDB Grants and Schemas explained

In this post we are going to walk through some common tasks related to granting non-admin users privileges on CockroachDB tables and schemas, and explain why the results you get may not be what you are expecting. By the end of this post I hope you'll have a much clearer picture of how database, table, and schema privileges work in CockroachDB.

Note: This blog was written when CockroachDB 21.1 was the current version. CockroachDB 21.2 introduced DEFAULT PRIVILEGES to bring CockroachDB's privilege model in alignment with PostgreSQL. The main difference is when you want to grant privileges on future tables/objects, you wil now define those future privileges as DEFAULT PRIVILEGES instead of granting those privileges to the database object itself. See: https://www.cockroachlabs.com/docs/stable/alter-default-privileges.html

Let's use a simple scenario.

We are responsible for a CockroachDB cluster, and support developers who need a database as they develop an awesome new application. With an ever increasing number of high-profile hacks in the news, we know we should follow the principle of least privilege and not simply make all of the developers admin users. So we decide to grant them only the privileges they need to do their job.

Let's get started.

The developer contacts us and requests a database named appdb and a table for users and orders.

We connect to the cluster as an admin user. We'll use root for now, but it could be any user with the admin role (and that would also be a good idea from a security point of view).

Let's create the database and the first table.

Note: The tables are ridiculously simple (and empty), because this is not a blog on data modeling.

root/defaultdb> create database appdb;
CREATE DATABASE

root/defaultdb> use appdb;
SET

root/appdb> create table users (
         -> id uuid primary key, details string);
CREATE TABLE

Before we get too carried away with creating tables, let's create the user and grant it only the privileges it needs. They only need CRUD privileges on the tables.

Note: For simplicity, and since this is a blog only on authorization, I'm skipping over the authentication configuration for the user.

root/appdb> create user devuser;
CREATE ROLE

root/appdb> grant select, insert, update, delete
         -> on database appdb to devuser;
GRANT

Let's create another table

root/appdb> create table orders (
         -> id uuid primary key, details string);
CREATE TABLE

Very straight forward so far. We send the devuser connection details to the developer. They log in, but they immediately ping us saying that they can't see the users table.

devuser/appdb> show tables;
  schema_name | table_name | type  | owner | estimated_row_count | locality
--------------+------------+-------+-------+---------------------+-----------
  public      | orders     | table | root  |                   0 | NULL
(1 row)

devuser/appdb> select * from orders;
  id | details
-----+----------
(0 rows)

devuser/appdb> select * from users;
ERROR: user devuser does not have SELECT privilege on relation users
SQLSTATE: 42501

We take a look at the grants using our admin user, and see there are no privileges on the users table for the devuser user.

root/appdb> show grants on users for devuser;
  database_name | schema_name | table_name | grantee | privilege_type
----------------+-------------+------------+---------+-----------------
(0 rows)

What happened here?

The short answer is that in CockroachDB, the GRANT … ON DATABASE statement sets the privileges on the database going forward, but does not set privileges on existing tables. This is different from what you may be familiar with in PostgreSQL.

You can read more about this here: https://www.cockroachlabs.com/docs/v21.1/grant.html#granting-privileges

And here: https://github.com/cockroachdb/cockroach/issues/16790

Remember that we created the users table, then created the devuser and issued its grants, and finally created the orders table. This explains why devuser can see the orders table but not users table.

To fix this, we need to grant the same privileges on the existing tables themselves. Let's do this.

root/appdb> grant select, insert, update, delete
         -> on appdb.* to devuser;
GRANT

We ask the developer to try again, and they report back that it works now.

devuser/appdb> show tables;
  schema_name | table_name | type  | owner | estimated_row_count | locality
--------------+------------+-------+-------+---------------------+-----------
  public      | orders     | table | root  |                   0 | NULL
  public      | users      | table | root  |                   0 | NULL
(2 rows)

devuser/appdb> select * from users;
  id | details
-----+----------
(0 rows)

Whew, that's a good thing to keep in mind later on when adding privileges.

A few days later, the developer tells us they want to organize the application into modules. They will organize the module's tables into schemas to prevent table name conflicts when multiple modules need to use the same table name. Their first module will be for user preferences. We didn't grant them the ability to create their own schemas or tables, so we'll have to do that with our admin privileges.

root/appdb> create schema prefs;
CREATE SCHEMA

root/appdb> create table prefs.user_prefs (
         -> id uuid primary key, details string);
CREATE TABLE

To avoid the surprise we had before, we double-check the privileges on this new table.

root/appdb> show grants on prefs.user_prefs for devuser;

  database_name | schema_name | table_name | grantee | privilege_type
----------------+-------------+------------+---------+-----------------
  appdb         | prefs       | user_prefs | devuser | DELETE
  appdb         | prefs       | user_prefs | devuser | INSERT
  appdb         | prefs       | user_prefs | devuser | SELECT
  appdb         | prefs       | user_prefs | devuser | UPDATE
(4 rows)

Cool, looks good.

We let the developer know the new schema and table is ready. A few moments later, they ping us and say they can't see the table.

devuser/appdb> show tables;
  schema_name | table_name | type  | owner | estimated_row_count | locality
--------------+------------+-------+-------+---------------------+-----------
  public      | orders     | table | root  |                   0 | NULL
  public      | users      | table | root  |                   0 | NULL
(2 rows)

devuser/appdb> select * from prefs.user_prefs;
ERROR: user devuser does not have USAGE privilege on schema prefs
SQLSTATE: 42501

What @$!?

The error message from their select is our clue. The devuser user does not have the USAGE privilege on the schema prefs.

What is the USAGE privilege on a schema? They already have read privileges on table after all.

This is a privilege model that CockroachDB inherits from PostgreSQL and it's not common in other databases, so it's easy to miss this one. In PostgreSQL and CockroachDB, a schema is conceptually similar to a directory in Linux. You can have permissions in Linux to read a file, but if you don't have the execute permission on the directory you can't actually reach the file to read it. So in Linux you need both read permissions on the file and execute permissions on the directory. In PostgreSQL and CockroachDB to access a table in a schema you need to have both the required privileges on the table, and also the USAGE privilege on the schema.

Hmm, that makes some sense..but wait. Why don't we have to grant USAGE to access tables that are not in a schema?

Well, actually all tables are in a schema. But if we create a table without specifying a schema, that table is put into the public schema. You've probably seen this in various places where tables you created in a database have the name like appdb.public.users, and not just appdb.users. And by default, the public role has USAGE on the public schema. Also by default, all users are members of the public role.

TL/DR: Tables in user-defined schemas (i.e. tables not in the public schema) need to also have their users granted USAGE on the schema object in order to reach those tables.

Ok, let's fix this.

root/appdb> grant usage on schema prefs to devuser;
GRANT

We're going to connect as the devuser and test this out before telling them it's OK.

devuser/appdb> show tables;
  schema_name | table_name | type  | owner | estimated_row_count | locality
--------------+------------+-------+-------+---------------------+-----------
  prefs       | user_prefs | table | root  |                   0 | NULL
  public      | orders     | table | root  |                   0 | NULL
  public      | users      | table | root  |                   0 | NULL
(3 rows)

devuser/appdb> select * from prefs.user_prefs;
  id | details
-----+----------
(0 rows)

Looks good.

We tell the developer it has been resolved, and apologize for the back and forth. They report back that it works, and thank us for the quick response. All good!

A few days later...

The developer pings us and says they will need a bunch of new modules and tables as they iterate on the application design. They want to know if it's OK to reach out whenever they need a new schema or table, or if they have permission problems.

Oh...hmm.

We wanted to limit the privileges to only those needed to do their job, so we gave them CRUD privileges on the tables we created. But now it seems their job requires them to create tables on a regular basis. Our job is to help them be productive, so making them go back and forth every time they need a table change seems like a barrier we should try to eliminate.

We decide to give them the CREATE privilege on the database so they can create their own schemas and tables. Considering what we've been through already we test it out and confirm that the CREATE privilege does allow both the creation of schemas and tables, and also tables within schemas. We don't have to make any retroactive grants for existing tables since there's no such thing as creating an existing table. We're feeling good about this.

root/appdb> grant create on database appdb to devuser;
GRANT

We tell the developer they have this new privilege, and they confirm it works as expected. They create a new module for social features, and a table to track friends.

devuser/appdb> create schema social;
CREATE SCHEMA

devuser/appdb> create table social.friends(user_id uuid, friend_id uuid);
CREATE TABLE

The developer is happy since they can evolve the database design as fast as they want.

More time goes by...

Later we hear from the dev manager that the developer has switched projects. And we should revoke their privileges on that database. OK, sounds good.

root/appdb> revoke all on database appdb from devuser;
REVOKE

And now we know that ALL ON DATABASE only applies to new objects, so we remember to revoke privileges on the existing tables also.

root/appdb> revoke all on appdb.* from devuser;
REVOKE

root/appdb> show grants for devuser;
  database_name | schema_name | relation_name | grantee | privilege_type
----------------+-------------+---------------+---------+-----------------
  appdb         | prefs       | NULL          | devuser | USAGE
  appdb         | prefs       | user_prefs    | devuser | DELETE
  appdb         | prefs       | user_prefs    | devuser | INSERT
  appdb         | prefs       | user_prefs    | devuser | SELECT
  appdb         | prefs       | user_prefs    | devuser | UPDATE
  appdb         | social      | NULL          | devuser | CREATE
  appdb         | social      | friends       | devuser | CREATE
  appdb         | social      | friends       | devuser | DELETE
  appdb         | social      | friends       | devuser | INSERT
  appdb         | social      | friends       | devuser | SELECT
  appdb         | social      | friends       | devuser | UPDATE
(11 rows)

Wait, why didn't that work?

Oh, we only revoked privileges on the tables in the public schema with ON appdb.*, so let's revoke privileges on the other tables in the user-defined schemas.

The target matching glob appdb.*.* doesn't work, so we'll have to issue the revoke for each schema in the database. If we had lots of schemas, we could query the information_schema to retrieve the list of schemas and generate REVOKE statements for each schema.

root/appdb> revoke all on appdb.prefs.* from devuser;
REVOKE

root/appdb> revoke all on appdb.social.* from devuser;
REVOKE

root/appdb> show grants for devuser;
  database_name | schema_name | relation_name | grantee | privilege_type
----------------+-------------+---------------+---------+-----------------
  appdb         | prefs       | NULL          | devuser | USAGE
  appdb         | social      | NULL          | devuser | CREATE
(2 rows)

Getting closer. But we have privileges left on the schema objects themselves.

root/appdb> revoke all on schema prefs, social from devuser;
REVOKE

root/appdb> show grants for devuser;
  database_name | schema_name | relation_name | grantee | privilege_type
----------------+-------------+---------------+---------+-----------------
(0 rows)

Looks good!

A few days later, the developer pings us and tells us they switched projects and suggests that we remove their privileges.

We say...proudly...that we've already taken care of that!

Then they send this to us.

devuser/appdb> select * from social.friends;
  user_id | friend_id
----------+------------
(0 rows)

<insert mild expletive>…pardon our redacted language.

Why is this happening?

It's because of object ownership. This is another thing CockroachDB inherits from PostgreSQL and other databases do have similar concepts.

Whichever user (or role) creates an object is by default that object's owner. An owner of an object has ALL privileges on that object. The owner can be changed to another user or role afterwards using the ALTER … OWNER TO statement, or when creating a schema using the AUTHORIZATION clause.

root/appdb> show tables;
  schema_name | table_name | type  |  owner  | estimated_row_count | locality
--------------+------------+-------+---------+---------------------+-----------
  prefs       | user_prefs | table | root    |                   0 | NULL
  public      | orders     | table | root    |                   0 | NULL
  public      | users      | table | root    |                   0 | NULL
  social      | friends    | table | devuser |                   0 | NULL
(4 rows)

root/appdb> show schemas;
     schema_name     |  owner
---------------------+----------
  crdb_internal      | NULL
  information_schema | NULL
  pg_catalog         | NULL
  pg_extension       | NULL
  prefs              | root
  public             | admin
  social             | devuser
(7 rows)

Our devuser is still the owner of the social.friends table, and the social schema. This is why they can still access that table. In fact, they can do more in that schema than just access that table.

devuser/appdb> create table social.foo(a int);
CREATE TABLE

devuser/appdb> drop table social.foo;
DROP TABLE

It's worth noting that to see the tables and schemas in the database, a user needs to have SELECT on the database. We revoked ALL ON DATABASE appdb from our devuser, so they do not have SELECT on the database, therefore they can't see this schema or its tables. But they still have the privileges to access them.

devuser/appdb> show tables;
  schema_name | table_name | type | owner | estimated_row_count | locality
--------------+------------+------+-------+---------------------+-----------
(0 rows)

devuser/appdb> show schemas;
  schema_name | owner
--------------+--------
(0 rows)

We can resolve this by changing the schema ownership to another user or role. It's a good idea to change the ownership to an admin user. We've been using the root user in these examples, so we could give ownership to root, but let's instead give it to the admin role.

root/appdb> alter schema social owner to admin;
ALTER SCHEMA

root/appdb> alter table social.friends owner to admin;
ALTER TABLE OWNER

root/appdb> show schemas;
     schema_name     | owner
---------------------+--------
  crdb_internal      | NULL
  information_schema | NULL
  pg_catalog         | NULL
  pg_extension       | NULL
  prefs              | root
  public             | admin
  social             | admin
(7 rows)

root/appdb> show tables;
  schema_name | table_name | type  | owner | estimated_row_count | locality
--------------+------------+-------+-------+---------------------+-----------
  prefs       | user_prefs | table | root  |                   0 | NULL
  public      | orders     | table | root  |                   0 | NULL
  public      | users      | table | root  |                   0 | NULL
  social      | friends    | table | admin |                   0 | NULL
(4 rows)

We log in using devuser and verify.

devuser/appdb> select * from social.friends;
ERROR: user devuser does not have USAGE privilege on schema social
SQLSTATE: 42501

The devuser no longer has USAGE on the schema (granted through the ALL privilege which comes with being the schema owner) so they can't reach the social.friends table. But we also changed the owner on the table too.

Hmm…we wonder...

If we made devuser the owner of the schema again, it will inherit USAGE on the schema again. But that doesn't mean it inherits ALL on the social.friends table...right?

Let's check it out!

root/appdb> alter schema social owner to devuser;
ALTER SCHEMA

devuser/appdb> select * from social.friends;
ERROR: user devuser does not have SELECT privilege on relation friends
SQLSTATE: 42501

Indeed, we are correct!

Congratulations! You are well on your way to being a CockroachDB access control ninja.