Requesting PostgreSQL access

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

Amazon RDS

P0 recommends using the P0 CLI to request access to a PostgreSQL database 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

Prerequisites:

• An installed PostgreSQL P0 integration on the database

• P0 access policies that allow you to request access to the PostgreSQL database

• P0 CLI version 0.28.0 or higher, which is the first release that supports SQL-scoped access

• An open CloudShell terminal in the database's VPC

  • Open a CloudShell instance by navigating to the database'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

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

Request a database role

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

From the CLI, execute:

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:

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 will receive access to the database.

Request SQL-scoped access

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:

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.

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.

SQL-scoped access uses the connector deployed with the integration. If you haven't connected the database yet, see Installing an RDS database.

Google Cloud SQL

Cloud SQL for MySQL is not supported. This integration manages PostgreSQL instances only.

Requesting access

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.

The P0 CLI does not provide a Cloud SQL connection command. Request Cloud SQL access from the P0 app or a notifier.

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 database, 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 for the full list.

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.

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.