cockroachdb-list-schemas

About

The cockroachdb-list-schemas tool retrieves a list of schemas (namespaces) in a CockroachDB database. Schemas are used to organize database objects such as tables, views, and functions into logical groups.

This tool is useful for:

• Understanding database organization
• Discovering available schemas
• Multi-tenant application analysis
• Schema-level access control planning

Example

sources:
  my_cockroachdb:
    type: cockroachdb
    host: your-cluster.cockroachlabs.cloud
    port: "26257"
    user: myuser
    password: mypassword
    database: defaultdb
    queryParams:
      sslmode: require

tools:
  list_schemas:
    type: cockroachdb-list-schemas
    source: my_cockroachdb
    description: List all schemas in the database

Configuration

Required Fields

Optional Fields

Output Structure

The tool returns a list of schemas with the following information:

[
  {
    "catalog_name": "defaultdb",
    "schema_name": "public",
    "is_user_defined": true
  },
  {
    "catalog_name": "defaultdb",
    "schema_name": "analytics",
    "is_user_defined": true
  }
]

Fields

Usage Example

{}

No parameters are required. The tool automatically lists all user-defined schemas.

Default Schemas

CockroachDB includes several standard schemas:

• public: The default schema for user objects
• pg_catalog: PostgreSQL system catalog (excluded from results)
• information_schema: SQL standard metadata views (excluded from results)
• crdb_internal: CockroachDB internal metadata (excluded from results)
• pg_extension: PostgreSQL extension objects (excluded from results)

The tool filters out system schemas and only returns user-defined schemas.

Schema Management in CockroachDB

Creating Schemas

CREATE SCHEMA analytics;

Using Schemas

-- Create table in specific schema
CREATE TABLE analytics.revenue (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  amount DECIMAL(10,2),
  date DATE
);

-- Query from specific schema
SELECT * FROM analytics.revenue;

Schema Search Path

The search path determines which schemas are searched for unqualified object names:

-- Show current search path
SHOW search_path;

-- Set search path
SET search_path = analytics, public;

Multi-Tenant Applications

Schemas are commonly used for multi-tenant applications:

-- Create schema per tenant
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;

-- Create same table structure in each schema
CREATE TABLE tenant_acme.orders (...);
CREATE TABLE tenant_globex.orders (...);

The cockroachdb-list-schemas tool helps discover all tenant schemas:

tools:
  list_tenants:
    type: cockroachdb-list-schemas
    source: my_cockroachdb
    description: |
      List all tenant schemas in the database.
      Each schema represents a separate tenant's data namespace.

Best Practices

Use Schemas for Organization

Group related tables into schemas:

CREATE SCHEMA sales;
CREATE SCHEMA inventory;
CREATE SCHEMA hr;

CREATE TABLE sales.orders (...);
CREATE TABLE inventory.products (...);
CREATE TABLE hr.employees (...);

Schema Naming Conventions

Use clear, descriptive schema names:

• Lowercase names
• Use underscores for multi-word names
• Avoid reserved keywords
• Use prefixes for grouped schemas (e.g., tenant_, app_)

Schema-Level Permissions

Schemas enable fine-grained access control:

-- Grant access to specific schema
GRANT USAGE ON SCHEMA analytics TO analyst_role;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analyst_role;

-- Revoke access
REVOKE ALL ON SCHEMA hr FROM public;

Integration with Other Tools

Combined with List Tables

tools:
  list_schemas:
    type: cockroachdb-list-schemas
    source: my_cockroachdb
    description: List all schemas first
    
  list_tables:
    type: cockroachdb-list-tables
    source: my_cockroachdb
    description: |
      List tables in the database.
      Use list_schemas first to understand schema organization.

Schema Discovery Workflow

1. Call cockroachdb-list-schemas to discover schemas
2. Call cockroachdb-list-tables to see tables in each schema
3. Generate queries using fully qualified names: schema.table

Common Use Cases

Discover Database Structure

tools:
  discover_schemas:
    type: cockroachdb-list-schemas
    source: my_cockroachdb
    description: |
      Discover how the database is organized into schemas.
      Use this to understand the logical grouping of tables.

Multi-Tenant Analysis

tools:
  list_tenant_schemas:
    type: cockroachdb-list-schemas
    source: my_cockroachdb
    description: |
      List all tenant schemas (each tenant has their own schema).
      Schema names follow the pattern: tenant_<company_name>

Schema Migration Planning

tools:
  audit_schemas:
    type: cockroachdb-list-schemas
    source: my_cockroachdb
    description: |
      Audit existing schemas before migration.
      Identifies all schemas that need to be migrated.

Error Handling

The tool handles common errors:

• Connection errors: Returns connection failure details
• Permission errors: Returns error if user lacks USAGE privilege
• Empty results: Returns empty array if no user schemas exist

Permissions Required

To list schemas, the user needs:

• CONNECT privilege on the database
• No specific schema privileges required for listing

To query objects within schemas, the user needs:

• USAGE privilege on the schema
• Appropriate object privileges (SELECT, INSERT, etc.)

CockroachDB-Specific Features

System Schemas

CockroachDB includes PostgreSQL-compatible system schemas plus CockroachDB-specific ones:

• crdb_internal.*: CockroachDB internal metadata and statistics
• pg_catalog.*: PostgreSQL system catalog
• information_schema.*: SQL standard information schema

These are automatically filtered from the results.

User-Defined Flag

The is_user_defined field helps distinguish:

• true: User-created schemas
• false: System schemas (already filtered out)

See Also

• cockroachdb-sql - Execute parameterized queries
• cockroachdb-execute-sql - Execute ad-hoc SQL
• cockroachdb-list-tables - List tables in the database
• CockroachDB Source - Source configuration reference
• CockroachDB Schema Design - Official documentation