How to Set Vertica in Read-Only

This blog post was authored by Soniya Shah.

You probably know that you can create READ ONLY users in Vertica. These users can view everything within a schema, but don’t have the proper permissions to change anything within the database. This is useful for sets of users that don’t need as many permissions or for users that should not be able to change data within your database. Did you know you can also set your database to READ ONLY?

In Vertica, you can set the session characteristics to be either READ ONLY or READ WRITE. While READ WRITE is the default, it is easy to change the transaction mode. Let’s take a look at an example.

The TransactionMode configuration parameter can be set at the database or the node level: => SELECT parameter_name, database_value, allowed_levels, description FROM configuration_parameters WHERE parameter_name = 'TransactionMode'; parameter_name | database_value | allowed_levels | description -----------------+----------------+----------------+------------------------------------------------------------------------------- TransactionMode | READ WRITE | NODE, DATABASE | Determines whether the transaction is read/write or read-only. Read/write is the default (1 row) Let’s alter our database to set it to READ ONLY: => ALTER DATABASE mydatabase SET TransactionMode = ‘READ ONLY’; All new sessions will be READ ONLY. Let’s try an INSERT statement to test if our change to the TransactionMode parameter worked: => INSERT INTO sales VALUES(1); When we do this, we get the following error: ERROR 2448: Cannot issue this command in a read-only transaction Now, suppose we want to revert the database back to READ WRITE. First, we must change the session characteristics. The following command will fail: dbadmin=> ALTER DATABASE mydatabase SET TransactionMode = 'READ WRITE'; ROLLBACK 2448: Cannot issue this command in a read-only transaction Instead, you must change the session characteristics: => SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED READ WRITE; Then, you can reset the TransactionMode: => ALTER DATABASE mydatabase SET TransactionMode = 'READ WRITE'; For more information, see SET SESSION CHARACTERISTICS in the Vertica documentation.