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 auditability of databases. The net outcome is long-lived shared passwords with high levels of access to a critical piece of infrastructure.
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 organizations 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.
p0 psql -d mydatabase -c "select * from enterprise.markets" --reason "Generating reports"
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 Request Routing 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:
- 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.
- 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 inject 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 will be re-created with a new password, effectively implementing password rotation.
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.