DEV Community

Railander Marques for AWS Community Builders

Posted on Edited on

Athena via ODBC sem access key: IAM Roles Anywhere para Power BI e Excel fora da AWS

1. Objetivo

Liberar acesso ao Amazon Athena via driver ODBC (Power BI, Excel, DBeaver, qualquer coisa que fale ODBC) a partir de máquinas fora da AWS, sem criar access key e sem depender de IAM Identity Center. A máquina do usuário troca um certificado X.509 por credenciais temporárias de 1 hora através do IAM Roles Anywhere. O signing helper fica rodando em segundo plano e mantém essas credenciais sempre renovadas num profile do arquivo credentials, que é de onde o driver lê.

Resultado esperado:

  • Nenhuma access key fixa existe na conta para esse acesso.
  • Cada usuário tem o próprio certificado, com validade curta (o tempo do projeto).
  • A role só enxerga um workgroup do Athena e os dados que precisa.
  • No CloudTrail dá para saber qual certificado fez cada consulta.
  • Encerrar o acesso de todo mundo é um comando.

2. Por que não access key (e por que não Identity Center)

Opção Problema
Usuário IAM + access key Credencial de longa duração parada num arquivo da máquina de alguém. Vaza, ninguém percebe, ninguém rotaciona.
IAM Identity Center Ótimo quando existe. Mas nem sempre dá para usar: a organização pode ser só interna, o usuário pode ser de outra empresa, e o driver precisa de fluxo de navegador configurado.
Cognito + federação Funciona, mas é muita peça para um acesso que vai durar semanas.
IAM Roles Anywhere Credencial temporária, validade amarrada ao certificado, sem usuário IAM. O custo é gerenciar uma CA pequena.

Esse cenário aparece muito em projeto de dashboard: o time do cliente precisa conectar o Power BI direto no Athena durante a construção, e depois o acesso acaba. Roles Anywhere encaixa bem nisso porque o próprio certificado já carrega a data de fim.


3. Arquitetura

 Maquina do usuario (Windows)
 +--------------------------------------------------+
 |  Power BI / Excel                                |
 |      |                                           |
 |  Driver ODBC Athena 2.x  (Auth: IAM Profile)     |
 |      ^  le ~/.aws/credentials [athena-odbc]    |
 |      |                                           |
 |  aws_signing_helper.exe update (segundo plano)   |
 |      |  assina com usuario.key + usuario.pem     |
 +------|-------------------------------------------+
        | HTTPS
        v
 IAM Roles Anywhere  --valida contra-->  Trust anchor (CA propria)
        |
        | credenciais temporarias (1h)
        v
 Athena (workgroup dedicado) --> Glue Data Catalog --> S3 (dados) / S3 (resultados)
Enter fullscreen mode Exit fullscreen mode

4. Pré-requisitos

  • [ ] Dados já catalogados no Glue e consultáveis pelo Athena
  • [ ] OpenSSL na máquina onde a CA vai ficar (não na máquina do usuário)
  • [ ] Driver ODBC do Athena versão 2.x na máquina do usuário
  • [ ] AWS CLI v2 na sua máquina
export AWS_REGION="us-east-1"
export ACCOUNT_ID="<ACCOUNT_ID>"
export WORKGROUP="wg-dashboards"
export BUCKET_DADOS="<BUCKET_DADOS>"
export BUCKET_RESULTADOS="<BUCKET_RESULTADOS_ATHENA>"
export GLUE_DB="<DATABASE_GLUE>"
Enter fullscreen mode Exit fullscreen mode

5. Passo 1: Workgroup dedicado

Um workgroup só para esse acesso, com local de resultado forçado e limite de bytes escaneados por consulta. O limite evita que um SELECT * sem filtro numa tabela grande vire uma fatura surpresa.

aws athena create-work-group --name "$WORKGROUP" \
  --configuration "ResultConfiguration={OutputLocation=s3://${BUCKET_RESULTADOS}/${WORKGROUP}/},EnforceWorkGroupConfiguration=true,PublishCloudWatchMetricsEnabled=true,BytesScannedCutoffPerQuery=10737418240" \
  --description "Acesso ODBC externo via Roles Anywhere"
Enter fullscreen mode Exit fullscreen mode

10737418240 é 10 GB por consulta. Ajuste ao tamanho dos seus dados.

Com EnforceWorkGroupConfiguration=true, o usuário não consegue mandar o resultado para outro bucket, mesmo que configure isso no driver.


