PostgRegent: RBAC Tracking

Sat, Jul 22, 2023
6-minute read

How do we know what Postgres roles exist within our cluster?

In the last post I discussed the overall goal of the PostRegent project. As part of this project we will need to track all of the permissions, roles, and users that we add, remove, or modify within our Postgres cluster. This knowledge will protect us from some silly mistakes, such as granting a non-existent role to a user.

One obvious solutions to this is to store the information within a typical relational database. And then use use SQL to obtain the desired information. While this is a valid approach it is not one that I am particularly fond of. To begin with, this information is already stored in the cluster across multiple tables and view. Also we would have to (well probably should instead of have to) create roles to limit access to this data. And finally, fetching this data could be slow.

Thankfully, Redis comes to the rescue. It is able to solve the performance issue, and need to create additional roles to limit data access. As an added benefit we also simplify the queries that are needed to obtain our desired data. Most people know Redis as an in memory key/value store, but it is able to be so much more. In this situation we will be using RedisJSON and RediSearch. RedisJSON allows us to store JSON data within Redis without having to constantly serialize and deserialize it. One added benefit of this is that you are able to updated individual parts of the JSON data without having to retrieve the entire payload. Next RediSearch provides Redis with additional querying capabilities, such as full text search and vector similarity search.

Questions of Interest

Does there exists a permission/role/user with a given name?
For a given permission what are the sets of roles that have been granted this permission?
For a given role what set of users have been granted this role?

Data Format

First we need to structure our data in such a way that it will be possible to answer the above question with as few queries as possible. Thus for each role and user we store a list of all the permissions and roles that they are granted respectively. For the sake of this article we will only be interested in the name of a permission. Similarly, a simplified view will also be taken for roles and users.

Permission JSON

{
  "name": permission-name
}

Role JSON

{
  "name": "role-name",
  "permissions": [
    "permission1",
    "permission2"
  ]
}

User JSON

{
  "name": "username",
  "roles": [
    "role-1",
  ]
}

Redis Queries

Since we know that we will be searching for these documents we should first create a secondary index to allow RediSearch to operate. Each of permission, role, and user will have a primary index of permission:X, role:X, and user:X respectively. Where X is equal to the object’s name attribute. To create the secondary index, we can run the following from the redis-cli

FT.CREATE myIndex ON JSON PREFIX 3 permission: role: user: SCHEMA $.name AS name TEXT $.permissions AS permissions TAG $.roles AS roles TAG

This will create a secondary index (myIndex) over the primary keys that start with permission:, role:, and user:. We treat the .permissions and .roles fields as TAGS since RediSearch has limitations on what you are able to query on array fields. Fortunately, we are able to search over a small collection of strings in arrays, unlike TEXT fields which allow for arbitrary full-text search. With TAG fields we must match the item name exactly.

Now that the secondary index is created we can add our items to Redis using the JSON.SET command. To add a new user the query is as follows

JSON.SET user:intern . "{\"name\": \"intern\", \"roles\": [ \"read-only-role\", \"fall-intern-role\"]}"

And finally, to search over our collection we use the FT.SEARCH command. The following is an example of how we would find all users that have been granted the role of either a fall or spring intern.

FT.SEARCH myIndex '@roles:{fall-intern-role | spring-intern-role}'

Go Code

Knowing how to insert and search for data from the redis-cli is useful for getting your hands dirty. However, we will need to interact with Redis through our Go program. Since the Go standard library doesn’t provide a Redis client we need to make use of third party packages. I have chosen to use Go-ReJSON and redisearch-go. Adding these to our project is simple,

go get github.com/RediSearch/redisearch-go/redisearch@master
go get github.com/nitishm/go-rejson/v4

For redisearch-go, if you don’t add the @master and instead copy the command from the GitHub repository you will end up with v1 of the client instead of v2 (at the time of writing) and miss out on required features.

Adding JSON

Below is an example of adding a role to Redis. The code for obtaining a connection to Redis using the redigo client is left out since it’s not the focus of this snippet.

package main

import "github.com/nitishm/go-rejson/v4"

type Role struct {
  Name         string   `json:"name"`
  Permissions []string `json:"permissions"`
}

func main() {
  role1 := Role {
    Name: "example-role",
    Permissions: []string{
      "read-data",
      "update-data",
    },
  }

  rjson := rejson.NewReJSONHandler()
  rjson.SetRedigoClient(redisConnection)

  rjson.JSONSet("role:example-role", ".", role1)
}

Creating the Search Index

As mentioned previously we need to index our data so that RediSearch can quickly retrieve the desired information. Similar to the code for adding data to Redis, this code snippet will focus only on code required to create the search index.

import "github.com/RediSearch/redisearch-go/v2/redisearch"

rsearch := redisearch.NewClientFromPool(&rediGoConnectionPool, "indexName")

indexDef := redisearch.IndexDefinition{
  IndexOn: "JSON",
  Prefix: []string{"permission:", "role:", "user:"},
}

schema := redisearch.NewSchema(redisearch.DefaultOptions).
  AddField(redisearch.NewTextFieldOptions("$.name", redisearch.TextFieldOptions{As: "name"})).
  AddField(redisearch.NewTagFieldOptions("$.permissions", redisearch.TagFieldOptions{As: "permissions"})).
  AddField(redisearch.NewTagFieldOptions("$.groups", redisearch.TagFieldOptions{As: "groups"}))

// You may want to delete the index if it already exists. If it doesn't exist then the
// below line will return an error.
err := rsearch.Drop()
err = rsearch.CreateIndexWithIndexDefinition(schema, &indexDef)

Searching for Data

Now that all of the JSON data has been added to Redis and indexed it is time to show how to perform searches.

// Does there exist a role with a given name (i.e. testRole)?
item, err := rjson.JSONGet("role:testRole", ".name")

For this kind of query we don’t need to use RediSearch but can instead use the RedisJSON client due to how we constructed our keys.

// For a given permission (testPermission) what are the sets of roles that have been
// granted this permission?
docs, numDocs, err := rsearch.Search(redisearch.NewQuery("'@permissions:{testPermission}'"))

This query searches over the permission key in the index. The query returns all found documents, the number of documents, or an error if things go wrong. Note that the single quote (') is required, otherwise the incorrect results will be obtained. Additional filters and capabilities are possible through the RediSearch client, and details can be found on either the GitHub site or the documentation portion of Redis' website.

Posts in this series

go redis