Choose SQL database hosting by checking the connection route, the application’s permissions and a demonstrated recovery into a separate test database. Specify the database engine and version before comparing plans. A successful login and a readable backup file answer only part of the question; the restored application must still perform its intended task.
SQL describes a family of database interactions, so “SQL hosting” is not a complete product specification. This guide uses PostgreSQL as a worked example. Keep its documented tools and authentication behaviour tied to that engine. For another database product, ask for the equivalent applicable evidence rather than copying commands or assuming identical recovery formats.
Who is the application connecting as?
A PostgreSQL client requests a database user name when connecting. PostgreSQL authentication establishes the client identity for the requested database user. The authentication chapter distinguishes the requested identity from the process of establishing it. Ask the implementer to record the application’s intended database identity in the private deployment specification.
The active PostgreSQL database user determines access privileges to database objects. PostgreSQL can select an authentication method by client host address, database and user. These documented controls make connection and permission tests related but distinct. A connection succeeding does not by itself demonstrate that the application has exactly the access its task requires.
PostgreSQL database user names are logically separate from operating-system user names. The reference makes this separation explicit. Keep the host account and database identity as different fields in the handover. Similar names may be convenient, but a reviewer should not infer that access to one automatically explains access to the other.
Record the intended client environment, database destination and identity together. Ask who changes the connection configuration and who can approve access changes. Keep credentials out of shared planning documents. If a connection fails, preserve the relevant error and context without exposing secrets in screenshots or support messages.
What should a permission test prove?
Begin with a business task and the database operations it needs. Ask the application owner to define the relevant records and permitted actions, then test that definition in an isolated copy. Include an operation the application should not perform. This is an original acceptance method, not a universal permission template for every application.
For example, a reporting service may need to read approved data while a separate administrative workflow maintains it. Do not settle the distinction by giving every process the broadest available account. Request evidence for the intended roles and keep any migration or maintenance identity separate in the review record.
A failed operation also needs interpretation. It may expose an intended restriction, a missing permission or an application defect. Record the expected behaviour before testing so a denial is not automatically treated as an error to remove. The reviewer should be able to explain why the permission is required by the business task.
Original connection-and-recovery acceptance sheet
Consider a hypothetical stock-reporting application. It reads approved inventory records but should not edit them through its reporting identity. The worksheet is an authored test design with fictional data. Use a separate test environment and an agreed recovery procedure; do not import a trial dump over the live database.
| Test | Expected result to define beforehand | Evidence to retain | Owner who interprets it |
|---|---|---|---|
| Intended connection | Test app reaches the intended database as its named identity | Destination, identity and connection observation | Database operator |
| Approved read | Report displays the fictional approved records | Input records and resulting report | Product owner |
| Unwanted modification | Reporting identity cannot perform the forbidden task | Safe test result in the isolated copy | Access owner |
| Backup creation | Agreed database content is included | Backup scope, format and completion evidence | Backup operator |
| Separate restoration | New test database is recreated with intended ownership | Restore outcome and permission checks | Recovery operator |
| Application after restoration | Same report works against the recovered copy | Functional result compared with the baseline | Product owner |
Populate the worksheet with the actual application’s operations. The fictional read-only boundary is an example, not a permission recommendation for a transaction-processing app. Keep the identity used for backup separate from the application’s routine identity, then explain what access each needs and why.
What does the backup contain, and what does it leave out?
pg_dump needs read access to every table intended for backup. A pg_dump dump represents a consistent snapshot from when the dump began. These documented properties make scope and identity important. A completed file should be accompanied by the database identity, intended content and creation evidence, without storing the credentials in the handover.
pg_dump covers one database and does not include cluster-wide roles or tablespaces. Owners and users granted object permissions must exist before restoring an SQL dump with those original permissions. The dump documentation therefore requires a wider recovery discussion than “we have a database export”. Ask how the proposed recovery plan supplies the required identities and other relevant configuration.
Text dumps use psql for restoration, while non-text dumps use pg_restore. Match the documented restoration route to the actual file format before rehearsing recovery. Avoid choosing a tool by the filename alone. Ask the operator to explain the format, target database and failure-handling method in the proposed test plan.
For the fictional reporting app, prepare a baseline with named test records and an agreed report result before creating the backup. After restoration, use the same report inputs and compare the output with that baseline. Also include a deliberately restricted action in the recovery check, so the exercise does not validate data while forgetting access. If the report differs, retain the input, restored data observation and report output as separate evidence. That makes the discrepancy actionable for the appropriate owner and avoids a weak conclusion based only on matching table counts or a successful database connection.
How should the recovery rehearsal end?
Prepare a new isolated target and confirm its identity before loading any backup. Retain the original backup and record the restoration outcome. If an error occurs, preserve it and assess whether the result is partial before attempting another step. Do not label an import successful merely because some tables appear afterwards.
Run the application’s agreed functional check against the recovered copy. For the fictional stock report, compare the expected records and the report output, then repeat the permission checks. Inspect both data and access: an apparently complete dataset with the wrong operating permissions is not the same application state the worksheet intended to recover.
Keep any live cutover as a separate authorised change with its own backup and rollback plan. The rehearsal should leave a clear record of what was demonstrated and which dependencies still need evidence. Avoid inventing a recovery-time promise from a single illustrative exercise; define operational targets separately using the actual environment and business needs.
For the wider scope, see hosting, web development and business systems. To discuss requirements, get started with the engine, connection model and completed acceptance sheet. A useful hosting comparison should explain who owns permissions and recovery as clearly as it explains where the database runs.
By Spout Team. AI-assisted drafting by Codex using the linked primary documentation.