Support for Redshift instances

[OpsBurger]

Feature Request:

Currently, there is no Terraform provider support for Redshift instances, which are the main DB for my team. This makes it hard for us to declare the Bytebase configuration as fully IaC managed.

Moreover, today you only allow setting Redshift connections with user & password configuration, which is less secure then using IAM roles. I'd like to add this as a followup feature request to this one.

[d-bytebase]

Sure, we will look into that. Btw, feel free to join our community, and we can chat about technical details there.

https://www.bytebase.com/docs/faq/

[OpsBurger]

Thanks! I’ll join

On Sun, 21 May 2023 at 17:36 Danny Xu @.***> wrote:

Sure, we will look into that. Btw, feel free to join our community, and we can chat about technical details there.

https://www.bytebase.com/docs/faq/

— Reply to this email directly, view it on GitHub https://github.com/bytebase/terraform-provider-bytebase/issues/57#issuecomment-1556195547, or unsubscribe https://github.com/notifications/unsubscribe-auth/AZXMANAYOGGZAPHK4KZHF7LXHIR73ANCNFSM6AAAAAAYJJNOYQ . You are receiving this because you authored the thread.Message ID: @.***>

[d-bytebase]

Welcome! I've just updated the Terraform provider but it will only be compatible with the release next week. I'll have my team looking into connection over IAM role.

https://github.com/bytebase/terraform-provider-bytebase/pull/58

[OpsBurger]

Cool! Just saw your PR merge. What will the next version bring? 0.0.8?

Do you have a change log I can see?

On Sun, 21 May 2023 at 18:04 Danny Xu @.***> wrote:

Welcome! I've just updated the Terraform provider but it will only be compatible with the release next week. I'll have my team looking into connection over IAM role.

58 https://github.com/bytebase/terraform-provider-bytebase/pull/58

— Reply to this email directly, view it on GitHub https://github.com/bytebase/terraform-provider-bytebase/issues/57#issuecomment-1556201421, or unsubscribe https://github.com/notifications/unsubscribe-auth/AZXMANABVJTDIFXVAWZ2GJ3XHIU67ANCNFSM6AAAAAAYJJNOYQ . You are receiving this because you authored the thread.Message ID: @.***>

[d-bytebase]

Should be Thursday next week, Bytebase 2.1.0 and Terraform 0.0.9. I'll keep you updated.

[OpsBurger]

Thank you and thank you again for the super fast and informative response.

I like your product and like to help you develop it with some good feedback and maybe a contribution or two :)

On Sun, 21 May 2023 at 18:10 Danny Xu @.***> wrote:

Should be Thursday next week, Bytebase 2.1.0 and Terraform 0.0.9. I'll keep you updated.

— Reply to this email directly, view it on GitHub https://github.com/bytebase/terraform-provider-bytebase/issues/57#issuecomment-1556203477, or unsubscribe https://github.com/notifications/unsubscribe-auth/AZXMANBRWGWFEKHVH3HT363XHIV6HANCNFSM6AAAAAAYJJNOYQ . You are receiving this because you authored the thread.Message ID: @.***>

[h3n4l]

Moreover, today you only allow setting Redshift connections with user & password configuration, which is less secure then using IAM roles.

Hi @OpsBurger , unfortunately, this may require a dependency on JDBC, but it's hard to use it inside Go. Or maybe I'm misunderstanding, can you describe what you want the process to look like?

[OpsBurger]

Hi @h3n4l, I can think of a few ways to perform this, depends on the platform.

When talking about a self-managed Bytebase instance (Kuberentes), the platform can use the local service account permissions if it's being attached with an appropriate role using an annotation.

metadata:
    annotations:
        eks.amazonaws.com/role-arn: arn:aws:iam::xxxxxxxxxxxx:role/<role-name>

On the other hand, this is impossible when using the managed platform, but a way to do so is to assume a role on your side and supply the needed information to the client, as mentioned here

creds := Credentials{
    AccessKeyID: "key",             // AWS Access key ID
    SecretAccessKey: "secret",      // AWS Secret Access Key
    SessionToken: "token",          // AWS Session Token
}

I didn't explore it thorough enough, it's a general idea and not an actual solution. Hope it was more clear and maybe helpful 👍

[d-bytebase]

Thanks for the details. I'm more familiar with GCP than AWS, but they work in quite similar way. The first one allows applications inside the container/VM to obtain the access token via an internal endpoint. While the second one is more like user-password (probably less secure than actual database user-password). The real challenging is to use the access token for Go postgres driver. There is a JDBC driver available but we don't find a way in Go sadly. We're still looking into that.

https://docs.aws.amazon.com/redshift/latest/mgmt/generating-iam-credentials-configure-jdbc-odbc.html

Meanwhile, we're trying out this but not sure if this works for Redshift. Let's give it a minute.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.Go.html

[OpsBurger]

@d-bytebase Sure! The method you're trying to use (second link) would work perfectly in a Kubernetes environment:

sess := session.Must(session.NewSession())
creds := sess.Config.Credentials

This is, as suggested in my latest command, the way to assume the role attached to the service account.

When talking about your SaaS platform - if you have your own AWS account, you can configure a "user requirement" for an IAM role to be assumed by your account (aka trust policy) and that way you'll be able to use that role to interact with Redshift.

[d-bytebase]

Yup. Our SaaS runs on GCP currently. If the first solution (assume the compute environment is attached to the service account) works for you, we can add to our product plan.

[OpsBurger]

Unfortunately it's just an assumption, as in order to test it I need to edit the code itself. If you'll give the specific file for the connection is made (instead of me searching for it), I'll try to edit it locally and tell you my findings.

[d-bytebase]

Don't worry, we will be testing it. Thank you.

[OpsBurger]

Sure, thank you :)

[h3n4l]

@OpsBurger I'm trying to support this feature, and so far I'm working on getting a user temporary credential for a given redshift cluster with the Access Key ID and Secret Access Key of the AWS IAM User, and the user and IAM user are mapped 1:1. When I logged in, I found that I have almost no access and I have added the following policy to the IAM user:

Do you know the previous permission mapping between the IAM user and the redshift user?

And here is the demo code I use to test, https://go.dev/play/p/w_Lu-eCXVUG

[OpsBurger]

@h3n4l I would use the GetClusterCredentials boto3 method instead of GetClusterCredentialsWithIAM, as this way, you can bind specific permissions to an actual Redshift user and allow the IAM role, with specific permissions, to assume and use in in a connection to the database.

The following policy is an example for assuming a specific Redshift user and use a specific database in the Redshift instance:

{
    "Statement": [
        {
            "Action": "redshift:GetClusterCredentials",
            "Effect": "Allow",
            "Resource": [
                "arn:aws:redshift:eu-west-1:xxxxxxxxxxxx:dbuser:redshift_instance/username",
                "arn:aws:redshift:eu-west-1:xxxxxxxxxxxx:dbname:redshift_instance/database_name"
            ],
            "Sid": ""
        }
    ],
    "Version": "2012-10-17"
}

[d-bytebase]

With my reseaerch, Redshift connection (via Golang) can only go through 1) postgres driver, 2) AWS API. Connection using IAM service account credential can only be used for 1) AWS API, or 2) Java JDBC driver provided by AWS.

Since we use Go, there is no way to connect via service account credential at the moment. I suggest to use network group to provide additional security layer between Redshift cluster and Bytebase. Thanks!

https://rhuaridh.co.uk/blog/golang-redshift-example.html https://razorsql.com/articles/amazon_redshift_iam_jdbc.html