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

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:

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:

>>> 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

>>> 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:

>>> 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:

>>> 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() 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), which is the SQLSTATE and the server’s one-line message, and never the row data PostgreSQL can put in a failure’s detail.

>>> reader.close()