SQL connections
The Query data page and the Data tabs run queries in the app. A SQL connection lets the tools you already use do the same: Power BI, Excel, Tableau, DBeaver, Metabase, psql, and anything with a PostgreSQL JDBC or ODBC driver. Classifyre answers as a PostgreSQL database: the workspace is the database, each source schema is a schema, each table you may query is a table.
- Nothing is reachable until it is opened. A source or table is reachable through SQL connections only after its owner, a steward or an admin opens it on its Governance tab (opening a source opens all its tables). Opening gives nobody access: people still need the right to query it.
- It names what it is for. Like Give access, a connection is for a whole source or for single tables — chosen from what is open and what you may query. Only admins and people who answer for data (owners, stewards, custodians) may make or give one for everything open.
- It is yours. A connection belongs to one person and reads exactly what that person may query right now — their roles, their Data reader grants, the tables they own. It never gives access of its own: when a grant is removed, the connection stops reading that table at once.
- Masks apply. Columns governance masks or hides for its person are masked or missing here too, and a masked column’s description says so (see Masked and hidden columns).
- Row filters apply. A table that shows its readers only some rows shows the connection’s person only theirs (see Filtered rows).
- Everything is recorded. Every table read through a connection is in the table’s Activity and Usage, with the person and the connection’s name.
- It can be taken back. Its person or an admin can revoke it; a table’s owner can close their table to one connection; it expires.
- No password is stored. Only a SCRAM-SHA-256 verifier is kept. The password is shown once, to its person only, and never crosses the network.
Make a connection
Open Settings → SQL connections
Choose New connection, name it after the tool (Power BI, DBeaver), choose the sources or tables it is for, and pick when it expires. From a table’s or source’s Data tab, Connect a tool → New connection for … starts with that table or source already chosen.
Copy the password
It is shown once. Lost it? New password replaces it; tools signed in with the old one stop at their next sign-in.
Connect the tool
The dialog shows the host, port, database and user, and the same as a psql line, a JDBC URL, an ODBC string and a Power BI connection string. Connect a tool next to the SQL editor shows them again at any time.
You need the right to query tables to make a connection: the Data reader role (on the workspace, a source or a table), a workspace admin role, or tables you own.
Given by an owner or admin
A table’s or source’s owner or steward can give a connection for it to someone on its Governance tab, and an admin can give one for anything. The person is notified and reveals the password themselves — the one who gave it never sees it. The connection reads only what it was given for, and only if the person may read it: an owner who wants someone to read their table also grants them access (or approves their request).
Connect your tool
Every connection uses TLS and checks the server’s certificate (sslmode=verify-full). When your installation uses its own certificate authority, download it from the connection dialog (Download the certificate authority) and point your tool at it.
psql
psql "host=sql.example.com port=5432 dbname=finance user=ana_power_bi sslmode=verify-full sslrootcert=classifyre-sql-ca.crt"\dn lists the schemas (<source>.<schema>), \dt "lake_finance.ledger".* their tables, \d+ shows the descriptions the table’s stewards wrote.
Naming tables
A table you see on the Query data page as source.schema.table is the PostgreSQL table "source.schema".table — the quotes are needed because of the dot. Tools quote names for you; when writing SQL by hand, set the search path once:
SET search_path = "lake_finance.ledger";
SELECT * FROM invoices WHERE amount > 1000;Without quotes, PostgreSQL reads source.schema.table as database.schema.table and refuses it; write "source.schema".table, or "lakehouse-lab"."source.schema".table with the workspace as the database.
What happens to a query
Catalog queries — the schemas, tables and columns tools read to show you what is there — are answered at once from a PostgreSQL catalog made for your session. A query that reads your tables runs as a SQL job, exactly like one from the Query data page: the same SQL gate (one read-only statement, only the tables you may read), the same time limit, recorded per table.
- Rows. A result has at most the workspace’s row limit (Settings → General, 10,000 by default), or its export limit if you may export (
data.export). More is refused with an error, never cut silently: add a filter or aLIMIT. - Read only. Writes, temporary tables in your sources and settings that change data are refused.
- Cancel in your tool stops the job.
- Mixing. One statement reads either your tables or the catalog (
pg_catalog,information_schema), not both.
Speed
Each query starts a short-lived job, which takes seconds before the query itself runs. Tools that import data (Power BI import mode, Excel, extracts in Tableau) work well; tools that send a query for every click feel slow.
Opening a source or table
On a table’s or source’s Governance tab, the Access step starts with Open to SQL connections. Its owner, a steward or an admin switches it on; a table is open when it or its source is. Settings → SQL connections lists everything open, by whom and since when. Closing it again stops every connection from reaching it at once.
Governance and activity
On a table’s or source’s Governance tab, Access lists the SQL connections that read it in the last 90 days, how often and when, whose they are, and those its scope names or that are closed on it. Its owner and stewards can:
- give a connection for it to a person;
- close it here for one connection — the rest of that connection keeps working, and its person is told;
- open it again.
The table’s Activity shows each read with the connection’s name (via SQL connection Power BI), and its Usage counts the people reading it, however they read. Creating, giving, revealing, revoking and closing connections, sign-ins (at most once an hour per connection) and refused sign-ins are on the workspace’s activity too.
Who may do what
| Its person | Owner or steward of a table/source | Admin (data.connection.manage) | |
|---|---|---|---|
| Open a table or source to SQL connections | for what they govern | ✓ | |
| Make one for themselves | ✓ for open sources/tables they may query (data.connect, a Data reader grant, or tables they own) | ✓, also for everything | ✓, also for everything |
| See its password | ✓, once per password | never | never |
| Give one to someone | for open sources and tables they govern | for anything open; for everything only to an admin, owner, steward or custodian | |
| Close a table or source to one | for what they govern | ✓ | |
| Revoke | ✓ | ✓ | |
| See them | their own | those that concern their table | all |
The Data reader role includes data.connect; a Data reader grant on one table lets its holders connect a tool to that table.
Making the endpoint reachable
Administrators: the endpoint is the SQL gateway, a separate service of the same image (Helm sqlGateway, off by default). It always does TLS itself and speaks to clients directly, so it needs a plain TCP path, not an HTTP ingress.
No proxy
Without any TCP-capable proxy, give the gateway its own address:
sqlGateway:
enabled: true
publicHost: sql.example.com
publicPort: 5432
service:
type: LoadBalancer # or NodePort
loadBalancerSourceRanges: [203.0.113.0/24]
externalTrafficPolicy: Local # keeps client addresses for allowedCidrs
allowedCidrs: [203.0.113.0/24]The chart makes a certificate authority and a certificate for publicHost and keeps them across upgrades; people download the CA from the app. To use your own certificate, set sqlGateway.tls.existingSecret (a kubernetes.io/tls Secret) or let cert-manager issue one (sqlGateway.tls.certManager).
Connections use TLS 1.3 only. Other settings: maxSessions per gateway pod (each session holds a catalog of about 150 MB), maxSessionsPerConnection, idleMinutes. Ten failed sign-ins within 15 minutes lock a user name, and an address, out for 15 minutes.