Creating a database

Creating a database

stado database create places PostgreSQL or SQLite on a fleet host, can provision PostgreSQL through Supabase, or adopts an existing server of any engine through --provider external. The default is fleet PostgreSQL, so the operator need not choose a vendor:

stado database create <product> --consumer <product>

flag	default	what it chooses
--engine	postgres (fleet, supabase); the URL's engine (external)	postgres or sqlite (fleet), postgres (supabase); external: any engine, named by the connection URL, which --engine only confirms
--provider	fleet	fleet: Stado runs the database itself on a fleet host, no vendor and no bill; supabase: a hosted Postgres project; external: a server of any engine the user already runs anywhere (Postgres, MySQL, MongoDB, Redis, SQL Server, ...; RDS, Cloud SQL, Neon, PlanetScale, Atlas, Azure, their own server)
--ca-certificate	none	external only: PEM bundle of the server's certificate authority; refused without it
--host	the vault owner	fleet host the database is placed on
--port	the fleet host hands out a free one and the placement reports it	port a fleet Postgres listens on; the handed-out one is written to the item and the report
--anchor	the one organization and region every project the Supabase token sees shares	supabase only: declared database whose organization and region the project joins; a token that sees projects in several is refused with each named
--accept-monthly-usd	none	supabase only: the monthly compute cost accepted; refused without it

The Supabase commands (create --provider supabase, adopt, destroy) call the management API with field value of the vault item that plays role supabase-management. A fleet where no item plays it is refused with the two commands that give it: stado credentials item retag --host <owner> <ITEM> --tags stado:role:supabase-management and stado credentials grant add --host <owner> --role supabase-management --field value --token-file <bearer file> <consumer>.

create --provider supabase reuses a project of the same name, so it reads the token's projects first; an answer to GET /projects that is not a project list is refused (whether <name> exists is unknown; nothing was created) rather than read as no projects, which would create a second, billed one. adopt refuses the same answer, a key listing that is not a list, a key that comes back without its value, and a project listed without its ref, so no item is written with an empty key or from an empty project path. When Supabase answers a project's pooler without its user or host, the item gets no pooler_url or session_url and carries pooler_unreported with the reason instead; the password create generated is kept, and stado database adopt reads the pooler again.

A fleet database is placed by stado database place <name> [--engine] --port <port> (the port only for Postgres), which create runs on the chosen host through the host channel. place initialises the data under ~/.stado/databases/<name>/ on that host and writes the item that plays the credential role <name>-database into the owner vault. For Postgres it runs initdb with a generated password, issues a certificate authority of the database's own and a server certificate it signs for the host's routable address and loopback, accepts only TLS connections with a password, stores pooler_url and session_url (both the server's URL), ca_certificate, db_user and db_password on the item, and installs the managed unit <name>-database that runs postgres on that data. For SQLite it creates the file and stores host, path and pooler_url (sqlite://<file>); connect opens it for a consumer on that host. A database already placed is reported as reused and never initialised twice. Every provider declares the database for its consumers the same way.

The credential is the one owner-vault item carrying stado:role:<name>-database, whatever its id: database_api.databases.<name>.item names that role and must be <name>-database. Every verb — place, create, migrate, adopt, grant, client, rest, auth, tls reissue and destroy — reads and rewrites the role's holder, so renaming the item changes no output of any of them. A new item is created under the role's name with the tag; an existing holder is rewritten and keeps its id and tags. A grant names the holder at the moment it is issued. Two items in one role are refused naming the role. An item still named <name>-database that plays no role (it was stored before database items played roles) is refused with the item <name>-database … plays no role, so no database verb finds it and the fix stado credentials item retag --host <vault owner> <name>-database --tags stado:role:<name>-database. The same holds for <name>-database-previous and <name>-database-fleet-pending, which migrate keeps.

The routable address is the host's first declared SSH destination that a resolver answers: a Stado network name (<host>.stado.internal) is passed over, since only Stado's own node carries it and psql, pg_restore and a consumer's driver ask the system resolver (could not translate host name). A host that declares no other destination is refused with stado registry host path set as the way to add one. A server placed at a network name before this rule is re-addressed by stado database tls reissue <name> or by the migrate run that reuses it.

An existing server of any engine is brought in with --provider external. The connection URL is read from standard input, so it never appears in the process table or shell history:

stado database create <product> --consumer <product> --provider external \
  --ca-certificate <server-ca.pem> < <file holding the connection URL>

The URL's scheme names the engine: postgres:// or postgresql:// is Postgres, mysql:// MySQL, mongodb:// or mongodb+srv:// MongoDB, redis:// or rediss:// Redis, mssql:// SQL Server, and so on for any engine. --engine, when given, must agree with it. The item <name>-database then holds pooler_url (that URL; a Postgres one also as session_url, and its password, percent-escapes decoded, as db_password, which backup and migrate give libpq through its environment), ca_certificate (the bundle), host, engine and provider: external, the same fields a consumer reads from any other database. A password that carries a % not followed by two hexadecimal digits, or that is not UTF-8 once decoded, is refused. Running it again re-points the item at the URL given. The stado-database crate opens it like any other: connect opens Postgres and MySQL (a mysql:// URL as a MySQL pool verified with VerifyIdentity, and the synchronous Client binds parameters in the connection's own dialect), and credentials hands any other engine's URL and certificate authority to that engine's own driver.

Refusals name what failed: --anchor or --accept-monthly-usd with --provider fleet (a fleet database has no vendor bill), an --engine other than postgres with --provider supabase (supabase runs Postgres only), --host or --port with --provider supabase, --host, --port, --anchor or --accept-monthly-usd with --provider external (the server is already placed), --provider external without --ca-certificate, a certificate file that holds no PEM certificate, empty standard input, a URL with no host, a URL whose engine differs from --engine, --ca-certificate with another provider, a sqlite engine with --provider external, any engine other than postgres or sqlite with --provider fleet (named with --provider external as the way in), an unknown provider, an engine name that is not a lowercase URL scheme, a fleet Postgres without --port (refused before anything is initialised), a host without initdb, postgres or openssl on its PATH (the PATH is printed), a host with no routable address, an initialised data directory whose unit is missing (the exact stado service deploy line that installs it is printed), and each failed initdb or openssl step with its own error output.

Fleet PostgreSQL certificate policy

A new PostgreSQL placement requires the placement host to declare database.postgres_tls, the certificate validity and key size its authority and server certificate are issued with: Fleet PostgreSQL certificate policy. The server certificate names the routable address and the loopback by IP only, which every libpq release verifies; stado database tls reissue <name> signs a new one with the database's own authority for a server placed before, and restarts it.