Use the Data Explorer

The Data Explorer tab of a cluster works on the cluster's database as the database's owner — the user in the connection string. On top of what that user may do, every statement is checked against your own permissions before it runs.

Who can use it

The tab needs the Database Explorer role on the cluster, its resource group or its boundary. No other built-in role includes it — Owner and Platform Owner neither — so it is always assigned explicitly; see Database Explorer and Assign a role. Without it the tab says you do not have permission.

Database Explorer covers reading and changing rows, running queries that read or change data, and creating and altering objects. The DROP and TRUNCATE statements are not included: they need postgrescluster/schemaDrop, which no built-in role holds. Withholding it does not protect the data from someone with the role — see What is checked before a statement runs. Put it in a custom role and assign it, or request that role for a few hours — see Custom roles and Just-in-time access. Every statement kind and the permission it needs are listed in Permissions.

The tab is unavailable while the cluster is stopped or has no ready instance.

Browse and edit rows

1. Open the Data Explorer tab. The tree on the left lists the schemas, their tables and views, and each table's columns.
2. Pick a table. Browse shows its rows, 100 at a time, with Prev, Next and Refresh, and an estimate of the row count.
3. To change data, edit a cell, add a row with Row, or mark rows for deletion. The changes are held until you submit them; a changed cell shows its previous value.
4. Choose Submit, which counts the changes, or Discard all. The new rows are applied first, then the changed rows, then the deletions, each group in one transaction; a group that fails stops the ones after it and stays pending.

Editing needs a table with a primary key of exactly one column. For a table with a composite key, change rows in Query.

Run SQL

1. Open Query. The table picked in the tree is named above the editor.
2. Write one or more statements.
3. Choose one of:
   • Dry run — runs the batch in a transaction that is rolled back, and shows what would happen: the results, the rows affected and, for a drop, every object that would go with it.
   • Run — runs the batch and commits it.
4. For a batch that creates, alters or drops objects, Run is refused until a dry run has passed: choose Dry run, check the result, then Commit DDL changes. The commit must follow within two minutes, from you, with exactly the same SQL; changing the SQL means a new dry run.

The result shows each statement's kind, the permissions it needed and any that you lack. Refused batches are marked Refused — structurally not allowed.; batches you lack permissions for, Denied — missing permissions or guard. — with the actions that could be granted through just-in-time access.

stsh pg query orders-db "select count(*) from orders"
stsh pg query orders-db "alter table orders add column note text" --dry-run
stsh pg schemas orders-db
stsh pg rows orders-db public orders --page-size 20

stsh pg query runs a dry run first and commits only when it passes; --dry-run stops after it.

What is checked before a statement runs

Before anything runs, the platform parses the batch and classifies every statement:

• A statement that is never allowed — transaction control, roles and grants, extensions, ALTER SYSTEM and the others listed in Refused statements — refuses the whole batch.
• Every other statement needs the permissions of its kind. If any is missing, nothing in the batch runs, and the result lists all that are missing.

Some statements are allowed with a warning shown next to them: UPDATE or DELETE without WHERE, DROP (with CASCADE or without), TRUNCATE, dropping a column, a constraint or NOT NULL, changing an owner, CREATE INDEX without CONCURRENTLY, and a batch that mixes drops with other statements.

Caution

The checks apply to the statements you send, not to what those statements run in turn. A function or trigger created here runs its body as the database's owner, and the statements in it — DROP and TRUNCATE included — are not checked. Anyone who may create functions (schemaWrite) and run statements (queryExecute), which the Database Explorer role includes, can therefore do whatever the owner can. Data can also be removed without schemaDrop: ALTER TABLE … DROP COLUMN needs only schemaWrite, and DELETE without WHERE only queryExecuteWrite. Treat the Database Explorer role as full access to the database's data, and keep backups on for data you cannot lose.

Limits

• The whole batch runs in one transaction; a batch of reads only runs read-only.
• A statement may run for 30 seconds and wait 5 seconds for a lock. Through the API a request can ask for up to 5 minutes.
• At most 5,000 rows are returned per batch unless a request through the API asks for more; the result says when it was cut off.
• A group of row changes holds at most 500 rows.

The audit trail

Every batch — from Query, the forms and Browse — is recorded with the SQL, who sent it, the outcome and, when it ran, how long it took and how many rows it touched, dry runs and refused batches included. Listing the schema tree is not recorded. Changes committed from Query or the forms also appear in the cluster's Activity, as Editor: … with the statement kind and the table; row changes submitted on Browse do not.