Implementing Metadata Security for Multi-Tenant Amazon Redshift Environment

Overview

In multi-tenant Amazon Redshift environments, ensuring complete metadata isolation among tenants is critical for security and compliance. This article walks you through a hands-on demonstration of implementing metadata security that prevents tenants from viewing or querying other tenants' schemas, tables, and system catalog information. Follow this demo to implement a comprehensive metadata security model in your own Redshift environment.

What You'll Achieve

By the end of this demo, you'll have:

• Complete schema isolation between tenants
• Restricted access to system tables and catalogs
• Role-based access control for scalable tenant management
• Zero cross-tenant visibility at the metadata level

Prerequisites

• Access to Amazon Redshift (Serverless or Provisioned)
• Superuser or admin privileges to execute system-level commands
• Redshift Query Editor v2 or your preferred SQL client

Demo: Step-by-Step Implementation

Step 1: Create a Dedicated Database

Start with a clean database for your multi-tenant environment:

CREATE DATABASE private; 

This provides isolation from other databases and a fresh starting point for implementing security controls.

Important: After creating the private database, make sure to switch your database connection to the newly created database before proceeding with the remaining steps in this demo.

Step 2: Enable Metadata Security

Enable the metadata security feature at the cluster/workgroup level:

ALTER SYSTEM SET metadata_security = true;

What this does: Once enabled, users can only see metadata (in system catalogs and information_schema views) for objects they have explicit permissions to access. This is the foundation of tenant isolation.

Step 3: Create Tenant Schemas

Create separate schemas for each tenant:

CREATE SCHEMA tenant1;
CREATE SCHEMA tenant2;

Step 4: Create Tenant Roles

Define roles that represent each tenant's access permissions:

CREATE ROLE tenant1;
CREATE ROLE tenant2;

Step 5: Create Tenant Users

Create user accounts for each tenant:

CREATE USER tenant1_user PASSWORD ' TenantONE#1user';
CREATE USER tenant2_user PASSWORD ' TenantTWO#2user';

Security Note: In production, use strong passwords and consider integrating with IAM Identity Center for enterprise authentication.

Step 6: Assign Users to Roles

Grant the appropriate role to each user:

GRANT ROLE tenant1 TO tenant1_user;
GRANT ROLE tenant2 TO tenant2_user;

Step 7: Configure Metadata Security with Automated Stored Procedure

To simplify the metadata security configuration, we'll use a comprehensive stored procedure for automation. This procedure takes a tenant role name as input and applies all necessary security configurations in a single execution.

CREATE OR REPLACE PROCEDURE setup_tenant_metadata_security(tenant_role_name VARCHAR(128))
LANGUAGE plpgsql
AS $$
DECLARE
    db_record RECORD;
    table_rec RECORD;
    revoke_sql TEXT;
