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 Spannerpage.

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 tabor New tab.

In the Editortab, 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 Explorerpane.

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. For example, the following statements grant permissions to read from a table:

GRANT SELECT ON TABLE TABLE_NAME IN SCHEMA TO ROLE ROLE_NAME ( GoogleSQL and PostgreSQL ) grants the role permission to read from a specific table within a schema. Alternatively, you can grant the role permission to read from all tables within the schema using the ALL keyword. For example, GRANT SELECT ON ALL TABLES IN SCHEMA TO ROLE ROLE_NAME . The preceding ALL statement applies to only tables present at the time of executing the statement.
GRANT USAGE ON SCHEMA SCHEMA_NAME TO ROLE ROLE_NAME ( GoogleSQL and PostgreSQL ) gives the role permission to access objects contained in the schema if the contained object's permissions are also satisfied. For example, for a table in a schema, you would need 'USAGE' on the schema, and 'SELECT' on the table to read from the table.

In the Google Cloud console, open the Spannerpage.

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 tabor New tab.

In the Editortab, 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 Spannerpage.

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 tabor New tab.

In the Editortab, 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 Spannerpage.

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 tabor New tab.

In the Editortab, 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 .
```
  GRANT 
  
 USAGE 
  
 ON 
  
 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 Explorerpane, 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 Spannerpage.

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 tabor New tab.

In the Editortab, 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 .

Create and manage named schemas Stay organized with collections Save and categorize content based on your preferences.

Before you begin

Create a named schema

GoogleSQL

PostgreSQL

Add fine-grained access control to a named schema

GoogleSQL

PostgreSQL

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

Add fine-grained access control to a default schema

GoogleSQL

PostgreSQL

Revoke fine-grained access control to a default schema

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

View named schemas

Drop a named schema

GoogleSQL

PostgreSQL

What's next

Create and manage named schemas