如何导出Redshift权限？——UNLOAD导出至S3时HAS_TABLE_PRIVILEGE()与UNLOAD不兼容的替代方案咨询

Great question—this is a common pain point with Redshift's UNLOAD operation, since HAS_TABLE_PRIVILEGE() is a leader-node-only function that can't run in the parallel context UNLOAD relies on. Here are three solid workarounds to get your permissions data into S3 smoothly:

1. Use a Temporary Table as an Intermediate Step #

The simplest pure-SQL fix is to first run your privilege query (with HAS_TABLE_PRIVILEGE()) and store the results in a temporary table, then UNLOAD that table to S3. Temporary tables live on the leader node, so they play nicely with leader-only functions.

Example Workflow: #

-- 1. Create a temp table to hold permission results
CREATE TEMPORARY TABLE table_permissions AS
SELECT 
    schemaname,
    tablename,
    usename AS grantee,
    HAS_TABLE_PRIVILEGE(usename, schemaname || '.' || tablename, 'SELECT') AS has_select_access,
    HAS_TABLE_PRIVILEGE(usename, schemaname || '.' || tablename, 'INSERT') AS has_insert_access,
    HAS_TABLE_PRIVILEGE(usename, schemaname || '.' || tablename, 'UPDATE') AS has_update_access
FROM pg_tables
JOIN pg_user ON true; -- Adjust join logic to target specific users/tables as needed

-- 2. Unload the temp table to S3
UNLOAD ('SELECT * FROM table_permissions')
TO 's3://your-bucket-name/permissions-export/'
IAM_ROLE 'arn:aws:iam::123456789012:role/your-redshift-s3-access-role'
FORMAT AS CSV
HEADER
ALLOWOVERWRITE;

-- 3. Clean up (optional—temp tables auto-drop when your session ends)
DROP TABLE table_permissions;

Pros: No external tools needed, keeps all logic within Redshift.
Cons: Ties up minimal leader node resources, negligible for permission datasets.

2. Use a Client Tool to Query and Upload Directly #

If you’re open to scripting, you can fetch privilege data via a Redshift client, save it locally as a CSV, then upload to S3 using AWS SDKs. This skips UNLOAD entirely and lets you use HAS_TABLE_PRIVILEGE() freely.

Example Python Script: #

import psycopg2
import pandas as pd
import boto3

# Connect to Redshift
conn = psycopg2.connect(
    dbname="your-db-name",
    user="your-username",
    password="your-password",
    host="your-cluster-endpoint.redshift.amazonaws.com",
    port="5439"
)

# Run your privilege query
privilege_query = """
SELECT 
    schemaname,
    tablename,
    usename AS grantee,
    HAS_TABLE_PRIVILEGE(usename, schemaname || '.' || tablename, 'SELECT') AS has_select,
    HAS_TABLE_PRIVILEGE(usename, schemaname || '.' || tablename, 'DELETE') AS has_delete
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
"""

# Fetch results into a DataFrame
df = pd.read_sql(privilege_query, conn)
conn.close()

# Save to local CSV
local_file = "redshift_permissions.csv"
df.to_csv(local_file, index=False)

# Upload to S3
s3_client = boto3.client("s3")
s3_client.upload_file(local_file, "your-bucket-name", "permissions/redshift_permissions.csv")

Pros: Full control over data formatting, ideal for scheduled exports (e.g., with cron or Airflow).
Cons: Requires setting up client credentials and maintaining scripts.

3. Leverage Redshift's System Views Instead of HAS_TABLE_PRIVILEGE() #

Redshift maintains built-in system views that already contain permission data, so you don’t need HAS_TABLE_PRIVILEGE() at all. The pg_table_privileges view lists all table-level privileges granted to users/roles and works directly with UNLOAD.

Example UNLOAD Command: #

UNLOAD ('SELECT schemaname, tablename, grantee, privilege_type, is_grantable FROM pg_table_privileges')
TO 's3://your-bucket-name/table-privileges/'
IAM_ROLE 'arn:aws:iam::123456789012:role/your-redshift-s3-access-role'
FORMAT AS CSV
HEADER
ALLOWOVERWRITE;

If you need boolean flags (like has_select), transform the data in the query:

UNLOAD ("""
SELECT 
    schemaname,
    tablename,
    grantee,
    MAX(CASE WHEN privilege_type = 'SELECT' THEN true ELSE false END) AS has_select,
    MAX(CASE WHEN privilege_type = 'INSERT' THEN true ELSE false END) AS has_insert
FROM pg_table_privileges
GROUP BY schemaname, tablename, grantee
""")
TO 's3://your-bucket-name/table-privileges-boolean/'
IAM_ROLE 'arn:aws:iam::123456789012:role/your-redshift-s3-access-role'
FORMAT AS CSV
HEADER
ALLOWOVERWRITE;

Pros: Directly compatible with UNLOAD, no intermediate steps, uses native Redshift metadata.
Cons: Requires minor query transformation if you need boolean values instead of privilege strings.

内容的提问来源于stack exchange，提问作者Corentin Duhamel