Skip to content

Latest commit

 

History

History
379 lines (333 loc) · 24.6 KB

dsql.md

File metadata and controls

379 lines (333 loc) · 24.6 KB

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, or, with the external backend, in a PostgreSQL server that already runs, 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.

External PostgreSQL backend

With FLOCI_SERVICES_DSQL_BACKEND=external, every cluster lives in one PostgreSQL server that already runs, such as a sidecar, and Floci makes no Docker calls for DSQL. Clients see no change: TLS, IAM tokens, admin and DbConnect, and the dialect work as above. FLOCI_SERVICES_DSQL_MOCK still wins over the backend.

The server must:

  • run PostgreSQL 16 or later. Floci checks the version when a cluster starts and fails the cluster, creating nothing, on an older server, whose CREATEROLE would let one cluster's admin alter the roles of every other cluster;
  • let the configured user in as a superuser: Floci installs event triggers, reads pg_authid, runs CREATE DATABASE ... OWNER and ends sessions on a cluster's database when it is deleted;
  • be reachable over plain TCP. Floci does not use TLS to the server, so run both on a private network or on localhost;
  • authenticate the cluster roles' TCP logins with SCRAM-SHA-256 (scram-sha-256, or md5, which uses SCRAM for a role whose password is a SCRAM verifier, in pg_hba.conf), or trust them (trust, as the official image does for 127.0.0.1 and ::1), so a sidecar on localhost works. Floci never sends a password any other way: a server that asks for a cleartext or MD5 password is refused, and the client gets 08006.

Use a PostgreSQL server dedicated to Floci: clusters share its catalogs, and Floci's superuser manages roles and databases on it.

A cluster's start waits up to 90 seconds for the server to let Floci's superuser in, as a server started alongside Floci may still be starting; a login the server refuses, such as a wrong password, fails the cluster at once. Each login to the server may take up to 10 seconds and each call up to 120 seconds, so a server that accepts connections but never answers, such as a paused sidecar, fails the start, deletion or shutdown that waits for it instead of holding it.

Each cluster gets a database dsql_<cluster-id>, a role d<cluster-id>_<name> for each of its roles (d<cluster-id>_admin for admin), a hidden owner role dsqlowner_<cluster-id>, and a hidden role dsqlconnect_<cluster-id> that holds CONNECT on the database. PUBLIC may not connect to it; each of the cluster's roles is a member of dsqlconnect_<cluster-id>, which no client role can revoke. Clusters cannot connect to or read one another's data, and the same role name can exist in each. Floci signs in to the server for each client itself: role passwords are stored as SCRAM verifiers derived from a per-cluster secret that Floci keeps with the cluster and clients never see.

What differs from the Docker backend, each because many clusters share one server, where a Docker cluster has a server of its own. Databases and roles are named server-wide in PostgreSQL, so each cluster's must carry its identifier to be kept apart from the others':

  • current_user, session_user, pg_roles and PostgreSQL's own error messages show the backend names (d<cluster-id>_app); sys.roles and sys.iam_pg_role_mappings show the DSQL names. Floci translates the names in the statements a client sends, but not in the values PostgreSQL computes and returns.
  • current_database(), the *_catalog columns of information_schema (table_catalog and the like) and pg_database show the cluster's database as dsql_<cluster-id>, while clients still connect to postgres, as on DSQL. Each cluster needs a database of its own on the shared server, and only one there can be called postgres. A tool that compares the catalog with the database it was configured with should read current_database().
  • admin cannot create databases (no CREATEDB): a database it created would sit outside the cluster's own, where neither the cluster's isolation nor its deletion reaches. A DSQL cluster has the one database postgres in any case.
  • A role name is at most 35 bytes in UTF-8, so that with the 28-byte prefix it fits PostgreSQL's 63-byte identifier; a longer one is refused with 42602. PostgreSQL would cut a longer name short, and two names cut short could become the same role, so it is refused instead. A name of multi-byte characters reaches the limit with fewer than 35 characters. The Docker backend needs no prefix and keeps PostgreSQL's own limit.
  • Floci translates role names where the SQL syntax names a role (CREATE ROLE, GRANT ... TO, OWNER TO, SET ROLE app or SET ROLE 'app', AWS IAM GRANT and so on), not in LANGUAGE sql function bodies or in other string literals: has_table_privilege('app', ...), pg_has_role, set_config('role', ...) and information_schema filters on a grantee need the backend name.
  • Floci's own role procedures (sys.run_role_ddl, sys.drop_roles, sys.aws_iam_grant, sys.aws_iam_revoke) and every sys._* routine cannot be called by a client: a statement that names one with sys., spelled plainly or with U& escapes, fails with 42883, as for a routine that does not exist. Floci sends a client's role DDL through these routines once it has translated the names and checked that they are the cluster's; a client calling one itself would skip both. A call through a search_path that names sys, or from a function body, is not caught; the role DDL it runs is not translated, but a role it makes outside the cluster's prefix is not counted as the cluster's, and it cannot take over another cluster's roles.
  • A cluster counts as its own only the roles its admin, or a role it created, created or granted to it. A role with its prefix that another cluster's admin made, in a LANGUAGE sql function body where Floci translates no name, stays out of its sys.roles and its deletion.
  • Some errors the server sends while a connection starts may name a backend role or database.
  • Clients can see the other clusters' databases, roles and sessions in pg_database, pg_roles and pg_stat_activity: their names only, not their data or queries.
  • A role that owns objects in another database of the same server cannot be dropped, so the cluster stays DELETING until those objects are gone; Floci retries on its next start.

