Skip to content

Create R/O and R/W database console users

Summary

In order to migrate from using chef managed database consoles running on the console servers, to Teleport managed access using Okta for user management and authentication, we need to set up permissions on the database servers. See: &569

Currently, all database console access uses the same user and permissions as the rails application. We have no flexibility in how permissions are assigned.

Now that we can manage connections via Teleport, we need to direct these connections to specific database users that are NOT the rails application user.

Once that is in place, we can manage users in Okta, and we will have much more flexible control over who can take which actions in which databases, and we will be able to log what actions are taken.

Authentication

User management and group assignment is managed by IT, using Okta. Teleport associates a valid Okta session's group with a list of database users that the user is authorized to connect as. See this discussion for more detail: https://gitlab.com/gitlab-com/gl-infra/infrastructure/-/issues/13485

Teleport uses client certificates to promise the database server that the user is who they claim to be. Database user passwords are not required. However, the database must be set up to trust these certificates by adding the folowing to /var/opt/gitlab/postgresql/data/pg_hba.conf

hostssl all             all             ::/0                    cert
hostssl all             all             0.0.0.0/0               cert

The Teleport CA certificate also needs to be added to /opt/gitlab/embedded/ssl/certs/cacert.pem, or whatever ssl_ca_file is pointing to.

I have questions about whether all of the root certs in that file are necessary, and if the database should be using that as the ssl_ca_file, or one which only contains the root certs it needs.

Decisions

Do we want to have a third additional admin user, and limit system or schema changes for the read/write user?

What should we call those users? We could add some prefix or suffix. We could abbreviate. Or we could just use:

  • readonly
  • readwrite
  • admin

Also, the database server is currently using a self signed SSL certificate. Is this really what we want? How do we want to set up the SSL certificates and client auth on the database servers?

Implementation

This is the part I could use a little help with. What is the best way to make these changes so that they end up on all of the servers we want them on.

Luckily we don't have to manage individual login credentials, but we'll need to make sure the teleport root certificate ends up in the right place. (The certs are different in production and staging)

Criteria

  • Two or three new database users are created
  • Teleport users can connect to read-only and read-write database consoles
  • Configuration is managed in source control
  • Users exist on all database servers that need consoles (praefect, etc.)
  • Database servers are configured to trust users that Teleport proxies to it
Edited by Devin Sylva