Schema and table visibility filtering for Lotus.

Implements a two-level visibility system where schema visibility takes precedence:

1. Schema visibility is checked first - if a schema is denied, all its tables are blocked
2. Table visibility is only checked if the schema is allowed

This ensures security by default while providing fine-grained control.

Understanding Schemas Across Database Systems

Important: "Schema" means different things in different databases:

PostgreSQL

• True namespaced schemas within a single database
• Examples: public, reporting, tenant_123
• System schemas: pg_catalog, information_schema, pg_toast

MySQL

• Schemas = Databases (synonymous terms)
• Examples: lotus_production, analytics_db, warehouse
• System schemas: mysql, information_schema, performance_schema, sys

SQLite

• No schema support (schema-less database)
• Schema visibility rules don't apply

Quick Start

config :lotus,
  # Schema-level rules (higher precedence)
  schema_visibility: %{
    postgres: [
      allow: ["public", ~r/^tenant_/],    # Only public + tenant schemas
      deny: ["legacy"]                    # Block legacy schema
    ],
    mysql: [
      allow: ["app_db", "analytics_db"],  # Only these databases
      deny: ["staging_db"]                # Block staging database
    ]
  },

  # Table-level rules (lower precedence)
  table_visibility: %{
    default: [
      deny: ["user_passwords", "api_keys", ~r/^audit_/]
    ],
    postgres: [
      allow: [
        {"public", ~r/^dim_/},           # Dimension tables only
        {"analytics", ~r/.*/}            # All analytics tables
      ]
    ]
  }

Rule Evaluation

1. Schema Gating (First Check)

if not allowed_schema?(repo_name, schema) do
  false  # Schema denied → all tables blocked
else
  # Schema allowed → check table rules
end

2. Schema-Scoped Allow Posture

Allow rules are scoped to specific schemas, not global:

# Rules: allow: [{"restricted", "allowed_table"}]

{"restricted", "any_table"} → denied (has allow posture, must match)
{"public", "any_table"} → allowed (no allow posture for public)

3. Deny Always Wins

Any deny rule (builtin or user-defined) blocks access immediately.

Rule Formats

Schema Rules

• "exact_name" - Matches exact schema name
• ~r/pattern/ - Regex pattern for dynamic matching
• :all - Special allow value (permits all schemas)

Table Rules

• {"schema", "table"} - Exact schema.table match
• {"schema", ~r/pattern/} - Tables matching regex in specific schema
• {~r/schema_pattern/, "table"} - Table in schemas matching pattern
• "table" - Table name in any schema (global rule)

Built-in Security

System schemas are automatically denied:

• PostgreSQL: pg_catalog, information_schema, pg_toast, pg_temp_*
• MySQL: mysql, information_schema, performance_schema, sys
• All databases: schema_migrations, lotus_queries

Examples

Multi-tenant Application

config :lotus,
  schema_visibility: %{
    postgres: [
      allow: ["public", ~r/^tenant_\d+$/],  # tenant_123, etc.
      deny: ["admin"]
    ]
  },
  table_visibility: %{
    postgres: [
      allow: [
        {"public", ~r/^shared_/},        # Shared lookup tables
        {~r/^tenant_/, "users"},         # Users in each tenant
        {~r/^tenant_/, "orders"}         # Orders in each tenant
      ],
      deny: [
        {~r/^tenant_/, "audit_logs"}     # Hide audit logs
      ]
    ]
  }

Data Warehouse

config :lotus,
  schema_visibility: %{
    postgres: [
      allow: ["public", "warehouse", "analytics"]
    ]
  },
  table_visibility: %{
    postgres: [
      allow: [
        {"public", ~r/^dim_/},         # Dimension tables
        {"public", ~r/^fact_/},        # Fact tables
        {"warehouse", ~r/.*/},         # All warehouse
        {"analytics", ~r/^report_/}    # Only reports
      ],
      deny: [
        {"public", ~r/^raw_/}          # Hide raw data
      ]
    ]
  }

MySQL Multi-Database

config :lotus,
  schema_visibility: %{
    mysql: [
      # Remember: schemas = databases in MySQL
      allow: ["app_production", "analytics_warehouse"],
      deny: ["staging_db", "backup_db"]
    ]
  }

API

Direct visibility checking:

# Check schema visibility
Lotus.Visibility.allowed_schema?("postgres", "public")  # true/false

# Check table visibility
Lotus.Visibility.allowed_relation?("postgres", {"public", "users"})  # true/false

# Filter lists
Lotus.Visibility.filter_schemas(["public", "pg_catalog"], "postgres")  # ["public"]

# Validate requested schemas
Lotus.Visibility.validate_schemas(["public", "restricted"], "postgres")
# :ok | {:error, :schema_not_visible, denied: [...]}

Schema-aware Lotus functions automatically apply visibility:

{:ok, schemas} = Lotus.list_schemas("postgres")        # Filtered list
{:ok, tables} = Lotus.list_tables("postgres")          # Filtered list
{:error, msg} = Lotus.list_tables("postgres", schemas: ["denied"])  # Error

For more detailed examples and configuration patterns, see the Visibility Guide.