Floci marks the database, owner, admin and connect role it creates for a cluster with the comment floci:dsql:<cluster ARN>. A cluster whose database, owner, admin or connect role is already on the server without that comment fails to start, FAILED, and Floci changes nothing on the server; the log names the object in the way. The one exception is a database with no comment owned by the cluster's marked owner, which a start interrupted before it could comment the database left: Floci comments it and goes on.

When Floci restarts with persistent storage, each cluster keeps its database and data. Deleting a cluster drops its database and the roles it recorded, nothing else on the server: never a database, owner, admin or connect role without the cluster's comment. A FAILED cluster is deleted even when the server cannot be cleaned up; Floci then logs the database and roles that may remain. With in-memory storage, Floci deletes its external clusters when it stops. A cluster still starting or being deleted then is waited for, up to 3 minutes, and goes as the others do; one still starting is never served once Floci has begun to stop.

Docker Compose, with the server as another service:

services:
  floci:
    image: floci/floci:latest
    ports:
      - "4566:4566"
      - "5432:5432"
    environment:
      FLOCI_SERVICES_DSQL_BACKEND: external
      FLOCI_SERVICES_DSQL_EXTERNAL_HOST: postgres
      FLOCI_SERVICES_DSQL_EXTERNAL_PASSWORD: postgres
    depends_on:
      - postgres
  postgres:
    image: postgres:17
    environment:
      POSTGRES_PASSWORD: postgres

An ECS task on Fargate, where there is no Docker socket, with PostgreSQL as a sidecar on localhost (excerpt of containerDefinitions; give the PostgreSQL container its own port, since Floci serves clusters on 5432):

[
  {
    "name": "postgres",
    "image": "postgres:17",
    "essential": true,
    "command": ["postgres", "-p", "5433"],
    "environment": [{ "name": "POSTGRES_PASSWORD", "value": "postgres" }]
  },
  {
    "name": "floci",
    "image": "floci/floci:latest",
    "essential": true,
    "dependsOn": [{ "containerName": "postgres", "condition": "START" }],
    "environment": [
      { "name": "FLOCI_SERVICES_DSQL_BACKEND", "value": "external" },
      { "name": "FLOCI_SERVICES_DSQL_EXTERNAL_HOST", "value": "localhost" },
      { "name": "FLOCI_SERVICES_DSQL_EXTERNAL_PORT", "value": "5433" },
      { "name": "FLOCI_SERVICES_DSQL_EXTERNAL_PASSWORD", "value": "postgres" }
    ]
  }
]

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
FLOCI_SERVICES_DSQL_BACKEND docker docker (a container per cluster) or external (a server that already runs, see External PostgreSQL backend)
FLOCI_SERVICES_DSQL_EXTERNAL_HOST none Host of the external server; required with external
FLOCI_SERVICES_DSQL_EXTERNAL_PORT 5432 Port of the external server
FLOCI_SERVICES_DSQL_EXTERNAL_DATABASE postgres Database Floci connects to on the external server to manage the clusters' databases and roles
FLOCI_SERVICES_DSQL_EXTERNAL_USERNAME postgres Superuser Floci signs in as
FLOCI_SERVICES_DSQL_EXTERNAL_PASSWORD none Its password, if the server asks for one

With the Docker backend, 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.