BEGIN
    -- Step A: Revoke public access from all databases
    RAISE INFO '=== Step A: Revoking PUBLIC access from all databases ===';
    FOR db_record IN
        SELECT datname
        FROM pg_database
        WHERE datname NOT IN ('template0', 'template1', 'rdsdb', 'sys:internal')
        AND datname NOT LIKE '%@%'
    LOOP
        revoke_sql := 'REVOKE ALL ON DATABASE ' || db_record.datname || ' FROM PUBLIC;';
        RAISE INFO 'Executing: %', revoke_sql;
        EXECUTE revoke_sql;
    END LOOP;
    RAISE INFO 'Successfully revoked PUBLIC access from all databases';

    -- Step B: Revoke public access to internal schemas
    RAISE INFO '=== Step B: Revoking PUBLIC access to internal schemas ===';
    EXECUTE 'REVOKE ALL ON SCHEMA PG_CATALOG FROM PUBLIC';
    EXECUTE 'REVOKE ALL ON SCHEMA INFORMATION_SCHEMA FROM PUBLIC';
    EXECUTE 'REVOKE USAGE ON SCHEMA PUBLIC FROM PUBLIC';
    RAISE INFO 'Successfully revoked PUBLIC access to internal schemas';

    -- Step C: Grant pg_catalog schema access to tenant role
    RAISE INFO '=== Step C: Granting pg_catalog schema access to role % ===', tenant_role_name;
    EXECUTE 'GRANT USAGE ON SCHEMA pg_catalog TO ROLE ' || tenant_role_name;
    RAISE INFO 'Successfully granted pg_catalog usage to role %', tenant_role_name;

    -- Step D: Revoke public access to all pg_catalog tables and views
    RAISE INFO '=== Step D: Revoking PUBLIC access to pg_catalog objects ===';
    -- Revoke access to all tables in pg_catalog
    FOR table_rec IN
        SELECT tablename
        FROM pg_tables
        WHERE schemaname = 'pg_catalog'
    LOOP
        EXECUTE 'REVOKE ALL ON pg_catalog.' || table_rec.tablename || ' FROM PUBLIC';
    END LOOP;

    -- Revoke access to all views in pg_catalog
    FOR table_rec IN
        SELECT viewname as tablename
        FROM pg_views
        WHERE schemaname = 'pg_catalog'
    LOOP
        EXECUTE 'REVOKE ALL ON pg_catalog.' || table_rec.tablename || ' FROM PUBLIC';
    END LOOP;
    RAISE INFO 'Successfully revoked PUBLIC access from all pg_catalog objects';

    -- Step E: Grant selective access to core catalog tables
    RAISE INFO '=== Step E: Granting selective access to core catalog tables for role % ===', tenant_role_name;
    EXECUTE 'GRANT SELECT ON pg_catalog.pg_database TO ROLE ' || tenant_role_name;
    EXECUTE 'GRANT SELECT ON pg_catalog.pg_type TO ROLE ' || tenant_role_name;
    EXECUTE 'GRANT SELECT ON pg_catalog.pg_class TO ROLE ' || tenant_role_name;
    EXECUTE 'GRANT SELECT ON pg_catalog.pg_description TO ROLE ' || tenant_role_name;
    EXECUTE 'GRANT SELECT ON pg_catalog.pg_namespace TO ROLE ' || tenant_role_name;
    EXECUTE 'GRANT SELECT ON pg_catalog.pg_language TO ROLE ' || tenant_role_name;
    EXECUTE 'GRANT SELECT ON svv_redshift_databases TO ROLE ' || tenant_role_name;
    EXECUTE 'GRANT SELECT ON svv_datashares TO ROLE ' || tenant_role_name;
    EXECUTE 'GRANT SELECT ON svv_all_schemas TO ROLE ' || tenant_role_name;
    EXECUTE 'GRANT SELECT ON sys_query_history TO ROLE ' || tenant_role_name;
    EXECUTE 'GRANT SELECT ON svv_all_tables TO ROLE ' || tenant_role_name;
    EXECUTE 'GRANT SELECT ON pg_proc_info TO ROLE ' || tenant_role_name;
    RAISE INFO 'Successfully granted selective access to core catalog tables for role %', tenant_role_name;

    -- Step F: Grant database and schema permissions to tenant role
    RAISE INFO '=== Step F: Granting database and schema permissions to role % ===', tenant_role_name;
    EXECUTE 'GRANT ALL ON DATABASE private TO ROLE ' || tenant_role_name;
    EXECUTE 'GRANT USAGE, CREATE ON SCHEMA ' || tenant_role_name || ' TO ROLE ' || tenant_role_name;
    EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA ' || tenant_role_name || ' GRANT ALL ON TABLES TO ROLE ' || tenant_role_name;
    RAISE INFO 'Successfully granted database and schema permissions to role %', tenant_role_name;

    RAISE INFO '=== Metadata security setup completed successfully for role % ===', tenant_role_name;
END;
$$;

Execute the Stored Procedure for Each Tenant

-- Execute for tenant1
CALL setup_tenant_metadata_security('tenant1');
-- Execute for tenant2
CALL setup_tenant_metadata_security('tenant2');

Understanding What the Stored Procedure Does

The stored procedure automates six critical security configuration steps:

Step A - Revoke Public Access from All Databases:

• Iterates through all user databases (excluding system databases like template0, template1, rdsdb, sys:internal)
• Revokes all public access from each database
• Ensures consistent security policies across all databases
• Particularly valuable when onboarding new tenants or creating additional databases

Step B: Revoke Public Access to Internal Schemas

Remove public access to system schemas to prevent tenants from querying system catalogs and metadata

• PG_CATALOG: Revokes access to PostgreSQL system catalog tables that contain metadata about database objects
• INFORMATION_SCHEMA: Revokes access to ANSI-standard information schema views that expose database metadata
• PUBLIC schema: Revokes usage rights on the default public schema

Why this is critical: Even with metadata security enabled, explicitly revoking access to these schemas ensures that tenants cannot:

• Query system tables to discover other tenants' objects
• Use information_schema views to enumerate schemas, tables, or columns
• Access or create objects in the shared public schema

Combined effect: When used together with metadata security (Step 2), this creates a defense-in-depth approach where tenants are completely isolated from system-level metadata and can only see and query their own schemas and tables.

Step C: Grant pg_catalog Schema Access to Tenant Roles

Grant each tenant role access to the pg_catalog schema so they can execute basic queries that require system catalog access.

Why this is needed: Many standard SQL operations and clients require reading from pg_catalog (e.g., checking data types, query planning). By granting schema-level access but controlling table-level access (in Step D), you maintain functionality while enforcing security.

Step D: Revoke Public Access to System Tables and Views

Remove public access to all system tables and views within pg_catalog using a stored procedure:

What this does:

• Iterates through all tables and views in pg_catalog
• Revokes all public access to each system table and view
• Ensures comprehensive coverage of all system catalog objects

Why this is critical: This step completes the metadata security lockdown by:

• Preventing tenants from querying system tables to discover other tenants' objects
• Blocking access to sensitive system information (user lists, permissions, database structure)
• Working in conjunction with metadata security (Step 2) to create defense-in-depth

Step E: Grant Selective Access to Core Catalog Tables

