Trade-offs in AWS Aurora PostgreSQL

psql 'service=aurora-probe-admin' -X -v ON_ERROR_STOP=1 <<'SQL'
CREATE ROLE tradeoff_reader LOGIN;
GRANT rds_iam TO tradeoff_reader;
CREATE SCHEMA tradeoff_admission;
CREATE TABLE tradeoff_admission.orders (id text PRIMARY KEY, status text NOT NULL);
INSERT INTO tradeoff_admission.orders VALUES ('order-A', 'ready');
GRANT USAGE ON SCHEMA tradeoff_admission TO tradeoff_reader;
GRANT SELECT ON tradeoff_admission.orders TO tradeoff_reader;
SQL

import boto3
import psycopg
from psycopg.errors import InsufficientPrivilege

signer = boto3.Session(profile_name="adapter-test", region_name="us-east-1").client("rds")
host = "writer.example.com"

def token(user):
    return signer.generate_db_auth_token(
        DBHostname=host, Port=5432, DBUsername=user
    )

def connect(password):
    return psycopg.connect(
        host=host, port=5432, dbname="postgres", user="tradeoff_reader",
        password=password, sslmode="verify-full",
        sslrootcert="/absolute/path/to/proxy-ca.pem", connect_timeout=10,
        autocommit=True,
    )

with connect(token("tradeoff_reader")) as conn:
    assert conn.execute("SELECT current_user").fetchone()[0] == "tradeoff_reader"
    assert conn.execute(
        "SELECT status FROM tradeoff_admission.orders WHERE id = 'order-A'"
    ).fetchone()[0] == "ready"
    try:
        conn.execute("UPDATE tradeoff_admission.orders SET status = 'changed'")
    except InsufficientPrivilege:
        print("Database privileges rejected the write")
    else:
        raise AssertionError("Read-only role unexpectedly wrote data")

try:
    wrong = connect(token("different_user"))
except psycopg.OperationalError:
    print("Wrong-user connection failed; verify authentication rejection in diagnostics")
else:
    wrong.close()
    raise AssertionError("Wrong-user token was accepted")

with connect(token("tradeoff_reader")) as conn:
    assert conn.execute("SELECT current_user").fetchone()[0] == "tradeoff_reader"

psql 'service=aurora-probe-admin' -X -v ON_ERROR_STOP=1 <<'SQL'
DROP TABLE tradeoff_admission.orders;
DROP SCHEMA tradeoff_admission;
DROP ROLE tradeoff_reader;
SQL