# RDS and Aurora PostgreSQL

ARNscope lists the PostgreSQL databases in your account and connects to them with **IAM database authentication**: it asks AWS for a short-lived token and uses it as the password. There are no database passwords to type, store or rotate. Once connected you get a SQL console, a browsable object tree, query history and a query plan view.

<figure class="doc-video">
<video controls playsinline preload="none" poster="/media/docs/rds/rds-explainer.webp?v=1" aria-label="The RDS console in ARNscope: a narrated tour of under two minutes">
<source src="/media/docs/rds/rds-explainer.webm?v=1" type="video/webm">
<source src="/media/docs/rds/rds-explainer.mp4?v=1" type="video/mp4">
<track kind="captions" srclang="en" label="English" src="/media/docs/rds/rds-explainer.vtt?v=1" default>
</video>
<figcaption>A narrated tour of under two minutes, with captions: connecting through a tunnel, the object tree, completion, results, plans, transactions, favourites, Top SQL and metrics. Sample data.</figcaption>
</figure>

## Which databases appear

Open **RDS** and choose a profile and region. ARNscope lists:

- **RDS instance**: standalone RDS for PostgreSQL instances (instances that belong to a cluster are reached through the cluster).
- **Multi-AZ cluster**: RDS for PostgreSQL Multi-AZ DB clusters.
- **Aurora PostgreSQL**: Aurora PostgreSQL clusters, with writer and reader endpoints.