After revoking all public access to pg_catalog objects, grant tenant roles selective access to essential system tables needed for basic database operations.

Why this is necessary: After revoking all public access in Step D, tenants need access to these specific system objects to:

• Execute queries that reference data types
• Use BI tools and SQL clients that query system catalogs
• View their own query history
• Access schema and table metadata (filtered by metadata security to show only their objects)

Combined effect of Steps C, D and E: Tenant roles can access the pg_catalog schema for necessary database operations, but they cannot query individual system tables or views to discover metadata about objects they don't have permissions for. This creates a secure environment where tenants can only see their own data and metadata.

Best practice: This selective granting approach follows the principle of least privilege i.e. providing only the minimum system table access needed for functionality while maintaining complete isolation between tenants.

Step F: Grant Database and Schema Permissions to Roles

• Grants full database access to the tenant role
• Grants USAGE and CREATE permissions on the tenant's schema
• Configures default privileges so future tables are automatically accessible to the role
• Completes the tenant-specific permission setup

Benefits of This Approach

1. Simplified Execution: Execute a single stored procedure call per tenant instead of multiple individual steps
2. Consistency: Ensures all security configurations are applied uniformly across all tenants
3. Reusability: Easily add new tenants by calling the procedure with the new role name
4. Auditability: Built-in RAISE INFO statements provide visibility into each step's execution
5. Maintainability: All security logic is centralized in one place for easier updates
6. Error Reduction: Eliminates the risk of missing steps when configuring tenant security

Testing the Implementation

Before verifying the metadata security implementation, let's create sample tables and load data for both tenants using TPC-H benchmark data.

Create Tables and Load Sample Data

Connect as a superuser or admin and create tables in each tenant schema with sample data from Amazon S3:

-- Create region table for tenant1
CREATE TABLE tenant1.region (
  r_regionkey INT4 NOT NULL,
  r_name CHAR(25) NOT NULL,
  r_comment VARCHAR(152) NOT NULL,
  PRIMARY KEY(r_regionkey)
);

-- Create nation table for tenant2
CREATE TABLE tenant2.nation (
  n_nationkey INT4 NOT NULL,
  n_name CHAR(25) NOT NULL,
  n_regionkey INT4 NOT NULL,
  n_comment VARCHAR(152) NOT NULL,
  PRIMARY KEY(n_nationkey)
);

-- Load data into tenant1.region from S3
COPY tenant1.region
FROM 's3://redshift-downloads/TPC-H/2.18/3TB/region/'
IAM_ROLE default
DELIMITER '|'
REGION 'us-east-1';

-- Load data into tenant2.nation from S3
COPY tenant2.nation
FROM 's3://redshift-downloads/TPC-H/2.18/3TB/nation/'
IAM_ROLE default
DELIMITER '|'
REGION 'us-east-1';

Note: Ensure your Redshift cluster or serverless workgroup has an IAM role attached with permissions to read from the S3 bucket. The IAM_ROLE default parameter uses the default IAM role associated with your cluster.

Now proceed with the verification tests below to confirm that each tenant can only access their own data.

Test 1: Verify Schema and Data Access

Connect as tenant1_user and run:

-- This should only show tenant1 schema (and pg_catalog, information_schema)
SELECT * FROM tenant1.region;

You should only see schemas that tenant1_user has access to and can run SELECT on region table.

The tenant2 schema will not appear and can’t be queried as well.

Test 2: Verify System Tables Access Denial

1. Try to discover other tenants through system tables:

-- This should fail with permission denied
SELECT * FROM pg_catalog.pg_users;

Permission denied error.

2. Try to discover other tenants’ tables through system tables:

-- This should fail with permission denied
SELECT * FROM information_schema.tables;

Permission denied error.

What Metadata Security Accomplishes

With this configuration in place:

1. Complete Schema Isolation: Each tenant can only see and query objects in their own schema
2. System Catalog Filtering: System tables (pg_catalog, information_schema) are automatically filtered to show only objects the user has permissions for
3. Metadata Invisibility: Tenants cannot discover the existence of other tenants' schemas, tables, or database objects
4. Zero Cross-Tenant Awareness: Each tenant operates as if they are the only tenant in the database

Use Cases

This metadata security model is ideal for:

• SaaS Applications: Serving multiple customers from a shared Redshift cluster
• Compliance Requirements: Meeting HIPAA, PCI-DSS, or SOC 2 data isolation requirements
• Enterprise Departments: Isolating data between business units or departments
• Partner Ecosystems: Providing secure data access to external partners

Conclusion

This demo shows how Amazon Redshift's metadata security feature provides enterprise-grade isolation for multi-tenant architectures. By following these steps, you can ensure that tenants operate in completely isolated environments with zero visibility into other tenants' data or metadata. The combination of metadata security, role-based access control, and schema isolation creates a robust security model that meets the highest compliance and security standards.

Considerations

The code provided in this article is for TESTING and POC PURPOSES ONLY. Not for Production use. Please modify and test thoroughly in your own environment before any implementation.