Create and manage named schemas

This page describes how to create, use, and drop named schemas, and how to apply fine-grained access control to objects in named schemas. For overview information about named schemas, see Named schemas.

Before you begin

To perform procedures on this page, you need the following:

The Database Admin roles/spanner.databaseAdmin role on your user account.
Understand how fine-grained access control works.

Create a named schema

The CREATE SCHEMA command (GoogleSQL and PostgreSQL) is used to create a named schema.

In the Google Cloud console, open the Spanner page.

Go to Spanner
Select an instance from the list.
Select a database.
In the navigation menu, click Spanner Studio.
Open a new tab by clicking New SQL editor tab or New tab.

In the Editor tab, enter your DDL.

GoogleSQL

Run the CREATE SCHEMA statement to create the named schema, for example:

CREATE SCHEMA sch1;

Add database objects in the named schema, for example:

CREATE SEQUENCE sch1.sequence OPTIONS (
  sequence_kind = 'bit_reversed_positive'
);

CREATE TABLE Singers (
  SingerId INT64 NOT NULL,
  FirstName STRING(1024),
  LastName STRING(1024),
  SingerInfo BYTES(MAX),
) PRIMARY KEY(SingerId);

CREATE INDEX indexOnSingers ON Singers(FirstName);

CREATE TABLE Albums (
  SingerId INT64 NOT NULL,
  AlbumId INT64 NOT NULL,
  AlbumTitle STRING(MAX),
) PRIMARY KEY(SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE TABLE Songs (
  SingerId INT64 NOT NULL,
  AlbumId INT64 NOT NULL,
  TrackId INT64 NOT NULL,
  SongName STRING(MAX),
) PRIMARY KEY(SingerId, AlbumId, TrackId),
  INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

CREATE TABLE sch1.Singers (
  SingerId INT64 NOT NULL,
  FirstName STRING(1024),
  LastName STRING(1024),
  SingerInfo BYTES(MAX),
) PRIMARY KEY(SingerId);

CREATE INDEX sch1.indexOnSingers ON sch1.Singers(FirstName);

CREATE TABLE sch1.Albums (
  SingerId INT64 NOT NULL,
  AlbumId INT64 NOT NULL,
  AlbumTitle STRING(MAX),
) PRIMARY KEY(SingerId, AlbumId),
  INTERLEAVE IN PARENT sch1.Singers ON DELETE CASCADE;

CREATE TABLE sch1.Songs (
  SingerId INT64 NOT NULL,
  AlbumId INT64 NOT NULL,
  TrackId INT64 NOT NULL,
  SongName STRING(MAX),
) PRIMARY KEY(SingerId, AlbumId, TrackId),
  INTERLEAVE IN PARENT sch1.Albums ON DELETE CASCADE;

CREATE VIEW sch1.SingerView SQL SECURITY INVOKER
  AS Select s.FirstName, s.LastName, s.SingerInfo
  FROM sch1.Singers AS s WHERE s.SingerId = 123456;

CREATE VIEW SingerView SQL SECURITY INVOKER
  AS Select s.FirstName, s.LastName, s.SingerInfo
  FROM Singers AS s WHERE s.SingerId = 123456;

Spanner only lets you create an index that uses the same schema as the table that uses the index. We need to make sure that the index and table schema names are the same.

PostgreSQL

Run the CREATE SCHEMA statement to create the named schema, for example:

CREATE SCHEMA sch1;

Add database objects in the named schema, for example:

CREATE SEQUENCE sch1.sequence BIT_REVERSED_POSITIVE
CREATE TABLE sch1.singers(
  singer_id bigint primary key, album_id bigint default(nextval('sch1.sequence')))
CREATE TABLE sch1.albums(k bigint default(nextval('sch1.sequence'))primary key, album_id bigint)

CREATE VIEW sch1.singer_view SQL SECURITY INVOKER
  AS SELECT * FROM sch1.singers
CREATE INDEX index_singers ON TABLE sch1.singers(album_id)

Spanner only allows index creation in the same schema. In Spanner, PostgreSQL statements do this by default. You don't need to use fully qualified names to create indexes on named schemas.

View the named schemas and related objects in the Explorer pane.

Add fine-grained access control to a named schema

The following DDL statements add fine-grained access control to a named schema:

The USAGE privilege grants privileges to the schema object. The USAGE privilege is granted, by default, to the default schema. However, you can revoke the USAGE privilege for the default schema. Use caution when revoking access because users and roles that are revoked lose all access to objects in the default schema.
The ALL statement performs bulk grant privileges on ALL objects of a type in the schema.
The DEFAULT keyword refers to the default schema in FGAC DDL statements.

To access an object in a named schema, you must have usage permission on the named schema and corresponding permissions on database objects that use that schema. The following statements provide these permissions:

GRANT ALL (GoogleSQL and PostgreSQL) grants the role permission to access all objects in the table created with the named schema. This statement only applies to objects that exist at that point in time. If you add more objects to the table later, you need to grant access to those objects as you create them.
GRANT USAGE (GoogleSQL and PostgreSQL) gives the role permission to access objects contained in the schema. This lets the grantee look up objects within the schema.

In the Google Cloud console, open the Spanner page.

Go to Spanner
Select an instance from the list.
Select a database.
In the navigation menu, click Spanner Studio.
Open a new tab by clicking New SQL editor tab or New tab.
In the Editor tab, enter your DDL.
GoogleSQL
Create your custom role for the named schema. In the following example, we use role1 and role2.
```
CREATE ROLE role1
CREATE ROLE role2
```
Grant the role to the tables that use the named schema using GRANT ALL. In the following example, we use sch1 for the named schema and role1 for the role.
```
GRANT SELECT ON ALL TABLES IN SCHEMA sch1 TO ROLE role1
```
Grant usage on the schema for the roles you created. In the following example, we grant usage on sch1 to role1 and role2.
```
GRANT USAGE ON SCHEMA sch1 TO ROLE role1, role2
```
PostgreSQL
Create your custom role for the named schema. In the following example, we use role1 and role2.
```
CREATE ROLE role1
CREATE ROLE role2
```
Grant the role to the tables that use the named schema using GRANT ALL. In the following example, we use sch1 for the named schema and role1 for the role.
```
GRANT SELECT ON ALL TABLES IN SCHEMA sch1 TO role1
```
Grant usage on the schema for the roles you created. In the following example, we grant usage on sch1 to role1 and role2.
```
GRANT USAGE ON SCHEMA sch1 TO role1, role2
```

Add and revoke fine-grained access control to a default schema

When you have named schemas, the default schema is called default. You need to use the default schema name when adding or revoking fine-grained access control.

Add fine-grained access control to a default schema

By default, all users and roles have the USAGE permission on the default schema.

In the Google Cloud console, open the Spanner page.

Go to Spanner
Select an instance from the list.
Select a database.
In the navigation menu, click Spanner Studio.
Open a new tab by clicking New SQL editor tab or New tab.
In the Editor tab, enter your DDL.
GoogleSQL
In the following example, we grant access to all tables to role1.
```
GRANT SELECT ON ALL TABLES IN SCHEMA default TO ROLE role1
```
PostgreSQL
In the following example, we grant access to all tables to role1.
```
GRANT SELECT ON ALL TABLES IN SCHEMA default TO role1
```

Revoke fine-grained access control to a default schema

You can revoke the default fine-grained access control permissions on the default schema using the REVOKE USAGE command.

In the Google Cloud console, open the Spanner page.

Go to Spanner
Select an instance from the list.
Select a database.
In the navigation menu, click Spanner Studio.
Open a new tab by clicking New SQL editor tab or New tab.

In the Editor tab, enter your DDL.

GoogleSQL

REVOKE USAGE ON SCHEMA DEFAULT FROM ROLE public

PostgreSQL

REVOKE USAGE ON SCHEMA public FROM public

After the previous command is run, we must explicitly grant permissions to the roles that need to access the default schema. In the following example, we grant permissions to role1.
GoogleSQL
In the following example, we grant access to the default schema to role1.
```
SCHEMA default to ROLE role1
```
PostgreSQL
In the following example, we grant access to the default schema to role1.
```
GRANT USAGE ON SCHEMA public To role1
```

View named schemas

Select an instance from the list.
Select a database.
In the navigation menu, click Spanner Studio.
In the Explorer pane, expand the Schemas drop-down list.

Alternatively, you can use SQL to view all schemas in the information_schema.schemata table.

The following example shows how view names schemas and their owners:

SELECT schema_name, schema_owner
  FROM information_schema.schemata
  ORDER BY schema_owner

This statement provides a list of schemas and owners, similar to the following:

public spanner_admin
products  spanner_admin
analytics  spanner_admin
logs  spanner_admin
pg_catalog spanner_system
information_schema spanner_system
spanner_sys   spanner_system

Drop a named schema

The DROP SCHEMA command (GoogleSQL and PostgreSQL) is used to drop a named schema.

In the Google Cloud console, open the Spanner page.

Go to Spanner
Select an instance from the list.
Select a database.
In the navigation menu, click Spanner Studio.
Open a new tab by clicking New SQL editor tab or New tab.
In the Editor tab, enter your DDL.
GoogleSQL
In the following example, we drop sch1.
```
DROP SCHEMA IF EXISTS sch1;
```
PostgreSQL
In the following example, we drop sch1.
```
DROP SCHEMA IF EXISTS sch1;
```

What's next

Learn about Schema best practices.
Learn about Views.
Learn about Secondary indexes.
Learn about Foreign keys.