Ministack: Free, open-source local AWS emulator - 60+ services, Terraform compatible, real databases. Free forever. MIT licensed.
Python awsaws-emulatoraws-localaws-sdkdevtoolsdockerdynamodbec2emulatorlambdalocalstacklocalstack-alternativeministackmock-awsopen-sourcepythons3sqsterraform
"""
Integration tests for the Aurora DSQL control-plane emulator, plus
validator unit tests and live data-plane (wire proxy) tests.
"""
import asyncio
import logging
import os
import re
import secrets
import socket
import struct
import threading
import time
from urllib.parse import urlparse
import pytest
from botocore.exceptions import ClientError
from ministack.core import pgproxy
# Raw HTTP calls here must hit the same server the boto3 fixtures use. Hardcoding
# 4566 silently fails everywhere except a default-port run (CI), which is exactly
# the kind of test that looks green until someone runs the suite on another port.
_ENDPOINT = os.environ.get("MINISTACK_ENDPOINT", "http://localhost:4566").rstrip("/")
_ENDPOINT_HOST = urlparse(_ENDPOINT).hostname or "localhost"
_ENDPOINT_PORT = urlparse(_ENDPOINT).port or 4566
ID_RE = re.compile(r"^[a-z0-9]{26}$")
def _create(dsql, **kwargs):
resp = dsql.create_cluster(**kwargs)
return resp["identifier"]
def _cleanup(dsql, identifier):
try:
dsql.update_cluster(identifier=identifier, deletionProtectionEnabled=False)
except ClientError:
pass
try:
dsql.delete_cluster(identifier=identifier)
except ClientError:
pass
def _wait_active(dsql, identifier, timeout=60):
"""Poll GetCluster until the cluster is ACTIVE (backend may spin up async)."""
deadline = time.time() + timeout
while time.time() < deadline:
cluster = dsql.get_cluster(identifier=identifier)
if cluster["status"] == "ACTIVE":
return cluster
time.sleep(1)
raise TimeoutError(f"cluster {identifier} not ACTIVE after {timeout}s")
class TestClusterLifecycle:
def test_create_get_list_update_delete(self, dsql):
resp = dsql.create_cluster()
identifier = resp["identifier"]
assert ID_RE.match(identifier)
assert resp["arn"].endswith(f":cluster/{identifier}")
assert ":dsql:us-east-1:" in resp["arn"]
# CREATING while the backend spins up (Docker), ACTIVE without it.
assert resp["status"] in ("CREATING", "ACTIVE")
assert resp["deletionProtectionEnabled"] is True # AWS default: on
assert resp["creationTime"] is not None
assert resp["endpoint"].startswith("localhost:")
got = _wait_active(dsql, identifier)
assert got["identifier"] == identifier
assert got["tags"] == {}
listed = dsql.list_clusters()
assert any(c["identifier"] == identifier for c in listed["clusters"])
summary = next(c for c in listed["clusters"] if c["identifier"] == identifier)
assert summary["arn"] == resp["arn"]
updated = dsql.update_cluster(identifier=identifier, deletionProtectionEnabled=True)
assert updated["identifier"] == identifier
assert updated["status"] == "ACTIVE"
assert dsql.get_cluster(identifier=identifier)["deletionProtectionEnabled"] is True
dsql.update_cluster(identifier=identifier, deletionProtectionEnabled=False)
deleted = dsql.delete_cluster(identifier=identifier)
assert deleted["identifier"] == identifier
assert deleted["status"] == "DELETING"
with pytest.raises(ClientError) as exc:
dsql.get_cluster(identifier=identifier)
assert exc.value.response["Error"]["Code"] == "ResourceNotFoundException"
def test_create_with_multi_region_properties_echoed(self, dsql):
identifier = _create(
dsql,
multiRegionProperties={"witnessRegion": "us-west-2", "clusters": []},
)
try:
got = dsql.get_cluster(identifier=identifier)
assert got["multiRegionProperties"] == {
"witnessRegion": "us-west-2",
"clusters": [],
}
finally:
_cleanup(dsql, identifier)
def test_encryption_details_default_aws_owned(self, dsql):
resp = dsql.create_cluster()
try:
enc = resp["encryptionDetails"]
assert enc["encryptionType"] == "AWS_OWNED_KMS_KEY"
assert enc["encryptionStatus"] == "ENABLED"
assert ":key/aws/dsql" in enc["kmsKeyArn"]
finally:
_cleanup(dsql, resp["identifier"])
def test_encryption_details_customer_managed(self, dsql, kms_client):
key_arn = kms_client.create_key()["KeyMetadata"]["Arn"]
resp = dsql.create_cluster(kmsEncryptionKey=key_arn)
identifier = resp["identifier"]
try:
enc = resp["encryptionDetails"]
assert enc["encryptionType"] == "CUSTOMER_MANAGED_KMS_KEY"
assert enc["kmsKeyArn"] == key_arn
assert enc["encryptionStatus"] == "ENABLED"
# UpdateCluster with a new key updates encryptionDetails
new_key = kms_client.create_key()["KeyMetadata"]["Arn"]
dsql.update_cluster(identifier=identifier, kmsEncryptionKey=new_key)
got = dsql.get_cluster(identifier=identifier)
assert got["encryptionDetails"]["kmsKeyArn"] == new_key
assert got["encryptionDetails"]["encryptionType"] == "CUSTOMER_MANAGED_KMS_KEY"
finally:
_cleanup(dsql, identifier)
def test_kms_key_validated_against_kms_service(self, dsql, kms_client):
bogus = "arn:aws:kms:us-east-1:000000000000:key/12345678-1234-1234-1234-123456789012"
with pytest.raises(ClientError) as exc:
dsql.create_cluster(kmsEncryptionKey=bogus)
assert exc.value.response["Error"]["Code"] == "ValidationException"
# A real key (by id, ARN, or alias) is accepted.
key = kms_client.create_key()["KeyMetadata"]
kms_client.create_alias(AliasName="alias/dsql-test", TargetKeyId=key["KeyId"])
for ref in (key["KeyId"], key["Arn"], "alias/dsql-test"):
resp = dsql.create_cluster(kmsEncryptionKey=ref)
identifier = resp["identifier"]
try:
enc = resp["encryptionDetails"]
assert enc["encryptionType"] == "CUSTOMER_MANAGED_KMS_KEY"
# bare ids/aliases expand to a full key ARN
assert enc["kmsKeyArn"].startswith("arn:aws:kms:")
finally:
_cleanup(dsql, identifier)
# UpdateCluster validates too.
identifier = _create(dsql)
try:
with pytest.raises(ClientError) as exc:
dsql.update_cluster(identifier=identifier, kmsEncryptionKey=bogus)
assert exc.value.response["Error"]["Code"] == "ValidationException"
finally:
_cleanup(dsql, identifier)
def test_kms_aws_managed_alias_accepted(self, dsql):
# AWS-managed aliases exist by definition — no KMS store entry needed.
resp = dsql.create_cluster(kmsEncryptionKey="alias/aws/dsql")
try:
assert resp["encryptionDetails"]["encryptionType"] == "CUSTOMER_MANAGED_KMS_KEY"
finally:
_cleanup(dsql, resp["identifier"])
def test_endpoints_get_unique_ports(self, dsql):
a = dsql.create_cluster()
b = dsql.create_cluster()
try:
assert a["endpoint"] != b["endpoint"]
assert a["endpoint"].split(":")[1].isdigit()
finally:
_cleanup(dsql, a["identifier"])
_cleanup(dsql, b["identifier"])
def test_deletion_protection_blocks_delete(self, dsql):
identifier = _create(dsql, deletionProtectionEnabled=True)
try:
with pytest.raises(ClientError) as exc:
dsql.delete_cluster(identifier=identifier)
err = exc.value.response["Error"]
assert err["Code"] == "ValidationException"
assert "deletion protection" in err["Message"].lower()
assert exc.value.response["ResponseMetadata"]["HTTPStatusCode"] == 400
# Disable protection, then delete succeeds.
dsql.update_cluster(identifier=identifier, deletionProtectionEnabled=False)
deleted = dsql.delete_cluster(identifier=identifier)
assert deleted["status"] == "DELETING"
finally:
_cleanup(dsql, identifier)
def test_unknown_cluster_id(self, dsql):
unknown = "a" * 26
for call in (
lambda: dsql.get_cluster(identifier=unknown),
lambda: dsql.update_cluster(identifier=unknown, deletionProtectionEnabled=True),
lambda: dsql.delete_cluster(identifier=unknown),
):
with pytest.raises(ClientError) as exc:
call()
assert exc.value.response["Error"]["Code"] == "ResourceNotFoundException"
assert exc.value.response["ResponseMetadata"]["HTTPStatusCode"] == 404
def test_client_token_idempotency(self, dsql):
token = "test-idempotency-token-12345"
first = dsql.create_cluster(clientToken=token)
second = dsql.create_cluster(clientToken=token)
try:
assert second["identifier"] == first["identifier"]
assert second["arn"] == first["arn"]
finally:
_cleanup(dsql, first["identifier"])
def test_list_clusters_pagination(self, dsql):
ids = {_create(dsql), _create(dsql)}
try:
seen = []
kwargs = {"maxResults": 1}
pages = 0
while True:
resp = dsql.list_clusters(**kwargs)
assert len(resp["clusters"]) <= 1
seen.extend(c["identifier"] for c in resp["clusters"])
pages += 1
token = resp.get("nextToken")
if not token:
break
kwargs["nextToken"] = token
assert ids.issubset(set(seen))
assert pages >= 2
finally:
for identifier in ids:
_cleanup(dsql, identifier)
def test_list_clusters_invalid_next_token(self, dsql):
with pytest.raises(ClientError) as exc:
dsql.list_clusters(nextToken="not-a-valid-token")
assert exc.value.response["Error"]["Code"] == "ValidationException"
def test_list_clusters_max_results_out_of_range(self):
# botocore validates maxResults (model min 1 / max 100) client-side,
# so exercise the server-side validation with a raw HTTP call.
import urllib.error
import urllib.request
for bad in (0, 101):
req = urllib.request.Request(
f"{_ENDPOINT}/cluster?max-results={bad}",
headers={"Host": "dsql.us-east-1.localhost"},
)
with pytest.raises(urllib.error.HTTPError) as exc:
urllib.request.urlopen(req, timeout=10)
assert exc.value.code == 400, bad
assert exc.value.headers["x-amzn-errortype"] == "ValidationException"
def test_update_cluster_empty_body_rejected(self, dsql):
identifier = _create(dsql)
try:
with pytest.raises(ClientError) as exc:
dsql.update_cluster(identifier=identifier)
err = exc.value.response["Error"]
assert err["Code"] == "ValidationException"
assert exc.value.response["ResponseMetadata"]["HTTPStatusCode"] == 400
finally:
_cleanup(dsql, identifier)
def test_delete_cluster_client_token_idempotency(self, dsql):
identifier = _create(dsql)
try:
dsql.update_cluster(identifier=identifier, deletionProtectionEnabled=False)
token = "delete-idempotency-token-abc"
first = dsql.delete_cluster(identifier=identifier, clientToken=token)
assert first["identifier"] == identifier
assert first["status"] == "DELETING"
# Second delete with same token returns idempotent success
second = dsql.delete_cluster(identifier=identifier, clientToken=token)
assert second["identifier"] == identifier
assert second["status"] == "DELETING"
assert second["arn"] == first["arn"]
assert second["creationTime"] == first["creationTime"]
finally:
_cleanup(dsql, identifier)
class TestTags:
def test_tags_via_create_and_tag_ops(self, dsql):
resp = dsql.create_cluster(tags={"env": "test", "team": "data"})
identifier = resp["identifier"]
arn = resp["arn"]
try:
got = dsql.get_cluster(identifier=identifier)
assert got["tags"] == {"env": "test", "team": "data"}
listed = dsql.list_tags_for_resource(resourceArn=arn)
assert listed["tags"] == {"env": "test", "team": "data"}
dsql.tag_resource(resourceArn=arn, tags={"owner": "alice", "env": "prod"})
listed = dsql.list_tags_for_resource(resourceArn=arn)
assert listed["tags"] == {"env": "prod", "team": "data", "owner": "alice"}
dsql.untag_resource(resourceArn=arn, tagKeys=["team", "owner"])
listed = dsql.list_tags_for_resource(resourceArn=arn)
assert listed["tags"] == {"env": "prod"}
finally:
_cleanup(dsql, identifier)
def test_tag_ops_unknown_arn(self, dsql):
unknown_arn = "arn:aws:dsql:us-east-1:000000000000:cluster/" + "z" * 26
with pytest.raises(ClientError) as exc:
dsql.list_tags_for_resource(resourceArn=unknown_arn)
assert exc.value.response["Error"]["Code"] == "ResourceNotFoundException"
with pytest.raises(ClientError) as exc:
dsql.tag_resource(resourceArn=unknown_arn, tags={"k": "v"})
assert exc.value.response["Error"]["Code"] == "ResourceNotFoundException"
with pytest.raises(ClientError) as exc:
dsql.untag_resource(resourceArn=unknown_arn, tagKeys=["k"])
assert exc.value.response["Error"]["Code"] == "ResourceNotFoundException"
class TestClusterPolicy:
POLICY = '{"Version":"2012-10-17","Statement":[]}'
def test_policy_put_get_delete(self, dsql):
identifier = _create(dsql)
try:
with pytest.raises(ClientError) as exc:
dsql.get_cluster_policy(identifier=identifier)
assert exc.value.response["Error"]["Code"] == "ResourceNotFoundException"
put = dsql.put_cluster_policy(identifier=identifier, policy=self.POLICY)
assert put["policyVersion"] == "1"
got = dsql.get_cluster_policy(identifier=identifier)
assert got["policy"] == self.POLICY
assert got["policyVersion"] == "1"
put2 = dsql.put_cluster_policy(
identifier=identifier, policy=self.POLICY, expectedPolicyVersion="1"
)
assert put2["policyVersion"] == "2"
deleted = dsql.delete_cluster_policy(identifier=identifier)
assert deleted["policyVersion"] == "2"
with pytest.raises(ClientError) as exc:
dsql.get_cluster_policy(identifier=identifier)
assert exc.value.response["Error"]["Code"] == "ResourceNotFoundException"
finally:
_cleanup(dsql, identifier)
def test_policy_version_conflict(self, dsql):
identifier = _create(dsql)
try:
dsql.put_cluster_policy(identifier=identifier, policy=self.POLICY)
with pytest.raises(ClientError) as exc:
dsql.put_cluster_policy(
identifier=identifier, policy=self.POLICY, expectedPolicyVersion="99"
)
assert exc.value.response["Error"]["Code"] == "ConflictException"
assert exc.value.response["ResponseMetadata"]["HTTPStatusCode"] == 409
finally:
_cleanup(dsql, identifier)
def test_policy_on_unknown_cluster(self, dsql):
unknown = "z" * 26
with pytest.raises(ClientError) as exc:
dsql.put_cluster_policy(identifier=unknown, policy=self.POLICY)
assert exc.value.response["Error"]["Code"] == "ResourceNotFoundException"
def test_create_cluster_with_policy(self, dsql):
resp = dsql.create_cluster(policy=self.POLICY)
identifier = resp["identifier"]
try:
got = dsql.get_cluster_policy(identifier=identifier)
assert got["policy"] == self.POLICY
assert got["policyVersion"] == "1"
finally:
_cleanup(dsql, identifier)
def test_delete_policy_with_expected_version_success(self, dsql):
identifier = _create(dsql)
try:
dsql.put_cluster_policy(identifier=identifier, policy=self.POLICY)
deleted = dsql.delete_cluster_policy(
identifier=identifier, expectedPolicyVersion="1"
)
assert deleted["policyVersion"] == "1"
finally:
_cleanup(dsql, identifier)
def test_delete_policy_expected_version_conflict(self, dsql):
identifier = _create(dsql)
try:
dsql.put_cluster_policy(identifier=identifier, policy=self.POLICY)
with pytest.raises(ClientError) as exc:
dsql.delete_cluster_policy(
identifier=identifier, expectedPolicyVersion="99"
)
assert exc.value.response["Error"]["Code"] == "ConflictException"
assert exc.value.response["ResponseMetadata"]["HTTPStatusCode"] == 409
finally:
_cleanup(dsql, identifier)
class TestStreams:
"""CDC streams (GA 2026-07-08): metadata only — nothing is delivered."""
ROLE_ARN = "arn:aws:iam::000000000000:role/dsql-cdc"
def _target(self, kin, name):
try:
kin.create_stream(StreamName=name, ShardCount=1)
except ClientError as e:
if e.response["Error"]["Code"] != "ResourceInUseException":
raise
arn = kin.describe_stream(StreamName=name)["StreamDescription"]["StreamARN"]
return {"kinesis": {"streamArn": arn, "roleArn": self.ROLE_ARN}}
def test_stream_lifecycle(self, dsql, kin):
identifier = _create(dsql)
try:
target = self._target(kin, "dsql-cdc-life")
created = dsql.create_stream(
clusterIdentifier=identifier,
targetDefinition=target,
ordering="UNORDERED",
format="JSON",
tags={"env": "test"},
)
stream_id = created["streamIdentifier"]
assert ID_RE.match(stream_id)
assert created["status"] == "ACTIVE"
assert created["arn"] == (
f"arn:aws:dsql:us-east-1:000000000000:cluster/{identifier}"
f"/stream/{stream_id}"
)
got = dsql.get_stream(
clusterIdentifier=identifier, streamIdentifier=stream_id
)
assert got["targetDefinition"] == target
assert got["ordering"] == "UNORDERED"
assert got["format"] == "JSON"
assert got["tags"] == {"env": "test"}
assert "statusReason" not in got
listed = dsql.list_streams(clusterIdentifier=identifier)["streams"]
assert [st["streamIdentifier"] for st in listed] == [stream_id]
deleted = dsql.delete_stream(
clusterIdentifier=identifier, streamIdentifier=stream_id
)
assert deleted["status"] == "DELETING"
with pytest.raises(ClientError) as exc:
dsql.get_stream(
clusterIdentifier=identifier, streamIdentifier=stream_id
)
assert exc.value.response["Error"]["Code"] == "ResourceNotFoundException"
finally:
_cleanup(dsql, identifier)
def test_stream_tags_and_client_token(self, dsql, kin):
identifier = _create(dsql)
try:
target = self._target(kin, "dsql-cdc-tags")
created = dsql.create_stream(
clusterIdentifier=identifier,
targetDefinition=target,
ordering="UNORDERED",
format="JSON",
clientToken="tok-cdc",
)
repeat = dsql.create_stream(
clusterIdentifier=identifier,
targetDefinition=target,
ordering="UNORDERED",
format="JSON",
clientToken="tok-cdc",
)
assert repeat["streamIdentifier"] == created["streamIdentifier"]
dsql.tag_resource(resourceArn=created["arn"], tags={"team": "data"})
assert dsql.list_tags_for_resource(resourceArn=created["arn"])["tags"] == {
"team": "data"
}
dsql.untag_resource(resourceArn=created["arn"], tagKeys=["team"])
assert dsql.list_tags_for_resource(resourceArn=created["arn"])["tags"] == {}
finally:
_cleanup(dsql, identifier)
def test_missing_kinesis_target_lands_failed(self, dsql):
identifier = _create(dsql)
try:
created = dsql.create_stream(
clusterIdentifier=identifier,
targetDefinition={"kinesis": {
"streamArn": "arn:aws:kinesis:us-east-1:000000000000:stream/absent",
"roleArn": self.ROLE_ARN,
}},
ordering="UNORDERED",
format="JSON",
)
assert created["status"] == "FAILED"
got = dsql.get_stream(
clusterIdentifier=identifier,
streamIdentifier=created["streamIdentifier"],
)
assert got["statusReason"]["error"] == "KINESIS_STREAM_NOT_FOUND"
finally:
_cleanup(dsql, identifier)
def test_stream_limit_per_cluster(self, dsql, kin):
identifier = _create(dsql)
try:
target = self._target(kin, "dsql-cdc-limit")
for _ in range(5):
dsql.create_stream(
clusterIdentifier=identifier,
targetDefinition=target,
ordering="UNORDERED",
format="JSON",
)
with pytest.raises(ClientError) as exc:
dsql.create_stream(
clusterIdentifier=identifier,
targetDefinition=target,
ordering="UNORDERED",
format="JSON",
)
assert exc.value.response["Error"]["Code"] == "ServiceQuotaExceededException"
assert "stream limit" in exc.value.response["Error"]["Message"]
finally:
_cleanup(dsql, identifier)
def test_stream_on_unknown_cluster(self, dsql):
unknown = "z" * 26
with pytest.raises(ClientError) as exc:
dsql.list_streams(clusterIdentifier=unknown)
assert exc.value.response["Error"]["Code"] == "ResourceNotFoundException"
def test_deleting_a_cluster_takes_its_streams(self, dsql, kin):
identifier = _create(dsql)
target = self._target(kin, "dsql-cdc-gone")
created = dsql.create_stream(
clusterIdentifier=identifier,
targetDefinition=target,
ordering="UNORDERED",
format="JSON",
)
_cleanup(dsql, identifier)
with pytest.raises(ClientError) as exc:
dsql.get_stream(
clusterIdentifier=identifier,
streamIdentifier=created["streamIdentifier"],
)
assert exc.value.response["Error"]["Code"] == "ResourceNotFoundException"
def test_stream_active_waiter(self, dsql, kin):
identifier = _create(dsql)
try:
target = self._target(kin, "dsql-cdc-waiter")
created = dsql.create_stream(
clusterIdentifier=identifier,
targetDefinition=target,
ordering="UNORDERED",
format="JSON",
)
dsql.get_waiter("stream_active").wait(
clusterIdentifier=identifier,
streamIdentifier=created["streamIdentifier"],
WaiterConfig={"Delay": 1, "MaxAttempts": 3},
)
finally:
_cleanup(dsql, identifier)
class TestVpcEndpointServiceName:
def test_service_name_is_stable_per_cluster(self, dsql):
identifier = _create(dsql)
try:
first = dsql.get_vpc_endpoint_service_name(identifier=identifier)
assert re.match(
r"^com\.amazonaws\.us-east-1\.dsql-[a-f0-9]{6}$", first["serviceName"]
)
second = dsql.get_vpc_endpoint_service_name(identifier=identifier)
assert second["serviceName"] == first["serviceName"]
finally:
_cleanup(dsql, identifier)
def test_unknown_cluster(self, dsql):
with pytest.raises(ClientError) as exc:
dsql.get_vpc_endpoint_service_name(identifier="z" * 26)
assert exc.value.response["Error"]["Code"] == "ResourceNotFoundException"
class TestBotoParity:
"""Waiters, paginators, and structured error members (botocore model)."""
def test_cluster_active_and_not_exists_waiters(self, dsql):
identifier = _create(dsql)
dsql.get_waiter("cluster_active").wait(
identifier=identifier, WaiterConfig={"Delay": 1, "MaxAttempts": 60}
)
assert dsql.get_cluster(identifier=identifier)["status"] == "ACTIVE"
dsql.update_cluster(identifier=identifier, deletionProtectionEnabled=False)
dsql.delete_cluster(identifier=identifier)
dsql.get_waiter("cluster_not_exists").wait(
identifier=identifier, WaiterConfig={"Delay": 1, "MaxAttempts": 10}
)
def test_list_clusters_paginator(self, dsql):
ids = {_create(dsql), _create(dsql)}
try:
paginator = dsql.get_paginator("list_clusters")
seen = []
for page in paginator.paginate(PaginationConfig={"PageSize": 1}):
seen.extend(c["identifier"] for c in page["clusters"])
assert ids.issubset(set(seen))
assert len(seen) >= 2
finally:
for identifier in ids:
_cleanup(dsql, identifier)
def test_error_members_not_found(self, dsql):
unknown = "z" * 26
with pytest.raises(ClientError) as exc:
dsql.get_cluster(identifier=unknown)
resp = exc.value.response
assert resp["Error"]["Code"] == "ResourceNotFoundException"
# botocore parses modeled error members at the response top level
assert resp["resourceId"] == unknown
assert resp["resourceType"] == "cluster"
def test_error_members_deletion_protection(self, dsql):
identifier = _create(dsql, deletionProtectionEnabled=True)
try:
with pytest.raises(ClientError) as exc:
dsql.delete_cluster(identifier=identifier)
resp = exc.value.response
assert resp["Error"]["Code"] == "ValidationException"
assert resp["reason"] == "deletionProtectionEnabled"
finally:
_cleanup(dsql, identifier)
def test_update_delete_response_shape(self, dsql):
identifier = _create(dsql)
try:
updated = dsql.update_cluster(
identifier=identifier, deletionProtectionEnabled=False
)
assert set(updated) == {
"identifier", "arn", "status", "creationTime", "ResponseMetadata",
}
finally:
deleted = dsql.delete_cluster(identifier=identifier)
assert set(deleted) == {
"identifier", "arn", "status", "creationTime", "ResponseMetadata",
}
class TestAuthTokens:
def test_generate_db_connect_auth_token(self, dsql):
token = dsql.generate_db_connect_auth_token("localhost", "us-east-1")
assert isinstance(token, str)
assert "Action=DbConnect" in token
def test_generate_db_connect_admin_auth_token(self, dsql):
token = dsql.generate_db_connect_admin_auth_token("localhost", "us-east-1")
assert isinstance(token, str)
assert "Action=DbConnectAdmin" in token
# ---------------------------------------------------------------------------
# Validator unit tests (no server, no Postgres needed)
# ---------------------------------------------------------------------------
class TestClassifyStatement:
@pytest.mark.parametrize(
"sql,expected",
[
("CREATE TABLE t (a int)", "ddl"),
("alter table t add column b int", "ddl"),
("DROP INDEX idx", "ddl"),
("INSERT INTO t VALUES (1)", "dml"),
("update t set a = 1", "dml"),
("DELETE FROM t", "dml"),
("BEGIN", "tcl_begin"),
("START TRANSACTION", "tcl_begin"),
("COMMIT", "tcl_end"),
("END", "tcl_end"),
("ROLLBACK", "tcl_end"),
("ABORT", "tcl_end"),
("SELECT 1", "other"),
("SET search_path TO public", "other"),
("SHOW server_version", "other"),
("GRANT SELECT ON t TO r", "other"),
("REVOKE SELECT ON t FROM r", "other"),
("AWS IAM GRANT r TO 'arn:aws:iam::123456789012:role/x'", "ddl"),
("aws iam revoke r FROM 'arn:aws:iam::123456789012:role/x'", "ddl"),
# Leading comments must not hide the statement keyword.
("-- migration 004\nCREATE TABLE t (a int)", "ddl"),
("/* flyway */ INSERT INTO t VALUES (1)", "dml"),
("/* a /* nested */ b */ COMMIT", "tcl_end"),
("\n\n-- one\n-- two\nBEGIN", "tcl_begin"),
# CTE-leading writes are still writes.
("WITH x AS (SELECT 1) INSERT INTO t SELECT 1", "dml"),
("WITH x AS (SELECT 1) UPDATE t SET a = 1", "dml"),
("WITH x AS (SELECT 1) DELETE FROM t", "dml"),
("WITH RECURSIVE x AS (SELECT 1) INSERT INTO t SELECT 1", "dml"),
("WITH x (a, b) AS (SELECT 1, 2) INSERT INTO t SELECT 1", "dml"),
("WITH x AS MATERIALIZED (SELECT 1) INSERT INTO t SELECT 1", "dml"),
("WITH a AS (SELECT 1), b AS (SELECT 2) INSERT INTO t SELECT 1", "dml"),
("WITH x AS (SELECT 1) SELECT * FROM x", "other"),
# EXPLAIN ANALYZE executes; plain EXPLAIN only plans.
("EXPLAIN ANALYZE INSERT INTO t VALUES (1)", "dml"),
("EXPLAIN (ANALYZE, BUFFERS) DELETE FROM t", "dml"),
("EXPLAIN INSERT INTO t VALUES (1)", "other"),
("EXPLAIN SELECT 1", "other"),
],
)
def test_classify(self, sql, expected):
assert pgproxy.classify_statement(sql) == expected
class TestAwsIamParsing:
"""``AWS IAM GRANT`` / ``REVOKE`` grammar and the errors DSQL raises for it
before touching a role (measured on Aurora DSQL, eu-west-1 2026-10-26)."""
def test_grant_with_several_arns(self):
trust = pgproxy.match_aws_iam(
"AWS IAM GRANT Probe_Role TO 'arn:aws:iam::123456789012:role/a', "
"'arn:aws:iam::123456789012:user/b'"
)
assert (trust.action, trust.role, trust.arns) == (
"GRANT", "probe_role",
["arn:aws:iam::123456789012:role/a", "arn:aws:iam::123456789012:user/b"],
)
assert trust.tag == "AWS IAM GRANT"
def test_revoke_keeps_a_quoted_role_verbatim(self):
trust = pgproxy.match_aws_iam(
"aws iam revoke \"Mixed\" from 'arn:aws:iam::123456789012:role/a';"
)
assert (trust.action, trust.role, trust.tag) == ("REVOKE", "Mixed", "AWS IAM REVOKE")
def test_other_statements_are_not_matched(self):
assert pgproxy.match_aws_iam("GRANT r TO s") is None
assert pgproxy.match_aws_iam("SELECT 'AWS IAM GRANT'") is None
def test_an_arn_must_be_a_string_literal(self):
err = pgproxy.match_aws_iam(
'AWS IAM GRANT probe_role TO "arn:aws:iam::123456789012:role/Quoted"'
)
assert err.sqlstate == "42601"
assert err.message == (
'syntax error at or near ""arn:aws:iam::123456789012:role/Quoted""'
)
def test_a_value_that_is_not_an_arn_is_refused(self):
err = pgproxy.match_aws_iam("AWS IAM GRANT probe_role TO 'not-an-arn'")
assert (err.sqlstate, err.message) == ("0P000", "IAM role must look like an IAM ARN")
def test_comments_inside_the_statement_are_ignored(self):
trust = pgproxy.match_aws_iam(
"AWS IAM GRANT r /* trust */ TO -- the API\n"
"'arn:aws:iam::123456789012:role/a' -- done"
)
assert trust.arns == ["arn:aws:iam::123456789012:role/a"]
def test_grant_needs_to_and_revoke_needs_from(self):
assert pgproxy.match_aws_iam(
"AWS IAM GRANT r FROM 'arn:aws:iam::123456789012:role/a'"
).message == 'syntax error at or near "FROM"'
assert pgproxy.match_aws_iam(
"AWS IAM REVOKE r TO 'arn:aws:iam::123456789012:role/a'"
).message == 'syntax error at or near "TO"'
assert pgproxy.match_aws_iam("AWS IAM GRANT r TO").message == (
"syntax error at end of input"
)
class TestSqlLexing:
"""Statement splitting and comment stripping must not be fooled by
literals — a ';' or ')' inside a string used to truncate the text a
validation rule saw, so the rule passed on the fragment."""
@pytest.mark.parametrize(
"sql,expected",
[
("SELECT 1; SELECT 2", ["SELECT 1", "SELECT 2"]),
("SELECT 'a;b'", ["SELECT 'a;b'"]),
("SELECT 'it''s; fine'", ["SELECT 'it''s; fine'"]),
("SELECT $$a;b$$", ["SELECT $$a;b$$"]),
("SELECT $tag$a;b$tag$", ["SELECT $tag$a;b$tag$"]),
('SELECT "we;ird"', ['SELECT "we;ird"']),
("SELECT 1 -- c;omment\n; SELECT 2", ["SELECT 1 -- c;omment", "SELECT 2"]),
("SELECT 1 /* c;omment */; SELECT 2", ["SELECT 1 /* c;omment */", "SELECT 2"]),
("SELECT E'a\\';b'", ["SELECT E'a\\';b'"]),
("SELECT 1;", ["SELECT 1"]),
(" ; ", []),
],
)
def test_split_statements(self, sql, expected):
assert pgproxy.split_statements(sql) == expected
@pytest.mark.parametrize(
"sql,expected",
[
("-- x\nSELECT 1", "SELECT 1"),
("/* x */SELECT 1", "SELECT 1"),
("/* a /* b */ c */ SELECT 1", "SELECT 1"),
(" \n\t-- a\n /* b */\nSELECT 1", "SELECT 1"),
("SELECT 1 -- trailing", "SELECT 1 -- trailing"),
("'-- not a comment'", "'-- not a comment'"),
],
)
def test_strip_leading_comments(self, sql, expected):
assert pgproxy.strip_leading_comments(sql) == expected
def test_semicolon_in_literal_does_not_hide_a_bad_column(self):
# The fragment left by a naive ';' split had unbalanced parens, so the
# column-type rule silently passed and `serial` got through.
err = pgproxy.validate(
"CREATE TABLE t (a text DEFAULT 'x;y', b serial)"
)
assert isinstance(err, pgproxy.DsqlError)
assert "serial" in err.message
def test_paren_in_literal_does_not_hide_a_bad_column(self):
err = pgproxy.validate("CREATE TABLE t (a text DEFAULT ')(', b serial)")
assert isinstance(err, pgproxy.DsqlError)
assert "serial" in err.message
@pytest.mark.parametrize(
"sql",
[
"-- migration\nCREATE EXTENSION pgcrypto",
"/* tooling */ TRUNCATE t",
"\n-- a\n/* b */ CREATE TEMP TABLE t (a int)",
],
)
def test_leading_comment_does_not_bypass_the_denylist(self, sql):
assert isinstance(pgproxy.validate(sql), pgproxy.DsqlError)
class TestDenylist:
@pytest.mark.parametrize(
"sql",
[
"CREATE DATABASE foo",
"CREATE TYPE mood AS ENUM ('sad')",
"CREATE EXTENSION pgcrypto",
"CREATE TRIGGER trg AFTER INSERT ON t EXECUTE FUNCTION f()",
"CREATE PROCEDURE p() LANGUAGE sql AS $$ SELECT 1 $$",
"CREATE TABLESPACE ts LOCATION '/tmp/x'",
"CREATE TEMP TABLE t (a int)",
"CREATE TEMPORARY TABLE t (a int)",
"CREATE UNLOGGED TABLE t (a int)",
"COPY t FROM STDIN",
"LISTEN chan",
"NOTIFY chan",
"UNLISTEN chan",
"TRUNCATE t",
"DO $$ BEGIN NULL; END $$",
"SAVEPOINT sp",
"LOCK TABLE t IN ACCESS EXCLUSIVE MODE",
"CREATE FUNCTION f() RETURNS int LANGUAGE plpgsql AS $$ BEGIN RETURN 1; END $$",
"CREATE FUNCTION f() RETURNS int LANGUAGE C AS 'x'",
"CREATE MATERIALIZED VIEW mv AS SELECT 1",
"CREATE TABLE t (a int) PARTITION BY RANGE (a)",
"CREATE TABLE p (a int, EXCLUDE (a WITH =))",
"CREATE RULE r AS ON DELETE TO t DO INSTEAD NOTHING",
"CREATE AGGREGATE a (int) (sfunc = f, stype = int)",
"CREATE DOMAIN d AS int",
"CREATE CAST (int AS text) WITH FUNCTION f(int)",
"CREATE COLLATION c (locale = 'en_US')",
"CREATE OPERATOR + (leftarg = int, rightarg = int, function = f)",
"CREATE PUBLICATION p FOR ALL TABLES",
"CREATE SUBSCRIPTION s CONNECTION 'x' PUBLICATION p",
"CREATE FOREIGN TABLE ft (a int) SERVER s",
"CREATE SERVER s FOREIGN DATA WRAPPER w",
"VACUUM t",
"VACUUM FULL ANALYZE t",
"CLUSTER t USING idx",
"REINDEX TABLE t",
"ALTER SYSTEM SET work_mem = '4MB'",
"PREPARE TRANSACTION 'gid'",
"COMMIT PREPARED 'gid'",
"ROLLBACK PREPARED 'gid'",
],
)
def test_denied(self, sql):
err = pgproxy.validate(sql, pgproxy.TxnState())
assert isinstance(err, pgproxy.DsqlError), sql
assert err.sqlstate == "0A000"
assert "is not supported" in err.message
@pytest.mark.parametrize(
"sql,message",
[
("ALTER TABLE t ADD COLUMN c int DEFAULT 0",
"ALTER TABLE ADD COLUMN with constraint not supported"),
("ALTER TABLE t ADD COLUMN c text NOT NULL",
"ALTER TABLE ADD COLUMN with constraint not supported"),
("ALTER TABLE t ADD COLUMN c int UNIQUE",
"ALTER TABLE ADD COLUMN with constraint not supported"),
("ALTER TABLE t ADD COLUMN c int CHECK (c > 0)",
"ALTER TABLE ADD COLUMN with constraint not supported"),
("ALTER TABLE t ADD COLUMN c int REFERENCES o",
"ALTER TABLE ADD COLUMN with constraint not supported"),
("ALTER TABLE t ADD COLUMN c text GENERATED ALWAYS AS (lower(e)) STORED",
"ALTER TABLE ADD COLUMN with constraint not supported"),
("ALTER TABLE t ADD COLUMN c bigint GENERATED BY DEFAULT AS IDENTITY "
"(CACHE 1)",
"ALTER TABLE ADD COLUMN with constraint not supported"),
('ALTER TABLE t ADD COLUMN c text COLLATE "C"',
"COLLATE clause not supported"),
],
)
def test_add_column_takes_no_constraint(self, sql, message):
err = pgproxy.validate(sql, pgproxy.TxnState())
assert isinstance(err, pgproxy.DsqlError), sql
assert err.sqlstate == "0A000"
assert err.message == message, sql
def test_create_function_language_sql_allowed(self):
sql = "CREATE FUNCTION f() RETURNS int LANGUAGE sql AS $$ SELECT 1 $$"
assert pgproxy.validate(sql, pgproxy.TxnState()) is None
class TestColumnTypes:
@pytest.mark.parametrize(
"typ",
[
"serial", "bigserial", "smallserial",
"money", "xml", "inet", "cidr", "macaddr", "macaddr8",
"bit", "bit varying", "varbit",
"point", "line", "lseg", "box", "path", "polygon", "circle",
"hstore",
"int[]", "text[]",
],
)
def test_unsupported_types_denied(self, typ):
err = pgproxy.validate(
f"CREATE TABLE t (id int, c {typ})", pgproxy.TxnState()
)
assert isinstance(err, pgproxy.DsqlError), typ
assert err.sqlstate == "0A000"
assert 'type "' in err.message and "is not supported" in err.message
def test_unsupported_type_in_alter_add_column(self):
err = pgproxy.validate(
"ALTER TABLE t ADD COLUMN c serial", pgproxy.TxnState()
)
assert isinstance(err, pgproxy.DsqlError)
assert err.sqlstate == "0A000"
assert '"serial"' in err.message
@pytest.mark.parametrize(
"typ",
[
"smallint", "int2", "integer", "int", "int4", "bigint", "int8",
"real", "float4", "double precision", "float8",
"numeric", "decimal", "dec", "numeric(18,6)", "dec(10,2)",
"char(1)", "character(1)", "bpchar", "varchar(255)",
"character varying(10)", "text",
"date", "time", "time with time zone", "timetz",
"timestamp", "timestamp with time zone", "timestamptz",
"interval", "interval year", "interval day to second",
"interval year to month", "interval second", "boolean", "bool", "bytea", "uuid", "json", "jsonb",
],
)
def test_supported_types_allowed(self, typ):
assert pgproxy.validate(
f"CREATE TABLE t (id int, c {typ})", pgproxy.TxnState()
) is None
def test_table_constraints_skipped(self):
sql = "CREATE TABLE t (id int PRIMARY KEY, c text UNIQUE, CHECK (id > 0))"
assert pgproxy.validate(sql, pgproxy.TxnState()) is None
def test_storage_modifier_allowed(self):
assert pgproxy.validate(
"CREATE TABLE t (id int, j jsonb STORAGE PLAIN)", pgproxy.TxnState()
) is None
assert pgproxy.validate(
"ALTER TABLE t ADD COLUMN j jsonb STORAGE EXTERNAL", pgproxy.TxnState()
) is None
assert pgproxy.validate(
"ALTER TABLE t ADD COLUMN j jsonb STORAGE DEFAULT", pgproxy.TxnState()
) is None
def test_bogus_interval_fields_rejected(self):
err = pgproxy.validate(
"CREATE TABLE t (id int, c interval foo)", pgproxy.TxnState()
)
assert isinstance(err, pgproxy.DsqlError)
assert err.sqlstate == "0A000"
class TestAlterTableSubset:
"""ALTER TABLE action subset per the AWS alter-table-syntax-support doc."""
@pytest.mark.parametrize(
"sql",
[
"ALTER TABLE t ALTER COLUMN c TYPE text",
"ALTER TABLE t ALTER COLUMN c SET DATA TYPE text",
"ALTER TABLE t ALTER COLUMN c SET NOT NULL",
"ALTER TABLE t ADD CONSTRAINT pk PRIMARY KEY (id)",
"ALTER TABLE t ADD PRIMARY KEY (id)",
"ALTER TABLE t ADD CONSTRAINT u UNIQUE (a)",
"ALTER TABLE t ADD CONSTRAINT c CHECK (a > 0)", # missing NOT VALID
"ALTER TABLE t ALTER COLUMN c ADD GENERATED BY DEFAULT AS IDENTITY", # no CACHE
],
)
def test_unsupported_actions_denied(self, sql):
err = pgproxy.validate(sql, pgproxy.TxnState())
assert isinstance(err, pgproxy.DsqlError), sql
assert err.sqlstate == "0A000"
@pytest.mark.parametrize(
"sql",
[
"ALTER TABLE t DROP COLUMN c",
"ALTER TABLE t DROP COLUMN IF EXISTS c CASCADE",
"ALTER TABLE t ALTER COLUMN c SET DEFAULT 0",
"ALTER TABLE t ALTER COLUMN c DROP DEFAULT",
"ALTER TABLE t ALTER COLUMN c DROP NOT NULL",
"ALTER TABLE t ALTER COLUMN c SET STORAGE PLAIN",
"ALTER TABLE t RENAME COLUMN a TO b",
"ALTER TABLE t RENAME TO t2",
"ALTER TABLE t SET SCHEMA app",
"ALTER TABLE t ADD CONSTRAINT c CHECK (a > 0) NOT VALID",
"ALTER TABLE t ADD CONSTRAINT u UNIQUE USING INDEX idx",
"ALTER TABLE t DROP CONSTRAINT c",
"ALTER TABLE t ALTER COLUMN c ADD GENERATED BY DEFAULT AS IDENTITY (CACHE 1)",
"ALTER TABLE t ADD COLUMN c integer",
"ALTER TABLE t ADD COLUMN j jsonb STORAGE PLAIN",
"ALTER TABLE t ADD COLUMN c int NULL",
"ALTER TABLE t ADD COLUMN IF NOT EXISTS c numeric(18,6)",
"ALTER TABLE t ADD COLUMN a int, ADD COLUMN b text",
],
)
def test_supported_actions_allowed(self, sql):
assert pgproxy.validate(sql, pgproxy.TxnState()) is None, sql
@pytest.mark.parametrize(
"sql,expected",
[
("ALTER TABLE t DROP COLUMN c", ["c"]),
("ALTER TABLE t DROP c", ["c"]),
("ALTER TABLE t DROP COLUMN IF EXISTS c CASCADE", ["c"]),
("ALTER TABLE t DROP COLUMN a, DROP COLUMN b", ["a", "b"]),
("ALTER TABLE t DROP a, DROP COLUMN b, DROP IF EXISTS c", ["a", "b", "c"]),
('ALTER TABLE t DROP COLUMN "MixedCase"', ["MixedCase"]),
# Actions that merely start with DROP are not column drops.
("ALTER TABLE t DROP CONSTRAINT c", []),
("ALTER TABLE t ALTER COLUMN c DROP DEFAULT", []),
("ALTER TABLE t ALTER COLUMN c DROP NOT NULL", []),
("ALTER TABLE t ALTER COLUMN c DROP EXPRESSION", []),
("ALTER TABLE t ALTER COLUMN c DROP IDENTITY", []),
("ALTER TABLE t ADD COLUMN c int", []),
("DROP TABLE t", []),
],
)
def test_dropped_columns(self, sql, expected):
assert pgproxy.dropped_columns(sql) == expected
def test_drop_column_still_passes_text_only_validation(self):
# The primary-key restriction needs a catalog probe, so validate()
# stays clean and the rule lives in the proxy's statement planner.
assert pgproxy.validate("ALTER TABLE t DROP COLUMN c") is None
def test_validate_constraint_async_rewrite(self):
result = pgproxy.validate(
"ALTER TABLE ASYNC t VALIDATE CONSTRAINT c", pgproxy.TxnState()
)
assert isinstance(result, pgproxy.Rewrite)
assert result.sql == "ALTER TABLE t VALIDATE CONSTRAINT c"
assert result.job_type == "VALIDATE_CONSTRAINT"
assert result.object_name == "public.t"
class TestForeignKeys:
"""Foreign keys, supported since 2026-08-26 (AWS release notes)."""
@pytest.mark.parametrize(
"sql",
[
"CREATE TABLE orders (id int PRIMARY KEY, p int REFERENCES products)",
"CREATE TABLE orders (id int PRIMARY KEY, "
"p int REFERENCES products (product_no))",
"CREATE TABLE orders (id int PRIMARY KEY, "
"p int CONSTRAINT fk_product REFERENCES products)",
"CREATE TABLE shipments (id int PRIMARY KEY, w int, p int, "
"FOREIGN KEY (w, p) REFERENCES inventory (warehouse_id, product_no))",
# Self-referencing.
"CREATE TABLE tree (id int PRIMARY KEY, parent int REFERENCES tree)",
# Every referential action DSQL supports.
"CREATE TABLE o (p int REFERENCES products ON DELETE NO ACTION)",
"CREATE TABLE o (p int REFERENCES products ON DELETE RESTRICT)",
"CREATE TABLE o (p int REFERENCES products ON DELETE CASCADE)",
"CREATE TABLE o (p int REFERENCES products ON DELETE SET NULL)",
"CREATE TABLE o (p int REFERENCES products ON DELETE SET DEFAULT)",
"CREATE TABLE o (p int REFERENCES products "
"ON DELETE CASCADE ON UPDATE CASCADE)",
# Match types and deferrability.
"CREATE TABLE s (w int, p int, FOREIGN KEY (w, p) "
"REFERENCES inventory (w, p) MATCH FULL)",
"CREATE TABLE s (w int, p int, FOREIGN KEY (w, p) "
"REFERENCES inventory (w, p) MATCH SIMPLE)",
"CREATE TABLE o (p int REFERENCES products DEFERRABLE)",
"CREATE TABLE o (p int REFERENCES products "
"DEFERRABLE INITIALLY DEFERRED)",
"CREATE TABLE o (p int REFERENCES products NOT DEFERRABLE)",
"ALTER TABLE o ALTER CONSTRAINT fk DEFERRABLE INITIALLY DEFERRED",
"ALTER TABLE o ADD CONSTRAINT fk FOREIGN KEY (p) "
"REFERENCES products NOT VALID",
"ALTER TABLE o ADD FOREIGN KEY (p) REFERENCES products "
"ON DELETE CASCADE NOT VALID",
"ALTER TABLE o ADD CONSTRAINT fk FOREIGN KEY (p) "
"REFERENCES products DEFERRABLE INITIALLY DEFERRED NOT VALID",
"ALTER TABLE o DROP CONSTRAINT fk",
"SET CONSTRAINTS ALL DEFERRED",
],
)
def test_allowed(self, sql):
assert pgproxy.validate(sql, pgproxy.TxnState()) is None, sql
@pytest.mark.parametrize(
"sql",
[
"ALTER TABLE o ADD CONSTRAINT fk FOREIGN KEY (p) REFERENCES products",
"ALTER TABLE o ADD FOREIGN KEY (p) REFERENCES products ON DELETE CASCADE",
],
)
def test_alter_table_requires_not_valid(self, sql):
err = pgproxy.validate(sql, pgproxy.TxnState())
assert isinstance(err, pgproxy.DsqlError), sql
assert err.sqlstate == "0A000"
assert err.message == "unsupported ALTER TABLE ADD CONSTRAINT statement"
@pytest.mark.parametrize(
"sql,message",
[
("CREATE TABLE t (a int, UNIQUE (a) DEFERRABLE)",
"DEFERRABLE constraint not supported"),
("CREATE TABLE t (a int PRIMARY KEY DEFERRABLE INITIALLY DEFERRED)",
"DEFERRABLE constraint not supported"),
# INITIALLY DEFERRED without DEFERRABLE is refused the same way.
("CREATE TABLE t (a int, UNIQUE (a) INITIALLY DEFERRED)",
"DEFERRABLE constraint not supported"),
# ADD CONSTRAINT ... UNIQUE has its own wording again.
("ALTER TABLE t ADD CONSTRAINT u UNIQUE USING INDEX idx DEFERRABLE",
"ALTER TABLE / ADD CONSTRAINT UNIQUE / DEFERRED / "
"INITIALLY DEFERRED not supported"),
("ALTER TABLE t ADD CONSTRAINT u UNIQUE USING INDEX idx "
"INITIALLY DEFERRED",
"ALTER TABLE / ADD CONSTRAINT UNIQUE / DEFERRED / "
"INITIALLY DEFERRED not supported"),
("ALTER TABLE t ADD CONSTRAINT c CHECK (a > 0) DEFERRABLE NOT VALID",
"CHECK constraints cannot be marked DEFERRABLE"),
# A CHECK constraint has its own wording.
("CREATE TABLE t (a int, CONSTRAINT c CHECK (a > 0) "
"DEFERRABLE INITIALLY IMMEDIATE)",
"CHECK constraints cannot be marked DEFERRABLE"),
],
)
def test_deferrable_on_a_non_foreign_key_denied(self, sql, message):
err = pgproxy.validate(sql, pgproxy.TxnState())
assert isinstance(err, pgproxy.DsqlError), sql
assert err.sqlstate == "0A000"
assert err.message == message
@pytest.mark.parametrize(
"sql",
[
# NOT DEFERRABLE is the default, so it is accepted anywhere.
"CREATE TABLE t (a int, UNIQUE (a) NOT DEFERRABLE)",
"CREATE TABLE t (a int PRIMARY KEY NOT DEFERRABLE)",
"CREATE TABLE t (a int, CHECK (a > 0) NOT DEFERRABLE)",
# A column carrying both constraints defers the foreign key.
"CREATE TABLE t (p int UNIQUE REFERENCES o DEFERRABLE)",
"CREATE TABLE t (a int UNIQUE, p int REFERENCES o "
"DEFERRABLE INITIALLY DEFERRED)",
# INITIALLY IMMEDIATE declares no deferral either.
"ALTER TABLE t ADD CONSTRAINT u UNIQUE USING INDEX idx "
"INITIALLY IMMEDIATE",
"ALTER TABLE t ADD CONSTRAINT u UNIQUE USING INDEX idx NOT DEFERRABLE",
"ALTER TABLE t ADD CONSTRAINT c CHECK (a > 0) NOT DEFERRABLE NOT VALID",
"ALTER TABLE t ADD CONSTRAINT f FOREIGN KEY (p) REFERENCES o "
"NOT VALID DEFERRABLE",
# The word as data, not as a constraint option.
"CREATE TABLE t (a int, s text, CHECK (s <> 'DEFERRABLE'))",
],
)
def test_deferrable_accepted(self, sql):
assert pgproxy.validate(sql, pgproxy.TxnState()) is None, sql
class TestIdentityAndSequences:
@pytest.mark.parametrize(
"sql",
[
"CREATE TABLE t (id bigint GENERATED BY DEFAULT AS IDENTITY (CACHE 1) PRIMARY KEY)",
"CREATE TABLE t (id bigint GENERATED ALWAYS AS IDENTITY (CACHE 65536))",
"CREATE TABLE t (id bigint GENERATED BY DEFAULT AS IDENTITY)",
"CREATE SEQUENCE s CACHE 1",
"CREATE SEQUENCE s CACHE 65536",
"CREATE SEQUENCE s CACHE 1000000",
"CREATE SEQUENCE s",
],
)
def test_allowed(self, sql):
assert pgproxy.validate(sql, pgproxy.TxnState()) is None, sql
@pytest.mark.parametrize(
"sql",
[
# identity is bigint-only
"CREATE TABLE t (id int GENERATED BY DEFAULT AS IDENTITY (CACHE 1))",
"CREATE TABLE t (id integer GENERATED ALWAYS AS IDENTITY (CACHE 65536))",
# CACHE must be 1 or >= 65536
"CREATE TABLE t (id bigint GENERATED BY DEFAULT AS IDENTITY (CACHE 100))",
"CREATE SEQUENCE s CACHE 100",
"CREATE SEQUENCE s CACHE 65535",
],
)
def test_denied(self, sql):
err = pgproxy.validate(sql, pgproxy.TxnState())
assert isinstance(err, pgproxy.DsqlError), sql
assert err.sqlstate == "0A000"
class TestIndexLimits:
# Messages, sqlstates and precedence verified against a live Aurora DSQL
# cluster (eu-west-2, Aug 2026).
@pytest.mark.parametrize(
"sql,sqlstate,message",
[
# IF NOT EXISTS requires a name (grammar-level)
("CREATE INDEX ASYNC IF NOT EXISTS ON t (a)", "42601",
'syntax error at or near "ON"'),
("CREATE INDEX CONCURRENTLY i ON t (a)", "0A000",
"CONCURRENTLY not supported for CREATE INDEX"),
("CREATE INDEX i ON t USING gin (data)", "0A000",
"USING not supported for CREATE INDEX"),
("CREATE INDEX ASYNC i ON t USING btree (a)", "0A000",
"USING not supported for CREATE INDEX"),
# plain (synchronous) mode is rejected outright, empty table or not
("CREATE INDEX i ON t (a)", "0A000",
"unsupported mode. please use CREATE INDEX ASYNC."),
("CREATE UNIQUE INDEX i ON t (a NULLS LAST) INCLUDE (b)", "0A000",
"unsupported mode"),
# mode is reported before immutability
("CREATE INDEX i ON t (now())", "0A000", "unsupported mode"),
# volatile functions in index expressions (must be immutable)
("CREATE INDEX ASYNC i ON t (now())", "42P17",
"functions in index expression must be marked IMMUTABLE"),
("CREATE INDEX ASYNC i ON t (random())", "42P17", "IMMUTABLE"),
("CREATE INDEX ASYNC i ON t (gen_random_uuid())", "42P17", "IMMUTABLE"),
("CREATE INDEX ASYNC i ON t (lower(email), nextval('s'))", "42P17",
"IMMUTABLE"),
# INCLUDE takes plain columns only (no expressions)
("CREATE INDEX ASYNC i ON t (a) INCLUDE (lower(b))", "0A000",
"expressions are not supported in included columns"),
("CREATE UNIQUE INDEX ASYNC i ON t (a) INCLUDE ((b + c))", "0A000",
"included columns"),
# partial indexes: plain mode still needs ASYNC
("CREATE INDEX i ON t (a) WHERE a > 0", "0A000",
"unsupported mode. please use CREATE INDEX ASYNC."),
# the predicate must be immutable too (Postgres' own message)
("CREATE INDEX ASYNC i ON t (a) WHERE created > now()", "42P17",
"functions in index predicate must be marked IMMUTABLE"),
("CREATE INDEX ASYNC i ON t (a) INCLUDE (b) WHERE random() < 0.5",
"42P17", "index predicate"),
("CREATE INDEX ASYNC i ON t (a) WHERE b IN (SELECT id FROM u)",
"0A000", "cannot use subquery in index predicate"),
# Precedence measured on real DSQL (eu-central-1, 2026-10-04):
# key count -> mode -> predicate -> key expressions -> INCLUDE.
("CREATE INDEX i ON t (a,b,c,d,e,f,g,h,i)", "54011",
"more than 8 column keys"),
("CREATE INDEX i ON t (now(),a,b,c,d,e,f,g,h)", "54011",
"more than 8 column keys"),
("CREATE INDEX ASYNC i ON t (now(),a,b,c,d,e,f,g,h)", "54011",
"more than 8 column keys"),
("CREATE INDEX ASYNC i ON t (a,b,c,d,e,f,g,h,i) INCLUDE ((b + c))",
"54011", "more than 8 column keys"),
("CREATE INDEX ASYNC i ON t (a,b,c,d,e,f,g,h,i) WHERE b IN (SELECT 1)",
"54011", "more than 8 column keys"),
("CREATE INDEX CONCURRENTLY i ON t (a,b,c,d,e,f,g,h,i)", "0A000",
"CONCURRENTLY"),
("CREATE INDEX i ON t (a) WHERE random() < 0.5", "0A000",
"unsupported mode"),
("CREATE INDEX i ON t (a) INCLUDE ((b + c))", "0A000",
"unsupported mode"),
("CREATE INDEX ASYNC i ON t (now()) WHERE random() < 0.5", "42P17",
"index predicate"),
("CREATE INDEX ASYNC i ON t (now()) WHERE b IN (SELECT 1)", "0A000",
"subquery"),
("CREATE INDEX ASYNC i ON t (a) INCLUDE ((b + c)) WHERE random() < 0.5",
"42P17", "index predicate"),
("CREATE INDEX ASYNC i ON t (now()) INCLUDE ((b + c))", "42P17",
"index expression"),
],
)
def test_unsupported_index_forms_denied(self, sql, sqlstate, message):
err = pgproxy.validate(sql, pgproxy.TxnState())
assert isinstance(err, pgproxy.DsqlError), sql
assert err.sqlstate == sqlstate, sql
assert message in err.message, sql
@pytest.mark.parametrize(
"sql",
[
"CREATE INDEX ASYNC i ON t (a)",
"CREATE INDEX ASYNC i ON t (a, b DESC) INCLUDE (c)",
"CREATE UNIQUE INDEX ASYNC i ON t (a NULLS LAST) NULLS NOT DISTINCT",
# expression index keys (immutable functions/operators)
"CREATE INDEX ASYNC i ON t (lower(email))",
"CREATE INDEX ASYNC i ON t ((data->>'city'))",
"CREATE INDEX ASYNC ON t (upper(title), (a + b) NULLS LAST)",
# partial indexes (AWS DSQL, 2026-09-15)
"CREATE INDEX ASYNC high_rating_idx ON films (title) WHERE rating > 5",
"CREATE UNIQUE INDEX ASYNC i ON t (email) WHERE NOT archived",
"CREATE UNIQUE INDEX ASYNC i ON t (a) INCLUDE (b) NULLS NOT DISTINCT "
"WHERE status = 'active'",
"CREATE INDEX ASYNC i ON t (lower(title)) WHERE deleted_at IS NULL",
# function names and keywords inside literals aren't calls
"CREATE INDEX ASYNC i ON t (a) WHERE note <> 'now() or SELECT'",
],
)
def test_supported_index_forms_allowed(self, sql):
result = pgproxy.validate(sql, pgproxy.TxnState())
assert isinstance(result, pgproxy.Rewrite), sql
def test_partial_index_predicate_reaches_the_backend(self):
result = pgproxy.validate(
"CREATE INDEX ASYNC i ON t (a) WHERE a > 0", pgproxy.TxnState()
)
assert result.sql == "CREATE INDEX i ON t (a) WHERE a > 0"
assert result.job_type == "INDEX_BUILD"
def test_more_than_8_columns_denied(self):
err = pgproxy.validate(
"CREATE INDEX ASYNC i ON t (a,b,c,d,e,f,g,h,i)", pgproxy.TxnState()
)
assert isinstance(err, pgproxy.DsqlError)
assert err.sqlstate == "54011"
assert err.message == "more than 8 column keys in an index are not supported"
def test_exactly_8_columns_allowed(self):
result = pgproxy.validate(
"CREATE INDEX ASYNC i ON t (a,b,c,d,e,f,g,h)", pgproxy.TxnState()
)
assert isinstance(result, pgproxy.Rewrite)
class TestDsqlSettings:
"""dsql.* planner settings (dsql.enable_batched_nestloop, 2026-09-11)."""
@pytest.mark.parametrize(
"sql",
[
"SET dsql.enable_batched_nestloop = off",
"SET dsql.enable_batched_nestloop TO on",
"SET SESSION dsql.enable_batched_nestloop = 'false'",
"SET LOCAL dsql.enable_batched_nestloop = true",
"set dsql.enable_batched_nestloop = 0;",
"SET dsql.enable_batched_nestloop = of", # unique prefix of off
"SET dsql.enable_batched_nestloop = y",
"SET dsql.enable_batched_nestloop TO DEFAULT",
'SET dsql.enable_batched_nestloop = "on"',
"SET DSQL.ENABLE_BATCHED_NESTLOOP = OFF",
"RESET dsql.enable_batched_nestloop",
"SHOW dsql.enable_batched_nestloop",
"SELECT set_config('dsql.enable_batched_nestloop', 'off', false)",
# Ordinary settings DSQL lets a session change.
"SET enable_hashjoin = off",
"SET search_path = public",
# Refused settings can still be reset.
"RESET default_statistics_target",
"SET default_statistics_target TO DEFAULT",
],
)
def test_accepted(self, sql):
assert pgproxy.validate(sql, pgproxy.TxnState()) is None, sql
def test_value_list_rejected(self):
err = pgproxy.validate(
"SET dsql.enable_batched_nestloop = on, off", pgproxy.TxnState()
)
assert (err.sqlstate, err.message) == (
"22023", "SET dsql.enable_batched_nestloop takes only one argument",
)
def test_set_config_validates_the_value(self):
err = pgproxy.validate(
"SELECT set_config('dsql.enable_batched_nestloop', 'banana', false)",
pgproxy.TxnState(),
)
assert err.sqlstate == "22023"
assert "requires a Boolean value" in err.message
@pytest.mark.parametrize(
"sql",
[
"SET dsql.bogus = 1",
"SET LOCAL dsql.bogus = 1",
"SET SESSION dsql.bogus TO 1",
"RESET dsql.bogus",
"SET dsql.bogus TO DEFAULT",
"SELECT set_config('dsql.bogus', '1', false)",
],
)
def test_dsql_prefix_is_reserved(self, sql):
# Exact message and detail from real DSQL (eu-central-1, 2026-10-04).
err = pgproxy.validate(sql, pgproxy.TxnState())
assert err.sqlstate == "42602", sql
assert err.message == 'invalid configuration parameter name "dsql.bogus"'
assert err.detail == '"dsql" is a reserved prefix.'
@pytest.mark.parametrize(
"sql,param",
[
("SET default_statistics_target = 200", "default_statistics_target"),
("SET LOCAL default_statistics_target = 50", "default_statistics_target"),
("SELECT set_config('default_statistics_target', '50', false)",
"default_statistics_target"),
("SET work_mem = '64MB'", "work_mem"),
("SET statement_timeout = '5s'", "statement_timeout"),
],
)
def test_unsettable_parameters_rejected(self, sql, param):
err = pgproxy.validate(sql, pgproxy.TxnState())
assert err.sqlstate == "0A000", sql
assert err.message == f'setting configuration parameter "{param}" not supported'
@pytest.mark.parametrize(
"value", ["banana", "'maybe'", "2", "o", "''", "onn"],
)
def test_non_boolean_rejected(self, value):
err = pgproxy.validate(
f"SET dsql.enable_batched_nestloop = {value}", pgproxy.TxnState()
)
assert isinstance(err, pgproxy.DsqlError), value
assert err.sqlstate == "22023"
assert err.message == (
'parameter "dsql.enable_batched_nestloop" requires a Boolean value'
)
def test_backend_starts_with_dsql_defaults(self):
from ministack.services import dsql as dsql_mod
assert dsql_mod.PG_COMMAND == [
"postgres", "-c", "dsql.enable_batched_nestloop=on",
]
class TestStatisticsRules:
"""Extended statistics (AWS DSQL, 2026-08-27); messages measured live."""
@pytest.mark.parametrize("target", ["100", "-1", "0"])
def test_statistics_target_up_to_100_allowed(self, target):
sql = f"ALTER STATISTICS s SET STATISTICS {target}"
assert pgproxy.validate(sql, pgproxy.TxnState()) is None
def test_statistics_target_over_100_rejected(self):
err = pgproxy.validate(
"ALTER STATISTICS s SET STATISTICS 101", pgproxy.TxnState()
)
assert (err.sqlstate, err.message) == (
"22023", "statistics target 101 exceeds maximum allowed value of 100",
)
@pytest.mark.parametrize(
"sql",
[
"ALTER TABLE t ALTER COLUMN a SET STATISTICS 50",
"ALTER TABLE t ALTER a SET STATISTICS 200",
],
)
def test_column_statistics_target_rejected(self, sql):
err = pgproxy.validate(sql, pgproxy.TxnState())
assert (err.sqlstate, err.message) == (
"0A000",
"unsupported ALTER TABLE ALTER COLUMN ... SET STATISTICS statement",
)
@pytest.mark.parametrize(
"sql",
[
"CREATE STATISTICS s1 (dependencies) ON a, b FROM t",
"CREATE STATISTICS ON (lower(a)) FROM public.t",
"ALTER STATISTICS s1 RENAME TO s2",
"DROP STATISTICS s1",
],
)
def test_statements_pass_text_validation(self, sql):
assert pgproxy.validate(sql, pgproxy.TxnState()) is None
class TestWaitForJob:
def test_documented_form(self):
sql = "SELECT sys.wait_for_job(job_id) 'abc123'"
assert pgproxy.match_wait_for_job(sql) == "abc123"
def test_conventional_form(self):
sql = "SELECT sys.wait_for_job('abc123')"
assert pgproxy.match_wait_for_job(sql) == "abc123"
def test_call_form(self):
assert pgproxy.match_wait_for_job("CALL sys.wait_for_job('abc123')") == "abc123"
def test_no_match(self):
assert pgproxy.match_wait_for_job("SELECT 1") is None
assert pgproxy.match_wait_for_job("SELECT sys.wait_for_job(job_id)") is None
@pytest.mark.parametrize(
"sql",
[
"CALL sys.wait_for_job($1)",
"CALL sys.wait_for_job( $1::text )",
"SELECT sys.wait_for_job($1)",
],
)
def test_bound_parameter_form(self, sql):
# A client that binds the job id over the extended protocol sends
# the statement text as ...($1).
assert pgproxy.match_wait_for_job(sql) == pgproxy.BindParam(1)
def test_literal_with_cast(self):
sql = "CALL sys.wait_for_job('abc123'::text)"
assert pgproxy.match_wait_for_job(sql) == "abc123"
def test_sys_jobs_bound_filter(self):
assert pgproxy.match_sys_jobs(
"SELECT job_id, status FROM sys.jobs WHERE job_id = $2"
) == (["job_id", "status"], pgproxy.BindParam(2))
def test_job_ids_are_base32(self):
job = pgproxy._register_job("idtest", "public.i")
assert re.fullmatch(r"[a-z2-7]{26}", job["job_id"])
pgproxy._jobs.pop("idtest", None)
class TestReset:
def test_reset_stops_proxies_and_clears_jobs(self):
"""reset() runs in a worker thread (no running loop) — it must still
schedule proxy shutdown on the captured app loop and drop job state."""
from ministack.services import dsql as dsql_mod
loop = asyncio.new_event_loop()
thread = threading.Thread(target=loop.run_forever, daemon=True)
thread.start()
old_loop = dsql_mod._main_loop
dsql_mod._main_loop = loop
try:
port = _free_port()
asyncio.run_coroutine_threadsafe(
pgproxy.start_proxy("resettest", port, "127.0.0.1", 1), loop
).result(timeout=5)
pgproxy._jobs["resettest"] = [{"job_id": "x"}]
assert "resettest" in pgproxy._proxies
dsql_mod.reset() # called from this thread: no running loop here
deadline = time.time() + 5
while time.time() < deadline and "resettest" in pgproxy._proxies:
time.sleep(0.05)
assert "resettest" not in pgproxy._proxies
assert pgproxy.get_jobs("resettest") == []
finally:
dsql_mod._main_loop = old_loop
loop.call_soon_threadsafe(loop.stop)
thread.join(timeout=5)
class TestContainerCap:
def test_cap_exceeded_logs_and_degrades_to_stub(self, monkeypatch, caplog):
"""When the backend port window is exhausted, creating another cluster
logs a warning naming DSQL_BASE_PORT and goes metadata-only."""
import json
import logging
from ministack.services import dsql as dsql_mod
monkeypatch.setattr(dsql_mod, "_backends_enabled", lambda: True)
monkeypatch.setattr(dsql_mod, "_BACKEND_PORT_WINDOW", 0)
with caplog.at_level(logging.WARNING, logger="dsql"):
status, _, body = dsql_mod._create_cluster({})
assert status == 200
cluster = json.loads(body)
identifier = cluster["identifier"]
try:
assert cluster["status"] == "ACTIVE" # stub, not CREATING
assert dsql_mod._clusters[identifier]["_has_backend"] is False
assert any(
"DSQL_BASE_PORT" in r.message and "metadata-only" in r.message
for r in caplog.records
if r.name == "dsql" and r.levelno == logging.WARNING
)
finally:
dsql_mod._clusters.clear()
def test_containers_disabled_means_stub_even_with_docker(self, monkeypatch):
"""Default posture: Docker available but DSQL_STRICT unset/off —
clusters are metadata-only stubs and no backend is started."""
import json
from ministack.services import dsql as dsql_mod
monkeypatch.setattr(dsql_mod, "DSQL_STRICT", False)
monkeypatch.setattr(dsql_mod, "_docker_available", lambda: True)
assert dsql_mod._backends_enabled() is False
status, _, body = dsql_mod._create_cluster({})
assert status == 200
cluster = json.loads(body)
identifier = cluster["identifier"]
try:
assert cluster["status"] == "ACTIVE" # stub, not CREATING
assert dsql_mod._clusters[identifier]["_has_backend"] is False
finally:
dsql_mod._clusters.clear()
def test_backends_enabled_requires_flag_and_docker(self, monkeypatch):
from ministack.services import dsql as dsql_mod
monkeypatch.setattr(dsql_mod, "_docker_available", lambda: True)
monkeypatch.setattr(dsql_mod, "DSQL_STRICT", False)
assert dsql_mod._backends_enabled() is False
monkeypatch.setattr(dsql_mod, "DSQL_STRICT", True)
assert dsql_mod._backends_enabled() is True
monkeypatch.setattr(dsql_mod, "_docker_available", lambda: False)
assert dsql_mod._backends_enabled() is False
class TestIndexAsync:
def test_rewrite_strips_async(self):
assert pgproxy.rewrite_index_async(
"CREATE UNIQUE INDEX ASYNC IF NOT EXISTS idx ON t (a)"
) == "CREATE UNIQUE INDEX IF NOT EXISTS idx ON t (a)"
def test_validate_returns_rewrite(self):
result = pgproxy.validate(
"CREATE INDEX ASYNC idx ON t (a)", pgproxy.TxnState()
)
assert isinstance(result, pgproxy.Rewrite)
assert result.kind == "index_async"
assert result.sql == "CREATE INDEX idx ON t (a)"
assert result.object_name == "public.idx"
assert result.table == "t"
def test_named_index_takes_table_schema(self):
result = pgproxy.validate(
"CREATE INDEX ASYNC idx ON app.t (a)", pgproxy.TxnState()
)
assert isinstance(result, pgproxy.Rewrite)
assert result.object_name == "app.idx"
def test_schema_qualified_index_name_kept(self):
result = pgproxy.validate(
"CREATE INDEX ASYNC app.idx ON app.t (a)", pgproxy.TxnState()
)
assert isinstance(result, pgproxy.Rewrite)
assert result.object_name == "app.idx"
def test_unnamed_index_auto_named(self):
result = pgproxy.validate(
"CREATE INDEX ASYNC ON public.t (a, b)", pgproxy.TxnState()
)
assert isinstance(result, pgproxy.Rewrite)
assert result.object_name == "public.t_a_b_idx"
def test_unnamed_index_default_schema(self):
result = pgproxy.validate(
"CREATE INDEX ASYNC ON t (email)", pgproxy.TxnState()
)
assert isinstance(result, pgproxy.Rewrite)
assert result.object_name == "public.t_email_idx"
def test_plain_create_index_rejected(self):
# Real DSQL: index creation is always asynchronous; plain CREATE
# INDEX fails regardless of table contents.
err = pgproxy.validate("CREATE INDEX idx ON t (a)", pgproxy.TxnState())
assert isinstance(err, pgproxy.DsqlError)
assert err.sqlstate == "0A000"
assert "unsupported mode" in err.message
class TestTxnDiscipline:
def test_second_ddl_in_txn_rejected(self):
txn = pgproxy.TxnState()
txn.apply("BEGIN")
txn.apply("CREATE TABLE a (x int)")
err = pgproxy.validate("CREATE TABLE b (x int)", txn)
assert isinstance(err, pgproxy.DsqlError)
assert err.sqlstate == "25006"
assert "only one DDL" in err.message
def test_ddl_after_dml_rejected(self):
txn = pgproxy.TxnState()
txn.apply("BEGIN")
txn.apply("INSERT INTO t VALUES (1)")
err = pgproxy.validate("CREATE TABLE b (x int)", txn)
assert isinstance(err, pgproxy.DsqlError)
assert err.sqlstate == "25006"
assert "cannot be mixed" in err.message
def test_dml_after_ddl_rejected(self):
txn = pgproxy.TxnState()
txn.apply("BEGIN")
txn.apply("CREATE TABLE a (x int)")
err = pgproxy.validate("INSERT INTO a VALUES (1)", txn)
assert isinstance(err, pgproxy.DsqlError)
assert err.sqlstate == "25006"
assert "cannot be mixed" in err.message
def test_commit_resets_state(self):
txn = pgproxy.TxnState()
txn.apply("BEGIN")
txn.apply("CREATE TABLE a (x int)")
txn.apply("COMMIT")
assert pgproxy.validate("CREATE TABLE b (x int)", txn) is None
def test_autocommit_sequential_ddl_fine(self):
txn = pgproxy.TxnState()
assert pgproxy.validate("CREATE TABLE a (x int)", txn) is None
txn.apply("CREATE TABLE a (x int)")
assert pgproxy.validate("CREATE TABLE b (x int)", txn) is None
def test_select_insert_outside_txn_fine(self):
txn = pgproxy.TxnState()
assert pgproxy.validate("SELECT 1", txn) is None
assert pgproxy.validate("INSERT INTO t VALUES (1)", txn) is None
class TestTxnLimits:
def test_single_insert_over_3000_rows_rejected(self):
sql = "INSERT INTO t VALUES " + ",".join(f"({i})" for i in range(3001))
err = pgproxy.validate(sql, pgproxy.TxnState())
assert isinstance(err, pgproxy.DsqlError)
assert err.sqlstate == "25006"
assert "3,000" in err.message
def test_exactly_3000_rows_allowed(self):
sql = "INSERT INTO t VALUES " + ",".join(f"({i})" for i in range(3000))
assert pgproxy.validate(sql, pgproxy.TxnState()) is None
def test_cumulative_rows_across_txn(self):
txn = pgproxy.TxnState()
txn.apply("BEGIN")
batch = "INSERT INTO t VALUES " + ",".join(f"({i})" for i in range(2000))
assert pgproxy.validate(batch, txn) is None
txn.apply(batch)
err = pgproxy.validate(batch, txn) # 2000 + 2000 > 3000
assert isinstance(err, pgproxy.DsqlError)
assert "3,000" in err.message
def test_oversized_statement_rejected(self):
sql = "INSERT INTO t VALUES ('" + "x" * (10 * 1024 * 1024 + 1) + "')"
err = pgproxy.validate(sql, pgproxy.TxnState())
assert isinstance(err, pgproxy.DsqlError)
assert "10 MiB" in err.message
def test_duration_limit(self):
txn = pgproxy.TxnState()
txn.apply("BEGIN")
txn.started_at -= 301 # simulate a 5+ minute old transaction
err = pgproxy.validate("INSERT INTO t VALUES (1)", txn)
assert isinstance(err, pgproxy.DsqlError)
assert "5 minute" in err.message
def test_values_tuple_count(self):
assert pgproxy._values_tuple_count("INSERT INTO t VALUES (1, '(a'), (2)") == 2
assert pgproxy._values_tuple_count("INSERT INTO t SELECT * FROM s") == 0
assert pgproxy._values_tuple_count("UPDATE t SET a = 1") == 0
class TestIdentifierNormalization:
"""``"id"``, ``id`` and ``ID`` all name the same column; ``"ID"`` does not.
Rules that compare a statement's identifiers against catalog names have to
normalize the same way the server does, or quoted SQL stops matching."""
@pytest.mark.parametrize(
"raw,expected",
[
("id", ("id", False)),
("ID", ("id", False)),
(" id ", ("id", False)),
('"id"', ("id", True)),
('"ID"', ("ID", True)),
('"we ird"', ("we ird", True)),
('"quo""ted"', ('quo"ted', True)),
],
)
def test_fold_identifier(self, raw, expected):
assert pgproxy.fold_identifier(raw) == expected
@pytest.mark.parametrize(
"raw,expected",
[
("id", ["id"]),
("t.id", ["t", "id"]),
('"T".id', ["T", "id"]),
('t."ID"', ["t", "ID"]),
('"my schema"."My Table"', ["my schema", "My Table"]),
("Schema.Tbl.Col", ["schema", "tbl", "col"]),
],
)
def test_identifier_path(self, raw, expected):
assert pgproxy.identifier_path(raw) == expected
class TestLockingClauses:
"""What Aurora DSQL restricts about a locking read is the lock *strength*,
not the predicate. Measured against a live cluster (eu-central-1,
2026-08-18, server_version 16.15): FOR UPDATE locks whatever the query
selects — no WHERE clause, a non-key column, an inequality, IN/OR, a join,
a table with no primary key — while FOR NO KEY UPDATE and FOR SHARE are
refused with 0A000. FOR KEY SHARE joined FOR UPDATE on 2026-08-25."""
MESSAGE = "locking clauses other than FOR UPDATE/FOR KEY SHARE are not supported"
@pytest.mark.parametrize(
"sql",
[
"SELECT s FROM t WHERE id = '1' FOR UPDATE",
'SELECT "id", "s" FROM "t" WHERE "t"."id" = $1 FOR UPDATE',
"SELECT s FROM t FOR UPDATE",
"SELECT s FROM t WHERE s = 'a' FOR UPDATE",
"SELECT s FROM t WHERE id > '1' FOR UPDATE",
"SELECT s FROM t WHERE id IN (1, 2) FOR UPDATE",
"SELECT s FROM t WHERE id = 1 OR s = 'a' FOR UPDATE",
"SELECT a.s FROM t a JOIN t b ON a.id = b.id FOR UPDATE",
"SELECT s FROM t, u WHERE t.id = u.id FOR UPDATE",
"SELECT 1 FOR UPDATE",
"SELECT s FROM t WHERE id = 1 FOR UPDATE NOWAIT",
"SELECT s FROM t WHERE id = 1 FOR UPDATE SKIP LOCKED",
"SELECT s FROM t WHERE id = 1 FOR UPDATE OF t NOWAIT",
"SELECT * FROM (SELECT 1 FOR UPDATE) x FOR UPDATE",
# FOR KEY SHARE, supported since 2026-08-25.
"SELECT s FROM t WHERE id = 1 FOR KEY SHARE",
"SELECT s FROM t FOR KEY SHARE NOWAIT",
"SELECT * FROM (SELECT s FROM t FOR KEY SHARE) y",
"WITH x AS (SELECT s FROM t FOR KEY SHARE) SELECT * FROM x",
# The words only as text — not a locking clause at all.
"SELECT s FROM t WHERE s = 'FOR SHARE'",
"SELECT 'FOR NO KEY UPDATE' AS lit",
# FOR introducing something else entirely.
"DECLARE c CURSOR FOR SELECT 1",
"CREATE POLICY p ON t FOR UPDATE TO someone",
],
)
def test_supported_locking_reads_pass(self, sql):
assert pgproxy.validate(sql, pgproxy.TxnState()) is None, sql
@pytest.mark.parametrize(
"sql",
[
"SELECT s FROM t WHERE id = 1 FOR SHARE",
"SELECT s FROM t WHERE id = 1 FOR NO KEY UPDATE",
"SELECT s FROM t for share",
"SELECT s FROM t FOR SHARE NOWAIT",
# Any nesting depth: DSQL refuses it inside a CTE or a subquery.
"WITH x AS (SELECT s FROM t WHERE id = 1 FOR SHARE) SELECT * FROM x",
"SELECT * FROM (SELECT s FROM t FOR SHARE) y",
# A supported clause earlier in the statement must not hide one
# that still reaches a relation (both verified rejected by DSQL).
"SELECT x.id FROM (SELECT id FROM t FOR UPDATE) x, "
"(SELECT a FROM two) y FOR SHARE",
"WITH c AS (SELECT id FROM t FOR UPDATE) SELECT * FROM two FOR SHARE",
],
)
def test_unsupported_lock_strength_rejected(self, sql):
err = pgproxy.validate(sql, pgproxy.TxnState())
assert isinstance(err, pgproxy.DsqlError), sql
assert err.sqlstate == "0A000", sql
assert err.message == self.MESSAGE, sql
# ---------------------------------------------------------------------------
# Live proxy tests (need Docker — the backend is a real postgres container)
# ---------------------------------------------------------------------------
def _docker_daemon_available():
try:
import docker
docker.from_env().ping()
return True
except Exception:
return False
requires_docker = pytest.mark.skipif(
not _docker_daemon_available(), reason="Docker daemon not available"
)
def _free_port():
with socket.socket() as s:
s.bind(("127.0.0.1", 0))
return s.getsockname()[1]
def _prepare_postgres_image(client, image):
"""Keep registry acquisition out of the backend lifecycle being tested."""
from docker.errors import APIError, ImageNotFound
try:
client.images.get(image)
return
except ImageNotFound:
pass
# Retry only the Engine API 500 wrapping a registry connection reset.
for attempt in range(1, 4):
try:
client.images.pull(image)
return
except APIError as exc:
if exc.status_code != 500 or "connection reset by peer" not in str(exc).lower():
raise
logging.getLogger(__name__).warning(
"DSQL test image %s pull failed (attempt %d/3): %s", image, attempt, exc
)
if attempt == 3:
raise RuntimeError(f"Could not prepare DSQL test image {image} after 3 pull attempts") from exc
time.sleep(attempt)
@pytest.fixture(scope="module")
def pg_image():
docker = pytest.importorskip("docker")
from ministack.services import dsql as dsql_mod
client = docker.from_env()
try:
_prepare_postgres_image(client, dsql_mod.PG_IMAGE)
finally:
client.close()
return dsql_mod.PG_IMAGE
@pytest.fixture(scope="module")
def pg_backend(pg_image):
"""Run a trust-auth postgres container on an ephemeral published port."""
docker = pytest.importorskip("docker")
psycopg2 = pytest.importorskip("psycopg2")
client = docker.from_env()
from ministack.services import dsql as dsql_mod
container = client.containers.run(
image=pg_image,
command=dsql_mod.PG_COMMAND,
detach=True,
environment={
"POSTGRES_USER": "postgres",
"POSTGRES_DB": "postgres",
"POSTGRES_HOST_AUTH_METHOD": "trust",
},
ports={"5432/tcp": None}, # docker-assigned host port
)
try:
container.reload()
port = int(
container.attrs["NetworkSettings"]["Ports"]["5432/tcp"][0]["HostPort"]
)
deadline = time.time() + 60
while time.time() < deadline:
try:
conn = psycopg2.connect(
host="127.0.0.1",
port=port,
user="postgres",
dbname="postgres",
connect_timeout=2,
)
conn.close()
break
except psycopg2.OperationalError:
time.sleep(0.5)
else:
pytest.fail(f"postgres container not ready on port {port}")
yield "127.0.0.1", port
finally:
container.remove(force=True, v=True)
@pytest.fixture(scope="module")
def dsql_proxy(pg_backend):
"""Run a pgproxy in a background event loop in front of pg_backend."""
loop = asyncio.new_event_loop()
thread = threading.Thread(target=loop.run_forever, daemon=True)
thread.start()
proxy_port = _free_port()
asyncio.run_coroutine_threadsafe(
pgproxy.start_proxy("livetest", proxy_port, pg_backend[0], pg_backend[1]),
loop,
).result(timeout=10)
yield proxy_port
asyncio.run_coroutine_threadsafe(pgproxy.stop_all_proxies(), loop).result(timeout=10)
loop.call_soon_threadsafe(loop.stop)
thread.join(timeout=5)
def _pg_connect(port, autocommit=True):
psycopg2 = pytest.importorskip("psycopg2")
# Any user/password must be accepted (IAM-token posture).
conn = psycopg2.connect(
host="127.0.0.1",
port=port,
user="admin",
password="anything",
dbname="postgres",
connect_timeout=10,
)
conn.autocommit = autocommit
return conn
@requires_docker
@pytest.mark.data_plane
class TestContainersE2E:
"""End-to-end for DSQL_STRICT=1: _create_cluster spins up a real
Postgres container behind the wire proxy, reachable over SQL. Runs
in-process (flag monkeypatched on) wherever a Docker daemon exists."""
def test_env_flag_spins_up_real_backend(self, monkeypatch, pg_image, caplog):
import json
psycopg2 = pytest.importorskip("psycopg2")
pytest.importorskip("docker")
from ministack.services import dsql as dsql_mod
monkeypatch.setattr(dsql_mod, "DSQL_STRICT", True)
# _start_backend stashes the test's ephemeral loop in this global;
# put the original back afterwards (all consumers guard is_running(),
# but don't leak a closed loop into other tests).
old_main_loop = dsql_mod._main_loop
async def _run():
status, _, body = dsql_mod._create_cluster({})
assert status == 200
cluster = json.loads(body)
identifier = cluster["identifier"]
try:
# Backend container + proxy spin up in the background.
assert cluster["status"] == "CREATING"
deadline = time.time() + 120
while dsql_mod._clusters[identifier]["status"] != "ACTIVE":
assert time.time() < deadline, "backend did not start in time"
await asyncio.sleep(0.5)
assert dsql_mod._clusters[identifier]["_has_backend"] is True, caplog.text
def _select_one():
conn = psycopg2.connect(
host="127.0.0.1",
port=int(cluster["endpoint"].split(":")[1]),
user="admin",
password="anything",
dbname="postgres",
connect_timeout=10,
)
try:
conn.autocommit = True
with conn.cursor() as cur:
cur.execute("SELECT 1")
return cur.fetchone()
finally:
conn.close()
assert await asyncio.to_thread(_select_one) == (1,)
finally:
await dsql_mod._teardown_backend(identifier)
dsql_mod._clusters.pop(identifier, None)
try:
asyncio.run(_run())
finally:
dsql_mod._main_loop = old_main_loop
class _WireClient:
"""Raw Postgres wire-protocol client.
psycopg2 interpolates parameters client-side and always sends the simple
'Q' protocol, so it cannot reach the extended (Parse/Bind/Execute) path
that pgjdbc, pgx, asyncpg and psycopg3 use. These tests speak the protocol
directly so both paths are covered.
"""
def __init__(self, port, user="admin", database="postgres"):
self.sock = socket.create_connection(("127.0.0.1", port), timeout=15)
params = f"user\0{user}\0database\0{database}\0\0".encode()
payload = struct.pack("!I", 196608) + params
self.sock.sendall(struct.pack("!I", len(payload) + 4) + payload)
self.startup = self._until_ready()
def __enter__(self):
return self
def __exit__(self, *exc):
self.close()
def _recv(self, n):
buf = b""
while len(buf) < n:
chunk = self.sock.recv(n - len(buf))
if not chunk:
raise ConnectionError("proxy closed the connection")
buf += chunk
return buf
def _until_ready(self):
frames = []
while True:
type_byte = self._recv(1)
(length,) = struct.unpack("!I", self._recv(4))
payload = self._recv(length - 4)
frames.append((type_byte, payload))
if type_byte == b"Z":
return frames
@staticmethod
def _frame(type_byte, payload):
return type_byte + struct.pack("!I", len(payload) + 4) + payload
def simple(self, sql):
"""Simple query protocol — what psql and psycopg2 send."""
self.sock.sendall(self._frame(b"Q", sql.encode() + b"\0"))
return _WireResult(self._until_ready())
def extended(self, sql, params=()):
"""Parse/Bind/Describe/Execute/Sync — what prepared statements send.
``params`` are bound as text, so a ``$1`` placeholder reaches the proxy
as one, the way an ORM sends it."""
bind = b"\0\0" + struct.pack("!HH", 0, len(params))
for value in params:
if value is None:
bind += struct.pack("!i", -1)
else:
raw = str(value).encode()
bind += struct.pack("!i", len(raw)) + raw
bind += struct.pack("!H", 0) # result format codes
msg = self._frame(b"P", b"\0" + sql.encode() + b"\0" + struct.pack("!H", 0))
msg += self._frame(b"B", bind)
msg += self._frame(b"D", b"P\0")
msg += self._frame(b"E", b"\0" + struct.pack("!I", 0))
msg += self._frame(b"S", b"")
self.sock.sendall(msg)
return _WireResult(self._until_ready())
def close(self):
try:
self.sock.sendall(self._frame(b"X", b""))
except OSError:
pass
self.sock.close()
class _WireResult:
"""Decoded response: error state, rows, command tag, txn status byte."""
def __init__(self, frames):
self.sqlstate = None
self.message = None
self.rows = []
self.columns = [] # (name, type oid) from RowDescription
self.tag = None
self.txn_status = None
for type_byte, payload in frames:
if type_byte == b"T":
(n,) = struct.unpack("!H", payload[:2])
off = 2
for _ in range(n):
end = payload.index(b"\0", off)
name = payload[off:end].decode()
(oid,) = struct.unpack("!I", payload[end + 7 : end + 11])
self.columns.append((name, oid))
off = end + 19
if type_byte == b"E":
for part in payload.split(b"\0"):
if part[:1] == b"C":
self.sqlstate = part[1:].decode()
elif part[:1] == b"M":
self.message = part[1:].decode("utf-8", "replace")
elif type_byte == b"C":
self.tag = payload[:-1].decode("utf-8", "replace")
elif type_byte == b"D":
(n,) = struct.unpack("!H", payload[:2])
values, off = [], 2
for _ in range(n):
(vlen,) = struct.unpack("!i", payload[off:off + 4])
off += 4
if vlen < 0:
values.append(None)
else:
values.append(payload[off:off + vlen].decode("utf-8", "replace"))
off += vlen
self.rows.append(tuple(values))
elif type_byte == b"Z":
self.txn_status = payload.decode()
@property
def ok(self):
return self.sqlstate is None
@requires_docker
@pytest.mark.data_plane
class TestExtendedProtocol:
"""The extended protocol must enforce the same DSQL subset as 'Q'.
Relaying Parse unvalidated let every prepared-statement client — pgjdbc,
pgx, asyncpg, psycopg3, and the ORMs on top of them — past the validator.
"""
@pytest.mark.parametrize(
"sql,expected",
[
("CREATE TABLE xp_serial (id serial)", "serial"),
("CREATE TABLE xp_uq (id int, UNIQUE (id) DEFERRABLE)",
"DEFERRABLE"),
("CREATE EXTENSION pgcrypto", "CREATE EXTENSION"),
("CREATE TEMP TABLE xp_tmp (id int)", "temporary"),
("TRUNCATE xp_base", "TRUNCATE"),
("CREATE MATERIALIZED VIEW xp_mv AS SELECT 1", "materialized view"),
("CREATE TABLE xp_part (id int) PARTITION BY RANGE (id)",
"partitioning"),
("CREATE TABLE xp_bad (id int, ts money)", "money"),
],
)
def test_unsupported_sql_is_rejected_over_parse(self, dsql_proxy, sql, expected):
with _WireClient(dsql_proxy) as c:
result = c.extended(sql)
assert not result.ok, f"{sql} was accepted over the extended protocol"
assert result.sqlstate == "0A000"
assert expected in result.message
def test_rejected_parse_leaves_the_connection_usable(self, dsql_proxy):
"""After an error the client must be able to keep working (the proxy
has to honour skip-until-Sync, then answer the Sync)."""
with _WireClient(dsql_proxy) as c:
assert not c.extended("CREATE TABLE xp_reuse (id serial)").ok
result = c.extended("SELECT 1")
assert result.ok and result.rows == [("1",)]
def test_valid_ddl_still_passes_over_parse(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
assert c.extended("CREATE TABLE xp_good (id bigint, name text)").ok
assert c.extended("INSERT INTO xp_good VALUES (1, 'a')").ok
result = c.extended("SELECT name FROM xp_good WHERE id = 1")
assert result.rows == [("a",)]
def test_transaction_limits_apply_over_parse(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE xp_rows (id int)")
big = "INSERT INTO xp_rows VALUES " + ", ".join(
f"({i})" for i in range(3100)
)
result = c.extended(big)
assert result.sqlstate == "25006"
assert "3,000 row" in result.message
def test_locking_clause_rules_apply_over_parse(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE xp_lock (id int PRIMARY KEY, v int)")
# An unsupported lock strength is refused ...
result = c.extended("SELECT * FROM xp_lock WHERE id = 1 FOR SHARE")
assert result.sqlstate == "0A000"
assert "locking clauses other than FOR UPDATE" in result.message
# ... while FOR UPDATE reaches the backend whatever the predicate.
assert c.extended("SELECT * FROM xp_lock WHERE v > 1 FOR UPDATE").ok
def test_describe_statement_on_create_index_async(self, dsql_proxy):
"""Parse + Describe('S'), as asyncpg prepares: no parameters, a job_id column."""
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE xp_desc (id int, name text)")
c.sock.sendall(
c._frame(b"P", b"\0CREATE INDEX ASYNC xp_desc_i ON xp_desc (name)\0" + struct.pack("!H", 0))
+ c._frame(b"D", b"S\0") + c._frame(b"S", b"")
)
frames = c._until_ready()
assert [t for t, _ in frames] == [b"1", b"t", b"T", b"Z"]
assert dict(frames)[b"t"] == struct.pack("!H", 0)
assert c.simple("SELECT 1").rows == [("1",)]
def test_create_index_async_returns_a_job_over_parse(self, dsql_proxy):
"""DSQL-only syntax has to be rewritten on this path too, or the
backend answers with a bare syntax error."""
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE xp_idx (id int, name text)")
result = c.extended("CREATE INDEX ASYNC xp_i ON xp_idx (name)")
assert result.ok, result.message
assert len(result.rows) == 1
job_id = result.rows[0][0]
assert ID_RE.match(job_id)
jobs = c.extended(
f"SELECT job_id, status FROM sys.jobs WHERE job_id = '{job_id}'"
)
assert jobs.rows == [(job_id, "completed")]
def test_sys_jobs_is_emulated_over_parse(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
result = c.extended("SELECT job_id FROM sys.jobs")
assert result.ok, result.message
def test_txn_state_survives_begin_and_commit_over_parse(self, dsql_proxy):
"""Transaction state comes from the backend's ReadyForQuery byte, so a
BEGIN or COMMIT sent over either protocol keeps the validator honest."""
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE xp_txn (id int)")
assert c.extended("BEGIN").ok
assert c.simple("CREATE TABLE xp_txn_a (id int)").ok
# Second DDL in the same transaction: DSQL allows only one.
result = c.simple("CREATE TABLE xp_txn_b (id int)")
assert result.sqlstate == "25006"
c.simple("ROLLBACK")
def test_commit_over_parse_does_not_strand_txn_state(self, dsql_proxy):
"""A stranded in_txn made the validator reject valid autocommit DDL —
a false rejection, worse than a missed one."""
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE xp_strand (id int)")
assert c.simple("BEGIN").ok
assert c.extended("INSERT INTO xp_strand VALUES (1)").ok
assert c.extended("COMMIT").ok
assert c.simple("CREATE TABLE xp_strand_a (id int)").ok
assert c.simple("CREATE TABLE xp_strand_b (id int)").ok
assert c.simple("INSERT INTO xp_strand VALUES (2)").ok
def test_ready_for_query_status_tracks_the_backend(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
assert c.extended("BEGIN").txn_status == "T"
# Rejected by the proxy, but the block really is in a transaction.
assert c.simple("TRUNCATE xp_base").txn_status == "E"
assert c.simple("ROLLBACK").txn_status == "I"
@requires_docker
@pytest.mark.data_plane
class TestDropColumn:
"""Aurora DSQL gained ALTER TABLE ... DROP COLUMN on 2026-08-03, including
several columns in one statement, but dropping a primary key column is not
supported. The rule needs a catalog probe, so it runs in the proxy."""
def test_drop_non_pk_column(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE dc_a (id int PRIMARY KEY, x int, y int)")
assert c.simple("ALTER TABLE dc_a DROP COLUMN x").ok
cols = c.simple(
"SELECT column_name FROM information_schema.columns "
"WHERE table_name = 'dc_a' ORDER BY column_name"
)
assert cols.rows == [("id",), ("y",)]
def test_drop_several_non_pk_columns_in_one_statement(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE dc_b (id int PRIMARY KEY, x int, y int, z int)")
assert c.simple("ALTER TABLE dc_b DROP COLUMN x, DROP COLUMN y").ok
cols = c.simple(
"SELECT column_name FROM information_schema.columns "
"WHERE table_name = 'dc_b' ORDER BY column_name"
)
assert cols.rows == [("id",), ("z",)]
def test_drop_pk_column_rejected(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE dc_c (id int PRIMARY KEY, x int)")
result = c.simple("ALTER TABLE dc_c DROP COLUMN id")
assert result.sqlstate == "0A000"
# exact message from real DSQL (verified in eu-west-2, Aug 2026)
assert result.message == "cannot drop primary key column id"
# The column must still be there.
assert c.simple("SELECT id FROM dc_c").ok
def test_drop_pk_column_rejected_over_extended_protocol(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE dc_d (id int PRIMARY KEY, x int)")
result = c.extended("ALTER TABLE dc_d DROP COLUMN id")
assert result.sqlstate == "0A000"
assert "primary key" in result.message
def test_pk_column_in_a_multi_drop_rejects_the_whole_statement(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE dc_e (id int PRIMARY KEY, x int)")
result = c.simple("ALTER TABLE dc_e DROP COLUMN x, DROP COLUMN id")
assert result.sqlstate == "0A000"
assert "primary key" in result.message
# Nothing was dropped — the statement never reached the backend.
assert c.simple("SELECT id, x FROM dc_e").ok
def test_composite_pk_columns_are_all_protected(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE dc_f (a int, b int, x int, PRIMARY KEY (a, b))")
assert c.simple("ALTER TABLE dc_f DROP COLUMN b").sqlstate == "0A000"
assert c.simple("ALTER TABLE dc_f DROP COLUMN x").ok
def test_other_drop_actions_are_unaffected(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
c.simple(
"CREATE TABLE dc_g (id int PRIMARY KEY, x int DEFAULT 1 NOT NULL)"
)
assert c.simple("ALTER TABLE dc_g ALTER COLUMN x DROP DEFAULT").ok
assert c.simple("ALTER TABLE dc_g ALTER COLUMN x DROP NOT NULL").ok
def test_table_without_a_primary_key(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE dc_h (x int, y int)")
assert c.simple("ALTER TABLE dc_h DROP COLUMN x").ok
@requires_docker
@pytest.mark.data_plane
class TestLockingReads:
"""A locking read has to reach the backend whatever its predicate looks
like — an ORM quotes every identifier, and DSQL itself places no
restriction on the predicate (measured, eu-central-1, 2026-08-18). What it
does refuse is a lock strength other than FOR UPDATE/FOR KEY SHARE."""
UUID = "11111111-1111-1111-1111-111111111111"
MESSAGE = "locking clauses other than FOR UPDATE/FOR KEY SHARE are not supported"
@staticmethod
def _predicates(table, value):
"""The same key equality in every quoting and qualification form."""
return [
f"id = {value}",
f'"id" = {value}',
f"{table}.id = {value}",
f'"{table}".id = {value}',
f'{table}."id" = {value}',
f'"{table}"."id" = {value}',
]
def _setup(self, client, name, schema=None):
relation = f"{schema}.{name}" if schema else name
if schema:
client.simple(f"CREATE SCHEMA IF NOT EXISTS {schema}")
client.simple(f"DROP TABLE IF EXISTS {relation}")
assert client.simple(
f"CREATE TABLE {relation} (id uuid PRIMARY KEY, s text)"
).ok
assert client.simple(
f"INSERT INTO {relation} VALUES ('{self.UUID}', 'a')"
).ok
def test_quoted_and_qualified_keys_accepted(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
self._setup(c, "fu_q")
for relation in ("fu_q", '"fu_q"', "public.fu_q", '"public"."fu_q"'):
for predicate in self._predicates("fu_q", f"'{self.UUID}'"):
sql = f"SELECT s FROM {relation} WHERE {predicate} FOR UPDATE"
result = c.simple(sql)
assert result.ok, f"{sql} -> {result.sqlstate} {result.message}"
assert result.rows == [("a",)], sql
def test_schema_qualified_relation_accepted(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
self._setup(c, "fu_s", schema="myschema")
for relation in ("myschema.fu_s", '"myschema".fu_s',
'myschema."fu_s"', '"myschema"."fu_s"'):
for predicate in self._predicates("fu_s", f"'{self.UUID}'"):
sql = f"SELECT s FROM {relation} WHERE {predicate} FOR UPDATE"
result = c.simple(sql)
assert result.ok, f"{sql} -> {result.sqlstate} {result.message}"
assert result.rows == [("a",)], sql
def test_bound_parameter_key_accepted_over_extended_protocol(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
self._setup(c, "fu_b")
for predicate in self._predicates("fu_b", "$1"):
sql = f"SELECT s FROM fu_b WHERE {predicate} FOR UPDATE"
result = c.extended(sql, params=(self.UUID,))
assert result.ok, f"{sql} -> {result.sqlstate} {result.message}"
assert result.rows == [("a",)], sql
def test_orm_generated_locking_read(self, dsql_proxy):
"""Verbatim shape of a Drizzle ``eq(orders.id, id)`` locking read."""
with _WireClient(dsql_proxy) as c:
c.simple("DROP TABLE IF EXISTS orders")
c.simple(
'CREATE TABLE "orders" ('
'"id" uuid PRIMARY KEY, "order_status" text, "total" int)'
)
c.simple(f"INSERT INTO orders VALUES ('{self.UUID}', 'new', 7)")
sql = (
'SELECT "id", "order_status", "total" FROM "orders" '
'WHERE "orders"."id" = $1 FOR UPDATE'
)
result = c.extended(sql, params=(self.UUID,))
assert result.ok, f"{result.sqlstate} {result.message}"
assert result.rows == [(self.UUID, "new", "7")]
def test_locking_read_inside_a_transaction(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
self._setup(c, "fu_t")
assert c.simple("BEGIN").ok
result = c.simple(
f'SELECT s FROM "fu_t" WHERE "fu_t"."id" = \'{self.UUID}\' FOR UPDATE'
)
assert result.ok, f"{result.sqlstate} {result.message}"
assert c.simple("COMMIT").ok
@pytest.mark.parametrize(
"tail",
[
"FOR UPDATE", # no predicate at all
"WHERE s = 'a' FOR UPDATE", # a non-key column
"WHERE id > '{uuid}' FOR UPDATE", # not an equality
"WHERE id IN ('{uuid}') FOR UPDATE",
"WHERE id = '{uuid}' OR s = 'a' FOR UPDATE",
"WHERE id = '{uuid}' FOR UPDATE NOWAIT",
"WHERE id = '{uuid}' FOR UPDATE SKIP LOCKED",
"WHERE id = '{uuid}' FOR UPDATE OF fu_p",
],
)
def test_predicate_is_not_restricted(self, dsql_proxy, tail):
"""DSQL locks whatever the query selects — the emulator must not be
stricter than the service it stands in for."""
with _WireClient(dsql_proxy) as c:
self._setup(c, "fu_p")
sql = f"SELECT s FROM fu_p {tail.format(uuid=self.UUID)}"
for send in (c.simple, c.extended):
result = send(sql)
assert result.ok, f"{sql} -> {result.sqlstate} {result.message}"
def test_join_and_missing_primary_key_accepted(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
self._setup(c, "fu_j")
c.simple("DROP TABLE IF EXISTS fu_nokey")
c.simple("CREATE TABLE fu_nokey (a int, s text)")
c.simple("INSERT INTO fu_nokey VALUES (1, 'a')")
for sql in (
"SELECT a.s FROM fu_j a JOIN fu_j b ON a.id = b.id FOR UPDATE",
"SELECT s FROM fu_nokey WHERE a = 1 FOR UPDATE",
"SELECT s FROM fu_nokey FOR UPDATE",
):
result = c.simple(sql)
assert result.ok, f"{sql} -> {result.sqlstate} {result.message}"
@pytest.mark.parametrize("clause", ["FOR SHARE", "FOR NO KEY UPDATE"])
def test_other_lock_strengths_rejected(self, dsql_proxy, clause):
with _WireClient(dsql_proxy) as c:
self._setup(c, "fu_l")
sql = f"SELECT s FROM fu_l WHERE id = '{self.UUID}' {clause}"
for send in (c.simple, c.extended):
result = send(sql)
assert result.sqlstate == "0A000", sql
assert result.message == self.MESSAGE, sql
def test_key_share_reaches_the_backend(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
self._setup(c, "fu_ks")
sql = f"SELECT s FROM fu_ks WHERE id = '{self.UUID}' FOR KEY SHARE"
for send in (c.simple, c.extended):
result = send(sql)
assert result.ok, f"{sql} -> {result.sqlstate} {result.message}"
assert result.rows == [("a",)], sql
def test_lock_strength_in_a_literal_is_not_a_clause(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
self._setup(c, "fu_lit")
result = c.simple("SELECT s FROM fu_lit WHERE s = 'FOR SHARE'")
assert result.ok, f"{result.sqlstate} {result.message}"
@requires_docker
@pytest.mark.data_plane
class TestWaitForJobOverTheWire:
"""``CALL sys.wait_for_job`` as drivers send it. Shapes measured on Aurora
DSQL (eu-central-1, 2026-10-04): one bool OUT column named ``succeeded``,
command tag ``CALL``, with a literal or a bound ``$1`` alike."""
def _job(self, c, table, unique=False, dup=False):
c.simple(f"DROP TABLE IF EXISTS {table}")
c.simple(f"CREATE TABLE {table} (id int PRIMARY KEY, a int)")
if dup:
c.simple(f"INSERT INTO {table} VALUES (1, 1), (2, 1)")
kind = "UNIQUE INDEX" if unique else "INDEX"
result = c.extended(f"CREATE {kind} ASYNC {table}_a ON {table} (a)")
assert result.ok, result.message
return result.rows[0][0]
def test_bound_job_id(self, dsql_proxy):
"""``CALL sys.wait_for_job($1)`` with the id bound, read back through
the ``succeeded`` column."""
with _WireClient(dsql_proxy) as c:
job = self._job(c, "wfj_drz")
result = c.extended("CALL sys.wait_for_job($1)", params=(job,))
assert result.ok, f"{result.sqlstate} {result.message}"
assert result.columns == [("succeeded", 16)]
assert result.rows == [("t",)]
assert result.tag == "CALL"
c.simple("DROP TABLE wfj_drz")
def test_cast_and_literal_forms(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
job = self._job(c, "wfj_forms")
cast = c.extended("CALL sys.wait_for_job($1::text)", params=(job,))
assert (cast.columns, cast.rows, cast.tag) == (
[("succeeded", 16)], [("t",)], "CALL",
)
literal = c.simple(f"CALL sys.wait_for_job('{job}')")
assert (literal.columns, literal.rows, literal.tag) == (
[("succeeded", 16)], [("t",)], "CALL",
)
c.simple("DROP TABLE wfj_forms")
def test_failed_job_answers_false(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
job = self._job(c, "wfj_fail", unique=True, dup=True)
result = c.extended("CALL sys.wait_for_job($1)", params=(job,))
assert result.rows == [("f",)]
jobs = c.extended(
"SELECT status, details FROM sys.jobs WHERE job_id = $1",
params=(job,),
)
assert jobs.rows == [
("failed", "found duplicate key(s) while validating index uniqueness")
]
c.simple("DROP TABLE wfj_fail")
def test_sys_jobs_bound_filter(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
job = self._job(c, "wfj_jobs")
result = c.extended(
"SELECT job_id, status FROM sys.jobs WHERE job_id = $1", params=(job,)
)
assert result.rows == [(job, "completed")]
assert result.tag == "SELECT 1"
c.simple("DROP TABLE wfj_jobs")
def test_async_ddl_command_tags(self, dsql_proxy):
"""CREATE INDEX ASYNC answers CREATE INDEX, ALTER TABLE ASYNC answers
ALTER TABLE; both carry the job_id row."""
with _WireClient(dsql_proxy) as c:
c.simple("DROP TABLE IF EXISTS wfj_tags")
c.simple("CREATE TABLE wfj_tags (id int PRIMARY KEY, a int)")
c.simple("ALTER TABLE wfj_tags ADD CONSTRAINT wfj_ck CHECK (a > 0) NOT VALID")
for result in (
c.simple("CREATE INDEX ASYNC wfj_tags_a ON wfj_tags (a)"),
c.extended("CREATE INDEX ASYNC wfj_tags_b ON wfj_tags (a)"),
):
assert (result.tag, result.columns) == ("CREATE INDEX", [("job_id", 25)])
for sql in (
"ALTER TABLE ASYNC wfj_tags VALIDATE CONSTRAINT wfj_ck",
):
result = c.simple(sql)
assert result.tag == "ALTER TABLE", sql
validate_job = result.rows[0][0]
jobs = c.extended(
"SELECT class_id, start_time FROM sys.jobs WHERE job_id = $1",
params=(validate_job,),
)
# oid and timestamptz, rendered the way Postgres renders them
assert jobs.columns == [("class_id", 26), ("start_time", 1184)]
assert jobs.rows[0][0] == "2606" # pg_constraint
assert re.fullmatch(
r"\d{4}-\d\d-\d\d \d\d:\d\d:\d\d\+00", jobs.rows[0][1]
)
c.simple("DROP TABLE wfj_tags")
def test_startup_reports_timezone(self, dsql_proxy):
"""Drivers decode timestamptz by the reported TimeZone. Values as
real DSQL reports them."""
with _WireClient(dsql_proxy) as c:
status = dict(
p[:-1].decode().split("\0", 1) for t, p in c.startup if t == b"S"
)
assert status["TimeZone"] == "UTC"
assert status["DateStyle"] == "ISO, MDY"
assert status["IntervalStyle"] == "postgres"
def test_malformed_job_id_rejected(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
result = c.extended("CALL sys.wait_for_job($1)", params=("not-a-job",))
assert (result.sqlstate, result.message) == (
"22P02", "Unable to convert text to UUID",
)
# The error ends at Sync; the session carries on.
assert c.simple("SELECT 1").rows == [("1",)]
def test_placeholder_in_a_simple_query(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
result = c.simple("CALL sys.wait_for_job($1)")
assert (result.sqlstate, result.message) == (
"42P02", "there is no parameter $1",
)
def test_named_statement_rebinds(self, dsql_proxy):
"""One prepared statement, two Binds: each portal gets its own answer."""
with _WireClient(dsql_proxy) as c:
job = self._job(c, "wfj_named")
f = c._frame
def bind(portal, value):
raw = value.encode()
return f(
b"B",
portal + b"\0wfj\0" + struct.pack("!HHi", 0, 1, len(raw)) + raw
+ struct.pack("!H", 0),
)
msg = f(b"P", b"wfj\0CALL sys.wait_for_job($1)\0" + struct.pack("!H", 0))
msg += f(b"D", b"Swfj\0")
msg += bind(b"p1", job) + f(b"E", b"p1\0" + struct.pack("!I", 0))
msg += bind(b"p2", "a" * 26) + f(b"E", b"p2\0" + struct.pack("!I", 0))
msg += f(b"S", b"")
c.sock.sendall(msg)
frames = c._until_ready()
param_desc = next(p for t, p in frames if t == b"t")
assert param_desc == struct.pack("!HI", 1, 25) # one text parameter
assert _WireResult(frames).rows == [("t",), ("f",)]
c.simple("DROP TABLE wfj_named")
@requires_docker
@pytest.mark.data_plane
class TestTransactionAbortSemantics:
"""A statement the proxy rejects must poison the transaction block the way
a real error does — otherwise the following statements still commit."""
def test_rejection_aborts_the_block(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE ab_t (id int)")
assert c.simple("BEGIN").ok
assert c.simple("INSERT INTO ab_t VALUES (1)").ok
assert c.simple("TRUNCATE ab_t").sqlstate == "0A000"
blocked = c.simple("INSERT INTO ab_t VALUES (2)")
assert blocked.sqlstate == "25P02"
assert blocked.txn_status == "E"
# Postgres reports ROLLBACK when a failed block is committed.
assert c.simple("COMMIT").tag == "ROLLBACK"
assert c.simple("SELECT count(*) FROM ab_t").rows == [("0",)]
def test_rollback_clears_the_abort(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE ab_r (id int)")
c.simple("BEGIN")
assert c.simple("CREATE TEMP TABLE nope (id int)").sqlstate == "0A000"
assert c.simple("ROLLBACK").ok
assert c.simple("INSERT INTO ab_r VALUES (1)").ok
assert c.simple("SELECT count(*) FROM ab_r").rows == [("1",)]
def test_abort_applies_to_the_extended_protocol_too(self, dsql_proxy):
with _WireClient(dsql_proxy) as c:
c.simple("CREATE TABLE ab_x (id int)")
c.simple("BEGIN")
assert c.extended("TRUNCATE ab_x").sqlstate == "0A000"
assert c.extended("INSERT INTO ab_x VALUES (1)").sqlstate == "25P02"
assert c.simple("COMMIT").tag == "ROLLBACK"
assert c.simple("SELECT count(*) FROM ab_x").rows == [("0",)]
_PROBE_ROLE_ARN = "arn:aws:iam::123456789012:role/ministack-probe"
_PROBE_USER_ARN = "arn:aws:iam::123456789012:user/ministack-probe"
_IN_TXN_ARN = "arn:aws:iam::123456789012:role/InTxn"
@requires_docker
@pytest.mark.data_plane
class TestAwsIamTrust:
"""IAM trust over the wire. Messages, SQLSTATEs, the transaction rules and
the shape of sys.iam_pg_role_mappings were measured on Aurora DSQL,
eu-west-1 2026-10-26."""
@pytest.fixture
def role(self, dsql_proxy):
name = f"iam_{secrets.token_hex(4)}"
conn = _pg_connect(dsql_proxy)
conn.cursor().execute(f"CREATE ROLE {name} WITH LOGIN")
yield name
cur = conn.cursor()
cur.execute(
f"AWS IAM REVOKE {name} FROM '{_PROBE_ROLE_ARN}', '{_PROBE_USER_ARN}', '{_IN_TXN_ARN}'"
)
cur.execute(f"DROP ROLE {name}")
conn.close()
@staticmethod
def _mappings(cur, role):
cur.execute(
"SELECT arn, pg_role_name, grantor_pg_role_name "
f"FROM sys.iam_pg_role_mappings WHERE pg_role_name = '{role}' ORDER BY arn"
)
return cur.fetchall()
def test_the_view_has_dsqls_columns_and_types(self, dsql_proxy):
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute(
"SELECT attname, format_type(atttypid, atttypmod), "
"(SELECT collname FROM pg_collation WHERE oid = attcollation) "
"FROM pg_attribute WHERE attrelid = 'sys.iam_pg_role_mappings'::regclass "
"AND attnum > 0 ORDER BY attnum"
)
assert cur.fetchall() == [
("iam_oid", "oid", None),
("arn", "text", "C"),
("pg_role_oid", "oid", None),
("pg_role_name", "name", "C"),
("grantor_pg_role_oid", "oid", None),
("grantor_pg_role_name", "name", "C"),
]
finally:
conn.close()
def test_grant_maps_the_arn_and_reports_the_grantor(self, dsql_proxy, role):
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute(f"AWS IAM GRANT {role} TO '{_PROBE_ROLE_ARN}'")
assert cur.statusmessage == "AWS IAM GRANT"
assert self._mappings(cur, role) == [(_PROBE_ROLE_ARN, role, "admin")]
cur.execute(
"SELECT m.pg_role_oid = r.oid, m.grantor_pg_role_oid = g.oid "
"FROM sys.iam_pg_role_mappings m JOIN pg_roles r ON r.rolname = m.pg_role_name "
f"JOIN pg_roles g ON g.rolname = 'admin' WHERE m.pg_role_name = '{role}'"
)
assert cur.fetchall() == [(True, True)]
finally:
conn.close()
def test_one_arn_keeps_one_iam_oid_across_roles(self, dsql_proxy, role):
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute(f"CREATE ROLE {role}_b")
cur.execute(f"AWS IAM GRANT {role} TO '{_PROBE_ROLE_ARN}'")
cur.execute(f"AWS IAM GRANT {role}_b TO '{_PROBE_ROLE_ARN}'")
cur.execute(
"SELECT count(DISTINCT iam_oid) FROM sys.iam_pg_role_mappings "
f"WHERE pg_role_name IN ('{role}', '{role}_b')"
)
assert cur.fetchone() == (1,)
cur.execute(
"SELECT count(*) FROM pg_roles r JOIN sys.iam_pg_role_mappings m "
f"ON r.oid = m.iam_oid WHERE m.pg_role_name = '{role}'"
)
assert cur.fetchone() == (0,)
cur.execute(f"AWS IAM REVOKE {role}_b FROM '{_PROBE_ROLE_ARN}'")
cur.execute(f"DROP ROLE {role}_b")
finally:
conn.close()
def test_regranting_raises_a_notice_and_grants_the_rest(self, dsql_proxy, role):
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute(f"AWS IAM GRANT {role} TO '{_PROBE_ROLE_ARN}'")
del conn.notices[:]
cur.execute(f"AWS IAM GRANT {role} TO '{_PROBE_ROLE_ARN}', '{_PROBE_USER_ARN}'")
assert cur.statusmessage == "AWS IAM GRANT"
assert conn.notices == [
f'NOTICE: IAM role "{_PROBE_ROLE_ARN}" has already been granted access '
f'to role "{role}" by role "admin"\n'
]
assert self._mappings(cur, role) == [
(_PROBE_ROLE_ARN, role, "admin"),
(_PROBE_USER_ARN, role, "admin"),
]
finally:
conn.close()
def test_an_unknown_role_is_refused(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
for sql in (
f"AWS IAM GRANT missing_role TO '{_PROBE_ROLE_ARN}'",
f"AWS IAM REVOKE missing_role FROM '{_PROBE_ROLE_ARN}'",
):
with pytest.raises(psycopg2.Error) as exc:
conn.cursor().execute(sql)
assert exc.value.pgcode == "42704"
assert 'role "missing_role" does not exist' in str(exc.value)
finally:
conn.close()
def test_the_signed_in_role_cannot_grant_itself(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
with pytest.raises(psycopg2.Error) as exc:
conn.cursor().execute(f"AWS IAM GRANT admin TO '{_PROBE_ROLE_ARN}'")
assert exc.value.pgcode == "42501"
assert exc.value.pgerror.startswith(
'ERROR: permission denied to grant IAM trust with role "admin"'
)
assert exc.value.diag.message_detail == (
'Only roles with the ADMIN option on role "admin" may grant IAM trust to this role.'
)
finally:
conn.close()
def test_rollback_undoes_a_grant(self, dsql_proxy, role):
conn = _pg_connect(dsql_proxy, autocommit=False)
try:
cur = conn.cursor()
cur.execute(f"AWS IAM GRANT {role} TO '{_IN_TXN_ARN}'")
assert self._mappings(cur, role) == [(_IN_TXN_ARN, role, "admin")]
conn.rollback()
assert self._mappings(cur, role) == []
finally:
conn.close()
def test_a_grant_after_other_ddl_in_a_transaction_is_refused(self, dsql_proxy, role):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy, autocommit=False)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE iam_after_ddl (id int PRIMARY KEY)")
with pytest.raises(psycopg2.Error) as exc:
cur.execute(f"AWS IAM GRANT {role} TO '{_IN_TXN_ARN}'")
assert exc.value.pgcode == "0A000"
assert "multiple ddl statements not supported in a transaction" in str(exc.value)
finally:
conn.rollback()
conn.close()
def test_a_grant_after_a_select_in_a_transaction_commits(self, dsql_proxy, role):
conn = _pg_connect(dsql_proxy, autocommit=False)
try:
cur = conn.cursor()
cur.execute("SELECT 1")
cur.execute(f"AWS IAM GRANT {role} TO '{_IN_TXN_ARN}'")
conn.commit()
assert self._mappings(cur, role) == [(_IN_TXN_ARN, role, "admin")]
finally:
conn.close()
def test_revoke_removes_the_mapping_and_warns_when_there_is_none(self, dsql_proxy, role):
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute(f"AWS IAM GRANT {role} TO '{_PROBE_ROLE_ARN}', '{_PROBE_USER_ARN}'")
cur.execute(f"AWS IAM REVOKE {role} FROM '{_PROBE_ROLE_ARN}'")
assert cur.statusmessage == "AWS IAM REVOKE"
assert self._mappings(cur, role) == [(_PROBE_USER_ARN, role, "admin")]
del conn.notices[:]
cur.execute(f"AWS IAM REVOKE {role} FROM '{_PROBE_ROLE_ARN}'")
assert cur.statusmessage == "AWS IAM REVOKE"
assert conn.notices == [
f'WARNING: IAM role "{_PROBE_ROLE_ARN}" has not been granted access '
f'to role "{role}"\n'
]
finally:
conn.close()
@pytest.mark.parametrize("failure", ["unreachable", "refused by the backend"])
def test_a_catalog_that_cannot_be_created_leaves_the_connection_usable(
self, dsql_proxy, pg_backend, role, monkeypatch, failure
):
psycopg2 = pytest.importorskip("psycopg2")
name = f"iam_db_{secrets.token_hex(4)}"
admin = psycopg2.connect(
host=pg_backend[0], port=pg_backend[1], user="postgres", dbname="postgres"
)
admin.autocommit = True
admin.cursor().execute(f"CREATE DATABASE {name}")
attempts = []
async def refused(conn):
attempts.append(conn.database)
if failure == "unreachable":
raise OSError("connection refused")
return "55P03 canceling statement due to lock timeout"
monkeypatch.setattr(pgproxy, "_create_iam_catalog", refused)
try:
conn = psycopg2.connect(
host="127.0.0.1", port=dsql_proxy, user="admin", password="x", dbname=name
)
conn.autocommit = True
try:
for _ in range(2):
with pytest.raises(psycopg2.Error) as exc:
conn.cursor().execute(f"AWS IAM GRANT {role} TO '{_PROBE_ROLE_ARN}'")
assert exc.value.pgcode == "42P01"
cur = conn.cursor()
cur.execute("SELECT 1")
assert cur.fetchone() == (1,)
assert attempts == [name, name]
finally:
conn.close()
finally:
admin.cursor().execute(f"DROP DATABASE {name} WITH (FORCE)")
admin.close()
def test_the_extended_protocol_is_refused(self, dsql_proxy, role):
with _WireClient(dsql_proxy) as c:
for sql in (
f"AWS IAM GRANT {role} TO '{_PROBE_ROLE_ARN}'",
f"AWS IAM GRANT admin TO '{_PROBE_ROLE_ARN}'",
):
result = c.extended(sql)
assert result.sqlstate == "0A000"
assert "simple query protocol" in result.message
assert c.extended(
"SELECT count(*) FROM sys.iam_pg_role_mappings WHERE pg_role_name = $1", [role]
).rows == [("0",)]
def test_quoting_holds_without_standard_conforming_strings(self, dsql_proxy, role):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("SET standard_conforming_strings = off")
with pytest.raises(psycopg2.Error) as exc:
cur.execute(
"AWS IAM GRANT \"x\\' AS oid) SELECT 1, 2 WHERE false; "
"CREATE TABLE iam_injected (id serial); --\" "
"TO 'arn:aws:iam::123456789012:role/x'"
)
assert exc.value.pgcode == "42704"
cur.execute("SET standard_conforming_strings = on")
cur.execute(
"SELECT count(*) FROM pg_tables WHERE tablename = 'iam_injected'"
)
assert cur.fetchone() == (0,)
cur.execute(f"AWS IAM GRANT {role} TO 'arn:aws:iam::123456789012:role/a\\b'")
assert [m[0] for m in self._mappings(cur, role)] == [
"arn:aws:iam::123456789012:role/a\\b"
]
cur.execute(f"AWS IAM REVOKE {role} FROM 'arn:aws:iam::123456789012:role/a\\b'")
finally:
conn.close()
def test_a_multi_statement_batch_is_refused(self, dsql_proxy, role):
with _WireClient(dsql_proxy) as c:
result = c.simple(f"SELECT 1; AWS IAM GRANT {role} TO '{_PROBE_ROLE_ARN}'")
assert result.sqlstate == "0A000"
assert "not in a batch" in result.message
assert c.simple(
f"SELECT count(*) FROM sys.iam_pg_role_mappings WHERE pg_role_name = '{role}'"
).rows == [("0",)]
def test_the_catalog_appears_only_in_a_database_that_uses_it(
self, dsql_proxy, pg_backend
):
psycopg2 = pytest.importorskip("psycopg2")
name = f"iam_db_{secrets.token_hex(4)}"
admin = psycopg2.connect(
host=pg_backend[0], port=pg_backend[1], user="postgres", dbname="postgres"
)
admin.autocommit = True
admin.cursor().execute(f"CREATE DATABASE {name}")
try:
main = _pg_connect(dsql_proxy)
main.cursor().execute("SELECT count(*) FROM sys.iam_pg_role_mappings")
main.close()
with _WireClient(dsql_proxy, database=name) as c:
result = c.extended(f"AWS IAM GRANT admin TO '{_PROBE_ROLE_ARN}'")
assert result.sqlstate == "0A000"
conn = psycopg2.connect(
host="127.0.0.1", port=dsql_proxy, user="admin", password="x", dbname=name
)
conn.autocommit = True
try:
cur = conn.cursor()
cur.execute("SELECT count(*) FROM pg_namespace WHERE nspname = 'sys'")
assert cur.fetchone() == (0,)
cur.execute("SELECT count(*) FROM sys.iam_pg_role_mappings")
assert cur.fetchone() == (0,)
finally:
conn.close()
finally:
admin.cursor().execute(f"DROP DATABASE {name} WITH (FORCE)")
admin.close()
@requires_docker
@pytest.mark.data_plane
class TestLiveProxy:
def test_create_insert_select_round_trip(self, dsql_proxy):
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute(
"CREATE TABLE rt_live (id int, name text, amt numeric(18,6), "
"flag boolean, ts timestamp with time zone, u uuid, j jsonb)"
)
cur.execute(
"INSERT INTO rt_live VALUES (1, 'alice', 12.5, true, now(), "
"'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', '{\"k\": 1}')"
)
cur.execute("SELECT id, name, flag FROM rt_live")
rows = cur.fetchall()
assert rows == [(1, "alice", True)]
cur.execute("DROP TABLE rt_live")
finally:
conn.close()
def test_create_index_async_returns_job(self, dsql_proxy):
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE idx_live (a int)")
cur.execute("INSERT INTO idx_live VALUES (1)")
cur.execute("CREATE INDEX ASYNC idx_live_a ON idx_live (a)")
job_id = cur.fetchone()[0]
assert re.match(r"^[a-z0-9]{26}$", job_id)
cur.execute("SELECT job_id, status, job_type, details FROM sys.jobs")
jobs = cur.fetchall()
# details is NULL for a job that succeeded (as on real DSQL).
assert (job_id, "completed", "INDEX_BUILD", None) in jobs
finally:
conn.close()
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute(
f"SELECT job_id, status FROM sys.jobs WHERE job_id = '{job_id}'"
)
assert cur.fetchall() == [(job_id, "completed")]
cur.execute(f"SELECT sys.wait_for_job(job_id) '{job_id}'")
assert cur.fetchone()[0] is True
cur.execute("SELECT sys.wait_for_job(job_id) 'nonexistent'")
assert cur.fetchone()[0] is False
# The rewritten (sync) index really exists on the backend.
cur.execute(
"SELECT indexname FROM pg_indexes WHERE tablename = 'idx_live'"
)
assert "idx_live_a" in {r[0] for r in cur.fetchall()}
cur.execute("DROP TABLE idx_live")
finally:
conn.close()
def test_plain_create_index_rejected_even_on_empty_table(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE ci_live (a int)")
with pytest.raises(psycopg2.Error) as exc:
cur.execute("CREATE INDEX ci_live_a ON ci_live (a)")
assert exc.value.pgcode == "0A000"
# exact message from real DSQL (verified in eu-west-2, Aug 2026)
assert "unsupported mode. please use CREATE INDEX ASYNC." in str(exc.value)
cur.execute("DROP TABLE ci_live")
finally:
conn.close()
def test_serial_column_rejected(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
with pytest.raises(psycopg2.Error) as exc:
cur.execute("CREATE TABLE ser_live (id serial)")
assert exc.value.pgcode == "0A000"
assert '"serial"' in str(exc.value)
finally:
conn.close()
def test_create_extension_rejected(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
with pytest.raises(psycopg2.Error) as exc:
cur.execute("CREATE EXTENSION pgcrypto")
assert exc.value.pgcode == "0A000"
assert "is not supported" in str(exc.value)
finally:
conn.close()
def test_foreign_key_enforced(self, dsql_proxy):
"""Foreign keys, supported since 2026-08-26, reach the backend and
enforce referential integrity."""
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE fk_parent (id int PRIMARY KEY, name text)")
cur.execute(
"CREATE TABLE fk_child (id int PRIMARY KEY, "
"p int REFERENCES fk_parent ON DELETE CASCADE)"
)
cur.execute("INSERT INTO fk_parent VALUES (1, 'a')")
cur.execute("INSERT INTO fk_child VALUES (10, 1)")
with pytest.raises(psycopg2.Error) as exc:
cur.execute("INSERT INTO fk_child VALUES (11, 99)")
assert exc.value.pgcode == "23503"
# ON DELETE CASCADE removes the referencing row with the parent.
cur.execute("DELETE FROM fk_parent WHERE id = 1")
cur.execute("SELECT count(*) FROM fk_child")
assert cur.fetchone()[0] == 0
cur.execute("DROP TABLE fk_child")
cur.execute("DROP TABLE fk_parent")
finally:
conn.close()
def test_foreign_key_via_alter_table_requires_not_valid(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE fka_parent (id int PRIMARY KEY)")
cur.execute("CREATE TABLE fka_child (id int PRIMARY KEY, p int)")
cur.execute("INSERT INTO fka_parent VALUES (1)")
cur.execute("INSERT INTO fka_child VALUES (10, 1)")
with pytest.raises(psycopg2.Error) as exc:
cur.execute(
"ALTER TABLE fka_child ADD CONSTRAINT fk "
"FOREIGN KEY (p) REFERENCES fka_parent"
)
assert exc.value.pgcode == "0A000"
assert "unsupported ALTER TABLE ADD CONSTRAINT" in str(exc.value)
cur.execute(
"ALTER TABLE fka_child ADD CONSTRAINT fk "
"FOREIGN KEY (p) REFERENCES fka_parent NOT VALID"
)
# Async validation of a NOT VALID foreign key registers a job.
cur.execute("ALTER TABLE ASYNC fka_child VALIDATE CONSTRAINT fk")
job_id = cur.fetchone()[0]
cur.execute(
"SELECT job_type, status FROM sys.jobs WHERE job_id = %s" % repr(job_id)
)
assert cur.fetchone() == ("VALIDATE_CONSTRAINT", "completed")
with pytest.raises(psycopg2.Error) as exc:
cur.execute("INSERT INTO fka_child VALUES (11, 99)")
assert exc.value.pgcode == "23503"
cur.execute("DROP TABLE fka_child")
cur.execute("DROP TABLE fka_parent")
finally:
conn.close()
def test_index_form_rejections(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE form_live (a int, data jsonb)")
for sql, sqlstate, fragment in [
("CREATE INDEX fi1 ON form_live USING gin (data)", "0A000",
"USING not supported"),
("CREATE INDEX fi2 ON form_live (a) WHERE a > 0", "0A000",
"unsupported mode"),
("CREATE INDEX ASYNC fi5 ON form_live (a) WHERE now() > now()",
"42P17", "index predicate"),
("CREATE INDEX fi3 ON form_live (now())", "0A000",
"unsupported mode"),
("CREATE INDEX ASYNC fi4 ON form_live (now())", "42P17",
"IMMUTABLE"),
("CREATE MATERIALIZED VIEW mv_live AS SELECT 1", "0A000",
"materialized"),
("CREATE TABLE part_live (a int) PARTITION BY RANGE (a)", "0A000",
"partition"),
]:
with pytest.raises(psycopg2.Error) as exc:
cur.execute(sql)
assert exc.value.pgcode == sqlstate, sql
assert fragment in str(exc.value), sql
cur.execute("DROP TABLE form_live")
finally:
conn.close()
def test_expression_index_accepted(self, dsql_proxy):
"""Expression index keys with immutable functions are supported."""
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE expr_live (id int, title text)")
cur.execute("CREATE INDEX ASYNC ei_live ON expr_live (lower(title))")
job_id = cur.fetchone()[0]
cur.execute(f"SELECT sys.wait_for_job('{job_id}')")
assert cur.fetchone()[0] is True
cur.execute("DROP TABLE expr_live")
finally:
conn.close()
def test_partial_index_built(self, dsql_proxy):
"""Partial indexes (AWS DSQL, 2026-09-15) are built with their predicate."""
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE pi_live (id int PRIMARY KEY, rating int, title text)")
cur.execute(
"CREATE INDEX ASYNC pi_live_high ON pi_live (title) WHERE rating > 5"
)
job_id = cur.fetchone()[0]
cur.execute(f"SELECT sys.wait_for_job('{job_id}')")
assert cur.fetchone()[0] is True
cur.execute(
"SELECT pg_get_expr(indpred, indrelid) FROM pg_index "
"WHERE indexrelid = 'pi_live_high'::regclass"
)
assert cur.fetchone()[0] == "(rating > 5)"
cur.execute("DROP TABLE pi_live")
finally:
conn.close()
def test_unique_partial_index_scopes_uniqueness(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute(
"CREATE TABLE upi_live (id int PRIMARY KEY, email text, archived bool)"
)
cur.execute(
"CREATE UNIQUE INDEX ASYNC upi_live_email ON upi_live (email) "
"WHERE NOT archived"
)
job_id = cur.fetchone()[0]
cur.execute(f"SELECT sys.wait_for_job('{job_id}')")
assert cur.fetchone()[0] is True
# Duplicates are fine among archived rows...
cur.execute(
"INSERT INTO upi_live VALUES (1, 'a@x', true), (2, 'a@x', true), "
"(3, 'a@x', false)"
)
# ...but not among the rows the predicate covers.
with pytest.raises(psycopg2.Error) as exc:
cur.execute("INSERT INTO upi_live VALUES (4, 'a@x', false)")
assert exc.value.pgcode == "23505"
cur.execute("DROP TABLE upi_live")
finally:
conn.close()
def test_volatile_predicate_fails_the_submit(self, dsql_proxy):
"""A bad predicate is refused up front, not handed back as a failed job."""
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE vp_live (id int, created timestamptz)")
cur.execute("SELECT job_id FROM sys.jobs")
jobs_before = len(cur.fetchall())
with pytest.raises(psycopg2.Error) as exc:
cur.execute(
"CREATE INDEX ASYNC vp_live_i ON vp_live (id) WHERE created > now()"
)
assert exc.value.pgcode == "42P17"
assert "index predicate must be marked IMMUTABLE" in str(exc.value)
cur.execute("SELECT job_id FROM sys.jobs")
assert len(cur.fetchall()) == jobs_before
cur.execute("DROP TABLE vp_live")
finally:
conn.close()
def test_batched_nestloop_setting(self, dsql_proxy):
"""dsql.enable_batched_nestloop (AWS DSQL, 2026-09-11) defaults to on."""
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("SHOW dsql.enable_batched_nestloop")
assert cur.fetchone()[0] == "on"
cur.execute("SET dsql.enable_batched_nestloop = off")
cur.execute("SHOW dsql.enable_batched_nestloop")
assert cur.fetchone()[0] == "off"
cur.execute("RESET dsql.enable_batched_nestloop")
cur.execute("SHOW dsql.enable_batched_nestloop")
assert cur.fetchone()[0] == "on"
with pytest.raises(psycopg2.Error) as exc:
cur.execute("SET dsql.enable_batched_nestloop = banana")
assert exc.value.pgcode == "22023"
with pytest.raises(psycopg2.Error) as exc:
cur.execute("SET dsql.bogus_setting = 1")
assert exc.value.pgcode == "42602"
assert exc.value.diag.message_detail == '"dsql" is a reserved prefix.'
with pytest.raises(psycopg2.Error) as exc:
cur.execute("SHOW dsql.bogus_setting")
assert exc.value.pgcode == "42704"
finally:
conn.close()
def test_max_5_statistics_per_table(self, dsql_proxy):
"""Extended statistics (AWS DSQL, 2026-08-27): at most 5 per table."""
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE st_live (a int, b int, c int)")
for i in range(5):
cur.execute(f"CREATE STATISTICS st_live_{i} ON a, b FROM st_live")
with pytest.raises(psycopg2.Error) as exc:
cur.execute("CREATE STATISTICS st_live_5 ON a, c FROM st_live")
assert exc.value.pgcode == "54000"
assert "more than 5 extended statistics per table are not allowed" in str(
exc.value
)
with pytest.raises(psycopg2.Error):
cur.execute("CREATE STATISTICS ON (a + b) FROM st_live")
# IF NOT EXISTS on an existing name is a no-op, not a sixth object;
# on a new name it is refused like any other.
cur.execute(
"CREATE STATISTICS IF NOT EXISTS st_live_0 ON a, b FROM st_live"
)
with pytest.raises(psycopg2.Error) as exc:
cur.execute(
"CREATE STATISTICS IF NOT EXISTS st_live_9 ON a, b FROM st_live"
)
assert exc.value.pgcode == "54000"
# The statement's own errors come before the limit, as on DSQL.
with pytest.raises(psycopg2.Error) as exc:
cur.execute("CREATE STATISTICS st_live_x ON a FROM st_live")
assert exc.value.pgcode == "42P17"
assert "at least 2 columns" in str(exc.value)
# The dry run leaves nothing behind.
cur.execute(
"SELECT count(*) FROM pg_statistic_ext "
"WHERE stxrelid = 'st_live'::regclass"
)
assert cur.fetchone()[0] == 5
# The limit holds inside a transaction too, and aborts it.
cur.execute("BEGIN")
with pytest.raises(psycopg2.Error) as exc:
cur.execute("CREATE STATISTICS st_live_t ON a, c FROM st_live")
assert exc.value.pgcode == "54000"
with pytest.raises(psycopg2.Error) as exc:
cur.execute("SELECT 1")
assert exc.value.pgcode == "25P02"
cur.execute("ROLLBACK")
# ALTER and DROP pass through; dropping one frees a slot.
cur.execute("ALTER STATISTICS st_live_0 SET STATISTICS 100")
with pytest.raises(psycopg2.Error) as exc:
cur.execute("ALTER STATISTICS st_live_0 SET STATISTICS 200")
assert exc.value.pgcode == "22023"
cur.execute("DROP STATISTICS st_live_0")
cur.execute("CREATE STATISTICS st_live_5 (mcv) ON a, c FROM st_live")
cur.execute("DROP TABLE st_live")
finally:
conn.close()
def test_numeric_precision_and_scale_limits(self, dsql_proxy):
"""numeric takes precision up to 1000, scale -1000..1000 (2026-08-25)."""
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute(
"CREATE TABLE num_live (id int PRIMARY KEY, "
"big numeric(1000, 500), neg numeric(1000, -1000))"
)
cur.execute("INSERT INTO num_live VALUES (1, 1.5, 1234)")
cur.execute("SELECT neg FROM num_live")
assert cur.fetchone()[0] == 0 # rounded to the 10^1000 place
cur.execute("DROP TABLE num_live")
finally:
conn.close()
def test_alter_column_type_rejected(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE alt_live (a int)")
with pytest.raises(psycopg2.Error) as exc:
cur.execute("ALTER TABLE alt_live ALTER COLUMN a TYPE text")
assert exc.value.pgcode == "0A000"
assert "ALTER COLUMN TYPE" in str(exc.value)
cur.execute("DROP TABLE alt_live")
finally:
conn.close()
def test_check_constraint_not_valid_and_async_validate(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE chk_live (a int)")
cur.execute("INSERT INTO chk_live VALUES (1)")
# CHECK via ALTER TABLE requires NOT VALID
with pytest.raises(psycopg2.Error) as exc:
cur.execute(
"ALTER TABLE chk_live ADD CONSTRAINT chk_a CHECK (a > 0)"
)
assert exc.value.pgcode == "0A000"
assert "unsupported ALTER TABLE ADD CONSTRAINT" in str(exc.value)
cur.execute(
"ALTER TABLE chk_live ADD CONSTRAINT chk_a CHECK (a > 0) NOT VALID"
)
# Async validation returns a job_id and registers a job
cur.execute("ALTER TABLE ASYNC chk_live VALIDATE CONSTRAINT chk_a")
job_id = cur.fetchone()[0]
cur.execute(
"SELECT job_type, status FROM sys.jobs WHERE job_id = %s" % repr(job_id)
)
assert cur.fetchone() == ("VALIDATE_CONSTRAINT", "completed")
# Backend actually validated: violating rows are now rejected
with pytest.raises(psycopg2.Error):
cur.execute("INSERT INTO chk_live VALUES (-1)")
cur.execute("DROP TABLE chk_live")
finally:
conn.close()
def test_validate_constraint_over_violating_rows_fails_the_job(self, dsql_proxy):
"""Existing data that breaks the constraint fails the job, not the
submit: DSQL answers with a job_id and reports it through sys.jobs
(measured, eu-central-1 2026-08-30)."""
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE vj_parent (id int PRIMARY KEY)")
cur.execute("CREATE TABLE vj_child (id int PRIMARY KEY, p int)")
cur.execute("INSERT INTO vj_parent VALUES (1)")
cur.execute("INSERT INTO vj_child VALUES (1, 999)")
cur.execute(
"ALTER TABLE vj_child ADD CONSTRAINT vj_fk FOREIGN KEY (p) "
"REFERENCES vj_parent NOT VALID"
)
cur.execute("ALTER TABLE ASYNC vj_child VALIDATE CONSTRAINT vj_fk")
job_id = cur.fetchone()[0]
cur.execute(
"SELECT status, details FROM sys.jobs WHERE job_id = %s" % repr(job_id)
)
status, details = cur.fetchone()
assert status == "failed"
assert "violates foreign key constraint" in details
cur.execute(f"SELECT sys.wait_for_job('{job_id}')")
assert cur.fetchone()[0] is False
# A NOT VALID constraint still holds against new rows.
with pytest.raises(psycopg2.Error) as exc:
cur.execute("INSERT INTO vj_child VALUES (2, 998)")
assert exc.value.pgcode == "23503"
cur.execute("DROP TABLE vj_child")
cur.execute("DROP TABLE vj_parent")
finally:
conn.close()
def test_max_24_indexes_per_table(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE idx_live (a int)")
for i in range(24):
cur.execute(f"CREATE INDEX ASYNC idx_live_{i} ON idx_live (a)")
cur.fetchone() # consume the job_id row
with pytest.raises(psycopg2.Error) as exc:
cur.execute("CREATE INDEX ASYNC idx_live_24 ON idx_live (a)")
assert exc.value.pgcode == "0A000"
assert "24 indexes" in str(exc.value)
cur.execute("DROP TABLE idx_live")
finally:
conn.close()
def test_oc001_stale_catalog(self, dsql_proxy):
"""A txn that outlives another session's DDL gets 40001 OC001 once."""
psycopg2 = pytest.importorskip("psycopg2")
stale = _pg_connect(dsql_proxy, autocommit=False)
other = _pg_connect(dsql_proxy)
try:
stale.cursor().execute("SELECT 1") # opens txn, caches catalog
other.cursor().execute("CREATE TABLE oc_live (a int)") # bumps it
with pytest.raises(psycopg2.Error) as exc:
stale.cursor().execute("SELECT 1")
assert exc.value.pgcode == "40001"
assert "OC001" in str(exc.value)
# Retry on the same session sees the fresh catalog.
stale.cursor().execute("SELECT 1")
other.cursor().execute("DROP TABLE oc_live")
finally:
stale.close()
other.close()
def test_locking_clause_rules(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE fu_live (id int primary key, v text)")
cur.execute("INSERT INTO fu_live VALUES (1, 'x')")
# FOR UPDATE locks whatever the query selects, as on DSQL.
for sql in (
"SELECT * FROM fu_live WHERE id = 1 FOR UPDATE",
'SELECT * FROM "fu_live" WHERE "fu_live"."id" = 1 FOR UPDATE',
"SELECT * FROM fu_live FOR UPDATE",
"SELECT * FROM fu_live WHERE v = 'x' FOR UPDATE",
"SELECT * FROM fu_live a JOIN fu_live b ON a.id = b.id FOR UPDATE",
):
cur.execute(sql)
cur.fetchall()
# Any other lock strength is not supported.
cur.execute("SELECT * FROM fu_live WHERE id = 1 FOR KEY SHARE")
cur.fetchall()
for sql in (
"SELECT * FROM fu_live WHERE id = 1 FOR SHARE",
"SELECT * FROM fu_live WHERE id = 1 FOR NO KEY UPDATE",
):
with pytest.raises(psycopg2.Error) as exc:
cur.execute(sql)
assert exc.value.pgcode == "0A000", sql
assert "locking clauses other than FOR UPDATE" in str(exc.value), sql
cur.execute("DROP TABLE fu_live")
finally:
conn.close()
def test_row_limit_enforced(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE rl_live (a int)")
big = "INSERT INTO rl_live VALUES " + ",".join(
f"({i})" for i in range(3001)
)
with pytest.raises(psycopg2.Error) as exc:
cur.execute(big)
assert exc.value.pgcode == "25006"
assert "3,000" in str(exc.value)
cur.execute("SELECT count(*) FROM rl_live")
assert cur.fetchone()[0] == 0
cur.execute("DROP TABLE rl_live")
finally:
conn.close()
def test_mixed_ddl_dml_in_txn_rejected(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy, autocommit=False)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE tx_live (a int)") # psycopg2 sends BEGIN
with pytest.raises(psycopg2.Error) as exc:
cur.execute("INSERT INTO tx_live VALUES (1)")
assert exc.value.pgcode == "25006"
assert "cannot be mixed" in str(exc.value)
conn.rollback()
finally:
conn.close()
# Rollback discarded the CREATE TABLE (PG transactional DDL) — the
# DROP is just a safety net if the backend behaved differently.
conn = _pg_connect(dsql_proxy)
try:
conn.cursor().execute("DROP TABLE IF EXISTS tx_live")
finally:
conn.close()
def test_two_ddl_in_txn_rejected(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy, autocommit=False)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE dd1_live (a int)")
with pytest.raises(psycopg2.Error) as exc:
cur.execute("CREATE TABLE dd2_live (a int)")
assert exc.value.pgcode == "25006"
assert "only one DDL" in str(exc.value)
conn.rollback()
finally:
conn.close()
conn = _pg_connect(dsql_proxy)
try:
conn.cursor().execute("DROP TABLE IF EXISTS dd1_live")
finally:
conn.close()
def test_autocommit_sequential_ddl_fine(self, dsql_proxy):
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
cur.execute("CREATE TABLE ac1_live (a int)")
cur.execute("CREATE TABLE ac2_live (a int)")
cur.execute("DROP TABLE ac1_live")
cur.execute("DROP TABLE ac2_live")
finally:
conn.close()
def test_multi_statement_batch_txn_rules(self, dsql_proxy):
psycopg2 = pytest.importorskip("psycopg2")
conn = _pg_connect(dsql_proxy)
try:
cur = conn.cursor()
with pytest.raises(psycopg2.Error) as exc:
cur.execute(
"CREATE TABLE ba1_live (a int); CREATE TABLE ba2_live (a int)"
)
assert exc.value.pgcode == "25006"
# Nothing in the batch was forwarded.
cur.execute(
"SELECT tablename FROM pg_tables WHERE tablename LIKE 'ba%_live'"
)
assert cur.fetchall() == []
finally:
conn.close()
# ---------------------------------------------------------------------------
# Container lifecycle smoke test (needs a Docker daemon)
# ---------------------------------------------------------------------------
@requires_docker
@pytest.mark.data_plane
@pytest.mark.skipif(
os.environ.get("DSQL_STRICT", "0").lower() not in ("1", "true", "yes"),
reason="the server needs DSQL_STRICT=1 to spawn the backend behind the endpoint",
)
def test_cluster_data_plane_end_to_end(dsql, pg_image):
"""CreateCluster -> poll ACTIVE -> psycopg2 through the endpoint."""
psycopg2 = pytest.importorskip("psycopg2")
resp = dsql.create_cluster()
identifier = resp["identifier"]
try:
deadline = time.time() + 180
cluster = None
while time.time() < deadline:
cluster = dsql.get_cluster(identifier=identifier)
if cluster["status"] == "ACTIVE":
break
time.sleep(2)
else:
pytest.fail(f"cluster {identifier} not ACTIVE after 180s")
port = int(cluster["endpoint"].rsplit(":", 1)[1])
deadline = time.time() + 60
while True:
try:
conn = psycopg2.connect(
host="127.0.0.1",
port=port,
user="admin",
password="anything",
dbname="postgres",
connect_timeout=5,
)
break
except psycopg2.OperationalError:
if time.time() > deadline:
raise
time.sleep(1)
try:
conn.autocommit = True
cur = conn.cursor()
cur.execute("CREATE TABLE e2e (id int, name text)")
cur.execute("INSERT INTO e2e VALUES (1, 'x')")
cur.execute("SELECT name FROM e2e WHERE id = 1")
assert cur.fetchone()[0] == "x"
cur.execute("DROP TABLE e2e")
finally:
conn.close()
finally:
_cleanup(dsql, identifier)