Create a User-defined Schema

On this page

This page provides best-practice guidance on creating user-defined schemas, with some simple examples based on Cockroach Labs' fictional vehicle-sharing company, MovR.

Before you begin

Before reading this page, do the following:

• Install CockroachDB.
• Start a local cluster, or create a CockroachDB Cloud cluster.
• Review the database schema objects.
• Create a database.

Create a user-defined schema

User-defined schemas belong to the second level of the CockroachDB naming hierarchy.

To create a user-defined schema, use a CREATE SCHEMA statement, following the user-defined schema best practices. After reviewing the best practices, see the example we provide below.

User-defined schema best practices

Here are some best practices to follow when creating and using user-defined schemas:

• If you want to create multiple objects (e.g., tables or views) with the same name in your cluster, do so in different user-defined schemas in the same database.

• If you want to separate lower-level objects (e.g., a set of tables or views) for access or organizational purposes, do not create those objects in the preloaded public schema. Instead, create user-defined schemas, and then create the objects in the user-defined schemas.

• Create user-defined schemas as a member of the admin role (e.g., as the root user), and then give ownership of them to a different user, with fewer privileges across the database, following authorization best practices.

• When you create a user-defined schema, take note of the object's owner. You can specify the owner in a CREATE SCHEMA statement with the AUTHORIZATION keyword. If AUTHORIZATION is not specified, the owner will be the user creating the user-defined schema.

• Do not create user-defined schemas in the preloaded defaultdb database. Instead, use a database you have created. User-defined schemas can only be created in your SQL session's current database, so be sure to change the session's database to a database that you have created before creating a user-defined schema.

• When referring to a lower-level object in a database (e.g., a table), include the object's schema name (e.g., schema_name.table_name). Specifying the schema name in a lower-level object reference can prevent users from attempting to access the wrong object, if there are multiple objects with the same name in a database.

• As a general best practice, we discourage the use of client libraries to execute database schema changes. Instead, use a database schema migration tool, or the CockroachDB SQL client.

Examples

Suppose you want to separate the tables and indexes in your cluster such that one user manages a group of tables, and another user manages a different group of tables. You can do this with two different user-defined schemas, owned by two different SQL users, with fewer privileges than the root user.

Open the dbinit.sql file that you created in the Create a Database example, and add the following statements under the CREATE DATABASE statement:

USE movr;

CREATE USER IF NOT EXISTS max;
GRANT CREATE ON DATABASE movr TO max;

CREATE USER IF NOT EXISTS abbey;
GRANT CREATE ON DATABASE movr TO abbey;

The first statement sets the movr database as the current database. The next two sets of statements create SQL users named max and abbey in the movr database, with CREATE privileges on the database. CREATE privileges will allow each user to create tables in the database.

Now, under the CREATE USER statements, add DROP SCHEMA and CREATE SCHEMA statements for each user's user-defined schema:

DROP SCHEMA IF EXISTS max_schema CASCADE;
CREATE SCHEMA max_schema AUTHORIZATION max;

DROP SCHEMA IF EXISTS abbey_schema CASCADE;
CREATE SCHEMA abbey_schema AUTHORIZATION abbey;

The first set of statement clears the database of any existing schema named max_schema, and then creates a schema named max_schema with the owner max. The next set of statements does the same, but for abbey_schema, with abbey as the owner.

It might also be a good idea to grant the USAGE privilege on each schema to the other user in the database. This will allow the other user to access objects in the schema, but it will not let them delete the schema, or create objects inside of it.

Under the CREATE SCHEMA statements for each user-defined schema, add a GRANT statement granting USAGE privileges on the schema to the other user.

The dbinit.sql file should now look something link this:

CREATE DATABASE IF NOT EXISTS movr;

USE movr;

CREATE USER IF NOT EXISTS max;
GRANT CREATE ON DATABASE movr TO max;

CREATE USER IF NOT EXISTS abbey;
GRANT CREATE ON DATABASE movr TO abbey;

DROP SCHEMA IF EXISTS max_schema CASCADE;
CREATE SCHEMA max_schema AUTHORIZATION max;
GRANT USAGE ON SCHEMA max_schema TO abbey;

DROP SCHEMA IF EXISTS abbey_schema CASCADE;
CREATE SCHEMA abbey_schema AUTHORIZATION abbey;
GRANT USAGE ON SCHEMA abbey_schema TO max;

To execute the statements in the dbinit.sql file as the root user, run the following command:

$ cockroach sql \
--certs-dir={certs-directory} \
--user=root \
< dbinit.sql

Before the new users can connect to the cluster and start creating objects, they each need a user certificate. To create a user certificate for max, open a new terminal, and run the following cockroach cert command:

$ cockroach cert create-client max --certs-dir={certs-directory} --ca-key={my-safe-directory}/ca.key

Create a user certificate for abbey as well:

$ cockroach cert create-client abbey --certs-dir={certs-directory} --ca-key={my-safe-directory}/ca.key

As one of the new users, use a SHOW SCHEMAS statement to show the preloaded and user-defined schemas in the movr database:

$ cockroach sql \
--certs-dir={certs-directory} \
--user=abbey \
--database=movr \
--execute="SHOW SCHEMAS;"

     schema_name     | owner
---------------------+--------
  abbey_schema       | abbey
  crdb_internal      | NULL
  information_schema | NULL
  max_schema         | max
  pg_catalog         | NULL
  pg_extension       | NULL
  public             | admin
(7 rows)

You're now ready to start adding tables to the max_schema user-defined schema as the max user, and to the abbey_schema user-defined schema as the abbey user.

For guidance on creating tables, see at Create a Table.

What's next?

• Create a Table
• Add Secondary Indexes

You might also be interested in the following pages:

• CREATE SCHEMA
• Cockroach Commands
• Create a Database
• Schema Design Overview
• CockroachDB naming hierarchy

Was this helpful?