> For the complete documentation index, see [llms.txt](https://docs.p0.dev/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.p0.dev/integrations/resource-integrations/postgresql/requesting-postgresql-access.md).

# Requesting PostgreSQL access

How to request just-in-time access to PostgreSQL instances through P0. The way you request access depends on whether your instance is hosted on Amazon RDS or Google Cloud SQL.

## Amazon RDS

P0 recommends using the P0 CLI to request access to a PostgreSQL instance on Amazon RDS. You can request access in one of two ways:

* **A database role.** Request an existing PostgreSQL role. P0 grants you that role, with whatever privileges the role already holds.
* **SQL-scoped access.** Give P0 the SQL you plan to run. P0 parses it and grants only the schema and table privileges that script requires.

Both methods use the same `p0 aws rds generate-db-auth-token` command and produce the same `psql` connection instructions once the request is approved. Request a role when you want a predefined set of privileges, or SQL-scoped access when you want least-privilege access shaped to a specific query.

#### Via the P0 CLI <a href="#via-the-p0-cli" id="via-the-p0-cli"></a>

**Prerequisites:**

* An installed [PostgreSQL](https://docs.p0.dev/integrations/resource-integrations/postgresql) P0 integration on the instance
* P0 access policies that allow you to request access to the PostgreSQL instance
* P0 CLI version 0.28.0 or higher, which is the first release that supports SQL-scoped access
* An open CloudShell terminal in the instance's VPC
  * Open a CloudShell instance by navigating to the instance's page within the "RDS" service in the AWS cloud console, then choosing "CloudShell" under "Connectivity & security", then clicking "Launch CloudShell"

**Listing requestable fields**

You can list requestable instances, roles, and databases with the commands below. In order, they list:

* instances and clusters
* roles
* databases within an instance

```bash
p0 ls postgres role instance
p0 ls postgres role name --instance "${INSTANCE}"
p0 ls postgres role database --instance "${INSTANCE}"
```

#### Request a database role <a href="#request-a-database-role" id="request-a-database-role"></a>

Request an existing PostgreSQL role, and P0 grants you that role for the duration of the access.

From the CLI, execute:

```bash
p0 aws rds generate-db-auth-token --arch postgres --role "${ROLE}" --instance "${INSTANCE}"
```

where `<role>` is the name of the role you want to access, and `<instance>` is the identifier configured in P0 when the instance was installed in P0.

After executing `p0 aws rds generate-db-auth-token`, P0 creates an access request. Have the request approved. Once it's approved, the command emits instructions to connect to the instance.

*Example:*

<a class="button secondary">Copy</a>

```bash
export RDS_SSL_CA='/certs/global-bundle.pem'
export RDS_HOST=<hostname>
export PGPASSWORD=<password>

psql "host=${RDS_HOST} port=5432 sslmode=verify-full sslrootcert=${RDS_SSL_CA} dbname=postgres user=p0_john_doe"
```

Copy these instructions.

Now, navigate to the open CloudShell terminal, and execute these instructions. You now have access to the instance.

#### Request SQL-scoped access <a href="#request-sql-scoped-access" id="request-sql-scoped-access"></a>

Instead of naming a role, give P0 the SQL you plan to run and let it grant only the privileges that script requires. Pass your script to `--sql` in place of `--role`:

```bash
p0 aws rds generate-db-auth-token --arch postgres --sql "${QUERY}" --instance "${INSTANCE}" --database "${DATABASE}"
```

where `<query>` is a SQL script of one or more statements. Specify either `--role` or `--sql`, not both.

P0 parses the script and requests the least-privilege grants needed to run it. Each schema the script touches gets `USAGE`, and a schema gets `CREATE` when the script creates a relation in it. Table privileges depend on the statement:

| Statement | Privileges P0 requests on each table |
| --------- | ------------------------------------ |
| `SELECT`  | `SELECT`                             |
| `INSERT`  | `INSERT`                             |
| `UPDATE`  | `SELECT` and `UPDATE`                |
| `DELETE`  | `SELECT` and `DELETE`                |

From there the flow matches a role request: P0 creates an access request, and once it's approved the command emits the same `psql` connection instructions.

{% hint style="warning" %}
P0 grants only privileges it can delegate. It can't grant object ownership, so statements that need ownership, such as `ALTER` or `DROP`, aren't supported. P0 parses ANSI SQL only.
{% endhint %}

SQL-scoped access uses the connector deployed with the integration. If you haven't connected the instance yet, see [Installing an RDS instance](/integrations/resource-integrations/postgresql/installing-an-rds-database.md).

## Google Cloud SQL

{% hint style="warning" %}
Cloud SQL for MySQL is not supported. This integration manages PostgreSQL instances only.
{% endhint %}

#### Requesting access <a href="#cloudsql-requesting-access" id="cloudsql-requesting-access"></a>

Users request access to a Cloud SQL instance through the P0 app or a P0 notifier such as Slack. Select the **GCP CloudSQL** resource, choose the VPC and instance, and submit the request. After an approver grants the request, P0 provisions the requestor's IAM database user and delegates the `roles/cloudsql.instanceUser` role for the approved duration.

The requestor then connects to the instance as their IAM database user using [GCP IAM database authentication](https://docs.cloud.google.com/sql/docs/postgres/iam-logins).

{% hint style="info" %}
The P0 CLI does not provide a Cloud SQL connection command. Request Cloud SQL access from the P0 app or a notifier.
{% endhint %}

## PostgreSQL roles

Roles in PostgreSQL are a fundamental part of database access control and permission management. They define and group users, letting you control who can access the database and what actions they can perform.

When you grant access to a PostgreSQL instance, consider the specific roles that need access and the required permissions. You can grant various privileges to roles, including `SELECT`, `INSERT`, `UPDATE`, `DELETE`, and `CREATE`. Careful management of roles and permissions is crucial for maintaining the security and integrity of your database.

PostgreSQL provides a set of predefined roles that grant commonly needed privileged capabilities and information. See the [PostgreSQL predefined roles documentation](https://www.postgresql.org/docs/current/predefined-roles.html) for the full list.

{% hint style="warning" %}
For security reasons, P0 cannot manage roles that have the `superuser` status (also called the superuser attribute). You cannot request access to a `superuser` role through P0, and such roles are not listed as available roles in P0.
{% endhint %}

For GCP Cloud SQL PostgreSQL instances, `cloudsqladmin` is a superuser role by default, but `cloudsqlsuperuser` is not (though it does grant many other permissions).

P0 recommends creating your own custom, least-privileged roles to use with P0. For more on role management, see the [PostgreSQL user management documentation](https://www.postgresql.org/docs/current/user-manag.html).