6. Passo 2: CA e trust anchor

6.1 CA

mkdir -p ~/ca-rolesanywhere/{private,usuarios} && cd ~/ca-rolesanywhere
chmod 700 private

cat > ca.cnf <<'EOF'
[ req ]
distinguished_name = dn
prompt = no
x509_extensions = ext_ca
[ dn ]
CN = CA Acesso Athena
O  = <SUA_ORG>
C  = BR
[ ext_ca ]
basicConstraints = critical, CA:true
keyUsage = critical, keyCertSign, cRLSign
subjectKeyIdentifier = hash
EOF

openssl genrsa -out private/ca.key 4096
chmod 400 private/ca.key
openssl req -new -x509 -days 1825 -config ca.cnf -key private/ca.key -out ca.pem
Enter fullscreen mode Exit fullscreen mode

6.2 Trust anchor

jq -n --arg pem "$(cat ca.pem)" \
  '{sourceType:"CERTIFICATE_BUNDLE", sourceData:{x509CertificateData:$pem}}' > source.json

TA_ARN=$(aws rolesanywhere create-trust-anchor \
  --name ta-athena-odbc \
  --source file://source.json \
  --enabled \
  --query 'trustAnchor.trustAnchorArn' --output text)

echo "$TA_ARN"
Enter fullscreen mode Exit fullscreen mode

Se a CA tiver custo de gestão que você não quer, a alternativa é o AWS Private CA como fonte do trust anchor. Funciona sem OpenSSL na emissão, mas tem custo mensal fixo. Para acesso de poucas semanas, a CA local resolve.


7. Passo 3: Role com trust policy restrita

O Roles Anywhere transforma o subject do certificado em session tags (x509Subject/CN, x509Subject/OU etc.). Isso permite que a trust policy aceite só certificados de um OU específico, mesmo que a CA emita certificados para outros fins.

trust-ra.json:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": { "Service": "rolesanywhere.amazonaws.com" },
      "Action": ["sts:AssumeRole", "sts:TagSession", "sts:SetSourceIdentity"],
      "Condition": {
        "ArnEquals": {
          "aws:SourceArn": "arn:aws:rolesanywhere:us-east-1:<ACCOUNT_ID>:trust-anchor/<TRUST_ANCHOR_ID>"
        },
        "StringEquals": {
          "aws:PrincipalTag/x509Subject/OU": "athena-odbc"
        }
      }
    }
  ]
}
Enter fullscreen mode Exit fullscreen mode

Permissões (policy-athena.json). O que mais esquecem: o PutObject no bucket de resultados e o kms:Decrypt se os dados usam SSE-KMS.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "AthenaWorkgroup",
      "Effect": "Allow",
      "Action": [
        "athena:StartQueryExecution",
        "athena:StopQueryExecution",
        "athena:GetQueryExecution",
        "athena:GetQueryResults",
        "athena:GetQueryResultsStream",
        "athena:ListQueryExecutions",
        "athena:GetWorkGroup"
      ],
      "Resource": "arn:aws:athena:us-east-1:<ACCOUNT_ID>:workgroup/wg-dashboards"
    },
    {
      "Sid": "AthenaMetadados",
      "Effect": "Allow",
      "Action": [
        "athena:ListWorkGroups",
        "athena:ListDataCatalogs",
        "athena:GetDataCatalog",
        "athena:ListDatabases",
        "athena:GetDatabase",
        "athena:ListTableMetadata",
        "athena:GetTableMetadata"
      ],
      "Resource": "*"
    },
    {
      "Sid": "Glue",
      "Effect": "Allow",
      "Action": [
        "glue:GetDatabase",
        "glue:GetDatabases",
        "glue:GetTable",
        "glue:GetTables",
        "glue:GetPartition",
        "glue:GetPartitions",
        "glue:BatchGetPartition"
      ],
      "Resource": [
        "arn:aws:glue:us-east-1:<ACCOUNT_ID>:catalog",
        "arn:aws:glue:us-east-1:<ACCOUNT_ID>:database/<DATABASE_GLUE>",
        "arn:aws:glue:us-east-1:<ACCOUNT_ID>:table/<DATABASE_GLUE>/*"
      ]
    },
    {
      "Sid": "LeituraDados",
      "Effect": "Allow",
      "Action": ["s3:GetObject", "s3:ListBucket", "s3:GetBucketLocation"],
      "Resource": [
        "arn:aws:s3:::<BUCKET_DADOS>",
        "arn:aws:s3:::<BUCKET_DADOS>/*"
      ]
    },
    {
      "Sid": "Resultados",
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:PutObject",
        "s3:ListBucket",
        "s3:GetBucketLocation",
        "s3:AbortMultipartUpload",
        "s3:ListMultipartUploadParts"
      ],
      "Resource": [
        "arn:aws:s3:::<BUCKET_RESULTADOS_ATHENA>",
        "arn:aws:s3:::<BUCKET_RESULTADOS_ATHENA>/wg-dashboards/*"
      ]
    }
  ]
}
Enter fullscreen mode Exit fullscreen mode

