Skip to content

armgit / floci

Public Imported from GitHub
0

Light, fluffy, and always free - The AWS Local Emulator alternative

15.2 KB
Raw Blame

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

  • 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, and the cluster's resource-based policy is evaluated as DSQL evaluates it on connect, with the client's address as aws:SourceIp. An explicit Deny in it refuses the connection with the hint User: <caller> is not authorized to perform: dsql:<action> on resource: <cluster ARN> with an explicit deny in a resource-based policy.
  • 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, and DSQL's dialect installed in it:

  • CREATE [UNIQUE] INDEX ASYNC answers a job_id; sys.jobs and sys.wait_for_job report the job. A unique index over duplicate keys fails its job with found duplicate key(s) while validating index uniqueness and stays, invalid, as on DSQL. A plain CREATE INDEX is refused.
  • 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.
  • One DDL statement per transaction, statements on roles (CREATE, ALTER and DROP ROLE, and GRANT or REVOKE of 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 ROLLBACK included, ends it with 54000 transaction age limit of 300s exceeded and closes the connection with 08P01. An idle transaction is ended the same way, unasked, 15 seconds after its limit. A statement still running at the limit fails with the same 54000: nothing its transaction wrote is kept, the rest of its query, a COMMIT included, 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 with 25P03. A connection closes after an hour.
  • Refused with DSQL's SQLSTATE and message: temporary and partitioned tables, CREATE TABLE AS, SELECT INTO and materialized views, triggers, CREATE TYPE, PL/pgSQL functions and DO, extensions, array columns, jsonb and bytea keys (an expression's, or a domain's over either, included), TRUNCATE, LISTEN/NOTIFY, VACUUM, sequences without a CACHE of 1 or at least 65536, ALTER TABLE ... ADD COLUMN with a constraint or default, ADD CONSTRAINT, ALTER DATABASE ... SET, FOR SHARE and FOR NO KEY UPDATE, advisory locks, and isolation levels other than repeatable read, whether chosen for a transaction, set for the session, set with set_config or set for a role.
  • Foreign keys work as on DSQL: declared in CREATE TABLE, enforced, with their ON DELETE and ON UPDATE actions and DEFERRABLE; rows a cascade changes count toward the 3,000-row limit. Adding one with ALTER TABLE is refused, as DSQL refuses it.
  • sys.iam_session_user() answers the connection's IAM principal and sys.current_session_id() its session id; sys.dsql_major_version(), sys.cluster_size() and sys.cluster_size_limit() answer 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 nothing Floci's sys functions ran in their context; notices keep their location. A write conflict is DSQL's 40001 change 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.
  • sys also holds the tables behind Floci's jobs, IAM mappings, session identities and transaction counts, and each table has the three dsql_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, 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's primary key covers every column of its table, and its definition reads PRIMARY KEY (id) INCLUDE (...); Floci's reads PRIMARY 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, behind CREATE 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 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. 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.