guitarrapc · December 4, 2024 06:30
diff --git a/README.md b/README.md
diff --git a/Program.cs b/Program.cs
 using System;
 using System.Linq;
 using System.Text;
 using Amazon;
 using Amazon.Runtime;
 using Amazon.Runtime.CredentialManagement;
 using Amazon.Runtime.Internal;
 using Amazon.Runtime.Internal.Auth;
 using Amazon.Runtime.Internal.Util;
 using Npgsql;

 // required replacement
 var profile = "YOUR_AWS_PROFILE"; // for local only YOUR_AWS_PROFILE
 var clusterEndpoint = "DSQL_CLUSTER_ENDPOINT"; // DSQL CLUSTER ENDPOINT shown on AWS Console > Aurora DSQL
 var region = RegionEndpoint.USEast1; // DSQL CLUSTER REGION

 // fixed for most cases
 var username = "admin"; // fixed if no user created
 var password = TokenGenerator.GenerateAuthToken(clusterEndpoint, region, profile);
 var database = "postgres"; // fixed if no schema created
 //var sslMode = SslMode.VerifyFull;
 var sslMode = SslMode.Prefer; // HACK: Current DSQL seems has problem with ssl verify
 var connectionString = $"Host={clusterEndpoint};Username={username};Password={password};Database={database};Port=5432;SSLMode={sslMode};";

 // gen sample data
 var records = Enumerable
 .Range(0, 100)
 .Select(x => new Owner(Guid.CreateVersion7().ToString(), $"John Doe {Random.Shared.Next(100000, 999999)}", $"{Random.Shared.Next(1000, 99999)}", Guid.NewGuid().ToString().Substring(0, 20)))
 .ToArray();

 // connect
 using var conn = new NpgsqlConnection(connectionString);
 await conn.OpenAsync();

 // table
 using var create = new NpgsqlCommand("CREATE TABLE IF NOT EXISTS owner (id UUID PRIMARY KEY, name VARCHAR(30) NOT NULL, city VARCHAR(80) NOT NULL, telephone VARCHAR(20))", conn);
 create.ExecuteNonQuery();

 // insert
 var sb = new StringBuilder("INSERT INTO owner (id, name, city, telephone) VALUES ");
 for (var i = 0; i < records.Length; i++)
 {
    if (i != 0) sb.Append(',');
    sb.Append("('").Append(records[i].Id).Append("'");
    sb.Append(", '").Append(records[i].Name).Append("'");
    sb.Append(", '").Append(records[i].City).Append("'");
    sb.Append(", '").Append(records[i].Telephone).Append("'");
    sb.Append(')');
 }
 var insertSQL = sb.ToString();
 using var insert = new NpgsqlCommand(connection: conn, cmdText: insertSQL);
 insert.ExecuteNonQuery();

 public record Owner(string Id, string Name, string City, string Telephone);

 public static class TokenGenerator
 {
    /// <summary>AWS Environment, use IAM Role Federated Credentials</summary>
    public static string GenerateAuthToken(string? clusterEndpoint, Amazon.RegionEndpoint region)
    {
        return GenerateAuthToken(clusterEndpoint, region, FallbackCredentialsFactory.GetCredentials());
    }
    /// <summary>Local Environment, use Local AWS Profile</summary>
    public static string GenerateAuthToken(string? clusterEndpoint, Amazon.RegionEndpoint region, string profile)
    {
        var chain = new CredentialProfileStoreChain();
        AWSCredentials awsCredentials;
        if (!chain.TryGetAWSCredentials(profile, out awsCredentials))
            throw new Exception("AWS Credentials not found for specified profile");
        return GenerateAuthToken(clusterEndpoint, region, awsCredentials);
    }
    public static string GenerateAuthToken(string? hostname, Amazon.RegionEndpoint region, AWSCredentials awsCredentials)
    {
        string accessKey = awsCredentials.GetCredentials().AccessKey;
        string secretKey = awsCredentials.GetCredentials().SecretKey;
        string token = awsCredentials.GetCredentials().Token;

        const string DsqlServiceName = "dsql";
        const string HTTPGet = "GET";
        const string HTTPS = "https";
        const string URISchemeDelimiter = "://";
        const string ActionKey = "Action";
        const string ActionValue = "DbConnectAdmin";
        const string XAmzSecurityToken = "X-Amz-Security-Token";

        ImmutableCredentials immutableCredentials = new ImmutableCredentials(accessKey, secretKey, token) ?? throw new ArgumentNullException("immutableCredentials");
        ArgumentNullException.ThrowIfNull(region);

        hostname = hostname?.Trim();
        if (string.IsNullOrEmpty(hostname))
            throw new ArgumentException("Hostname must not be null or empty.");

        GenerateDsqlAuthTokenRequest authTokenRequest = new GenerateDsqlAuthTokenRequest();
        IRequest request = new DefaultRequest(authTokenRequest, DsqlServiceName)
        {
            UseQueryString = true,
            HttpMethod = HTTPGet
        };
        request.Parameters.Add(ActionKey, ActionValue);
        request.Endpoint = new UriBuilder(HTTPS, hostname).Uri;

        if (immutableCredentials.UseToken)
        {
            request.Parameters[XAmzSecurityToken] = immutableCredentials.Token;
        }

        var signingResult = AWS4PreSignedUrlSigner.SignRequest(request, null, new RequestMetrics(), immutableCredentials.AccessKey,
            immutableCredentials.SecretKey, DsqlServiceName, region.SystemName);

        var authorization = "&" + signingResult.ForQueryParameters;
        var url = AmazonServiceClient.ComposeUrl(request);

        // remove the https:// and append the authorization
        return url.AbsoluteUri[(HTTPS.Length + URISchemeDelimiter.Length)..] + authorization;
    }

    private class GenerateDsqlAuthTokenRequest : AmazonWebServiceRequest
    {
        public GenerateDsqlAuthTokenRequest()
        {
            ((IAmazonWebServiceRequest)this).SignatureVersion = SignatureVersion.SigV4;
        }
    }
 }
	using System;
	using System.Linq;
	using System.Text;
	using Amazon;
	using Amazon.Runtime;
	using Amazon.Runtime.CredentialManagement;
	using Amazon.Runtime.Internal;
	using Amazon.Runtime.Internal.Auth;
	using Amazon.Runtime.Internal.Util;
	using Npgsql;

	// required replacement
	var profile = "YOUR_AWS_PROFILE"; // for local only YOUR_AWS_PROFILE
	var clusterEndpoint = "DSQL_CLUSTER_ENDPOINT"; // DSQL CLUSTER ENDPOINT shown on AWS Console > Aurora DSQL
	var region = RegionEndpoint.USEast1; // DSQL CLUSTER REGION

	// fixed for most cases
	var username = "admin"; // fixed if no user created
	var password = TokenGenerator.GenerateAuthToken(clusterEndpoint, region, profile);
	var database = "postgres"; // fixed if no schema created
	//var sslMode = SslMode.VerifyFull;
	var sslMode = SslMode.Prefer; // HACK: Current DSQL seems has problem with ssl verify
	var connectionString = $"Host={clusterEndpoint};Username={username};Password={password};Database={database};Port=5432;SSLMode={sslMode};";

	// gen sample data
	var records = Enumerable
	.Range(0, 100)
	.Select(x => new Owner(Guid.CreateVersion7().ToString(), $"John Doe {Random.Shared.Next(100000, 999999)}", $"{Random.Shared.Next(1000, 99999)}", Guid.NewGuid().ToString().Substring(0, 20)))
	.ToArray();

	// connect
	using var conn = new NpgsqlConnection(connectionString);
	await conn.OpenAsync();

	// table
	using var create = new NpgsqlCommand("CREATE TABLE IF NOT EXISTS owner (id UUID PRIMARY KEY, name VARCHAR(30) NOT NULL, city VARCHAR(80) NOT NULL, telephone VARCHAR(20))", conn);
	create.ExecuteNonQuery();

	// insert
	var sb = new StringBuilder("INSERT INTO owner (id, name, city, telephone) VALUES ");
	for (var i = 0; i < records.Length; i++)
	{
	if (i != 0) sb.Append(',');
	sb.Append("('").Append(records[i].Id).Append("'");
	sb.Append(", '").Append(records[i].Name).Append("'");
	sb.Append(", '").Append(records[i].City).Append("'");
	sb.Append(", '").Append(records[i].Telephone).Append("'");
	sb.Append(')');
	}
	var insertSQL = sb.ToString();
	using var insert = new NpgsqlCommand(connection: conn, cmdText: insertSQL);
	insert.ExecuteNonQuery();

	public record Owner(string Id, string Name, string City, string Telephone);

	public static class TokenGenerator
	{
	/// <summary>AWS Environment, use IAM Role Federated Credentials</summary>
	public static string GenerateAuthToken(string? clusterEndpoint, Amazon.RegionEndpoint region)
	{
	return GenerateAuthToken(clusterEndpoint, region, FallbackCredentialsFactory.GetCredentials());
	}
	/// <summary>Local Environment, use Local AWS Profile</summary>
	public static string GenerateAuthToken(string? clusterEndpoint, Amazon.RegionEndpoint region, string profile)
	{
	var chain = new CredentialProfileStoreChain();
	AWSCredentials awsCredentials;
	if (!chain.TryGetAWSCredentials(profile, out awsCredentials))
	throw new Exception("AWS Credentials not found for specified profile");
	return GenerateAuthToken(clusterEndpoint, region, awsCredentials);
	}
	public static string GenerateAuthToken(string? hostname, Amazon.RegionEndpoint region, AWSCredentials awsCredentials)
	{
	string accessKey = awsCredentials.GetCredentials().AccessKey;
	string secretKey = awsCredentials.GetCredentials().SecretKey;
	string token = awsCredentials.GetCredentials().Token;

	const string DsqlServiceName = "dsql";
	const string HTTPGet = "GET";
	const string HTTPS = "https";
	const string URISchemeDelimiter = "://";
	const string ActionKey = "Action";
	const string ActionValue = "DbConnectAdmin";
	const string XAmzSecurityToken = "X-Amz-Security-Token";

	ImmutableCredentials immutableCredentials = new ImmutableCredentials(accessKey, secretKey, token) ?? throw new ArgumentNullException("immutableCredentials");
	ArgumentNullException.ThrowIfNull(region);

	hostname = hostname?.Trim();
	if (string.IsNullOrEmpty(hostname))
	throw new ArgumentException("Hostname must not be null or empty.");

	GenerateDsqlAuthTokenRequest authTokenRequest = new GenerateDsqlAuthTokenRequest();
	IRequest request = new DefaultRequest(authTokenRequest, DsqlServiceName)
	{
	UseQueryString = true,
	HttpMethod = HTTPGet
	};
	request.Parameters.Add(ActionKey, ActionValue);
	request.Endpoint = new UriBuilder(HTTPS, hostname).Uri;

	if (immutableCredentials.UseToken)
	{
	request.Parameters[XAmzSecurityToken] = immutableCredentials.Token;
	}

	var signingResult = AWS4PreSignedUrlSigner.SignRequest(request, null, new RequestMetrics(), immutableCredentials.AccessKey,
	immutableCredentials.SecretKey, DsqlServiceName, region.SystemName);

	var authorization = "&" + signingResult.ForQueryParameters;
	var url = AmazonServiceClient.ComposeUrl(request);

	// remove the https:// and append the authorization
	return url.AbsoluteUri[(HTTPS.Length + URISchemeDelimiter.Length)..] + authorization;
	}

	private class GenerateDsqlAuthTokenRequest : AmazonWebServiceRequest
	{
	public GenerateDsqlAuthTokenRequest()
	{
	((IAmazonWebServiceRequest)this).SignatureVersion = SignatureVersion.SigV4;
	}
	}
	}