PostgreSQL
A great deal of operational truth lives in a database with no interface anyone outside the technical team can use. That is a reporting problem masquerading as an access problem.
A great deal of operational truth lives in a database with no interface anyone outside the technical team can use. That is a reporting problem masquerading as an access problem.
What PostgreSQL holds, and why that matters here
PostgreSQL underpins a large share of modern applications, and in many organisations it holds data that has no user-facing home — operational records, reference data, the output of internal systems built years ago by people who have since left.
The practical consequence is that answering an ordinary question requires someone who can write a query, and that person becomes a bottleneck for questions that are not remotely technical.
- Application data — whatever the systems built on it record.
- Reference data — the lookups other systems depend on.
- Operational records — transactions, events and states.
- Historical data — what happened previously, frequently unavailable elsewhere.
- Views — the curated slices a team has already defined.
Questions Elbi can answer from PostgreSQL
What is the current status of X?
A specific record, retrieved and phrased as a sentence.
How many of these are there?
A simple count that would otherwise be a request to a technical team.
What happened with this reference?
History against a record.
Which of these are outstanding?
A filtered list, described rather than dumped.
What was the value last month?
A period figure that currently requires someone to run a query.
Does this record exist?
Existence and basic state, which resolves a large share of internal queries.
How Elbi reads from PostgreSQL
- A login role that inherits nothingThe connection uses its own login role with CONNECT on one database and USAGE on one schema that holds the agreed views. It is not a member of any application role, so it inherits nothing it was not explicitly given.
- SELECT on views in a dedicated schemaThe views live in a schema of their own and expose only the columns each answer needs. The role gets SELECT on those views and nothing on the tables beneath them; broad built-in roles such as pg_read_all_data are deliberately left out.
- Read-only at the role level as wellSetting default_transaction_read_only and a statement_timeout on the role adds a second guard behind the grants, against a mistaken privilege or a query that runs far longer than a lookup should.
- Row-level security for per-person answersWhere answers must be limited to the asker's own records, PostgreSQL's row-level security can enforce that inside the database as well as in the assistant. Views normally run with their owner's rights, so recent versions let them be declared security_invoker for those policies to apply to the reader.
- Parameterised lookups on a standbyLookups run as parameterised statements, ideally on a hot standby. A standby is read-only by nature, though a long query there can be cancelled when it conflicts with replication, which is one more reason to keep lookups short.
- Values turned into languageEnum values, codes and timestamps are phrased as plain sentences in Arabic or English rather than shown as stored. JSON columns are best unpacked in the view, so the assistant reads named fields instead of interpreting a document.
Roles, privileges and what stays yours
| Direction of access | Read-only by grant and by role setting. The role holds SELECT on named views with default_transaction_read_only on; writes belong in the applications that own the data. |
|---|---|
| Who can see what | The views set what the role can reach. The asker's identity decides which answers they are given, and row-level security can enforce the same rule in the database for data that is personal to each customer. |
| Credentials | A login role you create, authenticated by SCRAM password or client certificate and admitted by pg_hba.conf only from the connection's address. Setting the role to NOLOGIN, or changing its password, ends access from your side without involving us. |
| Where data goes | Rows fetched for a lookup serve only the reply they were fetched for. Message content is destroyed within 24 hours, typically at the end of the conversation, and no external AI service processes it. |
| Audit | The pgAudit extension can log the role's reads at statement or object level, or statement logging can be switched on for this role alone. Either way the record of what was queried sits in your own logs. |
| If it is unavailable | If the standby is down, the role is refused or a statement hits its timeout, the assistant says it could not get the answer and offers a person. A timeout is never reported as "no records found". |
What the PostgreSQL connection replaces
The bottleneck in most organisations is not the database — it is that only a few people can ask it anything. Every question from operations, finance or support becomes a request to a technical team, and those requests are individually trivial and collectively enormous.
The right shape for an assistant here is narrow and curated: a small set of named, parameterised questions defined by people who understand the data, exposed conversationally to people who do not. That is fundamentally different from letting an assistant compose arbitrary queries, which is both slower and considerably less safe.
The discipline that makes it work is deciding the questions first. A curated view answering the twenty questions that actually get asked is more useful and far more defensible than open access to a schema.
Which teams rely on the PostgreSQL connection
Operations teams
Status and count questions without a technical request.
Finance
Period figures that currently require someone to run a query.
Support
Record lookups during a customer conversation.
Any organisation with a legacy system
Data with no usable interface left.
Getting value from PostgreSQL
Start with the requests your technical team currently answers by running a query by hand. Counting them over a few weeks usually reveals a small set of shapes — a status by reference, a count by period, a list of what is outstanding — and each shape becomes one view.
Design views around the question rather than the table. A view that already joins, filters and names things in business terms, open_orders_by_customer rather than a raw join, keeps the lookup trivial and lets someone who does not read SQL review it.
Decide early whether any answers are per customer. If they are, row-level security is worth planning from the start, because it lets the database itself refuse the wrong rows instead of relying on every view being written correctly.
What connecting PostgreSQL involves
- Write the questions as viewsEach recurring question becomes a view in a dedicated schema, reviewed by someone who knows the data and named so a non-specialist can tell what it returns.
- Create the roleCREATE ROLE with LOGIN, a strong password or certificate, CONNECT on the database, USAGE on the view schema and SELECT on the views. Nothing on the public schema, and no role memberships.
- Restrict where it connects fromAdd a pg_hba.conf entry that admits the role only from the connection's address, with SCRAM or certificate authentication, and only to the database it needs.
- Set the guard railsSet default_transaction_read_only and a statement_timeout on the role, and decide whether pgAudit should record its activity from the first day.
- Point it at a standbyWhere streaming replication is in place, use a hot standby for lookups and agree, question by question, how far behind the primary an answer may be.
- Test the refusalsQuery a table directly, ask for another customer's record, and run a lookup built to time out. Each should fail cleanly and be reported as a failure, not as an empty result.
Row-level control and personal data in PostgreSQL
PostgreSQL gives you two places to control personal data: the view, which decides which columns exist for the connection at all, and row-level security, which decides which rows a given lookup may return. Under the Saudi Personal Data Protection Law, using both for customer-facing answers makes the case for data minimisation easy to demonstrate.
Residency follows the server — your own infrastructure, a hosting provider, or a managed PostgreSQL service in a region you selected. The connection reads the data in place; record the location beside the list of views.
The role's grants, its pg_hba.conf entry and, if enabled, its pgAudit log together answer a reviewer's questions — what could it read, from where, and what did it read — from your own systems.
What organisations typically see
Other ways PostgreSQL data reaches people
Technical staff reach PostgreSQL through tools such as psql or pgAdmin, and analysts through BI tools reading from a replica. Both serve the people who use them well, and leave everyone else asking those people.
Some teams expose views as an API with a tool such as PostgREST, which builds a REST interface from the schema and relies on PostgreSQL roles and row-level security for access. It is a tidy pattern, and it still needs a front end that people can actually put questions to.
Letting a language model write SQL freely against the schema is increasingly common in demonstrations. Against production data it pairs the broadest access with the least predictable queries, which is why this connection uses fixed, parameterised views instead.
The recurring mistake is granting pg_read_all_data, or membership of the application's own role, because it was quicker. Either one quietly turns a narrow connection into a copy of the application's read access.
Common questions about the PostgreSQL connection
No. It runs named, parameterised lookups defined during deployment — safer, faster and reviewable.
No, by default and by recommendation. Writes belong in the applications that own the data and enforce its rules.
Databases are optimised for transactions rather than analysis. The assistant answers about specific records well; broad aggregation is the wrong instrument.
Yes, and having it is a good sign. Policies you already enforce apply to the connection's role like any other, provided the views are set up so the policies are evaluated for the reader rather than the view's owner.
No. It needs a login role with SELECT on named views. Superuser, replication and the broad built-in read roles are all unnecessary and should be refused if anyone suggests them.
Views can present either in the shape an answer needs: a distance, the name of an area, a field pulled out of a JSON document. The assistant reads the view's output, so the view is where that work belongs.
Connect it to your own systems
A working assistant against your own records, in both languages.