Se os dados estão sob Lake Formation, a parte de S3 de dados sai e entra lakeformation:GetDataAccess mais o grant da role na tabela pelo Lake Formation. Sem o grant, o erro é Insufficient Lake Formation permission(s), mesmo com a policy IAM certa.

aws iam create-role --role-name ra-athena-odbc \
  --assume-role-policy-document file://trust-ra.json \
  --max-session-duration 3600

aws iam put-role-policy --role-name ra-athena-odbc \
  --policy-name athena-wg-dashboards \
  --policy-document file://policy-athena.json
Enter fullscreen mode Exit fullscreen mode

8. Passo 4: Profile do Roles Anywhere

ROLE_ARN="arn:aws:iam::${ACCOUNT_ID}:role/ra-athena-odbc"

PROFILE_ARN=$(aws rolesanywhere create-profile \
  --name prof-athena-odbc \
  --role-arns "$ROLE_ARN" \
  --duration-seconds 3600 \
  --enabled \
  --query 'profile.profileArn' --output text)

echo "$PROFILE_ARN"
Enter fullscreen mode Exit fullscreen mode

O profile também aceita --managed-policy-arns e --session-policy, que funcionam como teto em cima da role. Útil se a mesma role atende mais de um perfil de uso.


9. Passo 5: Emitir o certificado do usuário

A validade do certificado é o prazo do acesso. Projeto de 45 dias, certificado de 45 dias.

#!/usr/bin/env bash
set -euo pipefail
USUARIO="$1"     # ex.: analista-01
DIAS="${2:-45}"
cd ~/ca-rolesanywhere

cat > "usuarios/${USUARIO}.ext" <<'EOF'
basicConstraints = critical, CA:false
keyUsage = critical, digitalSignature
subjectKeyIdentifier = hash
authorityKeyIdentifier = keyid
EOF

openssl genrsa -out "usuarios/${USUARIO}.key" 2048
openssl req -new -key "usuarios/${USUARIO}.key" \
  -subj "/CN=${USUARIO}/OU=athena-odbc/O=<SUA_ORG>/C=BR" \
  -out "usuarios/${USUARIO}.csr"

openssl x509 -req -in "usuarios/${USUARIO}.csr" \
  -CA ca.pem -CAkey private/ca.key -CAcreateserial \
  -days "$DIAS" -sha256 -extfile "usuarios/${USUARIO}.ext" \
  -out "usuarios/${USUARIO}.pem"

rm -f "usuarios/${USUARIO}.csr" "usuarios/${USUARIO}.ext"
openssl x509 -in "usuarios/${USUARIO}.pem" -noout -subject -serial -enddate
Enter fullscreen mode Exit fullscreen mode

Guarde numa planilha a relação serial, usuário, data de fim. O serial é o que aparece no CloudTrail.

O OU=athena-odbc é o que a trust policy do passo 3 exige. Certificado sem esse OU não assume a role, mesmo vindo da mesma CA.


10. Passo 6: Configurar a máquina do usuário (Windows)

10.1 Signing helper

Baixe o aws_signing_helper.exe da página de documentação do Roles Anywhere (seção "Obtaining temporary security credentials") e coloque em C:\aws\. Coloque o .pem e o .key em C:\aws\certs\ e restrinja a pasta ao próprio usuário:

New-Item -ItemType Directory -Force C:\aws\certs | Out-Null
icacls C:\aws\certs /inheritance:r /grant:r "$($env:USERNAME):(OI)(CI)F"
Enter fullscreen mode Exit fullscreen mode

Teste o helper sozinho antes de mexer no ODBC. Se isso não devolver JSON com AccessKeyId, nada depois vai funcionar:

