Just-in-time ephemeral database access

At a glance

• Just-in-time database access replaces shared, long-lived database logins with temporary access scoped to one task.
• With P0, an engineer requests a role or a query from the P0 CLI and gets only what that task needs.
• A fresh database user and password are created per request and removed on expiry, so credentials rotate automatically.
• Every session is attributed to a named engineer.

Databases are present in almost every internet-facing application. As a critical piece of infrastructure they require maintenance, troubleshooting, migrations, and other manual interventions by engineers. They bring their own authentication schemes, which is often a username-password, and they do not play well together with industry standards such as OpenID Connect. Most organizations manage engineering access to databases by creating shared users and credentials due to the difficulty of provisioning users and distributing passwords.

On the authorization side it’s much work to configure fine-grained roles for the different maintenance and troubleshooting tasks. This practice is detrimental to the security and audit-ability of databases. The net outcome is long-lived shared passwords with high levels of access to a critical piece of infrastructure. Shared users also make database access control and audit nearly impossible.

The P0 approach

P0 streamlines user provisioning and allows using short-lived, least-privileged roles based on the intent of the engineer. Let’s take a look at the general approach P0 takes to database access!

The components in the P0 access flow are:

• The P0 CLI, that the human user runs on their local computer. It establishes connection to the database using generated credentials.
• The P0 service, which is the control plane for granting the appropriate database role to the user
• The P0 agent, that is responsible for creating a user and generating credentials inside the database. The agent can be deployed as a serverless function (AWS Lambda, Google Cloud Function, Azure Function).

Note that P0 uses an agent that acts as an intermediary between the organization’s cloud services and the P0 service. This agent is not a proxy. It enhances the security of the P0 integration but it does not sit between the database and the user.

The flow starts by the user requesting access to the database from the P0 CLI either by specifying a pre-existing role they want to assume or by simply providing the query they want to run.

The P0 CLI detects the type of resource the user is trying to access: in this case it’s a PostgreSQL database. It authenticates the user with P0 service and sends the request information to the P0 service. The P0 service identifies the permissions needed to execute the query by parsing it and extracting the objects and types of operations involved. It then decides whether to grant access to the user based on the routing rules configured. For instance, certain tables may require approval from another user, while access to some other tables is granted automatically. See P0’s access policy docs for all options.

Access provisioning

Once access is granted, the P0 service contacts the P0 agent to create a dedicated database user that corresponds to the requestor. The purpose of the agent is twofold. It provides connection between P0’s cloud-based service and the database which is most often not exposed on the public internet. Equally importantly, the agent safeguards against P0 escalating its own permissions in the database. This is important in order to mitigate the vendor risk that is inherent when delegating permissions management to an external party. The agent addresses two security risks:

1. P0 has a service user inside the database that has permissions to grant roles to users. P0 could escalate its own privileges and the agent prevents that by intercepting and denying such access requests.
2. P0’s service user is able to create new users which could be abused by an attacker who has compromised P0 to ask the system to create a user for themselves. The agent generates a password for the database user but this password is not accessible to an attacker inside P0 because only an authenticated end user can decrypt it.

The P0 CLI generates a key pair on behalf of the end user. The public key and the ID token of the user are sent to the P0 agent. The agent verifies the ID token of the requestor to make sure that the request originated from an authenticated user of the organization. The agent can authenticate as the P0 service user in the database, and has permissions to create users and roles, and to grant roles to users. It creates a database user and generates a password which it encrypts with the public key. The P0 agent also creates an ephemeral role (in case of SQL-based access requests) in the database and grants the role to the new user. The user name, the encrypted password, and the generated role are then returned to the P0 service.

The P0 service forwards the user and the encrypted password to the P0 CLI, which transparently injects the database user name and the decrypted password into the database command.

Once access expires the role and the database user are removed by the P0 service. For new access requests the user is re-created with a new password, so each request gets new ephemeral database credentials and database credential rotation happens by design rather than on a schedule.

How to request just-in-time database access with P0

Here is JIT database access for PostgreSQL with P0, step by step:

1. Install the integration. Connect your PostgreSQL database on AWS RDS or Google Cloud SQL using the PostgreSQL integration guide.
2. Set access policy. In Policy Studio, define who can request which roles or queries, and who approves.
3. Request access from the P0 CLI. With P0 CLI version 0.25.0 or higher, list what you can request, then request a role:

p0 ls postgres role instance

p0 ls postgres role name –instance “${INSTANCE}”

p0 aws rds generate-db-auth-token –arch postgres –role “${ROLE}” –instance “${INSTANCE}”

Or request only what one SQL query needs (add –instance and –database as needed; see the p0 request reference):

p0 request postgres sql “SELECT * FROM orders WHERE created_at > now() – interval ‘1 day'”

1. Connect and work. Once the request is approved, the command emits connection instructions. Run them from a terminal in the database’s VPC (the docs use AWS CloudShell) to connect with psql. Access is removed when it expires.

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”

One limitation to know: P0 grants only privileges it can delegate, so statements that need object ownership, such as ALTER or DROP, are not supported in query-scoped requests.

Supported databases

P0 provides temporary database access for three databases today:

Setup details: PostgreSQL, MySQL and Snowflake integration docs.

Benefits

Just-in-time access provisioning eliminates the toil of managing users and defining roles upfront. Instead, users are created when needed, and their permissions reflect the task that engineers are trying to accomplish. By generating the user and password on-the-fly we can mitigate the risk of leaked passwords, remove shared users, and correctly attribute actions to users for better auditability.

In short, just-in-time database access reduces the risk of leaked shared passwords, removes shared users and attributes every action to an individual engineer.

Key takeaways

• Shared database logins are long-lived, over-privileged and unattributable.
• JIT database access grants a role or a single query for one task, then expires.
• P0’s agent creates the user and encrypted password, and blocks P0’s own privilege escalation.
• New credentials per request make database credential rotation automatic.

Frequently asked questions

What is just-in-time database access and why is it more secure than standing credentials?

Just-in-time database access grants credentials only for the duration of a specific task, making it more secure than standing access because no permission remains active between sessions.

How do you grant short-lived, least-privilege access to production databases?

Teams grant short-lived, least-privilege access to production databases by issuing ephemeral credentials scoped to a single session instead of standing database logins.

How does P0 Security automate JIT access to databases like Postgres, MySQL, and Snowflake?

P0 Security automates JIT access to databases like Postgres, MySQL, and Snowflake by granting time-bound, individually attributed access on request.

How can teams audit every just-in-time database access session?

Teams audit every just-in-time database access session through P0’s logs, which record exactly who accessed which database and for how long.

How does ephemeral database access work for Postgres and Snowflake?

For Postgres, P0 issues short-lived credentials per request for either a role or a single query and removes access on expiry, while for Snowflake it grants just-in-time access to databases, roles and grants, with SCIM provisioning users on demand and removing them afterward.

How do you request just-in-time access to a production database with P0?

Engineers request a role or a specific query from the P0 CLI, the request is approved according to your Policy Studio access policies, and P0 returns short-lived credentials, with access removed when it expires.