Requesting Access

Requesting from Slack

Open up the p0 request modal by typing the command /p0 request in any Slack channel and select PostgreSQL as the resource:

You'll see an "Access type" field with the option "Role".

• "Role": Select this option if you would like to request access to an existing PostgreSQL role. See Role Requests for details.

Role Requests

This will grant you the role for a certain amount of time and automatically remove the binding when the access expires.

PostgreSQL instance: The SQL instance you would like access to.

Role Name: The PostgreSQL role name is defined to control access and permissions within the database. The "Role Name" is typically associated with specific users or groups and determines their privileges and restrictions within the PostgreSQL instance. Please provide the appropriate "Role Name" associated with your access request; see PostgreSQL Roles for more details.

Reason: Optionally, provide a reason to be communicated to the approver(s).

Then click the button to submit the request, and see Next Steps.

Next Steps

Once you submit the request, you will get a Slack message from the p0 bot confirming your request creation. The p0 bot will also send a message to the approvers in the Slack channel designated by your org admin.

1. If your request is approved, you will receive a message from the p0 bot saying that your access has been granted and letting you know when it will expire. You can go ahead and use the permission.

2. If you are on-call (on a PagerDuty schedule), and your org admin has enabled PagerDuty routing, your access may be automatically approved for 1 hour.

3. After your request is approved, you'll see a "relinquish" button on the Slack message from the p0 bot. You can optionally use this button to let go of your access early if you finish what you want to do before the expiration date. This will revoke the access, and you must make another request if you need it again.

4. If you wait for the access to expire, you will get a message that it has expired once it does.

5. If your request is denied, you'll receive a message.

Using your CloudSql grant with the Google Cloud Console

1. Open Cloud SQL in the Google Cloud Console or, with the google cloud CLI

Using your CloudSql grant with the Google Cloud CLI

1. If you are not already authenticated in GCP user for which you requested the grant, run gcloud auth login and authenticate as the appropriate user

2. Run gcloud sql generate-login-token to generate a login token, and copy the output.

3. Run gcloud sql connect INSTANCE_NAME --user=USER_NAME --database=DATABASE_NAME, replacing INSTANCE_NAME, DATABASE_NAME and USER_NAME with the appropriate values. USER_NAME will be the email address you use to log into GCP.

Using your RDS grant with the AWS CLI (Identity Center Users):

1. In your request approval notification or in the Web interface, find the permission set that you have been given access through.

1. With the aws cli installed, run aws configure sso and follow the prompts, selecting the account in which the RDS database exists, and the role from step 6 above.

2. To store the password needed to authenticate to the RDS database with IAM run the following, replacing HOSTNAME, PORT, and REGION with the hostname, port, and region of your RDS database, and replacing USERNAME with your email address in all lower case.

   Copy

   export PGPASSWORD=$(aws rds generate-db-auth-token --hostname HOSTNAME --port PORT --region REGION --username USERNAME)

3. To connect to the database run the following, replacing DBNAME with the name of the database to which you are connecting and all the other fields exactly as in step 8

   Copy

   psql "host=HOSTNAME port=PORT user=USERNAME dbname=DBNAME sslmode=require"

PostgreSQL Roles

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

When granting access to a PostgreSQL database, consider the specific role(s) that need access and the required permissions. Roles can be granted various privileges, including SELECT, INSERT, UPDATE, DELETE, CREATE, and more. 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 provide access to certain, commonly needed, privileged capabilities and information. Refer to https://www.postgresql.org/docs/current/predefined-roles.html for predefined roles.

Note that, for security reasons, P0 cannot manage roles that have the superuser status (also called the superuser attribute); this means that it is not possible to request access to a superuser role using P0, and such roles will not be listed as available roles in P0.

Notably, for GCP CloudSQL-based Postgres instances, by default cloudsqladmin is a superuser role, but cloudsqlsuperuser is not (though it does grant plenty of permissions otherwise).

You are encouraged to create your custom least-privileged roles to use with p0.

Reference: https://www.postgresql.org/docs/current/user-manag.html