Skip to content

Aurora DSQL

Protocol: REST JSON (control plane) + PostgreSQL wire protocol over TLS (clusters)

Endpoint: http://localhost:4566 (control plane), <cluster-id>.dsql.<region>.localhost.floci.io:5432 (clusters)

Floci runs each Aurora DSQL cluster as a real PostgreSQL 16 database in a Docker container, behind one endpoint that admits clients the way DSQL does: TLS and an IAM token as the password. The answers, messages and SQLSTATEs below were measured on AWS.

Supported Actions

Action Description
CreateCluster Creates a cluster, CREATING until its database answers, then ACTIVE, or PENDING_SETUP for a multi-Region one; deletion protection is on unless turned off
ListClusters Lists the Region's clusters, paginated
GetCluster Returns a cluster, its endpoint and encryption details
UpdateCluster Changes deletion protection, the KMS key and the multi-Region properties
DeleteCluster Deletes a cluster and its database, DELETING in the answer or PENDING_DELETE for a multi-Region one; refused while deletion protection is on
GetVpcEndpointServiceName Returns the Region's VPC endpoint service name
PutClusterPolicy Sets the cluster's resource policy once it passes DSQL's checks, with optimistic versioning
GetClusterPolicy Returns the resource policy in DSQL's form, and its version
DeleteClusterPolicy Removes the resource policy, answering the version removed

TagResource, UntagResource and ListTagsForResource work on cluster ARNs. As on AWS, an ARN that is not a well-formed cluster ARN is refused with AccessDeniedException, one of another Region with BadRequestException, and one of no cluster with NotFoundException. Cluster identifiers are 26 characters of lowercase base32, as DSQL's are, and invalid ones are refused with DSQL's messages.

A cluster created with a witness Region is PENDING_SETUP, and lists itself among its clusters. Once it and each peer it is updated with name one another, all of them are ACTIVE, as on AWS, where this takes about two minutes; a cluster whose database has not answered yet is CREATING until it does. Each has a database of its own; nothing is replicated between them. A customer-managed KMS key is recorded and reported; nothing is encrypted with it.

Connecting

A cluster's endpoint is <cluster-id>.dsql.<region>.localhost.floci.io, port 5432. localhost.floci.io resolves to 127.0.0.1 on the host, and to Floci inside the containers Floci starts, so the same endpoint works from both. All clusters share the port: the TLS server name a client sends picks the cluster, as DNS does on AWS.

TLS is mandatory. The certificate covers *.dsql.<region>.localhost.floci.io for every Region and is signed by Floci's local CA (GET /_floci/ca.pem, see TLS), which the containers Floci starts already trust, so sslmode=verify-full works.

The password is an IAM authentication token for the endpoint, generated by the SDKs and the CLI exactly as for AWS:

curl -s http://localhost:4566/_floci/ca.pem -o floci-root-ca.pem
export PGPASSWORD=$(AWS_ACCESS_KEY_ID=test AWS_SECRET_ACCESS_KEY=test \
  aws dsql generate-db-connect-admin-auth-token --region us-east-1 \
  --hostname <cluster-id>.dsql.us-east-1.localhost.floci.io)
psql "host=<cluster-id>.dsql.us-east-1.localhost.floci.io port=5432 user=admin dbname=postgres \
  sslmode=verify-full sslrootcert=floci-root-ca.pem"

When Floci runs in Docker, publish 5432 alongside 4566.

Authentication

  • admin signs in with a DbConnectAdmin token; every other role with a DbConnect token. The wrong action is refused with the hint Wrong user to action mapping. user: <user>, action: <action>.
  • A token is accepted up to 5 seconds past its expiry, as DSQL accepts it; after that the hint is Signature expired: ....
  • A role other than admin signs in only for an IAM role or user that AWS IAM GRANT mapped to it. The token's principal is the identity behind its credentials (a Lambda function's execution role, an ECS task's role with FLOCI_SERVICES_ECS_TASK_ROLE_CREDENTIALS_ENABLED, an AssumeRole session, an IAM user's key), its ARN with its path.
  • The signature is verified like any other Floci IAM token: the test key, or a key or session Floci issued. With FLOCI_SERVICES_IAM_ENFORCEMENT_ENABLED, the principal also needs dsql:DbConnectAdmin or dsql:DbConnect on the cluster ARN.
  • Refusals are FATAL with DSQL's text and a Session Id detail: 08P01 without TLS, 08006 for a token refused, 28000 for a role the token cannot sign in as, 55000 for any database other than postgres, and 42704 for a custom (dotted) parameter in the startup.
  • options is read as DSQL reads it: only -c name=value counts, the first one for a name wins, and every other word, --name=value included, is ignored.

SQL

