references/example-queries.md
# Example Queries Against Published `SYS_*` System Tables
Loaded on demand from `querying-aws-redshift` SKILL.md. Timing columns are **microseconds** — divide by 1,000,000 for seconds. Confirm the namespace from the status API before substituting it below; never hand-construct it.
## Top 10 longest-running queries in the last 7 days (Athena)
```sql
SELECT query_id,
username,
database_name,
query_type,
round(elapsed_time / 1000000.0, 2) AS elapsed_sec,
round(queue_time / 1000000.0, 2) AS queue_sec,
round(execution_time / 1000000.0, 2) AS exec_sec,
start_time,
substr(query_text, 1, 120) AS query_preview
FROM "s3tablescatalog/aws-redshift"."<NAMESPACE>"."sys_query_history"
WHERE start_time > current_timestamp - interval '7' day
AND status = 'success'
ORDER BY elapsed_time DESC
LIMIT 10;
```
## Same query from Redshift (auto-mounted catalog)
```sql
SELECT query_id,
username,
database_name,
query_type,
round(elapsed_time / 1000000.0, 2) AS elapsed_sec,
round(queue_time / 1000000.0, 2) AS queue_sec,
round(execution_time / 1000000.0, 2) AS exec_sec,
start_time,
substring(query_text, 1, 120) AS query_preview
FROM "aws-redshift@s3tablescatalog"."<NAMESPACE>".sys_query_history
WHERE start_time > current_timestamp - interval '7 day'
AND status = 'success'
ORDER BY elapsed_time DESC
LIMIT 10;
```
## Query volume and latency percentiles by hour
```sql
SELECT hour(start_time) AS hour_of_day,
count(*) AS query_count,
round(approx_percentile(elapsed_time, 0.50) / 1000000.0, 2) AS p50_sec,
round(approx_percentile(elapsed_time, 0.95) / 1000000.0, 2) AS p95_sec
FROM "s3tablescatalog/aws-redshift"."<NAMESPACE>"."sys_query_history"
WHERE start_time > current_timestamp - interval '7' day
GROUP BY hour(start_time)
ORDER BY hour_of_day;
```
## Most expensive repeated query shapes
```sql
SELECT generic_query_hash,
count(*) AS executions,
round(sum(elapsed_time) / 1000000.0, 1) AS total_sec,
round(avg(elapsed_time) / 1000000.0, 2) AS avg_sec,
arbitrary(substr(query_text, 1, 120)) AS sample_query
FROM "s3tablescatalog/aws-redshift"."<NAMESPACE>"."sys_query_history"
WHERE start_time > current_timestamp - interval '7' day
AND query_type = 'SELECT'
GROUP BY generic_query_hash
ORDER BY total_sec DESC
LIMIT 10;
```
## Failed authentication attempts
```sql
SELECT user_name,
remote_host,
count(*) AS failed_attempts,
min(record_time) AS first_seen,
max(record_time) AS last_seen
FROM "s3tablescatalog/aws-redshift"."<NAMESPACE>"."sys_connection_log"
WHERE event = 'authentication failure'
AND record_time > current_timestamp - interval '30' day
GROUP BY user_name, remote_host
ORDER BY failed_attempts DESC
LIMIT 20;
```
## Reassemble full text of a long query
```sql
SELECT query_id,
array_join(array_agg(text ORDER BY sequence), '') AS full_query_text
FROM "s3tablescatalog/aws-redshift"."<NAMESPACE>"."sys_query_text"
WHERE query_id = <QUERY_ID>
GROUP BY query_id;
```
## Correlate expensive queries with client origin
```sql
SELECT qh.query_id,
qh.username,
round(qh.elapsed_time / 1000000.0, 2) AS elapsed_sec,
cl.remote_host,
cl.application_name,
cl.driver_version
FROM "s3tablescatalog/aws-redshift"."<NAMESPACE>"."sys_query_history" qh
LEFT JOIN "s3tablescatalog/aws-redshift"."<NAMESPACE>"."sys_connection_log" cl
ON qh.session_id = cl.session_id
AND cl.event = 'initiating session'
WHERE qh.start_time > current_timestamp - interval '1' day
ORDER BY qh.elapsed_time DESC
LIMIT 15;
```
references/permissions-setup.md
# Permissions Setup: Athena and Redshift Access to Published System Tables
Loaded on demand from `querying-aws-redshift` SKILL.md. Read this before running the IAM, Lake Formation, or auto-mount setup — the SKILL.md summary states the constraints but not the full command sequence.
## For Athena Querying
Requires:
- S3 Tables catalog registered in Glue (`s3tablescatalog/aws-redshift`)
- Athena execution permissions and a workgroup with an output location
- S3 Tables read permissions (see the least-privilege policy in `${SKILL_DIR}/references/security.md`)
**Encrypt the workgroup's output location.** Athena writes full result sets to S3, so `query_text`, `user_name`, and `remote_host` from the `SYS_*` tables land there in plaintext unless the workgroup enforces encryption. Configure SSE-KMS and lock it so query authors cannot override it:
```bash
aws athena update-work-group \
--region <REGION> \
--work-group <WORKGROUP> \
--configuration-updates 'EnforceWorkGroupConfiguration=true,ResultConfigurationUpdates={OutputLocation=s3://<RESULTS_BUCKET>/<PREFIX>/,EncryptionConfiguration={EncryptionOption=SSE_KMS,KmsKey=<KEY_ARN>}}'
```
`EnforceWorkGroupConfiguration=true` is the part that matters — without it a client can pass its own unencrypted `ResultConfiguration` per query. Verify with `aws athena get-work-group --work-group <WORKGROUP>`.
Confirm the catalog is registered:
```bash
aws glue get-databases --region <REGION> \
--catalog-id "<ACCOUNT>:s3tablescatalog/aws-redshift"
```
- Returns namespaces (databases) → catalog is registered and queryable.
- `EntityNotFoundException` / `CATALOG_NOT_FOUND` → S3 Tables integration not enabled. Enable the S3 Tables integration: S3 console > Table buckets > Enable integration.
## For Redshift Querying (Auto-Mounted S3 Tables Catalog)
Prerequisites:
- A Provisioned RA3 cluster. Auto-mount support depends on node type; confirm the current supported node types in the [Redshift documentation](https://docs.aws.amazon.com/redshift/latest/mgmt/working-with-clusters.html) or via `aws redshift describe-orderable-cluster-options` rather than assuming a fixed list.
- The Glue `s3tablescatalog` S3 Tables catalog must exist (auto-created when the table bucket is integrated with analytics services)
### Step 1: Create IAM Role with Required Permissions
Create a role (e.g., `query_s3_tables`) with:
Trust Policy:
```json
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {"Service": "redshift.amazonaws.com"},
"Action": "sts:AssumeRole",
"Condition": {"StringEquals": {"aws:SourceAccount": "<ACCOUNT>"}}
},
{
"Effect": "Allow",
"Principal": {"Service": "lakeformation.amazonaws.com"},
"Action": ["sts:AssumeRole", "sts:SetContext", "sts:SetSourceIdentity", "sts:TagSession"],
"Condition": {"StringEquals": {"aws:SourceAccount": "<ACCOUNT>"}}
}
]
}
```
All four actions (`sts:AssumeRole`, `sts:SetContext`, `sts:SetSourceIdentity`, `sts:TagSession`) are mandatory for `lakeformation.amazonaws.com`. Without them, Lake Formation cannot assume the role for federation.
The `aws:SourceAccount` conditions guard against the [confused deputy problem](https://docs.aws.amazon.com/IAM/latest/UserGuide/confused-deputy.html): a bare service principal with no condition can be assumed on behalf of *any* account, so a caller in another account could induce the service to use this role. Restrict to the account that owns the cluster. Use `aws:SourceArn` with the cluster ARN instead if you want to pin to a single cluster.
Create the role and attach the read-only query policy. Pass both documents inline so the commands work unchanged through the AWS MCP server's `call_aws` tool, which cannot read local files:
```bash
aws iam create-role \
--role-name query_s3_tables \
--assume-role-policy-document '{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Principal":{"Service":"redshift.amazonaws.com"},"Action":"sts:AssumeRole","Condition":{"StringEquals":{"aws:SourceAccount":"<ACCOUNT>"}}},{"Effect":"Allow","Principal":{"Service":"lakeformation.amazonaws.com"},"Action":["sts:AssumeRole","sts:SetContext","sts:SetSourceIdentity","sts:TagSession"],"Condition":{"StringEquals":{"aws:SourceAccount":"<ACCOUNT>"}}}]}'
aws iam put-role-policy \
--role-name query_s3_tables \
--policy-name S3TablesQueryAccess \
--policy-document '<the inline policy JSON below, minified>'
```
At a terminal you can substitute `file://trust-policy.json` / `file://inline-policy.json` for the inline strings, which avoids shell-quoting problems with long documents. Inline is the primary form because `file://` silently fails wherever the executing agent has no filesystem.
**Do not attach `AWSLakeFormationDataAdmin` to this role.** The role above is attached to the cluster (Step 2) and is used to *serve queries*; it needs read access only. The Lake Formation setup steps below (`register-resource` in Step 3, `put-data-lake-settings` in Step 4) are data-lake-administrator operations that `AdministratorAccess` alone does not satisfy — Lake Formation gates them on data lake admin status rather than on IAM alone. Run those steps as **the human or automation principal performing setup**, not as the cluster's role, so the cluster never holds administrative Lake Formation permissions at runtime:
```bash
# One-time, on the SETUP principal (not the cluster role):
aws iam attach-role-policy \
--role-name <YOUR_SETUP_ROLE> \
--policy-arn arn:aws:iam::aws:policy/AWSLakeFormationDataAdmin
```
`AWSLakeFormationDataAdmin` is a broad starting point, not a production posture: it grants administrative control over *every* Lake Formation resource in the account, including `PutDataLakeSettings`, which can rewrite the admin list. For production, replace it on the setup principal with a custom policy holding only the setup actions actually used here, and detach it once setup completes:
```json
{
"Version": "2012-10-17",
"Statement": [{
"Effect": "Allow",
"Action": [
"lakeformation:RegisterResource",
"lakeformation:DescribeResource",
"lakeformation:ListResources",
"lakeformation:GetDataLakeSettings",
"lakeformation:PutDataLakeSettings",
"lakeformation:GrantPermissions",
"lakeformation:ListPermissions"
],
"Resource": "*"
}]
}
```
Once setup is complete, detach it from the setup principal too — nothing in steady-state querying needs it. The cluster's role only ever needs the read-only inline policy below.
- Inline policy for S3 Tables, Glue, and Lake Formation access, scoped to the `aws-redshift` table bucket and its catalog:
```json
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"s3tables:GetTable",
"s3tables:GetTableMetadataLocation",
"s3tables:GetTableData",
"s3tables:GetNamespace",
"s3tables:ListTables",
"s3tables:ListNamespaces",
"s3tables:GetTableBucket"
],
"Resource": [
"arn:aws:s3tables:<REGION>:<ACCOUNT>:bucket/aws-redshift",
"arn:aws:s3tables:<REGION>:<ACCOUNT>:bucket/aws-redshift/*"
]
},
{
"Effect": "Allow",
"Action": [
"glue:GetDatabase",
"glue:GetDatabases",
"glue:GetTable",
"glue:GetTables"
],
"Resource": [
"arn:aws:glue:<REGION>:<ACCOUNT>:catalog",
"arn:aws:glue:<REGION>:<ACCOUNT>:catalog/s3tablescatalog",
"arn:aws:glue:<REGION>:<ACCOUNT>:catalog/s3tablescatalog/aws-redshift",
"arn:aws:glue:<REGION>:<ACCOUNT>:database/s3tablescatalog/aws-redshift/*",
"arn:aws:glue:<REGION>:<ACCOUNT>:table/s3tablescatalog/aws-redshift/*/*"
]
},
{
"Effect": "Allow",
"Action": "lakeformation:GetDataAccess",
"Resource": "*",
"Condition": {
"StringEquals": {
"aws:ResourceAccount": "<ACCOUNT>"
}
}
}
]
}
```
`lakeformation:GetDataAccess` is the one action here that cannot be scoped by resource ARN — the Lake Formation documentation states that `"Resource": "*"` is required and "specifying any other resource for this permission is not supported." The actual data authorization comes from the Lake Formation grants, not from this statement. The `aws:ResourceAccount` condition constrains it to same-account table buckets, so the role cannot be used to vend credentials for a table bucket shared in from another account. Use `StringEquals`, not `StringLike` — `aws:ResourceAccount` is a sensitive condition key and wildcards in it defeat the check.
Note the shape of the Glue database and table ARNs: federated S3 Tables catalogs nest under `s3tablescatalog/<table-bucket>`, so the resource path is `database/s3tablescatalog/aws-redshift/*`, not the bare `database/*`. The bare form would grant metadata read on every database and table in the account's default Glue catalog — far more than querying published system tables needs.
If you hit a permission error during initial setup that the scoped policy above doesn't cover, widen it deliberately and narrow it back down for production — do not fall back to `"Action": "*"` on `"Resource": "*"`.
### Step 2: Attach Role to Cluster
```bash
aws redshift modify-cluster-iam-roles \
--cluster-identifier <CLUSTER_ID> \
--add-iam-roles "arn:aws:iam::<ACCOUNT>:role/query_s3_tables" \
--region <REGION>
```
### Step 3: Register the Table Bucket with Lake Formation
Scope the registration to just the `aws-redshift` table bucket so the role cannot be used to reach other table buckets in the account:
```bash
aws lakeformation register-resource \
--region <REGION> \
--resource-arn "arn:aws:s3tables:<REGION>:<ACCOUNT>:bucket/aws-redshift" \
--role-arn "arn:aws:iam::<ACCOUNT>:role/query_s3_tables"
```
Note: `VerificationStatus: NOT_VERIFIED` after registration is normal and does not block functionality.
### Step 4: Add Redshift SLRs as Lake Formation Read-Only Admins
```bash
aws lakeformation put-data-lake-settings \
--region <REGION> \
--data-lake-settings '{
"DataLakeAdmins": [
{"DataLakePrincipalIdentifier": "arn:aws:iam::<ACCOUNT>:role/<YOUR_ADMIN_ROLE>"}
],
"ReadOnlyAdmins": [
{"DataLakePrincipalIdentifier": "arn:aws:iam::<ACCOUNT>:role/aws-service-role/redshift.amazonaws.com/AWSServiceRoleForRedshift"},
{"DataLakePrincipalIdentifier": "arn:aws:iam::<ACCOUNT>:role/aws-service-role/redshift.aws.internal/AWSServiceRoleForRedshiftInternal"}
]
}'
```
**WARNING:** `put-data-lake-settings` REPLACES the entire settings object. Always include your existing `DataLakeAdmins` alongside the new `ReadOnlyAdmins`.
### Step 5: Verify Auto-Mount
The cluster polls every 300 seconds. After up to 5 minutes:
```sql
SELECT datname FROM pg_database;
```
Expected output includes: `aws-redshift@s3tablescatalog`
If it doesn't appear after 5 minutes, a cluster reboot triggers immediate discovery.
references/security.md
# Security: Least Privilege, KMS, Data Sensitivity, and Audit Trail
Loaded on demand from `querying-aws-redshift` SKILL.md. Read this before granting anyone access to published system tables — `query_text` can carry credentials, and the KMS key policy needs two service principals, not one.
## Least-Privilege IAM Policy
Scope permissions to the S3 Tables catalog rather than using wildcards:
```json
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"s3tables:GetTable",
"s3tables:GetTableMetadataLocation",
"s3tables:GetTableData",
"s3tables:GetNamespace",
"s3tables:ListTables",
"s3tables:ListNamespaces",
"s3tables:GetTableBucket"
],
"Resource": [
"arn:aws:s3tables:<REGION>:<ACCOUNT>:bucket/aws-redshift",
"arn:aws:s3tables:<REGION>:<ACCOUNT>:bucket/aws-redshift/*"
]
}
]
}
```
## KMS Key Policy (for encrypted logs)
When using `--s3-table-kms-key-id` (both Provisioned and Serverless), the KMS key must grant both the Redshift and S3 Tables service principals access:
```json
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "EnableRedshiftSystemTableKeyUsage",
"Effect": "Allow",
"Principal": {
"Service": "systemtables.redshift.amazonaws.com"
},
"Action": [
"kms:DescribeKey",
"kms:GenerateDataKey",
"kms:Decrypt"
],
"Resource": "arn:aws:kms:<REGION>:<ACCOUNT>:key/<KEY_ID>",
"Condition": {
"StringEquals": {
"aws:SourceAccount": "<ACCOUNT>"
}
}
},
{
"Sid": "EnableS3TableMaintenanceKeyUsage",
"Effect": "Allow",
"Principal": {
"Service": "maintenance.s3tables.amazonaws.com"
},
"Action": [
"kms:GenerateDataKey",
"kms:Decrypt"
],
"Resource": "arn:aws:kms:<REGION>:<ACCOUNT>:key/<KEY_ID>",
"Condition": {
"StringLike": {
"kms:EncryptionContext:aws:s3:arn": "arn:aws:s3tables:<REGION>:<ACCOUNT>:bucket/aws-redshift/*"
}
}
}
]
}
```
## Data Sensitivity
`SYS_*` system-table data may contain sensitive fields:
- `query_text` — may reveal schema, data values, or business logic
- `username` / `user_id` — the principal that ran the query
- `remote_host` (in `sys_connection_log`) — client IP address
Store query results in encrypted, access-controlled locations. Avoid logging or sharing raw output that contains query text, principal identifiers, or IP addresses.
**`query_text` can contain credentials, not just schema.** Applications that build SQL by string interpolation rather than parameterized queries embed literal values directly in the statement text — including passwords in `CREATE USER` / `ALTER USER`, connection strings, API keys passed to UDFs, and PII in `WHERE` clauses. All of that is captured verbatim in `sys_query_history` and published to the S3 table. Audit your applications for interpolated SQL **before** making `sys_query_history` broadly readable; a query-history grant is effectively a secrets grant if that anti-pattern is present. Restrict the column via Lake Formation column-level permissions where readers only need timing and identity data.
Athena is a second copy of the same text: `StartQueryExecution` records the full SQL in CloudTrail. If those events are delivered to CloudWatch Logs, encrypt the receiving log group with a customer-managed key (`aws logs associate-kms-key --log-group-name <NAME> --kms-key-id <KEY_ARN>`), since the default log-group encryption is not customer-managed.
## Audit Trail
Enable CloudTrail logging for Athena (`StartQueryExecution`, `GetQueryResults`) and S3 Tables (`s3tables:GetTableData`) API calls to maintain an audit trail of who queried what. Ensure CloudTrail logs are encrypted with SSE-KMS and stored in a bucket with access logging enabled.
Collecting logs is passive; add active detection so misuse surfaces without someone reading them. Two alarms worth having:
- **Access-denied spikes on the published tables** — a burst of `AccessDenied` on `s3tables:GetTableData` is the signature of enumeration or a broken least-privilege change. With CloudTrail delivering to CloudWatch Logs, create a metric filter and alarm on it:
```bash
aws logs put-metric-filter \
--log-group-name <CLOUDTRAIL_LOG_GROUP> \
--filter-name S3TablesAccessDenied \
--filter-pattern '{ ($.eventSource = "s3tables.amazonaws.com") && ($.errorCode = "AccessDenied*") }' \
--metric-transformations metricName=S3TablesAccessDenied,metricNamespace=RedshiftSysTables,metricValue=1
aws cloudwatch put-metric-alarm \
--alarm-name S3TablesAccessDeniedSpike \
--metric-name S3TablesAccessDenied --namespace RedshiftSysTables \
--statistic Sum --period 300 --evaluation-periods 1 \
--threshold 10 --comparison-operator GreaterThanThreshold \
--alarm-actions <SNS_TOPIC_ARN>
```
- **Failed authentications against the cluster** — `sys_connection_log` records these, so once it is published you can detect credential-stuffing from the S3 table on a schedule rather than by ad-hoc query. Alert on a rising count of failed connections grouped by `remote_host`.
Tune both thresholds to your own baseline; the values above are starting points, not recommendations.
**Secure the notification path, not just the detection.** Alarm payloads describe who is touching which system tables, so the topic is itself sensitive. Encrypt it with a customer-managed key and audit who receives it:
```bash
aws sns set-topic-attributes \
--topic-arn <SNS_TOPIC_ARN> \
--attribute-name KmsMasterKeyId --attribute-value <KMS_KEY_ID>
aws sns list-subscriptions-by-topic --topic-arn <SNS_TOPIC_ARN>
```
The default `alias/aws/sns` key cannot be restricted by policy or revoked; a customer-managed key can. Review the subscription list on a schedule — an email or HTTP subscriber added later inherits every future alert, and confirm the key policy lets `cloudwatch.amazonaws.com` call `kms:GenerateDataKey*`/`kms:Decrypt`, or alarms will fail to publish silently.
SKILL.md
---
name: querying-aws-redshift
description: >-
Enables Redshift system-table (SYS_*) log publishing to S3 Tables in Apache
Iceberg format for both Provisioned clusters and Serverless namespaces,
verifies publishing status, and queries the published logs via any
Iceberg-compatible engine including Redshift and Athena. Covers system tables
such as sys_query_history, sys_query_text, sys_connection_log,
sys_query_detail, and sys_session_history. Applies when turning on S3 Tables
log publishing for a cluster or namespace, confirming publishing status and
locating the S3 Tables namespace, querying non-realtime data from Redshift
system tables off-cluster at scale, or building dashboards for Redshift
monitoring and auditing, especially for historical or high-volume system-table data
beyond the in-cluster SYS_ view retention window. Trigger phrases: publish
redshift system table log to s3 tables, enable-logging s3 tables, describe
redshift logging status, query redshift system tables in athena or redshift,
redshift log exports to iceberg.
version: 1
argument-hint: "['enable CLUSTER'|'status CLUSTER'|'query SQL'|'configure']"
---
# Query AWS Redshift System Tables
## Overview
**Works best with** the [AWS MCP server](https://docs.aws.amazon.com/aws-mcp/) for sandboxed execution and audit logging. All commands below use the AWS CLI and work in any environment with configured AWS credentials. Use IAM roles or temporary credentials; avoid long-lived access keys.
Redshift can publish **system tables** — the `SYS_*` monitoring data such as `sys_query_history`, `sys_query_detail`, and `sys_connection_log` — to **S3 Tables** as continuously-updated Apache Iceberg tables.
Terminology used throughout: **system table** refers to a `SYS_*` dataset generally, and each one maps 1:1 to a published Iceberg table. Where this skill says **`SYS_` view**, it means specifically the live in-cluster object you query on the cluster itself — that is a view, and it is a different thing from the published S3 Tables copy. This applies to both **Provisioned clusters** and **Serverless namespaces**. It is an opt-in extension of the existing logging APIs. Published tables are read-only, stored in the AWS-managed `aws-redshift` table bucket, and queryable via any Iceberg-compatible engine including Amazon Athena and Amazon Redshift itself.
Querying the S3 Tables copy is preferred over the live in-cluster `SYS_` views when analyzing historical or high-volume system-table data because:
- The in-cluster `SYS_` views have a limited retention window; S3 Tables retains history well beyond it.
- Querying S3 Tables adds **no load** to the running Redshift cluster.
- The logs are Iceberg tables, so they can be queried at scale from any Iceberg-compatible engine and joined with other lake data.
## Decision Tree
| User intent | Use this skill? | Alternative |
|---|---|---|
| Turn on S3 Tables log publishing for a cluster or namespace | **Yes** | — |
| Confirm a cluster/namespace is publishing / find its S3 Tables namespace | **Yes** | — |
| Querying non-realtime data from Redshift system tables | **Yes** | — |
| Build daily/weekly/monthly dashboard for Redshift monitoring and auditing | **Yes** | — |
| Selectively stop S3 Tables publishing | **Yes** | — |
| Query published system tables from Redshift (cross-database) | **Yes** | — |
| Query published system tables from Athena | **Yes** | — |
| Inspect the *current, real-time* `SYS_` state on a live cluster | **No** | Query the `SYS_` view on the cluster directly |
| Query data *inside* customer tables | **No** | Direct Redshift SQL on the cluster |
## Supported Data Sources
| Compute type | Enable / disable API | Status API | Granularity options |
|---|---|---|---|
| Redshift Provisioned cluster | `redshift enable-logging` / `redshift disable-logging` | `redshift describe-logging-status` | `cluster` (default), `account` |
| Redshift Serverless namespace | `redshift-serverless update-namespace` with `--s3-table-action Enable`/`Disable` | `redshift-serverless get-namespace` | `namespace` (default), `account` |
Both compute types publish into the same AWS-managed `aws-redshift` table bucket and are queried identically once published. They differ only in the enable/disable API surface and in the casing of the status response — see the flag and field tables in [Common Tasks](#common-tasks).
Not covered by this skill: Redshift audit logs delivered to S3 or CloudWatch (`useractivitylog`, `userlog`, `connectionlog`), which use the separate `--log-exports` mechanism on Serverless and are not `SYS_*` system tables.
## Common Tasks
### 1. Check If Configured
Before querying, confirm the cluster or namespace is publishing to S3 Tables.
```bash
# Provisioned
aws redshift describe-logging-status --region <REGION> --cluster-identifier <CLUSTER_ID>
# Serverless
aws redshift-serverless get-namespace --region <REGION> --namespace-name <NAMESPACE_NAME>
```
**Interpret the response.** The two compute types return the *same* information under **different field names and casing** — Provisioned uses PascalCase under `S3Tables`, Serverless uses camelCase under `namespace.s3TablePublishStatus`:
| Meaning | Provisioned (`describe-logging-status`) | Serverless (`get-namespace`) |
|---|---|---|
| Not enabled | `LoggingEnabled: false` or no `S3Tables` block | no `s3TablePublishStatus` block |
| Destination includes S3 Tables | `LogDestinationType` contains `s3table` | `logDestinationType` contains `s3table` |
| List of published `SYS_*` tables | `S3Tables.S3Tables` | `namespace.s3TablePublishStatus.s3Tables` |
| **The exact S3 Tables namespace** (required for querying) | `S3Tables.S3TableNamespace` | `namespace.s3TablePublishStatus.s3TableNamespace` |
| Granularity | `S3Tables.S3TableGranularity` (`cluster`/`account`) | `namespace.s3TablePublishStatus.s3TableGranularity` (`namespace`/`account`) |
| Per-table last ingest time | `S3Tables.LastIngestionTimes` | `namespace.s3TablePublishStatus.lastIngestionTimes` |
| All available system tables published | `S3Tables.EnabledAll` | `namespace.s3TablePublishStatus.enabledAll` |
Notes:
- `LogDestinationType` is a **comma-joined list** when more than one destination is active — e.g. `"cloudwatch,s3table"`. Test with a substring/contains check, not equality against `s3table`.
- An empty `LastIngestionTimes` / `lastIngestionTimes` map, or a table listed as published but absent from the map, means data for that table may still be in flight. Compare successive values to confirm new data is landing.
- On Serverless, do **not** read the top-level `logExports` field for this feature — that field carries the CloudWatch/S3 audit logs (`useractivitylog`, `userlog`, `connectionlog`) and is unrelated to `SYS_*` S3 Tables publishing.
### 2. Enable (if not configured)
```bash
# Provisioned
aws redshift enable-logging --region <REGION> --cluster-identifier <CLUSTER_ID> --log-destination-type s3table --log-exports <SYS_TABLE>... --s3-table-granularity <cluster|account> --s3-table-kms-key-id <KMS_KEY_ARN>
# Serverless
aws redshift-serverless update-namespace --region <REGION> --namespace-name <NAMESPACE_NAME> --log-destination-type s3table --s3-table-names <SYS_TABLE>... --s3-table-action Enable --s3-table-granularity <namespace|account> --s3-table-kms-key-id <KMS_KEY_ARN>
```
`--s3-table-kms-key-id` is part of both commands deliberately, not an optional add-on. Omitting it does not fail — the tables fall back to an AWS-owned key you cannot audit, restrict by policy, or revoke. Because `SYS_*` tables carry `query_text`, `user_name`, and `remote_host`, treat the customer-managed key as the default and drop the flag only for throwaway environments.
Enable from AWS console Amazon Redshift Console > Clusters > select your cluster > Tabs > Integrations / System table integration
**The two compute types take different flags for the same feature.** Do not carry Provisioned flag names over to Serverless:
| Purpose | Provisioned (`enable-logging`) | Serverless (`update-namespace`) |
|---|---|---|
| Which system tables to publish | `--log-exports` | `--s3-table-names` |
| Enable vs disable | separate `enable-logging` / `disable-logging` operations | `--s3-table-action Enable` \| `Disable` |
| Granularity | `--s3-table-granularity` `cluster` \| `account` | `--s3-table-granularity` `namespace` \| `account` |
| Customer-managed KMS key | `--s3-table-kms-key-id` | `--s3-table-kms-key-id` |
| Validate without applying | `--dry-run` | `--dry-run` |
Notes:
- Granularity: Provisioned supports `cluster` (default) or `account`; Serverless supports `namespace` (default) or `account`.
- `cluster`/`namespace` granularity → one S3 table per cluster/namespace; `account` → one shared table for all clusters/namespaces per account per region.
- Use `all` to publish all available `SYS_*` tables — `--log-exports all` on Provisioned, `--s3-table-names all` on Serverless.
- **Encryption at rest is strongly recommended for production.** Without `--s3-table-kms-key-id` the published tables are encrypted with an AWS-owned key, which you cannot audit, restrict by policy, or revoke. `SYS_*` tables carry `query_text`, `user_name`, and `remote_host` (see [Security Considerations](#security-considerations)), so pass a customer-managed key. Grant key access using the complete key policy in `${SKILL_DIR}/references/security.md` rather than an abbreviated action list — it needs **two** service principals (`systemtables.redshift.amazonaws.com` for publishing and `maintenance.s3tables.amazonaws.com` for table maintenance/compaction). Provisioning only the publishing principal lets writes succeed while compaction silently fails.
- Both operations accept `--dry-run` to validate the request without changing anything. Provisioned returns a `DryRunOperation` error on success ("Request would have succeeded, but DryRun flag is set"); Serverless returns an empty body and exit code 0. Note that the Serverless dry-run validates request *shape* only, not parameter values, so a successful dry-run there does not guarantee the values are accepted.
**Disable selectively:**
```bash
# Provisioned
aws redshift disable-logging --region <REGION> --cluster-identifier <CLUSTER_ID> --log-destination-type s3table --log-exports <SYS_TABLE>...
# Serverless
aws redshift-serverless update-namespace --region <REGION> --namespace-name <NAMESPACE_NAME> --log-destination-type s3table --s3-table-names <SYS_TABLE>... --s3-table-action Disable
```
### 3. Verify Permissions
Full setup commands for both paths: **`${SKILL_DIR}/references/permissions-setup.md`**. Load it before creating roles or registering resources.
**Athena path** — needs the `s3tablescatalog/aws-redshift` catalog registered in Glue, a workgroup with an output location, and S3 Tables read permissions. Confirm the catalog is queryable:
```bash
aws glue get-databases --region <REGION> \
--catalog-id "<ACCOUNT>:s3tablescatalog/aws-redshift"
```
Namespaces returned → registered and queryable. `EntityNotFoundException` / `CATALOG_NOT_FOUND` → the S3 Tables integration is not enabled (S3 console > Table buckets > Enable integration). **Encrypt the workgroup output location** — Athena writes full result sets, including `query_text` and `user_name`, to S3.
**Redshift auto-mount path** — needs a Provisioned RA3 cluster and a four-step setup: create the `query_s3_tables` role (trust policy must name *both* `redshift.amazonaws.com` and `lakeformation.amazonaws.com`, the latter with all four of `sts:AssumeRole`, `sts:SetContext`, `sts:SetSourceIdentity`, `sts:TagSession`), attach it to the cluster, register the table bucket with Lake Formation, and add the Redshift service-linked roles to `ReadOnlyAdmins`. Constraints that cause most failures:
- **Condition both trust statements on `aws:SourceAccount`** — a bare service principal is a confused-deputy risk.
- **Do not attach `AWSLakeFormationDataAdmin` to the cluster's query role.** It is needed only by the principal performing setup, and only during setup. The cluster's role needs read access alone.
- **Auto-mount is a poll, not a callback** — the catalog can take up to 300 seconds to appear in `pg_database`. A cluster reboot forces immediate discovery.
### 4. Identify the Target Table
**Namespace** — resolve it from the API, do not construct it:
- Read `S3Tables.S3TableNamespace` from `describe-logging-status` (Provisioned) or `s3TablePublishStatus.s3TableNamespace` from `get-namespace` (Serverless) and use it verbatim.
- Optional sanity check only: the API value typically follows `<namespace_arn_id>_sys` for `cluster`/`namespace` granularity and `<account>_sys` for `account` granularity. Use this only to *verify* the value looks right — never to generate the namespace when the API response is unavailable.
**Table** — each publishable system table maps 1:1 to a table in the `aws-redshift` table bucket. Do **not** work from a memorized list — resolve it at runtime, in this order:
1. **The published set for this cluster/namespace** — `S3Tables.S3Tables` (Provisioned) or `s3TablePublishStatus.s3Tables` (Serverless) from the status call above, e.g. `sys_query_history`. This is the only authoritative answer to "what can I query right now".
2. **The set this API accepts** — `aws redshift enable-logging help` (accepted `--log-exports` values) or `aws redshift-serverless update-namespace help` (accepted `--s3-table-names` values).
3. **What each table contains** — the public [Redshift SYS monitoring views reference](https://docs.aws.amazon.com/redshift/latest/dg/cm_chap_system-tables.html), which documents every `SYS_*` view and its columns. AWS adds views over time, so treat the docs as the current list rather than hardcoding one.
Column names and types come from the same public reference, or from the live table:
```bash
aws glue get-table --region <REGION> \
--catalog-id "<ACCOUNT>:s3tablescatalog/aws-redshift" \
--database-name "<NAMESPACE>" --name "<SYS_TABLE>"
```
Two caveats when reading the public docs against a published table: enum-valued columns (`query_type`, `status`, `event`) gain values over time, so confirm with `SELECT DISTINCT` rather than filtering on an assumed set; and the published Iceberg table prepends warehouse-identity columns (`warehouse_name`, `warehouse_namespace_arn`, and peers) that the in-cluster `SYS_` view does not have — they are how you tell apart multiple clusters publishing at `account` granularity.
### 5. Query
#### Query from Athena
**Query syntax:**
```sql
"s3tablescatalog/aws-redshift"."<NAMESPACE>"."<SYS_TABLE>"
```
#### Query from Redshift (Auto-Mounted Catalog)
Once the auto-mounted catalog is set up (see `${SKILL_DIR}/references/permissions-setup.md`), query using cross-database notation:
```sql
"aws-redshift@s3tablescatalog"."<NAMESPACE>".<SYS_TABLE>
```
#### Query from Redshift (External Schema)
Alternatively, create an external schema pointing to the S3 Tables catalog:
```sql
CREATE EXTERNAL SCHEMA <schema_name>
FROM DATA CATALOG
DATABASE '<NAMESPACE>'
CATALOG_ID '<ACCOUNT>:s3tablescatalog/aws-redshift'
IAM_ROLE 'arn:aws:iam::<ACCOUNT>:role/query_s3_tables'
REGION '<REGION>';
SELECT * FROM <schema_name>.<SYS_TABLE> LIMIT 10;
```
#### Constraints
- You MUST run `describe-logging-status` or `get-namespace` to get the namespace before writing any SQL query — never construct it manually
- For Athena queries, you MUST confirm workgroup and output location before executing
- **Timing columns are in microseconds.** Divide by `1000000.0` for seconds
- Tables are **read-only** — no `INSERT`/`UPDATE`/`DELETE`
- Always add a `LIMIT` when the user doesn't specify one; filter on `start_time`/`record_time` where possible
#### Examples
Worked SQL for the common asks — longest-running queries, error analysis, connection auditing, queue-time trends, cross-table joins — is in **`${SKILL_DIR}/references/example-queries.md`**. Two rules that apply to every one of them:
- **Timing columns are microseconds.** Divide by 1,000,000 for seconds. Reporting `elapsed_time` as-is overstates durations by 10^6.
- **Filter on the Iceberg partition columns** (`year`/`month`/`day` or the table's own partitioning) in addition to any timestamp predicate, or the engine scans the full history.
### Routing: Athena vs Redshift vs Direct SYS_ Access
| Scenario | Use |
|----------|-----|
| Historical/high-volume log analysis, no cluster load | Athena or Redshift on S3 Tables |
| Already connected to a Redshift cluster, want to query S3 Tables logs | Redshift cross-database or external schema |
| Join system table logs with other lake data | Athena or Redshift Spectrum |
| Real-time current state of the cluster | Direct `SYS_` view on the cluster |
| Quick ad-hoc query without Redshift cluster access | Athena |
## Key Behaviors
- **No backfill** — only events recorded after enabling are delivered to S3 Tables
- **Namespace from the API** — always read the namespace from `describe-logging-status` (`S3Tables.S3TableNamespace`) or `get-namespace` (`s3TablePublishStatus.s3TableNamespace`); never construct it manually
- **Microsecond timing** — all duration columns are in microseconds; divide by 1000000.0 for seconds
- **Read-only** — published tables cannot be written to
- **Both Provisioned and Serverless** — same table bucket (`aws-redshift`), different enable APIs
- **Any Iceberg-compatible engine** — query from Athena, Redshift, or any engine that reads Iceberg
## Troubleshooting
| Error | Cause | Fix |
|-------|-------|-----|
| `aws-redshift` bucket not found | S3 Tables integration not enabled or logging not started | Run `enable-logging` (Provisioned) or `update-namespace` (Serverless) with `--log-destination-type s3table` |
| `CATALOG_NOT_FOUND` in Athena | S3 Tables not registered in Glue | Enable integration: S3 console > Table buckets > Enable integration |
| Athena table empty after enabling | Ingestion still in flight | Check `LastIngestionTimes` (Provisioned) / `lastIngestionTimes` (Serverless); wait and re-query |
| `SYS_*` table missing from the namespace | System table not included when enabling | Re-run enable with that table included, or use `all` — `--log-exports` (Provisioned), `--s3-table-names` (Serverless) |
| Wrong / empty namespace | Namespace constructed instead of read from API | Use the namespace from the describe/get response — `S3Tables.S3TableNamespace` (Provisioned) or `s3TablePublishStatus.s3TableNamespace` (Serverless) |
| Status response has no `S3Tables` / `s3TablePublishStatus` field at all, even though publishing is on | Outdated AWS CLI / SDK. The field is **silently omitted** rather than raising an error, so this looks identical to the feature being disabled | Upgrade the CLI/SDK, then re-run. Confirm publishing is actually off before acting on the absence — check the `aws-redshift` table bucket for the namespace, or that `LogDestinationType` includes `s3table` |
| `Unknown options: --log-exports, --log-export-action` on Serverless | Provisioned flag names used against `update-namespace` | Use `--s3-table-names` and `--s3-table-action` — see the flag table in the Enable section |
| `AccessDenied` querying the table | Missing `s3tables:GetTable` or `GetTableData` | See `references/security.md` |
| Empty results from `sys_connection_log` | Querying identity lacks visibility | Use an identity with superuser-level access |
| Catalog doesn't appear in `pg_database` | LF resource not registered, or SLRs not ReadOnlyAdmins | Complete the Lake Formation steps in `references/permissions-setup.md`, wait 5 min or reboot |
| "Unable to assume role" from Glue | Missing `sts:SetContext`/`sts:SetSourceIdentity` in trust policy, or missing `AWSLakeFormationDataAdmin` | Fix trust policy and attach `AWSLakeFormationDataAdmin` |
| `VerificationStatus: NOT_VERIFIED` | Normal after Lake Formation registration | No action needed if queries work |
| Query fails with "does not exist" in Redshift | Catalog not yet auto-mounted (poll delay) | Wait up to 300s or reboot cluster |
## Security Considerations
Full policies, key policy, and detection setup: **`${SKILL_DIR}/references/security.md`**. Read it before granting access. The non-negotiables:
- **Scope IAM to the S3 Tables catalog**, not wildcards. Glue database/table ARNs nest under `s3tablescatalog/aws-redshift` — the bare `database/*` form grants metadata read on the whole account. `lakeformation:GetDataAccess` is the one action that must use `"Resource": "*"`; constrain it with an `aws:ResourceAccount` `StringEquals` condition.
- **The KMS key policy needs two principals**, not one: `systemtables.redshift.amazonaws.com` (publisher) and `maintenance.s3tables.amazonaws.com` (compaction). Granting only the publisher lets writes succeed while compaction silently fails.
- **`query_text` can contain credentials**, not just schema — interpolated SQL and `CREATE USER ... PASSWORD` land verbatim in `sys_query_history`. Treat broad access to that table as a secrets-exposure decision; restrict the column with Lake Formation.
- **Publishing is itself auditable and worth alarming on.** `s3tables.amazonaws.com` `AccessDenied` spikes and `sys_connection_log` failed-auth counts are the two signals to alert on; encrypt the alarm topic with a customer-managed key.
## Reference Files
`${SKILL_DIR}` is the absolute path of the directory containing this SKILL.md. Load these on demand; do not read them all up front.
| File | What it covers | When to load |
|---|---|---|
| `${SKILL_DIR}/references/permissions-setup.md` | Athena prerequisites and workgroup encryption; the full Redshift auto-mount path — IAM role trust/inline policies, Lake Formation `register-resource`, `put-data-lake-settings`, the SLR `ReadOnlyAdmins` step, and the 300s auto-mount poll | Before running any IAM or Lake Formation setup |
| `${SKILL_DIR}/references/example-queries.md` | Worked SQL for longest-running queries, error analysis, connection auditing, queue-time trends, and joins across `sys_*` tables | When writing queries against the published tables |
| `${SKILL_DIR}/references/security.md` | Full least-privilege policy, KMS key policy with both service principals, `query_text` sensitivity, CloudTrail/metric-filter detection | Before granting access, or when hardening an existing setup |
## Additional Resources
- [Integrating S3 Tables with AWS analytics services](https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-tables-integrating-aws.html)
- [Redshift System Tables](https://docs.aws.amazon.com/redshift/latest/dg/serverless_views-monitoring.html)
- [Lake Formation permissions](https://docs.aws.amazon.com/lake-formation/latest/dg/granting-catalog-permissions.html)
Security best practices:
- [Amazon Redshift security best practices](https://docs.aws.amazon.com/redshift/latest/mgmt/security-best-practices.html)
- [S3 Tables security](https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-tables-security.html) and [access management for S3 Tables](https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-tables-permissions.html)
- [IAM security best practices](https://docs.aws.amazon.com/IAM/latest/UserGuide/best-practices.html)
- [Lake Formation underlying data access control](https://docs.aws.amazon.com/lake-formation/latest/dg/access-control-underlying-data.html) — why `lakeformation:GetDataAccess` requires `"Resource": "*"`