Users, databases and tokens
PostgreSQL and MySQL instances hold one or more databases and the users that may open them. SQLite (libSQL) instances have a single database and use access tokens instead of users. You manage all of them on the instance's Databases, Users & roles, Tokens and Extensions pages, or with hubfly db. Every change on these pages needs the Database admin permission.
The admin user
Each PostgreSQL and MySQL instance is created with a default database and an admin user (app and admin unless you chose other names). The admin user is a managed admin role: it can create databases and users and read and write all data, but it is never a superuser. It is listed as Primary under Users & roles and cannot be edited or deleted there; you reveal or rotate its password on the Connect page or with hubfly db credentials.
hubfly db credentials app-db # admin user, password masked
hubfly db credentials app-db --reveal # recorded in the activity log
hubfly db credentials rotate app-db # prints the new password onceRotating the admin password updates the apps bound with the admin user (redeploy them); every other client needs the new password. See rotation and removal.
Databases
A database is a separate set of tables inside the instance, so one instance can serve several apps. Names use up to 63 lowercase letters, numbers and underscores; see limits for reserved names.
- PostgreSQL: a new database is owned by the admin user, or by another user you name with
--owner. - MySQL: databases have no owner. The admin user and users with access to all databases can use a new database right away; give other users access by editing them.
- Dropping a database is immediate and cannot be undone. The default database cannot be dropped. A drop fails while other sessions use the database; choose Disconnect active sessions first (
--force) to end them. Export it first if you need a copy.
hubfly db databases app-db
hubfly db databases create app-db analytics
hubfly db databases create app-db billing --owner billing_app # PostgreSQL
hubfly db databases delete app-db analytics --force # asks you to type the nameUsers and access presets
Give each app its own user with just the access it needs. Instead of individual grants, Hubfly space gives users one of three presets, either on the databases you pick or on all databases, including ones created later:
| Preset | Dashboard label | Access |
|---|---|---|
read_only | Read-only | Reads data on the chosen databases. Cannot change data or schema. |
read_write | Read & write | Reads and changes data and schema on the chosen databases, so it can also run migrations. On PostgreSQL, the objects it creates are owned by this user. |
admin | Admin | Full control on all databases, including creating databases and users. Never a superuser. |
A user whose privileges were changed outside Hubfly space, for example with GRANT in SQL, shows the preset Custom. Editing its access in Hubfly space applies the preset you choose.
- Passwords are generated by Hubfly space and shown once, when the user is created or its password is rotated. After a rotation, open connections stay open; new connections need the new password.
- Access changes apply to new and existing connections right away.
- Deleting a user drops it immediately and closes its connections. Data stays in the databases: on PostgreSQL, the objects the user owned are reassigned to the admin user (or to the user you name with
--reassign-to); on MySQL, views, routines, triggers and events defined by the user are moved the same way so they keep working. - User names follow the database name rules and are limited to 63 characters, or 32 on MySQL.
hubfly db users app-db
hubfly db users create app-db reporting --preset read_only --databases analytics
hubfly db users create app-db worker --preset read_write --all-databases
hubfly db users update app-db reporting --preset read_write --databases analytics,app
hubfly db users rotate app-db worker # prints the new password once
hubfly db users delete app-db worker --reassign-to adminLet a binding create the user
DATABASE_URL.libSQL access tokens
Clients authenticate to a SQLite (libSQL) database with an access token. Each token has a name, an access level and an optional expiry:
- Access: read-only, or full access (read and write).
- Expiry: a whole number of days from 1 to 3650, or none. A token without an expiry works until it is revoked.
- Shown once: the token is displayed only when you create it. Copy it into your app's environment or secret store.
hubfly db tokens notes
hubfly db tokens create notes web-app --access full_access
hubfly db tokens create notes ci-reader --access read_only --expires 30
hubfly db tokens revoke notes <tokenId>Revoking invalidates every token
Tokens created by app bindings are listed here as App: <project>. See libSQL tokens for apps.
PostgreSQL extensions
Extensions add types, functions and indexes to a database. The Extensions page lists, per database, the extensions you can install and the ones already installed. Installing runs CREATE EXTENSION in the chosen database as the admin user; some extensions add tables or functions to it.
Only extensions on Hubfly space's allowlist can be installed:
| Use | Extensions |
|---|---|
| Vectors and AI search | vector (pgvector) |
| Geospatial | postgis, cube, earthdistance |
| Text search and matching | pg_trgm, fuzzystrmatch, unaccent, citext |
| Data types | hstore, ltree, intarray, uuid-ossp |
| Indexing | btree_gin, btree_gist |
| Other | pgcrypto, tablefunc, pg_stat_statements |
A plain removal fails while other objects still depend on the extension. hubfly db extensions remove --cascade also drops those dependent objects, which can include tables and columns that use the extension's types, so export first if you are unsure.
hubfly db extensions app-db --database app
hubfly db extensions install app-db vector
hubfly db extensions install app-db postgis --database maps
hubfly db extensions remove app-db pg_trgm --cascadeMySQL and SQLite (libSQL) do not use extensions.