C:\aws\aws_signing_helper.exe credential-process `
  --certificate C:\aws\certs\analista-01.pem `
  --private-key C:\aws\certs\analista-01.key `
  --trust-anchor-arn <TRUST_ANCHOR_ARN> `
  --profile-arn <PROFILE_ARN> `
  --role-arn <ROLE_ARN>
Enter fullscreen mode Exit fullscreen mode

10.2 Manter as credenciais renovadas no credentials

Por que não credential_process no config: é o caminho mais comum com AWS CLI e SDKs, mas a documentação do IAM Profile do driver ODBC 2.x só cita credential_source (Ec2InstanceMetadata, EcsContainer, Environment) e plugin_name. Não dá para contar com credential_process ali. O que o driver garante é ler o profile do arquivo credentials e, nas versões mais novas, recarregar quando o arquivo muda.

Então o helper roda no modo update: ele escreve credenciais temporárias no profile athena-odbc do %USERPROFILE%\.aws\credentials e reescreve antes de expirar.

%USERPROFILE%\.aws\config:

[profile athena-odbc]
region = us-east-1
Enter fullscreen mode Exit fullscreen mode

Script C:\aws\athena-cred.ps1:

& C:\aws\aws_signing_helper.exe update `
  --profile athena-odbc `
  --certificate C:\aws\certs\analista-01.pem `
  --private-key C:\aws\certs\analista-01.key `
  --trust-anchor-arn <TRUST_ANCHOR_ARN> `
  --profile-arn <PROFILE_ARN> `
  --role-arn <ROLE_ARN>
Enter fullscreen mode Exit fullscreen mode

Para não depender de alguém lembrar de abrir, registre como tarefa agendada no logon do usuário:

$acao = New-ScheduledTaskAction -Execute "powershell.exe" `
  -Argument "-NoProfile -WindowStyle Hidden -File C:\aws\athena-cred.ps1"
$gatilho = New-ScheduledTaskTrigger -AtLogOn -User $env:USERNAME
Register-ScheduledTask -TaskName "athena-roles-anywhere" -Action $acao -Trigger $gatilho
Start-ScheduledTask -TaskName "athena-roles-anywhere"
Enter fullscreen mode Exit fullscreen mode

Confira que o profile apareceu no %USERPROFILE%\.aws\credentials com aws_access_key_id, aws_secret_access_key e aws_session_token. Essas chaves são temporárias e valem 1 hora; o helper troca sozinho.

Se a máquina tiver AWS CLI:

aws sts get-caller-identity --profile athena-odbc
Enter fullscreen mode Exit fullscreen mode

O Arn volta como assumed-role/ra-athena-odbc/<SERIAL>.

Chave privada fora de arquivo. O signing helper também lê do repositório de certificados do Windows com --cert-selector, no lugar de --certificate e --private-key. Se importar o .pfx marcando a chave como não exportável, o arquivo .key deixa de existir no disco. Vale para acesso mais longo. Para um acesso de semanas, a pasta com ACL resolve.

10.3 DSN ODBC

Abra o ODBC Data Sources (64-bit) (Power BI Desktop é 64 bits; usar o de 32 é o erro número um aqui) e crie um System DSN com o driver Amazon Athena ODBC (x64):

Campo Valor
Data Source Name Athena-Dashboards
Region us-east-1
Catalog AwsDataCatalog
Database <DATABASE_GLUE>
Workgroup wg-dashboards
S3 Output Location em branco (o workgroup força)
Authentication Type IAM Profile
AWS Profile athena-odbc

Clique em Test. Se der certo, no Power BI use Obter dados → ODBC → Athena-Dashboards.

Gateway on-premises do Power BI. O gateway roda como serviço (PBIEgwService), não como o usuário logado. Ele não lê o %USERPROFILE%\.aws de ninguém. Nesse caso aponte o update para um arquivo fixo com a variável AWS_SHARED_CREDENTIALS_FILE=C:\aws\credentials (no ambiente da tarefa agendada e como variável de sistema), faça o mesmo com AWS_CONFIG_FILE=C:\aws\config, dê leitura na pasta para a conta do serviço e reinicie o gateway. Esse detalhe consome uma tarde se ninguém avisar.


11. Passo 7: Rastrear quem consultou o quê

Por padrão, o nome da sessão é o serial do certificado. No CloudTrail:

aws cloudtrail lookup-events \
  --lookup-attributes AttributeKey=EventName,AttributeValue=StartQueryExecution \
  --max-results 20 \
  --query 'Events[].CloudTrailEvent' --output text \
  | jq -r 'select(.userIdentity.arn | contains("ra-athena-odbc")) | [.eventTime, .userIdentity.arn, .sourceIPAddress] | @tsv'
