Hey there! Let me walk you through exactly how to grant access to Redshift system tables, logs, and system views for a specific user. I’ve done this dozens of times, so here’s the step-by-step breakdown that works every time:

First off, you’ll need to be logged in as a superuser (or a role with full GRANT privileges) to make these changes—regular users don’t have the permissions to modify system-level access.

Redshift stores most core system metadata in the pg_catalog and information_schema schemas. Here’s how to give your target user access:

• First, switch to your privileged role (if you aren’t already using it):

  SET ROLE your_superuser_role;
  

• Grant USAGE on the schemas themselves (this is required before accessing any objects inside them):

  GRANT USAGE ON SCHEMA pg_catalog, information_schema TO target_user;
  

• Grant SELECT permission on all existing tables and views in these schemas:

  GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog, information_schema TO target_user;
  

• Set default privileges so any future system tables/views are automatically accessible to the user:

  ALTER DEFAULT PRIVILEGES IN SCHEMA pg_catalog, information_schema GRANT SELECT ON TABLES TO target_user;
  

Redshift’s system logs (like query history, error logs, and connection logs) live in two main places: pg_catalog (tables starting with stl_ or stv_) and the sys schema (modern, easier-to-use views). Here’s how to cover both:

• First, grant USAGE on the sys schema (if you want to give access to those simplified views):

  GRANT USAGE ON SCHEMA sys TO target_user;
  

• Grant SELECT on key log tables in pg_catalog (you can specify specific tables or use the wildcard for all):

  -- Grant access to specific log tables
  GRANT SELECT ON stl_query, stl_error, stl_connection_log, stl_querytext TO target_user;
  
  -- Or grant access to all stl_* and stv_* tables (broader access)
  GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO target_user;
  

• Grant SELECT on all views in the sys schema for simplified log access:

  GRANT SELECT ON ALL VIEWS IN SCHEMA sys TO target_user;
  

To make sure everything’s set up correctly, switch to the target user and run a test query:

SET ROLE target_user;

-- Test core system table access
SELECT * FROM pg_catalog.pg_user LIMIT 5;

-- Test log access
SELECT query, starttime, username FROM stl_query LIMIT 10;

-- Test sys schema view access
SELECT * FROM sys.query_history LIMIT 5;

If these queries return results without permission errors, you’re good to go!

• Some sensitive system tables (like pg_authid) are restricted to superusers only—even if you grant SELECT, regular users won’t be able to view them. This is intentional for security.
• If you’re working with roles instead of direct users, replace target_user with your role name, then make sure the user inherits the role:

  GRANT your_system_access_role TO target_user;
  

• Permissions are cumulative, so running these commands won’t revoke any existing access the user already has.

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