Automating SQL User Generation and Password Rotation With CockroachDB

Motivation

As with most of my tutorials, topic ideas come from user inquiries. I see this question come up quite often and we don't have a documented approach to bridge the gap today. Cockroach Labs engineering is hard at work to build an API that will make this point moot but until then this can be a viable alternative.  It is primarily directed at our cloud offering where we rely on password authentication today. There are also cases where password authentication serves other purposes and we need ways to automate the provisioning of passwords other than ALTER USER username WITH PASSWORD "password"; command.

You can take this approach and incorporate it into your CI/CD pipelines to onboard new users and manage their passwords in absence of certificate-based authentication and its associated revocation mechanisms or directory services and its password management capabilities.

Check out this page for more database-related articles.

High-Level Steps

• Access a CockroachDB cluster with admin privileges
• Generate a password
• Add a user with the generated password to CockroachDB
• Verify
• Rotating passwords
• Verify
• Clean up

Step by Step Instructions

Access CockroachDB Cluster With Admin Privileges

I will be using CockroachCloud Free cluster for the purposes of this tutorial but you can use any type of deployment that suits you.

The authorized users are located in system.users table.

artem@free-tier.gcp-us-central1.cockroachlabs.cloud:26257/defaultdb> select * from system.users;
  username |                        hashedPassword                        | isRole
-----------+--------------------------------------------------------------+---------
  admin    |                                                              |  true
  artem    | $2a$10$9DjXsaAVmYYvU63T4XLHpuzvF82PQ2yr1v0thLM5U8Q5/sTcuamWe | false
  root     |                                                              | false
(3 rows)

Let's create a user roach with password roach to demonstrate the salient point. We can create this user by directly inserting an entry into the table.

INSERT INTO system.users VALUES ('roach', 'roach', false);

Let's try to log in using this new user.

cockroach sql --url 'postgresql://roach:roach@clustername:26257/defaultdb?sslmode=...'

#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
ERROR: password authentication failed for user roach
Failed running "sql"

I removed the sensitive information from the connection string as it is not as important as the postgresql://roach:roach@ section.

The main point is it did not work because we attempted to insert the user using a clear-text password. The table expects the password in hashed form. The main question is how to generate this hashed password? We need to inspect the source code.

Generate a Hashed Password

I created a stand-alone program to generate passwords. I'm new to Go, I hope you can forgive the user experience of the program.

package main

import (
    "crypto/sha256"
    "fmt"
    "golang.org/x/crypto/bcrypt"
)

func main() {
    
    var BcryptCost = bcrypt.DefaultCost
    var sha256NewSum = sha256.New().Sum(nil)

    var test = append([]byte("roach"), sha256NewSum...)

    hashedPassword, err := bcrypt.GenerateFromPassword(test, BcryptCost)
    if err != nil {
        return
    }

    fmt.Printf("%s", hashedPassword)
}

As of CockroachDB 21.1, this is all you need to generate a hashed password. Now all is left is to run the code and replace the clear text password with the hashed one.

go run .
$2a$10$uI7GAG9NnsBSV.gsla4eGu6xXvYGpyR95Zl.BrYbyzoMcg0o8Zf0u% 

The details of the hashing function and bcrypt library can be found in the Golang documentation.

Add User With Generated Password to CockroachDB

The second line of the output is the hashed password. Let's insert it into the users' table. But first, we need to delete this user from the table.

DELETE FROM system.users WHERE username = 'roach';

We could've also updated the password instead of deleting the user but I will show that shortly when I touch on rotating passwords.

INSERT INTO system.users VALUES ('roach', '$2a$10$uI7GAG9NnsBSV.gsla4eGu6xXvYGpyR95Zl.BrYbyzoMcg0o8Zf0u%', false);

Verify

Now let's login into CockroachDB using this user and password:

cockroach sql --url 'postgresql://roach:roach@clustername:26257/defaultdb?sslmode=verify-full...'

# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Client version: CockroachDB CCL v21.1.9 (x86_64-apple-darwin19, built 2021/09/20 21:50:33, go1.15.14)
# Server version: CockroachDB CCL v21.1.6 (x86_64-unknown-linux-gnu, built 2021/07/20 15:30:39, go1.15.11)

warning: server version older than client! proceed with caution; some features may not be available.

# Cluster ID: c0854300-2c35-44b4-a7d1-2af71acd3e4c
#
# Enter \? for a brief introduction.
#
roach@free-tier.gcp-us-central1.cockroachlabs.cloud:26257/defaultdb>

We are in, notice I am using roach user to connect.

Let's view the users again:

select * from system.users;

  username |                        hashedPassword                         | isRole
-----------+---------------------------------------------------------------+---------
  admin    |                                                               |  true
  artem    | $2a$10$9DjXsaAVmYYvU63T4XLHpuzvF82PQ2yr1v0thLM5U8Q5/sTcuamWe  | false
  roach    | $2a$10$uI7GAG9NnsBSV.gsla4eGu6xXvYGpyR95Zl.BrYbyzoMcg0o8Zf0u% | false
  root     |                                                               | false
(4 rows)

So this is great and fits our requirements. Let's now cover the second part of the scenario, where we need to rotate passwords programmatically.

Rotating passwords

It's just as trivial, we need to hash a new password and update the system.users table with this new password.

I'm going to change roach user's password to changeme from roach. I will run my go program to get a new hash. The only line I change is:

var test = append([]byte("changeme"), sha256NewSum...)

I promise when I read up more on Go, I will change it to a command-line argument instead of hard coding!

Save the code and run it again. The output for the program is:  $2a$10$6Nxt.d8fxDcrJWZ6Y5QDZe8IecbBYMBCNmizm1MNKhx4dd1fcd5aG%.

Now we can issue an update statement to the table and verify.

update system.users set "hashedPassword" = '$2a$10$6Nxt.d8fxDcrJWZ6Y5QDZe8IecbBYMBCNmizm1MNKhx4dd1fcd5aG%' where username = 'roach';

A few caveats: we need to run this as a user with write access on the system.users table. The hashedPassword table will not be found unless you wrap the column name in double quotes "hashedPassword".

Now that we updated the password, we can try to authenticate as roach with password changeme.

Verify

cockroach sql --url 'postgresql://roach:changeme@clustername:26257/defaultdb?sslmode=verify-full...'

And we're in!

#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Client version: CockroachDB CCL v21.1.9 (x86_64-apple-darwin19, built 2021/09/20 21:50:33, go1.15.14)
# Server version: CockroachDB CCL v21.1.6 (x86_64-unknown-linux-gnu, built 2021/07/20 15:30:39, go1.15.11)

warning: server version older than client! proceed with caution; some features may not be available.

# Cluster ID: c0854300-2c35-44b4-a7d1-2af71acd3e4c
#
# Enter \? for a brief introduction.
#
roach@free-tier.gcp-us-central1.cockroachlabs.cloud:26257/defaultdb>

This concludes our tutorial. I hope it serves you well.

Clean up

Since I'm using a perpetually free instance of Cockroach Cloud Free product, there's no clean-up except for either dropping the user or changing the password to something else. Happy coding!

Update

CockroachDB version 22.1 slated for the first half of this year will introduce a new password authentication method, SCRAM, which will/may change the approach in this tutorial. Aside from that, this method should still be applicable for any release to date.