On-premises SQL database (ERP)
Overview
The org credential mode connects systems that have no per-user OAuth, such as an ERP's SQL database. IT stores one read-only service account in vastframe. Projects get grants for specific tables, and apps never see the connection string.
The catalog entry for this is erp ("Contoso ERP (AR)" in the sample catalog). You can rename it in Admin › Integrations.
| Integration | erp |
|---|---|
| Connection mode | Org credential |
| Identity | The app (the grant covers the project, whoever triggers the call) |
| Access | Read-only |
| Default sensitivity | High |
| Default approvers | The Finance - Accounting group (sample catalog) |
Locally, a PostgreSQL database stands in for the ERP. The SQL Server driver and network access to on-premises systems through Azure VNet peering are planned and not yet available. The gateway's read-only check is PostgreSQL-specific today.
Prerequisites
- A read-only database account that can see only the schemas and tables apps may use.
- Network reachability from vastframe to the database.
- An Org Admin in vastframe.
Permissions
Scopes are schema-qualified tables or views with :read:
| Scope | Allows |
|---|---|
ar.customers:read | SELECT from ar.customers |
ar.invoices:read | SELECT from ar.invoices |
ar.payments:read | SELECT from ar.payments |
ar.aging:read | SELECT from the ar.aging view |
ar.open_invoices:read | SELECT from the ar.open_invoices view |
These are the sample catalog's max scopes. There are no write scopes for this mode.
What the gateway allows
- One statement per call:
SELECT, orWITH … SELECT. - Every table must be schema-qualified (
ar.invoices, notinvoices). - No writes, DDL,
COPY,CALLorDO. - The gateway asks the database planner which relations a query reads. A table reached only through a granted view is allowed; any other table the plan reads needs its own grant.
- Each query runs in a read-only transaction with a 15-second statement timeout, and returns at most 5,000 rows (
truncatedtells you when rows were cut).
The text checks are a first filter. Rely on the database account's own SELECT-only permissions as the real boundary.
Setup steps
- Create the read-only database account and grant it
SELECTon the tables apps may read. - Open Admin › Integrations and select the ERP entry.
- Enter the connection string. It's encrypted and shown masked afterwards.
- Set Max scopes to the tables apps may ever ask for, and set Approvers (for example the data owners in Accounting).
- Save.
Use it in an app
const { rows } = await tc.int.erp.post('/query', {
sql: 'select * from ar.aging where total_open > $1',
params: [1000],
});
Declare the tables in tc.json so the request is filed at deploy:
{
"integrations": { "erp": ["ar.aging:read", "ar.invoices:read", "ar.customers:read"] },
"purpose": "Weekly AR aging report for the Controller; read-only."
}
Verify
- Check Grants in effect on the project's Integrations page lists the tables.
- Run a query from the app and confirm rows come back.
- Check Recent calls for the SQL operation and row size.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
TC_SQL_NOT_ALLOWED: Qualify "invoices" with its schema | Unqualified table name | Write ar.invoices. |
TC_SQL_NOT_ALLOWED: Only reads are allowed | The statement writes or changes schema | This connection is read-only. |
TC_SQL_NOT_ALLOWED: One statement per call | Several statements | Send one statement per call. |
TC_METHOD_NOT_ALLOWED | Not POST /query | Use POST /query with { "sql", "params" }. |
ACCESS_PENDING for a table you didn't expect | The query names a table outside the grant | Request that table, or query through a granted view. |
TC_SQL_NOT_ALLOWED: The query reads …, which this project has no grant for | The plan reads a table the query doesn't name (and no granted view covers it) | Name every table in FROM/JOIN and request it. |
VENDOR_ERROR with an SQL state | The database rejected the query | Check the SQL and the account's permissions. |