Skip to main content

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.

Integrationerp
Connection modeOrg credential
IdentityThe app (the grant covers the project, whoever triggers the call)
AccessRead-only
Default sensitivityHigh
Default approversThe Finance - Accounting group (sample catalog)
Availability

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:

ScopeAllows
ar.customers:readSELECT from ar.customers
ar.invoices:readSELECT from ar.invoices
ar.payments:readSELECT from ar.payments
ar.aging:readSELECT from the ar.aging view
ar.open_invoices:readSELECT 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, or WITH … SELECT.
  • Every table must be schema-qualified (ar.invoices, not invoices).
  • No writes, DDL, COPY, CALL or DO.
  • 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 (truncated tells 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​

  1. Create the read-only database account and grant it SELECT on the tables apps may read.
  2. Open Admin › Integrations and select the ERP entry.
  3. Enter the connection string. It's encrypted and shown masked afterwards.
  4. Set Max scopes to the tables apps may ever ask for, and set Approvers (for example the data owners in Accounting).
  5. 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​

  1. Check Grants in effect on the project's Integrations page lists the tables.
  2. Run a query from the app and confirm rows come back.
  3. Check Recent calls for the SQL operation and row size.

Troubleshooting​

SymptomCauseFix
TC_SQL_NOT_ALLOWED: Qualify "invoices" with its schemaUnqualified table nameWrite ar.invoices.
TC_SQL_NOT_ALLOWED: Only reads are allowedThe statement writes or changes schemaThis connection is read-only.
TC_SQL_NOT_ALLOWED: One statement per callSeveral statementsSend one statement per call.
TC_METHOD_NOT_ALLOWEDNot POST /queryUse POST /query with { "sql", "params" }.
ACCESS_PENDING for a table you didn't expectThe query names a table outside the grantRequest that table, or query through a granted view.
TC_SQL_NOT_ALLOWED: The query reads …, which this project has no grant forThe 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 stateThe database rejected the queryCheck the SQL and the account's permissions.
Was this page helpful?