PostgRegent: A User Management System

  • Sun, Jul 9, 2023
  • 5-minute read

There’s no better way to learn a new technology than trying to create something from scratch using that technology. I’ve found that this approach is much more rewarding and avoids the trappings of tutorial hell. This was the approach I took when learning Python, C++, Android development, and Change-point analysis for my PhD dissertation.

Identifying A Project

Now I just needed to decide on what I wanted to create. Ideally, this project should be easy to describe. While having a naive solution that can allow me to get my feet wet, but also have lots of areas for improvement and expansion. Apart from the characteristics of the project I also needed to select what skills I wanted to learn or improve upon. In the previous post I stated that I wanted to focus on Go, Kubernetes, and Rust. So the technology selection was somewhat narrowed down. I figured that I might as well tackle two of these at once since I wouldn’t be starting at ground zero when it comes to Kubernetes.

Many current Kubernetes projects provided me with inspiration.

At first I was leaning towards building a backend akin to Parseable, but for traces instead of logs. This would allow me to work with OpenTelemetry and learn Rust. I’ve been hearing many great things about Rust and its focus on memory safety definitely made it quite appealing. Unfortunately, when I was making this decision all of the drama around the Rust foundation was just coming to light. Needless to say that made me reconsider learning the language at that point in time.

Database user management

Although all of the above projects were interesting in their own right, I ended up going a different direction. In the end I decided to build a system that would allow you to manage database access through GitOps. This was inspired by SchemaHero which focuses on managing table schemas through GitOps. Surprisingly, I wasn’t able to find any similar projects. To start I will focus on managing access to Postgres databases and expand from there once I’m satisfied with the system’s capabilities.

Now that the project has been selected, it is now time to address the most difficult part, giving it a name. Naturally, I wanted something that is related to managing people/users. And if possible I would like to integrate Postgres into the name. Well after XXX (I’m ashamed to say exactly how long I spent thinking about this) hours, I settled upon PostgRegent. A combination of Postgres and regent.

PostgRegent

If you would like to follow along with PostgRegent’s progress here is the github link. In the remainder of this post I will outline what I envision as the end goal for the alpha version.

Within your Kubernetes cluster, there will be PostgRegent Controller that will be responsible for monitoring select secrets, CRDs, and the Regent pods. The Regent is the server that is responsible for keeping the database roles in sync with what is specified by our CRDs. Upon startup the Regent will create functions that will trigger notifications upon specified database actions. For example, one function could be triggered by any update to the pg_roles table and then send a notification. The Regent would be listening for this notification and then take the appropriate action based upon the notification’s content. This way the Regent is aware of all potential RBAC changes of interest.

The CRDs that this project will make use of are permissions, roles, users, and regent. Permissions are access level privileges within a database. A role is a grouping of permissions. And finally, a user is a role that has the ability to login to databases. A user can also inherit permissions from a collection of roles. Essentially, roles and users are the same, but I have chosen to distinguish them by their ability to login. Finally, a regent is a server that overseas the roles within a collection of databases.

Permissions

Below is a possible specification for a permission. These examples display a permission to read (SELECT statements) on any table in schema1 and schema2, a permission to read/write/delete flights data from any schema, and finally the ability to connect to mydb.

apiVersion: putnam120.postgregent/v1alpha1
kind: Permission
metadata:
  name: read-schemas-1-and-2
spec:
  database: mydb
  schemas:
    - schema1
    - schema2
  actions:
    - SELECT
  tables:
    - *
---
apiVersion: putnam120.postgregent/v1alpha1
kind: Permission
metadata:
  name: connect-to-mydb
spec:
  database: mydb
  actions:
    - CONNECT
---
apiVersion: putnam120.postgregent/v1alpha1
kind: Permission
metadata:
  name: read-write-flights
spec:
  database: mydb
  schemas:
    - *
  actions:
    - SELECT
    - INSERT
    - UPDATE
    - DELETE
  tables:
    - flights

Roles

Building upon the previously defined permissions we now create a couple roles. Here we have a read-only role (mydb-viewer) and a flights-admin role which has a wider selection of permissions.

apiVersion: putnam120.postgregent/v1alpha1
kind: Role
metadata:
  name: mydb-viewer
spec:
  database: mydb
  permissions:
    - connect-to-mydb
    - read-schemas-1-and-2
---
apiVersion: putnam120.postgregent/v1alpha1
kind: Role
metadata:
  name: flights-admin
spec:
  database: mydb
  permissions:
    - connect-to-mydb
    - read-write-flights

Users

Here we define a summer intern user which should have restricted access, and thus is assigned the mydb-viewer role. While we also have an admin user (flights-admin) that will need the expanded permission on the flights table.

apiVersion: putnam120.postgregent/v1alpha1
kind: User
metadata:
  name: summer-intern-2
spec:
  passwordSecretRef:
    name: mydb-intern-passwords
    key: intern-2
  roles:
    - mydb-viewer
---
apiVersion: putnam120.postgregent/v1alpha1
kind: User
metadata:
  name: flights-admin
spec:
  passwordSecretRef:
    name: mydb-passwords
    key: flights-admin
  roles:
    - flights-admin

Regent

Finally, the Regent is configured to connect to the Postgres cluster as the my-admin user. Similar to the definition of a user, the regent also obtains its login password from a Kubernetes secret. And the .spec.secretsSelector object tells the controller which secrets it needs to monitor for changes.

apiVersion: putnam120.postgregent/v1alpha1
kind: Regent
metadata:
  name: my-regent
spec:
  adminUser: my-admin
  adminPasswordSecretRef:
    name: mydb-passwords
    key: admin
  databaseHost: some.known.endpoint
  port: 5432
  databaseName: mydb
  secretsSelector:
    key1: value1
    key2: value2

Posts in this series

gokubernetespostgres