Enter fullscreen mode Exit fullscreen mode

Cruzando o serial com a planilha do passo 5, você tem usuário, horário e IP de origem. O texto da consulta fica no histórico do workgroup (aws athena list-query-executions --work-group wg-dashboards).

Para ver o consumo do workgroup:

aws cloudwatch get-metric-statistics --namespace AWS/Athena \
  --metric-name ProcessedBytes --dimensions Name=WorkGroup,Value=wg-dashboards \
  --start-time "$(date -u -d '-7 days' +%FT%TZ)" --end-time "$(date -u +%FT%TZ)" \
  --period 86400 --statistics Sum
Enter fullscreen mode Exit fullscreen mode

12. Passo 8: Encerrar o acesso

Tem três níveis, do mais pontual ao mais amplo:

Um usuário só, antes do certificado vencer: gere uma CRL com o serial dele e importe no Roles Anywhere. Como a CA do passo 2 é simples (sem o banco do openssl ca), monte um índice mínimo só para gerar a CRL:

cd ~/ca-rolesanywhere
SERIAL=$(openssl x509 -in usuarios/analista-01.pem -noout -serial | cut -d= -f2)
REVOGADO_EM=$(date -u +%y%m%d%H%M%SZ)
VENCE_EM=$(openssl x509 -in usuarios/analista-01.pem -noout -enddate | cut -d= -f2 \
  | xargs -I{} date -u -d "{}" +%y%m%d%H%M%SZ)

mkdir -p crl && touch crl/index.txt && echo 1000 > crl/crlnumber
printf 'R\t%s\t%s\t%s\tunknown\t/CN=analista-01\n' "$VENCE_EM" "$REVOGADO_EM" "$SERIAL" >> crl/index.txt

cat > crl.cnf <<'EOF'
[ ca ]
default_ca = ca_crl
[ ca_crl ]
database         = crl/index.txt
crlnumber        = crl/crlnumber
default_md       = sha256
default_crl_days = 30
EOF

openssl ca -config crl.cnf -gencrl -keyfile private/ca.key -cert ca.pem -out crl.pem
openssl crl -in crl.pem -noout -text | grep -A1 "Serial Number"

aws rolesanywhere import-crl --name crl-athena-odbc \
  --trust-anchor-arn "$TA_ARN" \
  --crl-data fileb://crl.pem --enabled
Enter fullscreen mode Exit fullscreen mode

Todo mundo, temporariamente:

aws rolesanywhere disable-profile --profile-id <PROFILE_ID>
Enter fullscreen mode Exit fullscreen mode

Fim do projeto:

aws rolesanywhere delete-profile --profile-id <PROFILE_ID>
aws rolesanywhere delete-trust-anchor --trust-anchor-id <TRUST_ANCHOR_ID>
aws iam delete-role-policy --role-name ra-athena-odbc --policy-name athena-wg-dashboards
aws iam delete-role --role-name ra-athena-odbc
Enter fullscreen mode Exit fullscreen mode

Credenciais já emitidas continuam válidas até expirar (no máximo 1 hora, pela configuração do profile). Se precisar cortar na hora, use Revoke active sessions na role pelo console, que adiciona uma deny por aws:TokenIssueTime.


13. Troubleshooting

Sintoma Causa provável Ação
Helper retorna AccessDeniedException OU do certificado não bate com a trust policy, ou trust anchor errado no ARN openssl x509 -noout -subject e conferir aws:SourceArn
Helper retorna erro de assinatura .pem e .key não são par Comparar openssl x509 -noout -modulus e openssl rsa -noout -modulus
DSN testa ok, Power BI não lista tabelas Falta glue:GetTables ou grant no Lake Formation Policy do passo 3
Access denied when writing output Falta PutObject no prefixo do workgroup Policy de resultados
Funciona no Desktop, falha no gateway Gateway roda como serviço AWS_SHARED_CREDENTIALS_FILE e AWS_CONFIG_FILE de sistema
Funciona e para de funcionar depois de 1 hora Helper update não está rodando Tarefa agendada e Get-ScheduledTask athena-roles-anywhere
Query exhausted resources ou corte por bytes Limite do workgroup Partições no filtro ou aumentar o limite
Driver não acha o profile DSN de 32 bits ou profile com nome diferente ODBC 64-bit, [profile athena-odbc]

14. Referências

Top comments (0)