MySQL, MariaDB and Aurora MySQL are listed too; see [MySQL, StarRocks and ClickHouse](https://arnscope.com/docs/mysql-clickhouse). SQL Server, Oracle and Db2 are not listed.

## Set up the database for IAM authentication

You do this once per database, outside ARNscope.

1. **Enable IAM database authentication** on the DB instance or cluster.
2. **Create a database user and grant it the `rds_iam` role.** Connect as the master user or another user who can create users and grant privileges:

   ```
   CREATE USER ops_readonly;
   GRANT rds_iam TO ops_readonly;
   GRANT CONNECT ON DATABASE commerce TO ops_readonly;
   GRANT USAGE ON SCHEMA public TO ops_readonly;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO ops_readonly;
   ```

   The first two lines are what IAM authentication needs. The rest are ordinary PostgreSQL grants for what the user may read. What the IAM role can reach through the user is limited to what the database user can reach. The name must match the case of the name in the IAM policy.
3. **Allow your IAM role to connect as that user** with `rds-db:connect` (next section).

A PostgreSQL user that has the `rds_iam` role must log in with IAM: password login stops working for it, including for the master user. See [Creating a database account using IAM authentication](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.DBAccounts.html).

### The IAM permission

The role you sign in with needs `rds-db:connect` on the database user:

```
{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": "rds-db:connect",
    "Resource": "arn:aws:rds-db:us-east-1:123456789012:dbuser:db-ABCDEFGHIJKL01234/ops_readonly"
  }]
}
```

The resource format is `arn:aws:rds-db:<region>:<account-id>:dbuser:<DbiResourceId>/<database-user>`. For an instance, `DbiResourceId` is its **Resource ID** (it starts with `db-` and does not change). For Aurora, use the cluster's `DbClusterResourceId`. `rds-db:connect` is not an `rds:` action and does not appear in the console's usual RDS permissions. See [Creating and using an IAM policy for IAM database access](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.IAMPolicy.html).

## Connect

1. Open **RDS**, choose the database, and stay on the **Connect** tab.
2. Choose the **Mode**: **Read only** or **Write**.
3. If the database has a reader endpoint, choose what the tunnel forwards to: **Writer endpoint** or **Reader endpoint**.
4. Choose how you reach the database (below).
5. Enter the **Database user** and the **Database**, and click **Connect**.

ARNscope remembers each connection (user, database, mode and tunnel) under **Previous connections** on this Mac, so next time one click on **Connect** is enough. You can forget one with the trash button.

### Through an ARNscope tunnel

If you have an [SSM tunnel](https://arnscope.com/docs/ssm-tunnels) that forwards to this endpoint, a **Tunnel** picker appears with **ARNscope tunnel** and **My own tunnel**. Pick the tunnel and, if it is stopped, click **Start tunnel** (this opens an SSM session). ARNscope then connects inside the app and nothing listens on a local port. The tunnel must forward to the endpoint you chose, writer or reader. If you have an RDS tunnel that goes to a different endpoint, ARNscope tells you which one it reaches.

<figure class="doc-clip">
<video data-clip controls muted loop playsinline preload="none" poster="/media/docs/rds/rds-connect.webp?v=1" aria-label="Choosing the writer endpoint and the ARNscope tunnel, starting the tunnel and connecting read-only.">
<source src="/media/docs/rds/rds-connect.webm?v=1" type="video/webm">
<source src="/media/docs/rds/rds-connect.mp4?v=1" type="video/mp4">
</video>
<figcaption>Choose the endpoint and <strong>ARNscope tunnel</strong>, start the tunnel and connect: the console header shows <strong>Read only</strong> and <strong>Connected</strong>.</figcaption>
</figure>

### Through your own tunnel

Start your own tunnel (SSM, SSH or anything else) that forwards a local port to the endpoint and port shown on the page, then enter that **Local tunnel port**. ARNscope connects to `127.0.0.1` on that port and checks that something is listening. The TLS certificate is still checked against the RDS endpoint name, so use the real endpoint in the tunnel, not a custom DNS name.

### What happens when you connect

- ARNscope generates an IAM authentication token with `aws rds generate-db-auth-token` using your profile and region, and uses it as the password.
- The connection uses TLS and is required to. ARNscope trusts only the AWS RDS certificate authorities bundled with the app and verifies the endpoint name.
- In **Read only** mode, the session starts with `default_transaction_read_only=on`, so the database rejects writes even if the user could write. A read-only session is a safety net on top of the database user's grants, not a replacement for them.
- A **Write** session can change data. The page and the console header show **Write** so you can tell at a glance.
- The connection is checked with `SELECT 1` every 30 seconds while the RDS page is in view. If it drops (a closed tunnel, sleep or a network change), the console shows **Disconnected** and a **Reconnect** button.

## The SQL console

After connecting, the **Query** tab has:

| Feature | Details |
| --- | --- |
| Editor | SQL editor with highlighting and completion from the database's objects: keywords (upper case), tables, views, functions and procedures, and the columns of the tables in the statement, each captioned with its table. A column that more than one of those tables has is inserted with the table's alias, for example `o.customer_id`. Ctrl+Cmd+L formats the statement at the cursor, or the selection. Your text is saved per database on this Mac and survives restarts. |
| Run | **Run** executes the statement at the cursor, or the selection. **Run all** executes everything. Shortcut: Cmd+Enter. A single `SELECT` without `LIMIT`, `FETCH` or `INTO` gets `LIMIT 500` added; write `LIMIT ALL` to get every row. |
| Results | A grid with sorting, copy and export. At most 1,000 rows and 1 MiB are kept per result; the header says when a result was cut. Several statements show several results. |
| Time limit | ARNscope sets none: a query runs until it finishes, the database's own `statement_timeout` stops it, or you press **Cancel**. The toolbar shows how long the query has been running. |
| Transactions | Statements commit on their own (**Auto-commit**). **Begin**, **Commit** and **Roll back** group them, and the header shows **Transaction open** while one is open. |
| Explain | **Explain plan** shows the estimated plan as a diagram without running the statement; the most expensive step is marked in red. **Explain analyze** runs the statement and adds real timings and row counts, and where the planner's estimate was off. It is refused, before anything runs, for anything but a plain `SELECT` (a `SELECT` with an `INSERT`, `UPDATE`, `DELETE` or `MERGE` in a `WITH` is refused too). |
| Database tree | **Database objects** on the right lists schemas, tables, views, functions, indexes and more, filterable. Open a table to browse 200 rows per page with a `WHERE` filter and sorting. **View source** shows an object's definition. The tree is saved and refreshed on demand. |
| History | **Query history** keeps the last 500 statements per database on this Mac, as they ran (with an added `LIMIT 500` or the `EXPLAIN` wrapper), with duration, row count and result. Click one to add it to the console. |
| Favourites | The star saves the statement at the cursor, with an optional label, under **Favourites** in the target list. A favourite remembers its profile, region, target and database: opening it switches there and adds the SQL to the console. It never runs on its own. |
| Switching | The database menu in the header switches to another database on the same server. |

<figure class="doc-clip">
<video data-clip controls muted loop playsinline preload="none" poster="/media/docs/rds/rds-completion.webp?v=1" aria-label="Typing a query: keyword, table and column completion, a shared column inserted with its alias, and a procedure.">
<source src="/media/docs/rds/rds-completion.webm?v=1" type="video/webm">
<source src="/media/docs/rds/rds-completion.mp4?v=1" type="video/mp4">
</video>
<figcaption>Completion offers keywords, tables and views, and columns captioned with their table; a column two joined tables share comes in as <code>o.customer_id</code>.</figcaption>
</figure>

<figure class="doc-clip">
<video data-clip controls muted loop playsinline preload="none" poster="/media/docs/rds/rds-format.webp?v=1" aria-label="Formatting a one-line query with Ctrl+Cmd+L.">
<source src="/media/docs/rds/rds-format.webm?v=1" type="video/webm">
<source src="/media/docs/rds/rds-format.mp4?v=1" type="video/mp4">
</video>
<figcaption>Ctrl+Cmd+L formats the statement at the cursor, keywords in upper case.</figcaption>
</figure>

<figure class="doc-clip">
<video data-clip controls muted loop playsinline preload="none" poster="/media/docs/rds/rds-run-results.webp?v=1" aria-label="Running the statement at the cursor, sorting a column, copying a cell as JSON and opening a JSON value in the value viewer.">
<source src="/media/docs/rds/rds-run-results.webm?v=1" type="video/webm">
<source src="/media/docs/rds/rds-run-results.mp4?v=1" type="video/mp4">
</video>
<figcaption>Cmd+Enter runs the statement under the cursor. The grid sorts, copies as JSON, exports to CSV, and shows JSON values in the value viewer.</figcaption>
</figure>

<figure class="doc-clip">
<video data-clip controls muted loop playsinline preload="none" poster="/media/docs/rds/rds-explain-plan.webp?v=1" aria-label="Explain plan: the estimated plan as a diagram, then the details of the Seq Scan on orders.">
<source src="/media/docs/rds/rds-explain-plan.webm?v=1" type="video/webm">
<source src="/media/docs/rds/rds-explain-plan.mp4?v=1" type="video/mp4">
</video>
<figcaption><strong>Explain plan</strong> draws the estimated plan; the costliest step is red. Click a step for its details.</figcaption>
</figure>

<figure class="doc-clip">
<video data-clip controls muted loop playsinline preload="none" poster="/media/docs/rds/rds-explain-analyze.webp?v=1" aria-label="Explain analyze: execution time and real row counts, then the refusal for an UPDATE.">
<source src="/media/docs/rds/rds-explain-analyze.webm?v=1" type="video/webm">
<source src="/media/docs/rds/rds-explain-analyze.mp4?v=1" type="video/mp4">
</video>
<figcaption><strong>Explain analyze</strong> shows real timings and misestimates, and refuses a write before anything runs.</figcaption>
</figure>

<figure class="doc-clip">
<video data-clip controls muted loop playsinline preload="none" poster="/media/docs/rds/rds-tree-source.webp?v=1" aria-label="Browsing the database tree, filtering it, opening a cron job's source and a table's rows.">
<source src="/media/docs/rds/rds-tree-source.webm?v=1" type="video/webm">
<source src="/media/docs/rds/rds-tree-source.mp4?v=1" type="video/mp4">
</video>
<figcaption>The database tree: expand a table, filter, open a cron job's source, and browse a table's rows.</figcaption>
</figure>

<figure class="doc-clip">
<video data-clip controls muted loop playsinline preload="none" poster="/media/docs/rds/rds-transactions.webp?v=1" aria-label="Begin, Commit, Begin again and Roll back, with the transaction chip changing.">
<source src="/media/docs/rds/rds-transactions.webm?v=1" type="video/webm">
<source src="/media/docs/rds/rds-transactions.mp4?v=1" type="video/mp4">
</video>
<figcaption><strong>Begin</strong> opens a transaction; the chip shows <strong>Transaction open</strong> until you commit or roll back.</figcaption>
</figure>

<figure class="doc-clip">
<video data-clip controls muted loop playsinline preload="none" poster="/media/docs/rds/rds-favourites.webp?v=1" aria-label="Adding the statement at the cursor to favourites with a label, then finding it under Favourites.">
<source src="/media/docs/rds/rds-favourites.webm?v=1" type="video/webm">
<source src="/media/docs/rds/rds-favourites.mp4?v=1" type="video/mp4">
</video>
<figcaption>Star a statement and give it a label; it appears under <strong>Favourites</strong>.</figcaption>
</figure>

The **Metrics** and **Top SQL** tabs read CloudWatch and Performance Insights (or `pg_stat_statements` when connected). **Metrics** also opens as a panel beside the console, from the strip on the right. Top SQL can hand a statement to the console, which adds it for you to review and never runs it.

<figure class="doc-clip">
<video data-clip controls muted loop playsinline preload="none" poster="/media/docs/rds/rds-top-sql.webp?v=1" aria-label="Top SQL from Performance Insights, then pg_stat_statements, then Open in console.">
<source src="/media/docs/rds/rds-top-sql.webm?v=1" type="video/webm">
<source src="/media/docs/rds/rds-top-sql.mp4?v=1" type="video/mp4">
</video>
<figcaption>Top SQL ranks statements by load (Performance Insights) or time (<code>pg_stat_statements</code>); <strong>Open in console</strong> adds one for review.</figcaption>
</figure>

<figure class="doc-clip">
<video data-clip controls muted loop playsinline preload="none" poster="/media/docs/rds/rds-metrics.webp?v=1" aria-label="Opening the Metrics panel beside the console, then the Metrics tab.">
<source src="/media/docs/rds/rds-metrics.webm?v=1" type="video/webm">
<source src="/media/docs/rds/rds-metrics.mp4?v=1" type="video/mp4">
</video>
<figcaption>The Metrics panel sits beside the console; the <strong>Metrics</strong> tab shows the full set.</figcaption>
</figure>

## Why IAM authentication

IAM authentication is the best way to sign in to a database from a tool like ARNscope:

- **No long-lived password.** Nothing to store, share, rotate or leak. ARNscope never sees or saves a database password.
- **Short-lived tokens.** An authentication token is valid for 15 minutes, and it is only used to open the connection: it does not limit how long an established session lasts. Temporary credentials must still be valid when you connect.
- **Access in IAM.** Grant and revoke access with IAM policies and roles, per database user, instead of managing it separately on each database. Access follows your SSO or role session.
- **Encrypted.** Traffic to the database is encrypted with TLS.

Be aware of the trade-offs, which AWS documents:

- RDS **CloudTrail and CloudWatch do not log IAM database authentication**, and they do not track `generate-db-auth-token` calls. IAM controls who can connect, but connection attempts are not recorded in CloudTrail. Use database logs for that.
- IAM authentication needs compute on the database. AWS recommends 300 to 1000 MiB of extra memory for reliable connections.
- It does not support all global condition keys, including `aws:SourceIp`, `aws:SourceVpc` and `aws:SourceVpce`.
- AWS recommends it as a mechanism for temporary, personal access, and not for workloads that open more than 256 new connections per second. A person using a console is the intended case.

Sources: [IAM database authentication](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.html), [Aurora IAM database authentication](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/UsingWithRDS.IAMDBAuth.html), and the AWS Database Blog post [Use IAM authentication to connect with SQL Workbench/J](https://aws.amazon.com/blogs/database/use-iam-authentication-to-connect-with-sql-workbenchj-to-amazon-aurora-mysql-or-amazon-rds-for-mysql/) for the connection-rate guidance.

## Requirements

- The AWS CLI installed. ARNscope runs `aws rds generate-db-auth-token` to create the token. It looks for `aws` on the app's `PATH`, then in `/opt/homebrew/bin`, `/usr/local/bin` and `/usr/bin`.
- An AWS profile that can sign in (SSO or keys). The token is signed with your credentials, which must still be valid when the connection opens.
- A route to the database: an ARNscope tunnel or a tunnel of your own.

## Permissions

| What | IAM actions |
| --- | --- |
| List databases | `rds:DescribeDBInstances`, `rds:DescribeDBClusters` |
| Connect | `rds-db:connect` on the database user (see above) |
| Metrics tab | `rds:DescribeDBInstances`, `rds:DescribeDBClusters`, `cloudwatch:GetMetricData` |
| Top SQL with Performance Insights | `pi:GetResourceMetrics`, `pi:DescribeDimensionKeys`, `pi:GetDimensionKeyDetails` |
| Start an ARNscope tunnel | See [SSM tunnels](https://arnscope.com/docs/ssm-tunnels) |

The [IAM permission check](https://arnscope.com/docs/iam-permission-check) also lists `secretsmanager:GetSecretValue` next to connecting. ARNscope does not read a secret to connect; you can ignore that line.

## Limitations

- **IAM authentication only.** ARNscope does not connect with a database password and does not read passwords from Secrets Manager. The database user needs the `rds_iam` role.
- **This page covers PostgreSQL.** RDS for PostgreSQL, Multi-AZ clusters and Aurora PostgreSQL. MySQL, MariaDB and Aurora MySQL are on [their own page](https://arnscope.com/docs/mysql-clickhouse). SQL Server, Oracle and Db2 are not supported.
- The database must be reachable through a tunnel. ARNscope does not connect to a database directly over the network, and it does not connect through RDS Proxy.
- The AWS CLI must be installed.
- Results are capped at 1,000 rows or 1 MiB, and statements over 1 MiB are refused. Use the table view or add `LIMIT` and `OFFSET` for more.
- **Explain analyze** only analyzes a single plain `SELECT`. To analyze a write, type `EXPLAIN (ANALYZE, FORMAT JSON)` yourself inside `BEGIN … ROLLBACK`.
- A single `SELECT` without a `LIMIT` stops at 500 rows unless you write `LIMIT ALL`; results are still capped at 1,000 rows or 1 MiB.
- ARNscope keeps one database connection open at a time. Connecting to another database, or switching databases on the server, closes the current one.
- Database connections are available in the desktop app only, not in the web preview.

Need another mode? Write to [hello@arnscope.com](mailto:hello@arnscope.com) and tell us what you use.

Source: https://arnscope.com/docs/rds-postgresql
