User creation in Discover RDS flow is a pain

[r0mant]

Once a user gets past the agent deployment step, we ask them to fill out principals and tell that these database users must have rds_iam role:

This remains probably the most cumbersome step of the flow because a user needs to connect to the database some other way (e.g. by using master user/password) and create the IAM users there. To get it done I had to: install psql locally, hunt down for my database's master user, realize that I don't remember the password and reset it, then connect using psql from my laptop and create the users (which wouldn't have worked at all if the database wasn't available publicly).

Unfortunately, there's no RDS HTTP API for creating the users but I wonder what we can do to simplify this for users. For example, can we implement a helper teleport CLI command that creates such users based on the entered principals, generate the command for user and give them a link to CloudShell in the database's VPC they can run similar to the IAM script from the "deploy database service" step?

[GavinFrazar]

cloudshell has psql installed, that's how I've done this for private databases. You have to set it up in the same subnet as the databases (or have a local route) with the right security group configured though:

I don't think we can just hand them a link for that unfortunately.

Another option is to use a Lambda function, which @greedy52 has already tested. By the time they reach this step, we should already have their selected security group and subnets, so we can re-use those for the lambda func. They will still need to provide master user, master user password, and db name to connect to.

@r0mant I think this step might be awkward for us because we are just using throwaway test databases, but not so bad for a customer? They are using these databases for a real purpose already, so they should know how to connect to their own databases by whatever means and provision a postgres role.

[greedy52]

I haven't tried this personally, but we should explore RDS data API as well: https://docs.aws.amazon.com/cli/latest/reference/rds-data/

[GavinFrazar]

For this issue we decided to just move the IAM user creation step to the first step in the flow. We can recommend cloudshell in that step as well, but we cannot give them a link that creates the custom VPC env automatically.

I don't think there's anything else we can do here, but this should not be a painful step for someone managing a real database.

[greedy52]

I don't think we check anywhere whether IAM authentication is enabled on the database either in this flow.

Our guide clearly stated IAM auth must be enabled: https://goteleport.com/docs/enroll-resources/database-access/enroll-aws-databases/rds/#prerequisites

Our ECS agent does not have permission to modify the db to enable IAM auth so it is important to check during the UI flow. (it takes time for RDS to enable it on the db too)

[GavinFrazar]

Just a note here since we've floated various ideas to automate or partially automate this step, but we've also discussed just re-ordering the steps:

If we make this step the first thing they have to do after selecting a database, but prior to the teleport db service deployment, then we won't have a database service deployment up to automate the user provisioning. That means automating and re-ordering is probably a bad idea.

I'll do my best to recall and summarize the options we've discussed here:

prompt for master password in web ui

Allow user to provide master db password as input in the web ui, then connect as master user and run a pre-baked script that either sets up their user or preferably a teleport admin user. The master user connection will flow through teleport db service.

If product doesn't like the idea of database service using passwords for this one case, then we could alternatively have teleport app service proxy the db as a tcp app and run some script through that.

Decision: don't do it because we didn't want to prompt for password in web ui iirc

Reorder the steps

We currently have a step that tells the user to connect as their master db user and provision an IAM auth user. That step happens after the deployment of teleport db service. People drop off at this step because they don't have access to that db's master user.

The db service deployment step demands a lot of users, so it's frustrating to get through that and then hit a blocker. We can move this step to before the deployment step instead. We can also provide a sort of "introduction" step that explains what we're going to do - actually we have docs that do exactly that, and we could just link to them: https://goteleport.com/docs/admin-guides/management/guides/awsoidc-integration-rds

Decision: do it

AWS Secrets Manager (partial automation)

For RDS databases with master password managed in AWS Secrets Manager, we can detect this case and ask the user if they'd like us to do the provisioning for them. If they agree, we fetch the master password from secrets manager instead of collecting it as web ui input and then provision a user or teleport admin for them.

The downside here is it only works for databases with password in secrets manager, and since we can't do both the step re-order and this, it leaves some databases in the current situation. We might be able to do the step re-ordering if the user can't, or doesn't want to, do this automated flow? Not sure how hard that would be.

Decision: pending - we just discussed this in call

cc @thedevelopnik @marcoandredinis

[greedy52]

an alternation to AWS Secrets Manager usage is to use RDS Data API (with secrets manager) on Aurora databases: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.html

We can provide the AWS CLIs to run (or a teleport script) in cloud shell. Network access to DB shouldn't be necessary.

We don't know how many users are indeed using secrets manager for RDS master users though. Also note that RDS Database API is Aurora only.