Database Console

Database Console

The Database console screen (Administration → Database, /admin/database) gives authorised users read-only SQL access to the entire shared PostgreSQL database. All service schemas live in the single PostgreSQL instance that the master-data datasource reaches, so every schema is visible from one place.

Access is governed by the admin-database screen in the Access control catalog — ADMIN by default, but an admin can grant READ access to any other role or individual user. READ is sufficient because the console is SELECT-only.

UI

Navigate to Administration → Database in the sidebar.

Schema / table tree (left panel)

Lists every non-system schema returned by GET /api/master-data/admin/db/schemas. Each schema is collapsible; the first schema that has tables opens automatically. Clicking a table:

1. Inserts select * from <schema>.<table> limit 100 into the SQL editor.
2. Runs it immediately.
3. Shows the table's column names and types beneath the selected table name (also available as a hover tooltip).

SQL editor (main panel)

A monospace editor accepts a single SQL statement. Execute with the Run button or Cmd/Ctrl+Enter. The last query is saved to localStorage (openwcs.admindb.sql) so the console reopens where you left off.

Results appear in the shared DataTable (client-side filter / sort / paging) together with:

• Row count and, when the cap was hit, a truncated badge.
• Execution time in milliseconds.
• An error banner that surfaces the PostgreSQL message verbatim when the query is rejected or fails.

Backend

/api/master-data/admin/db — access governed by the admin-database screen at the gateway (see Access below).

Endpoint	Description
GET /schemas	Non-system schemas with their tables and column name / type from information_schema.
POST /query	Execute a read-only query. Body: { "sql": "…", "maxRows": 200 }. Returns { columns, rows, rowCount, truncated, durationMs }.

POST /query safety layers

1. Statement validator — masks string literals, quoted identifiers, dollar-quoted blocks, and nested comments, then rejects: multi-statement input (; after the first statement), and any keyword that modifies data or schema — INSERT, UPDATE, DELETE, MERGE, TRUNCATE, DROP, CREATE, ALTER, EXECUTE, DO, COPY, CALL, EXPLAIN, and data-modifying CTEs. Violations return HTTP 400 with a plain-English message.
2. Read-only transaction — the query runs with autocommit off after SET TRANSACTION READ ONLY and is always rolled back, so anything that slips past the validator (e.g. nextval()) fails at the database level.
3. Timeout — SET LOCAL statement_timeout = 10s keeps runaway queries from tying up the connection pool.
4. Row cap — default 200 rows, configurable up to a hard maximum of 1 000. The response includes truncated: true when the cap was hit.

Response value serialisation

Values are typed as string, number, boolean, or null. Timestamps become ISO-8601 strings; exotic PostgreSQL types fall back to their text representation.

Audit logging

Full SQL is logged at DEBUG only. Every executed query produces an INFO audit line with the acting user (from X-Auth-User), duration, and row count — no query content is written at INFO.

Access

Access is controlled by the admin-database screen in the Access control screen (Administration → Access control). The default built-in level is ADMIN-only. An admin can grant READ (or WRITE) on that screen to any role or individual user; once granted, the gateway forwards the request to the controller.

The gateway enforces this as an access-required route: /api/master-data/admin/db/** is reachable on any HTTP method only if the caller's effective level on admin-database is at least READ. Because the console is SELECT-only, READ is sufficient — there is no separate write rule for this path. AdminDbController no longer performs its own ADMIN check; all authorization lives at the gateway (ScreenWriteCatalog access rules).

The endpoint lives on the master-data service because that datasource reaches the shared database.

Related: System Info · Services · Security.