Schema, table and column visibility filtering for Lotus.

Implements a three-level visibility system where the outer level takes precedence:

1. Schema visibility is checked first — if a schema is denied, every table in it is blocked
2. Table visibility is only checked if the schema is allowed
3. Column visibility applies to the columns of an allowed table, and can hide a column from discovery or mask its values in results

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.