# Untrusted statements

Sometimes the statement to run was not written by you: most often it came from a language model
asked to turn a question into Cypher, or from a user typing a query into a tool. Such a statement
must be able to read and must not be able to write.

The rule is that the **server** refuses the write, not the driver. A driver that reads the text to
decide whether it writes has to recognise every way of writing, and it would not: this is PostgreSQL
underneath, so besides Cypher's `CREATE`, `SET` and `DELETE` there are `INSERT`, `UPDATE`, `TRUNCATE`,
`DROP`, functions that write, and more. A read-only transaction refuses them all.

## Three conditions

```{code-block} python
:class: no-run

from agensgraph import cypher

cypher.check_single_statement(statement)        # 3. one statement, no more

with conn.read_only_transaction():              # 2. the server refuses every write
    result = conn.execute_query(statement)      # 1. as a role that cannot run host programs
```

Each closes a hole the others leave open.

## 1. A role that cannot run programs on the host

A read-only transaction stops writes to the database, and `COPY ... TO PROGRAM` is not one: it runs a
command on the server's machine and only takes rows *out* of the database, so there is nothing for a
read-only transaction to refuse. What stops it is not holding the privilege. Superusers hold it, and
so does any role that is a member of `pg_execute_server_program`.

So run untrusted statements as a role that is neither. Create one, once, with only what it needs to
read the graph:

```python
for statement in [
    "CREATE ROLE reader LOGIN",
    "GRANT USAGE ON SCHEMA moviekb TO reader",
    "GRANT SELECT ON ALL TABLES IN SCHEMA moviekb TO reader",
]:
    conn.execute(statement)
conn.commit()
```

`read_only_transaction()` checks this for you. On a connection whose role could run a program on the
host, it refuses to begin, saying what to change:

```pycon
>>> conn.can_run_server_programs()
True
>>> with conn.read_only_transaction():
...     pass
Traceback (most recent call last):
  ...
agensgraph.errors.ConfigurationError: this role may run a command on the server's host, through COPY ... TO PROGRAM, which a read-only transaction does not stop -- so a read-only transaction is not a boundary for a statement this role did not write. Connect as a role that is not a superuser and does not hold pg_execute_server_program, or pass allow_server_programs=True to accept it.
```

`can_run_server_programs()` asks the server once per connection and keeps the answer.
`allow_server_programs=True` accepts the risk, for a development database and nothing else.

## 2. A read-only transaction

```pycon
>>> reader = agensgraph.connect("host=localhost dbname=docs user=reader", autocommit=True)
>>> reader.graph("moviekb")
>>> with reader.read_only_transaction():
...     reader.execute_query(
...         "MATCH (m:movie) WHERE m.year > 2015 RETURN m.title ORDER BY m.title"
...     ).records
[('Blade Runner 2049',), ('The Matrix Resurrections',)]
```

A read runs. Every write is refused by the server:

```pycon
>>> with reader.read_only_transaction():
...     reader.execute_query("MATCH (n) DETACH DELETE n")
Traceback (most recent call last):
  ...
agensgraph.errors.ReadOnlyGraphWrite: cannot write to a graph in a read-only transaction
>>> with reader.read_only_transaction():
...     reader.execute_query("DROP TABLE moviekb.movie")
Traceback (most recent call last):
  ...
psycopg.errors.ReadOnlySqlTransaction: cannot execute DROP TABLE in a read-only transaction
>>> with reader.read_only_transaction():
...     reader.execute_query("COPY (SELECT 1) TO PROGRAM 'id'")
Traceback (most recent call last):
  ...
psycopg.errors.InsufficientPrivilege: permission denied to COPY to or from an external program
```

| Statement | Refused by |
|---|---|
| a Cypher write, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `DROP`, `CREATE VLABEL` | the read-only transaction, `25006` |
| `COPY ... TO PROGRAM` | the missing privilege, `42501` |
| a read | nothing: it runs |

A graph write is raised as `agensgraph.errors.ReadOnlyGraphWrite`, a subclass of psycopg's
`ReadOnlySqlTransaction`, since the server reports it with a message that varies between releases.

`read_only_transaction()` **always rolls back** at the end, even after a read, so nothing the
statement did, such as a `SET`, outlives it or reaches the next user of a pooled connection. Inside an
open transaction it is a savepoint, and rolls back only itself.

`conn.read_only = True` makes a whole session read-only, which is psycopg's setting; this adds the
privilege check and the rollback.

## 3. One statement

A statement sent with no parameters goes over PostgreSQL's simple protocol, which runs every statement
in the text and reports only the first one's result. A read with a write after a semicolon runs the
write, and looks like the read. Inside a read-only transaction the write is still refused, but nothing
else would refuse it, so check that the text is one statement before sending it:

```pycon
>>> from agensgraph import cypher
>>> cypher.check_single_statement(
...     "MATCH (m:movie) RETURN count(m); CREATE (:movie {title: 'smuggled'})"
... )
Traceback (most recent call last):
  ...
ValueError: this is more than one statement: there is a "; CREATE (:movie {title: 'smug" after the first one. Sent without parameters it would run all of them and report only the first one's result, so a statement from somewhere else is taken one at a time.
>>> cypher.check_single_statement("MATCH (m:movie {title: 'a;b'}) RETURN m;")
```

It reads the text with its literals blanked, so a semicolon inside a string, a dollar-quoted body or a
comment separates nothing, and one at the end is not a second statement. The driver does not call it
for you; it is for text you did not write.

Binding any parameter closes the same hole from the other side: it sends the statement over the
extended protocol, where the server refuses a second statement with `42601`. A statement with
parameters from a model that also produced the parameters gets both protections.

## A hint, not a boundary

`cypher.check_can_wrap(statement)` refuses a statement that contains a write clause, naming it. It is
the check [`stream()`](../guide/running-statements.md#streaming-a-large-result) uses, and it is a
reasonable thing to show a user early, before anything is sent. It reads the text, so for the reasons
at the top of this page it is not a boundary, and the driver never uses it as one.

## Telling the model what went wrong

A model that wrote a bad query does better when it reads the error. Hand it
[`safe_message(exc)`](errors.md#handing-a-failure-to-someone-else), which is the SQLSTATE and the
server's one-line message, and never the row data PostgreSQL can put in a failure's detail.

```pycon
>>> reader.close()
```