You can create a read-only

Cloud SQL for PostgreSQL

session using the
`cloudsql_session_read_only` session parameter. This approach is a more robust
way to prevent data modification during a session than the standard PostgreSQL
read-only options. Using the `cloudsql_session_read_only` parameter, you can
make the session read-only temporarily, or permanently for the duration of
the session. You can use read-only sessions of this kind
to help protect data integrity in various contexts, including sessions where
you're using Model Context Protocol (MCP) tools, reporting tools, and auditing
tools.

By default, `cloudsql_session_read_only` is set to `off`, which allows
data modification.

## What read-only mode prevents

In a read-only session, the database can't modify any data. Specifically,
read-only mode prevents the following:

- **Read-only mode prevents the generation of new transaction IDs.**
  No writable transactions can be started, and as a result
  no data manipulation language (DML) or data definition language (DDL)
  instructions can be executed. However, if the flag is set in the middle
  of an active writable transaction, then basic DML and DDL instructions can
  be executed until the transaction terminates.

- **Read-only mode prevents the following kinds of statements and calls:**

  - Statements like `VACUUM` and `NOTIFY`.
  - Functions with side effects. This means that you can't call a `FUNCTION` or execute a `PROCEDURE` that is defined as both `VOLATILE` and `LANGUAGE C` or `LANGUAGE internal`, because some of their side effects don't require a transaction ID. Examples are `pg_promote()`, `pg_cancel_backend()`, and `pg_terminate_backend()`.
  - Foreign data wrappers.
  - Any function or procedure that you created in a SQL procedural language (PL).

## Configure read-only status

During a

Cloud SQL for PostgreSQL

session, you can use the `SET` statement to
change the value of the `cloudsql_session_read_only` flag as follows:

- `SET cloudsql_session_read_only = 'on'`

  This statement makes the session read-only, preventing anyone from
  modifying data for as long as the flag remains set to `on`.
  To resume modifying data later in the session, change the value back to
  `off`:
- `SET cloudsql_session_read_only = 'off'`

  Setting the flag back to `off` restores write access in the session
  except when the flag is set to `locked`.
- `SET cloudsql_session_read_only = 'locked'`

  Setting the flag to `locked` makes the session permanently read-only.
  After the flag is set to `locked`, you can't change the flag to any other
  value for the duration of the session.

To make a session permanently read-only as soon as you connect to the database,
embed the `cloudsql_session_read_only` flag in the session's connection string:

```bash
postgresql://USER:PASSWORD@HOST:PORT/DATABASE?options=-ccloudsql_session_read_only=locked
```

Make the following replacements:

- <var translate="no">USER</var>: the username of the user creating the session.
- <var translate="no">PASSWORD</var>: the user's password.
- <var translate="no">HOST</var>: the host URL.
- <var translate="no">PORT</var>: the port number.
- <var translate="no">DATABASE</var>: the database name.

This approach has the added advantage that if you're trying to connect to
an older version of Cloud SQL that lacks support for read-only sessions,
then the connection fails.

You can also deny write access to a specific database user for all future
connections, like this:

```sql
ALTER USER USERNAME SET cloudsql_session_read_only = 'locked';
```

Replace <var translate="no">USERNAME</var> with the username of the user whose access you
want to restrict to read-only.

### Enable specific functions in read-only sessions

- You can enable use of a particular `FUNCTION` or `PROCEDURE` defined as both `VOLATILE` and `LANGUAGE C` or `LANGUAGE internal`. Do this by attaching a `SECURITY LABEL` to the function or procedure that is set to `allow`.
- To disallow a particular function in the session, set a `SECURITY LABEL` statement to `deny`.
- To allowlist functions that don't affect global state, create a predefined
  group of security labels:

  ```sql
  CREATE EXTENSION "google_read_only_session"
  ```

  The `google_read_only_session` extension enables the following:
  - All built-in volatile C functions that are allowed for read-only mode.
  - All functions allowed for read-only mode that are from extensions loaded in the database, including the handler functions for `pl/pgsql` and `pl/v8` languages.

  If you install one or more new extensions afterwards, then to apply group
  labels, drop and recreate the `google_read_only_session` extension:

  ```sql
  > DROP EXTENSION google_read_only_session;
  > CREATE EXTENSION google_read_only_session;
  ```

  The `DROP` command doesn't remove any existing labels. The second
  `CREATE` command just adds labels for the volatile C functions in the new
  extension.

## What's next

- Learn more about [Cloud SQL for PostgreSQL
  flags](https://docs.cloud.google.com/sql/docs/postgres/flags).
- Learn more about [Cloud SQL for PostgreSQL
  extensions](https://docs.cloud.google.com/sql/docs/postgres/extensions).