Client<->Server state out-of-sync with user without permission on a table and multiple attempts

[olesz]

When:

• prepared statements are enabled for a connection
• there is a user which has no permission to access a given table
• the user tries to access the table multiple times
  then after the 3rd attempt invalid message is detected by the vertica-python client.

Download and start vertica image:

:~$ docker pull molo17/vertica-ce:24.1.0-0
24.1.0-0: Pulling from molo17/vertica-ce
...
Digest: sha256:4e6029e9efa188a36304c79de354b8308ea4aa17befe05f4e6041a4b1bdffcaf
Status: Downloaded newer image for molo17/vertica-ce:24.1.0-0
docker.io/molo17/vertica-ce:24.1.0-0
:~$ docker run -d --name test-vertica molo17/vertica-ce:24.1.0-0
fa552a80e1e32e01e8ed1d91b8cdb8e775bfb47032577b94b5bd0f1f93c3a8b5
:~$ docker ps
CONTAINER ID   IMAGE                        COMMAND                  CREATED         STATUS         PORTS                NAMES
fa552a80e1e3   molo17/vertica-ce:24.1.0-0   "/bin/sh -c $ENTRYPO…"   2 seconds ago   Up 2 seconds   5433/tcp, 5444/tcp   test-vertica

Figure out IP address of container:

:~$ docker inspect   -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' fa552a80e1e3
172.17.0.2

Connect to database, create table and user:

:~$ vsql -h 172.17.0.2 -p 5433 -U dbadmin
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

demo=>
demo=> CREATE TABLE secret_table(id INT);
CREATE TABLE
demo=> CREATE USER test_user IDENTIFIED BY 'TestPassword123';

Check that there is no permission for the table for the user:

:~$ vsql -h 172.17.0.2 -p 5433 -U test_user
demo=> select * from secret_table;
ERROR 4367:  Permission denied for relation secret_table

Prepare test script with proper IP/user/password and execute it:

:~$ cat test.py
import vertica_python

conn_info = {
    "host": "172.17.0.2",
    "port": 5433,
    "user": "test_user",
    "password": "TestPassword123",
    "database": "demo",
    "use_prepared_statements": True,
    "autocommit": True,
}

sql = "SELECT * FROM secret_table LIMIT 1"

conn = vertica_python.connect(**conn_info)
cur = conn.cursor()

for i in range(1, 5):
    print(f"\n--- Attempt {i} ---")
    try:
        cur.execute(sql)
        print(cur.fetchall())
    except Exception as e:
        print(type(e).__name__, e)

cur.close()
conn.close()
:~$ python3 test.py

--- Attempt 1 ---
DatabaseError Severity: ERROR, Message: Permission denied for relation secret_table, Sqlstate: 42501, Routine: report_no_priv, File: /data/jenkins/workspace/RE-ReleaseBuilds/RE-Miner/server/vertica/Commands/GrantRevoke.cpp, Line: 532, Error Code: 4367

--- Attempt 2 ---
DatabaseError Severity: ERROR, Message: Permission denied for relation secret_table, Sqlstate: 42501, Routine: report_no_priv, File: /data/jenkins/workspace/RE-ReleaseBuilds/RE-Miner/server/vertica/Commands/GrantRevoke.cpp, Line: 532, Error Code: 4367

--- Attempt 3 ---
MessageError Received unexpected message type: ReadyForQuery. Expected type: BindComplete

--- Attempt 4 ---
DatabaseError Severity: ERROR, Message: Permission denied for relation secret_table, Sqlstate: 42501, Routine: report_no_priv, File: /data/jenkins/workspace/RE-ReleaseBuilds/RE-Miner/server/vertica/Commands/GrantRevoke.cpp, Line: 532, Error Code: 4367
Traceback (most recent call last):
  File "/home/elajolh/vertica_client/test.py", line 26, in <module>
    cur.close()
  File "/usr/lib/python3/dist-packages/vertica_python/vertica/cursor.py", line 205, in close
    self._close_prepared_statement()
  File "/usr/lib/python3/dist-packages/vertica_python/vertica/cursor.py", line 1032, in _close_prepared_statement
    self._message = self.connection.read_expected_message(messages.CloseComplete)
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/vertica_python/vertica/connection.py", line 765, in read_expected_message
    raise errors.MessageError(msg)
vertica_python.errors.MessageError: Received

It can be seen that attempt no. 3 has the error: "MessageError Received unexpected message type: ReadyForQuery. Expected type: BindComplete"

According to my understanding this is an issue in the vertica_python client.