Try IAM Database Authentication from Python

background

It seems that IAM can now be used for DB connection between RDS for MySQL and Aurora. Manage access to your RDS for MySQL and Amazon Aurora databases using AWS IAM IAM Database Authentication for MySQL and Amazon Aurora Since the sample was Java, I tried it from Python.

environment

Preparation

Aurora From the RDS cluster, open "Modify Cluster" and set "Enable DB Authentication for IAM" to "Yes". For Aurora db.t2.small does not support IAM database authentication, so try with db.t2.medium or higher. RDS_·_AWS_Console.png

Creating a DB user

Create a DB user for IAM access and grant the required permissions.

mysql> CREATE USER iam_auth_user@'testdb-cluster.cluster-abcdefghijkl.ap-northeast-1.rds.amazonaws.com' IDENTIFIED WITH AWSAuthenticationPlugin as 'RDS';
mysql> GRANT SELECT ON `testdb`.* TO iam_auth_user@'%';

Download public key

Since IAM database authentication requires SSL connection, download the public key and place it in an appropriate path on ec2. http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.Overview.html#Aurora.Overview.Security.SSL

IAM Grant the authority to IAM Role by referring to the document. The resource ID is specified for the cluster.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "rds-db:connect"
      ],
      "Resource": [
        "arn:aws:rds-db:ap-northeast-1:12345678:dbuser:cluster-12ABC34DEFG5HIJ6KLMNOP78QR/iam_auth_user"
      ]
    }
  ]
}

Connect

iam_db_auth.py


#  -*- coding: utf-8 -*-
from __future__ import print_function
import boto3
import mysql.connector
from mysql.connector.constants import ClientFlag

rds = boto3.client('rds', region_name='ap-northeast-1')

user = 'iam_auth_user'
host = 'testdb-cluster.cluster-abcdefghijkl.ap-northeast-1.rds.amazonaws.com'
db_auth_token = rds.generate_db_auth_token(host, 3306, user, 'ap-northeast-1')

config = {
    'user': user,
    'password': db_auth_token,
    'host': host,
    'db': 'testdb',
    'client_flags': [ClientFlag.SSL],
    'ssl_ca': 'rds-combined-ca-bundle.pem'
}

cnx = mysql.connector.connect(**config)
cur = cnx.cursor(buffered=True)

cur.execute('SELECT AURORA_VERSION();')
print(cur.fetchone())

cur.close()
cnx.close()
$ python iam_db_auth.py 
[(u'1.12',)]

that's all.

reference

Recommended Posts

Try IAM Database Authentication from Python
Try python
Connect to utf8mb4 database from python
Try using Amazon DynamoDB from Python
[SAM] Try using RDS Proxy with Lambda (Python) [user/pass, IAM authentication]
Passwordless authentication with RDS and IAM (Python)
Check cybozu.com's SAML authentication from python (pysaml2)
Try calling Python from Ruby with thrift
Python> try: / except:
sql from python
MeCab from Python
Python Https Authentication
Try operating Studio Library from Python. [Anim Save]
Hit Cognito-authenticated AppSync from behind with IAM authentication
Try accessing the YQL API directly from Python 3
Use thingsspeak from python
Touch MySQL from Python 3
Try scraping with Python.
Operate Filemaker from Python
Use fluentd from python
python try ~ except ~ else
Access bitcoind from python
Changes from Python 3.0 to Python 3.5
Changes from Python 2 to Python 3.0
Python from or import
Use MySQL from Python
Run python from excel
Install python from source
Execute command from Python
Try Debian + Python 3.4 + django1.7 ...
Operate neutron from Python!
Try gRPC in Python
Use MySQL from Python
Operate LXC from Python
Manipulate riak from python
Force Python from Fortran
Use BigQuery from python.
Try 9 slices in Python
Execute command from python
Try using Tweepy [Python2.7]
Get data from database via ODBC with Python (Access)
Python try / except notes
[Python] Read From Stdin
Use mecab-ipadic-neologd from python
Flatten using Python yield from
Call CPLEX from Python (DO cplex)
Deep Python learned from DEAP
Post from Python to Slack
Grammar features added from Python3.6
Cheating from PHP to Python
[Python] Try using Tkinter's canvas
BASIC authentication with Python bottle
Make MeCab available from Python3
Try to understand Python self
Try Python output with Haxe 3.2
Information obtained from tweet_id (Python)
Try using Kubernetes Client -Python-
OCR from PDF in Python
Run illustrator script from python
Use MySQL from Anaconda (python)
Anaconda updated from 4.2.0 to 4.3.0 (python3.5 updated to python3.6)