# MySQL, StarRocks and ClickHouse

ARNscope connects to two kinds of MySQL-family and ClickHouse databases:

- **Databases RDS lists.** Open **RDS**, choose a profile and region, and pick the database. MySQL, MariaDB and Aurora MySQL appear next to PostgreSQL. Nothing needs to be saved first.
- **Saved connections.** Anything RDS does not list: a self-hosted MySQL, StarRocks, ClickHouse, a database in another account. Add one under **Connections** on the RDS page (**Add database connection**), or on the StarRocks page.

Both kinds use the same three routes, the same TLS rules and the same sign-in choices. Saved connections stay on this Mac, in ARNscope's data folder. A password is never saved.

For PostgreSQL, see [RDS and Aurora PostgreSQL](https://arnscope.com/docs/rds-postgresql). For the StarRocks cluster views, see [StarRocks](https://arnscope.com/docs/starrocks).

## Engines

| Engine | Where | Default port | Sign-in |
| --- | --- | --- | --- |
| MySQL, Aurora MySQL | RDS list, or a saved connection (**MySQL**) | 3306 | IAM, Secrets Manager, password |
| MariaDB | RDS list, or a saved connection (**MariaDB**) | 3306 | IAM, Secrets Manager, password |
| StarRocks and other servers that speak MySQL | Saved connection (**StarRocks / MySQL-compatible**) | 9030 | Secrets Manager, password |
| ClickHouse (HTTPS interface) | Saved connection (**ClickHouse**) | 8443 with TLS, 8123 without | Secrets Manager, password |

RDS lists standalone MySQL and MariaDB instances, MySQL Multi-AZ clusters and Aurora MySQL clusters (with writer and reader endpoints).

## Routes

Pick one under **Connect through**:

| Route | What happens | Use it when |
| --- | --- | --- |
| **ARNscope tunnel** | ARNscope connects through one of your [SSM tunnels](https://arnscope.com/docs/ssm-tunnels), inside the app. Nothing listens on a local port. | The database is in a private subnet. |
| **My own tunnel** | ARNscope connects to `127.0.0.1` on the **Local port** you enter. Your tunnel or proxy forwards it to the database. | You already run SSH, SSM or a proxy yourself. |
| **Direct** | ARNscope connects straight to the host from this Mac. | A VPN, peering or office network reaches the database. |

ClickHouse offers **My own tunnel** and **Direct** only. Switching a saved connection to ClickHouse sets its tunnel aside; it comes back if you pick another engine.

Whatever the route, TLS is checked against the database's own host name, even when the connection goes to `127.0.0.1`. So the **Host** field always holds the real server name, as its certificate (and, for IAM, its token) names it.

If the chosen ARNscope tunnel is stopped, **Connect** reads **Start tunnel and connect**: it opens the SSM session first, then connects. How the tunnel is chosen, and the checks that keep a connection on the right instance, are on the [SSM tunnels](https://arnscope.com/docs/ssm-tunnels#choosing-a-tunnel-for-a-database) page.

## TLS

| Mode | In the app | Trusts | Refused when |
| --- | --- | --- | --- |
| `rds` | **RDS certificates** | Only the RDS certificate authorities bundled with ARNscope | The engine is ClickHouse. |
| `system` | **System certificates** | Public certificate authorities | Never. |
| `off` | **Off** | Nothing: plain text | The sign-in is IAM, or the route is an ARNscope tunnel and the host is not `localhost` or `127.0.0.1`. |

- Databases from the RDS list always connect with **RDS certificates**.
- A new saved connection starts on **RDS certificates** for Aurora MySQL or a host ending in `.rds.amazonaws.com`, and on **System certificates** otherwise.
- **Off** is offered only where it is allowed. If a change makes your choice impossible, the form switches and says why, for example "Switched to System certificates: IAM sign-in needs TLS."
- **Off** through an ARNscope tunnel is for a database on the tunnel's own instance (a StarRocks node, for example). The form notes that the connection travels inside the SSM session and never leaves the instance unencrypted. Anywhere else it warns that the password and every query cross the network in plain text.
- ClickHouse with **System certificates** through **My own tunnel** needs the server's DNS name in **Host**, not an IP address: ARNscope sends the request for that name to your local port and checks the certificate against it.

## Sign-in

Choose under **Authentication**.

### IAM (recommended where it applies)

ARNscope signs an RDS IAM authentication token with your profile and region and uses it as the password. Nothing is typed or stored, and the token expires by itself. Offered for MySQL, MariaDB and Aurora MySQL only, and always over TLS.

What you need, outside ARNscope:

1. IAM database authentication turned on for the instance or cluster.
2. A database user that logs in with the AWS plugin (`IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS'` on MySQL). See [Creating a database account using IAM authentication](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.DBAccounts.html).
3. `rds-db:connect` on that user for the role you sign in with. See [the RDS PostgreSQL page](https://arnscope.com/docs/rds-postgresql#the-iam-permission) for the policy format; it is the same for MySQL.

The token names the host and port, so **Host** must be the RDS endpoint, even through a tunnel.

### Secrets Manager

ARNscope reads the secret's current value each time you connect (`secretsmanager:GetSecretValue`) and keeps only its ARN. A rotated password just works on the next connect.

- **Secret** lists the secrets in the profile and region. Secrets whose name, description or tags mention the database or user come first, under **Suggested**. RDS-managed secrets (`rds!…`) are suggested only for an RDS host.
- **JSON key** picks the field that holds the password. ARNscope preselects `password`, `auth_token` or `token`. A plain-text secret (not JSON) is the password itself.
- Picking a secret fills empty fields from it: in a saved connection the host, user and default database (and the port while it is still the engine's default); on the RDS page the user and database.
- **Use the secret's details on every connect** (saved connections): the secret's `host`, `port`, `username` and `dbname` win over the form each time, so a moved or rotated database still connects.

### Typed password

For a saved connection, ARNscope asks for the password each time you connect and never saves it. On the RDS page you type it in the form; it is used for that session only.

### Profile and region

A saved connection always shows **AWS profile** and **AWS region**. IAM signs with them and Secrets Manager reads with them. With a typed password they only scope which tunnels the connection may use.

## Read only and Write

Choose **Read only** or **Write** before you connect. Read sessions get two guards:

1. **The server setting.** MySQL, MariaDB and Aurora MySQL sessions run `SET SESSION TRANSACTION READ ONLY`; if the server refuses it, the connect fails. ClickHouse requests carry `readonly=1`, unless the user's server profile is already read-only. StarRocks has no such statement, so it relies on the guard below.
2. **The keyword guard** (MySQL family and StarRocks). Each statement must start with `SELECT`, `SHOW`, `DESC`, `DESCRIBE`, `EXPLAIN`, `WITH`, `USE`, `HELP` or `TABLE`, and may not contain `INSERT`, `REPLACE`, `UPDATE` or `DELETE` anywhere (the string functions `INSERT(` and `REPLACE(` are fine), nor `INTO OUTFILE` or `INTO DUMPFILE`. `SELECT … FOR UPDATE` counts as a write.

The guard is a safety net on top of the database user's grants. Use a user with read-only grants for read work.

## The console

The SQL console works as described for [PostgreSQL](https://arnscope.com/docs/rds-postgresql#the-sql-console), with these differences:

- A single `SELECT` or `WITH` without `LIMIT` (and, on ClickHouse, without `SETTINGS` or `FORMAT`) gets `LIMIT 500`.
- Each result keeps at most 1,000 rows and 1 MiB. A script returns at most 100 results.
- SQL is limited to 1 MiB. The mysql client's `DELIMITER` command is refused: select a `CREATE PROCEDURE … BEGIN … END` and run it as one statement.
- ARNscope sets no time limit: the server's own `max_execution_time` (MySQL, ClickHouse), `max_statement_time` (MariaDB) or `query_timeout` (StarRocks) decides. **Cancel** stops a running query at any time and keeps the session.
- ClickHouse has no transactions.

## Messages and what to do

### While you fill in a saved connection

Save stays off and the footer says what is missing:

| Message | What to do |
| --- | --- |
| Name the connection. | Enter a name (1 to 100 characters). |
| Enter the database host. | Enter the server's name. |
| Enter a port between 1 and 65535. | Fix **Port**. |
| Enter the local port your tunnel listens on. | Fill **Local port** for **My own tunnel**. |
| Enter the database user. | Fill **User**. |
| Choose how to authenticate. | Pick IAM, a secret (and its key), or **Enter password**. |
| Choose the AWS profile and region. | IAM and Secrets Manager need both. |
| Choose an ARNscope tunnel. | Pick one, or click **New tunnel**. |
| Choose a tunnel that forwards to this host and port. | The tunnel must forward exactly this host and port. |
| Choose the tunnel for localhost:… | For a `localhost` host you pick the tunnel yourself, every time. |
| Choose a tunnel in this connection's AWS profile and region. | Pick a tunnel in the same profile and region, or change them. |
| Save needs the tunnel list. | Wait for it, or **Retry**. |

### When you save

| Message | What to do |
| --- | --- |
| Give the connection a name of 1 to 100 characters. | Shorten or fill the name. |
| Choose a supported database engine. | Use one of the engines above. |
| StarRocks has no IAM login. Use a Secrets Manager secret or type the password. | Switch sign-in. |
| ClickHouse has no IAM login. Use a Secrets Manager secret or type the password. | Switch sign-in. |
| ClickHouse connections cannot go through an ARNscope tunnel yet. Use a local port your own tunnel listens on, or connect directly. | Use **My own tunnel** or **Direct**. |
| ClickHouse is not an RDS engine: choose system TLS (public certificate authorities) or turn TLS off. | Pick **System certificates** or **Off**. |
| IAM authentication sends a token as the password, so it needs TLS. | Turn TLS on, or change sign-in. |
| Through an ARNscope tunnel, TLS can be off only when the tunnel forwards to the database's own instance (host localhost or 127.0.0.1). | Turn TLS on, or use a tunnel to the instance itself. |
| For TLS through a local port, enter the server's DNS name (the one on its certificate), not an IP address. | ClickHouse: put the DNS name in **Host**. |
| Enter a host name or IPv4 address. | ClickHouse: the host may hold letters, digits, `.`, `-` and `_` only. |
| Choose a Secrets Manager secret. | Pick a secret (an ARN like `arn:aws:secretsmanager:…`). |
| Invalid RDS host, Invalid database user, Invalid database, Invalid AWS profile, Invalid AWS region, Invalid secret key | No spaces, no leading `-`, no control characters. The host has no `/` or `:` (put the port in **Port**). |
| The chosen tunnel no longer exists. / The chosen tunnel doesn't forward to this host and port. / The chosen tunnel is in another AWS profile or region than this connection. | Pick another tunnel. |
| Choose the tunnel for this localhost connection yourself. | Pick the tunnel in the list. |
| The tunnels kept changing while you confirmed; check the tunnel and save again. | Check the tunnel and save again. |
| That saved connection no longer exists. | It was deleted elsewhere; add it again. |

Saving can also ask before it moves a `localhost` connection to another instance. See [Same port, other instance](https://arnscope.com/docs/ssm-tunnels#same-port-other-instance-another-database).

### When you connect

| Message | What to do |
| --- | --- |
| Enter the password. | Type it. |
| Start the tunnel first (Tunnels page). / The tunnel is still connecting. Try again in a moment. | Start the tunnel, or wait. |
| This tunnel does not forward to that database endpoint. | Pick a tunnel that forwards this host and port. |
| The tunnel saved with this connection no longer exists. Edit the connection to choose another. | Edit it and choose a tunnel. |
| The database rejected IAM login for '…'. Check that the user is created WITH AWSAuthenticationPlugin AS 'RDS' and that IAM authentication is on for … | Fix the user or turn on IAM authentication (see above). |
| The database rejected the password for '…'. / ClickHouse rejected the password for '…'. | Check the user, the secret or the key. |
| Database '…' does not exist. | Fix **Default database**, or leave it empty. |
| The server does not offer TLS. Turn TLS off only for a local or direct connection you trust. | The server has no TLS: use **Off** only where that is safe. |
| The server asked for a cleartext password; ARNscope only allows that for IAM over TLS. | Use a user with a standard password plugin. |
| TLS certificate does not match …. | **Host** must be the name on the certificate. |
| TLS certificate not trusted: it does not chain to the certificate authorities chosen for this connection. | Use **RDS certificates** for RDS and **System certificates** for public ones. |
| The server did not answer with TLS. Check the port (ClickHouse serves HTTPS on 8443 and plain HTTP on 8123), or turn TLS off for a local or direct connection you trust. | Match the port to the TLS mode. |
| Could not reach the server at …. Check the host, the port and that the tunnel (if any) is running. | Check the route. |
| Connection timed out | The server did not answer within 20 seconds. Check the route and security groups. |
| Could not make the session read-only: … | The server refused `SET SESSION TRANSACTION READ ONLY`. Connect in **Write** mode only if that is safe. |
| The secret has no key "…". / Choose which key of the secret holds the password (none of password, auth_token or token exists). | Pick the right **JSON key**. |
| The secret is plain text, not JSON, so it has no keys. | Clear the key. |
| This secret is binary; ARNscope needs a text or JSON secret. | Store the password as a text or JSON secret. |
| The secret's password is empty. / That key of the secret is not a text value. | Fix the secret. |
| The secret's connection details are not usable: … | The secret's `host`, `port`, `username` or `dbname` is invalid; fix it or turn off **Use the secret's details on every connect**. |
| AWS profile '…' lacks permission to read the secret (secretsmanager:GetSecretValue). | Allow `secretsmanager:GetSecretValue` on the secret. |
| This AWS profile is not authenticated. Run `aws sso login` for the profile, then try again. | Sign in to the profile. |

### When a read session refuses a statement

| Message | Meaning |
| --- | --- |
| This session is read-only: ARNscope runs only SELECT, SHOW, DESCRIBE, EXPLAIN, WITH, USE and HELP here, not …. Connect with write access to change data or schema. | The statement does not start with a read keyword. |
| This session is read-only, and this statement contains … (SELECT … FOR UPDATE counts too). Connect with write access to change data. | A write keyword appears inside the statement. |
| This session is read-only, and ClickHouse refused the statement: … | ClickHouse's own `readonly` setting refused it. |

## Permissions

| What | Actions |
| --- | --- |
| List MySQL, MariaDB and Aurora MySQL | `rds:DescribeDBInstances`, `rds:DescribeDBClusters` |
| IAM sign-in | `rds-db:connect` on the database user |
| List secrets | `secretsmanager:ListSecrets` |
| Read a secret's keys and value | `secretsmanager:GetSecretValue` |
| Connect through an ARNscope tunnel | The tunnel's permissions, on the [SSM tunnels](https://arnscope.com/docs/ssm-tunnels#permissions) page |

## Limitations

- IAM sign-in is for MySQL, MariaDB and Aurora MySQL. StarRocks and ClickHouse sign in with a secret or a typed password.
- ClickHouse connects over its HTTP(S) interface only, and not through an ARNscope tunnel yet: use **My own tunnel** or **Direct**.
- **System certificates** means public certificate authorities. A server whose certificate comes from a private CA is not trusted.
- The read-only keyword guard is a check on the statement's words, not a parser of every dialect. Grant read-only users for read work.
- StarRocks read sessions have no server-side read-only setting; the keyword guard is the check ARNscope adds.
- Saved connections live on this Mac. They are not synced between Macs.
- Metrics and Top SQL are for databases in the RDS list, not for saved connections.

Need another engine, sign-in or route? Write to [hello@arnscope.com](mailto:hello@arnscope.com).

Source: https://arnscope.com/docs/mysql-clickhouse
