Light, fluffy, and always free - The AWS Local Emulator alternative
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, an IAM token as the password, and DSQL's dialect and limits. 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. A connection that sends no server name is
refused once its password is in, with FATAL 08P01 unable to accept connection, sni was not received, as DSQL refuses it.
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. A client may also start TLS
at once, with no SSLRequest first (sslnegotiation=direct in libpq, sslNegotiation=direct in
pgjdbc), offering the ALPN protocol postgresql as those clients do; a direct handshake that does
not offer it ends the connection without a message.
The limits are DSQL's: a startup packet of 10,000 bytes or more, sent over TLS, ends the connection
with FATAL 08P01 invalid length of startup packet, and a client message over 10 MiB with
FATAL 08P01 invalid message length, as soon as its length arrives. A startup packet sent before
TLS is refused with FATAL 08P01 unable to accept connection, SSL is mandatory. AWS Authentication is required., whatever its length.
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
adminsigns in with aDbConnectAdmintoken; every other role with aDbConnecttoken. The wrong action is refused with the hintWrong 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
adminsigns in only for an IAM role or user thatAWS IAM GRANTmapped to it. The token's principal is the identity behind its credentials (a Lambda function's execution role, an ECS task's role withFLOCI_SERVICES_ECS_TASK_ROLE_CREDENTIALS_ENABLED, anAssumeRolesession, an IAM user's key), its ARN with its path. - The signature is verified like any other Floci IAM token: the
testkey, or a key or session Floci issued. WithFLOCI_SERVICES_IAM_ENFORCEMENT_ENABLED, the principal also needsdsql:DbConnectAdminordsql:DbConnecton the cluster ARN, and the cluster's resource-based policy is evaluated as DSQL evaluates it on connect, with the client's address asaws:SourceIp. An explicitDenyin it refuses the connection with the hintUser: <caller> is not authorized to perform: dsql:<action> on resource: <cluster ARN> with an explicit deny in a resource-based policy. - Refusals are
FATALwith DSQL's text and aSession Iddetail:08P01without TLS,08006for a token refused,28000for a role the token cannot sign in as,55000for any database other thanpostgres, and42704for a custom (dotted) parameter in the startup. optionsis read as DSQL reads it: only-c name=valuecounts, the first one for a name wins, and every other word,--name=valueincluded, 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, and DSQL's dialect installed in it:
CREATE [UNIQUE] INDEX ASYNCanswers ajob_id;sys.jobsandsys.wait_for_jobreport the job. A unique index over duplicate keys fails its job withfound duplicate key(s) while validating index uniquenessand stays, invalid, as on DSQL. A plainCREATE INDEXis refused.AWS IAM GRANT <role> TO '<arn>'andAWS IAM REVOKE <role> FROM '<arn>', for an IAM role or user, withsys.iam_pg_role_mappings. A superuser role cannot be mapped, only a role holding theADMINoption on a role can map it (adminmaps the roles it created), and a mapped role cannot be dropped until its mappings are revoked.- One DDL statement per transaction, statements on roles (
CREATE,ALTERandDROP ROLE, andGRANTorREVOKEof a role) included, no DDL with DML, and at most 3,000 modified rows per transaction. - A transaction lives 300 seconds, as on DSQL. A client message after that, a
ROLLBACKincluded, ends it with54000transaction age limit of 300s exceededand closes the connection with08P01. An idle transaction is ended the same way, unasked, 15 seconds after its limit. A statement still running at the limit fails with the same54000: nothing its transaction wrote is kept, the rest of its query, aCOMMITincluded, does not run, and the connection stays, in the failed transaction. The limit holds for a statement outside a transaction block too. A failed transaction left idle as long is closed with25P03. A connection closes after an hour. - Refused with DSQL's SQLSTATE and message: temporary and partitioned tables,
CREATE TABLE AS,SELECT INTOand materialized views, triggers,CREATE TYPE, PL/pgSQL functions andDO, extensions, array columns,jsonbandbyteakeys (an expression's, or a domain's over either, included),TRUNCATE,LISTEN/NOTIFY,VACUUM, sequences without aCACHEof 1 or at least 65536,ALTER TABLE ... ADD COLUMNwith a constraint or default,ADD CONSTRAINT,ALTER DATABASE ... SET,FOR SHAREandFOR NO KEY UPDATE, advisory locks, and isolation levels other than repeatable read, whether chosen for a transaction, set for the session, set withset_configor set for a role. - Foreign keys work as on DSQL: declared in
CREATE TABLE, enforced, with theirON DELETEandON UPDATEactions andDEFERRABLE; rows a cascade changes count toward the 3,000-row limit. Adding one withALTER TABLEis refused, as DSQL refuses it. sys.iam_session_user()answers the connection's IAM principal andsys.current_session_id()its session id;sys.dsql_major_version(),sys.cluster_size()andsys.cluster_size_limit()answer as on DSQL.dsqlis a reserved prefix, as on DSQL:SET,RESET,set_config,ALTER ROLE ... SETand a function'sSETclause of adsql.parameter fail with42602and"dsql" is a reserved prefix., andSHOWof one with42704.set_configof another dotted parameter is0A000, thoughSETof one works.- Errors carry no source location, as DSQL's carry none, and nothing Floci's
sysfunctions ran in their context; notices keep their location. A write conflict is DSQL's40001change conflicts with another transaction (OC000).
Differences from AWS
- DSQL is optimistic: conflicting transactions both run and the second to commit fails. Floci's database is PostgreSQL, so a write that conflicts waits for the other transaction and fails when it commits, with DSQL's error.
sysalso holds the tables behind Floci's jobs, IAM mappings, session identities and transaction counts, and each table has the threedsql_rows_*triggers that count its writes. Disabling them changes nothing, and dropping one is refused as for a trigger that does not exist.- Floci checks the statements a client sends, not what a SQL function's body runs. In one,
SET,RESETandset_configof adsql.parameter set it where DSQL refuses it, since only a PostgreSQL extension written in C can reserve a prefix, and so doesset_configof a name built at run time, as inset_config(chr(100) || 'sql.x', ...). Nothing Floci reads lives in such a parameter. Likewise aDROP ROLEin one drops a mapped role:sys.iam_pg_role_mappingsno longer lists its mappings, and the nextAWS IAM GRANTorREVOKEremoves them. - DSQL's primary key covers every column of its table, and its definition reads
PRIMARY KEY (id) INCLUDE (...); Floci's readsPRIMARY KEY (id). - A statement Floci refuses carries no position (
LINE 1: ...in psql), where DSQL gives one. - DSQL lets a statement running at the age limit finish, and fails it then; Floci stops it at the limit. The error, and what is kept, are the same.
- Floci's
sys.create_index_async, behindCREATE INDEX ASYNC, creates the one index it is handed when a client calls it itself; DSQL has no such function. - DSQL accepts connections while a cluster is still
CREATING; Floci's cluster isCREATINGonly 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
PrincipalorResource, or withNotPrincipalorNotResource; - a document missing or misusing an element: no
Statement, aVersionother than2008-10-17or2012-10-17, a member of no policy grammar, anEffectother thanAlloworDeny, 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
Allowto everyone ("*"or anAWS"*") that no condition restricts. A condition restricts with one of the trusted keys of DSQL's block public access page, oraws:SourceIp,aws:SourceVpcArn,aws:VpceAccount,aws:VpceOrgIDoraws: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 anaws:SourceIpprefix of at least /8 for IPv4 or /32 for IPv6; or withaws:PrincipalIsAWSServicetrue.IfExists,ForAllValues:and negated operators never restrict; - a policy that explicitly denies the caller both
dsql:PutClusterPolicyanddsql:DeleteClusterPolicy, unlessbypassPolicyLockoutSafetyCheckis 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. With IAM enforcement on, the cluster's policy is evaluated for every control plane
call on the cluster, as DSQL evaluates it, and when a client connects (see
Authentication). Each control plane call is checked against the cluster it names,
and CreateCluster and ListClusters against cluster/*.
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.