The database is PostgreSQL 16 at REPEATABLE READ, as DSQL's is, with the role admin, which owns the public schema and can create roles and databases. A connection closes after an hour, as DSQL closes it.

  • AWS IAM GRANT <role> TO '<arn>' and AWS IAM REVOKE <role> FROM '<arn>', for an IAM role or user, with sys.iam_pg_role_mappings. A superuser role cannot be mapped, only a role holding the ADMIN option on a role can map it (admin maps the roles it created), and a mapped role cannot be dropped until its mappings are revoked.
  • sys.iam_session_user() answers the connection's IAM principal and sys.current_session_id() its session id, as on DSQL.
  • dsql is a reserved prefix, as on DSQL: SET, RESET, set_config, ALTER ROLE ... SET and a function's SET clause of a dsql. parameter fail with 42602 and "dsql" is a reserved prefix., and SHOW of one with 42704. set_config of another dotted parameter is 0A000, though SET of one works.
  • Errors carry no source location, as DSQL's carry none, and no line of Floci's sys functions in their context; notices keep their location.

Differences from AWS

  • The rest of DSQL's SQL dialect is not applied yet: CREATE INDEX ASYNC and its jobs are refused as PostgreSQL refuses them, what DSQL refuses works, and DSQL's transaction limits (one DDL statement, 3,000 rows, 300 seconds) do not apply.
  • sys also holds the tables behind Floci's IAM mappings.
  • Floci checks the statements a client sends, not what a DO block or a function body runs. In one, SET, RESET and set_config of a dsql. parameter set it where DSQL refuses it, since only a PostgreSQL extension written in C can reserve a prefix, and so does set_config of a name built at run time, as in set_config(chr(100) || 'sql.x', ...). Nothing Floci reads lives in such a parameter. Likewise a DROP ROLE in one drops a mapped role: sys.iam_pg_role_mappings no longer lists its mappings, and the next AWS IAM GRANT or REVOKE removes them.
  • DSQL is optimistic: conflicting transactions both run and the second to commit fails with 40001. Floci's database is PostgreSQL, so a write that conflicts waits for the other transaction, then fails with PostgreSQL's 40001.
  • DSQL accepts connections while a cluster is still CREATING; Floci's cluster is CREATING only while its database starts, and serves once its database answers.

Cluster policies

PutClusterPolicy, and CreateCluster with a policy, check the document as DSQL does and refuse it with DSQL's messages:

  • JSON that does not parse, and a statement with no Principal or Resource, or with NotPrincipal or NotResource;
  • a document missing or misusing an element: no Statement, a Version other than 2008-10-17 or 2012-10-17, a member of no policy grammar, an Effect other than Allow or Deny, an unknown condition operator (operators are matched as written), and a principal that names no one: an IAM role or user is looked up in its account, and an account is always taken to exist;
  • a service principal;
  • an Allow to everyone ("*" or an AWS "*") that no condition restricts. A condition restricts with one of the trusted keys of DSQL's block public access page, or aws:SourceIp, aws:SourceVpcArn, aws:VpceAccount, aws:VpceOrgID or aws:VpceOrgPaths, which DSQL also accepts: under a string equality, or a pattern without a wildcard (for an ARN, without one in the account), and never with a policy variable; with an aws:SourceIp prefix of at least /8 for IPv4 or /32 for IPv6; or with aws:PrincipalIsAWSService true. IfExists, ForAllValues: and negated operators never restrict;
  • a policy that explicitly denies the caller both dsql:PutClusterPolicy and dsql:DeleteClusterPolicy, unless bypassPolicyLockoutSafetyCheck is true.

The policy is kept and returned in DSQL's form: pretty-printed, its elements in DSQL's order, a list of one value written as that value, condition values as text, an account id as its root ARN, and 2008-10-17 when it has no Version. A policy's version is the time it was set, in epoch milliseconds. Cluster policies are not enforced: Floci's IAM enforcement checks a connection's identity policies (see Authentication), and no control plane call.

CloudFormation

AWS::DSQL::Cluster waits for ACTIVE, or PENDING_SETUP for a multi-Region cluster. Ref returns the identifier; Fn::GetAtt supports Identifier, ResourceArn, Status, CreationTime, Endpoint, VpcEndpoint, VpcEndpointServiceName and, while the cluster has a policy, PolicyVersion. Properties are checked against the resource schema's types, as on AWS (a PolicyDocument is text). An update leaves a property the template no longer sets as it was, deletion protection included, but removes the tags and the policy it no longer sets; a policy the template does not change keeps its version. A protected cluster makes the stack's deletion fail, as on AWS.

Configuration

Variable Default Description
FLOCI_SERVICES_DSQL_ENABLED true Enable or disable Aurora DSQL
FLOCI_SERVICES_DSQL_MOCK false Clusters without a database, ACTIVE at once and without Docker: enough for the control plane and CloudFormation
FLOCI_SERVICES_DSQL_PORT 5432 Port every cluster endpoint is served on
FLOCI_SERVICES_DSQL_ENDPOINT_SUFFIX localhost.floci.io Domain the endpoints are named under, <cluster-id>.dsql.<region>.<suffix>
FLOCI_SERVICES_DSQL_IMAGE postgres:16-alpine Image each cluster's database runs in
FLOCI_SERVICES_DSQL_DOCKER_NETWORK Floci's network Docker network for the databases

A cluster's data lives in a Docker volume of its own, or under the host persistent path, and is removed with the cluster under the same policy as RDS's (prune-volumes-on-delete). With persistent storage a cluster comes back, with its data, when Floci restarts.

See the Aurora DSQL API Reference and the Aurora DSQL User Guide.