Link your AWS Account

Follow these instructions to link your AWS Account and RDS Aurora instance to Bipost API.

IMPORTANT NOTICE: Many settings suggested here are for testing purposes. If you are to use the following AWS services for production you may want to follow your company policies and understand how to use AWS security according to your needs.


Don't have an AWS account?

  1. Create an AWS account here aws.amazon.com

    Screenshot

  2. AWS usually makes an automated verification phone call, we suggest to provide a land line.

  3. Provide payment information.
  4. Select Basic Support (free plan).
  5. Check if you can open RDS Dashboard, by searching under AWS services.
  6. Congrats you have an AWS account!

Check closest AWS Region to you location

cloudping.info

Click the link above and hit HTTP Ping and look for the lowest latency.

Maybe you want to try this at different times of the day.

Take note of the closest region.

Screenshot


Get Canonical User ID from your IAM Home

To perform the following steps, you need to sign in with the root AWS account.

  1. Upper right corner of your AWS console, click your account name (or follow next link).
  2. My Security Credentials.
  3. Click Continue to Security Credentials if dialog appears.
  4. Account Identifiers.
  5. Copy AWS Account ID and Canonical User ID.

    Copy the entire string that starts with AWS Account ID up to the very end of a long hex string of 64 characters.

    Screenshot

  6. Email the above to jaime@factorbi.com so we can configure a dedicated bucket for your API calls.

Q: Is it secure to provide these numbers?

Yes, we use your Canonical User ID to create and provide access to a new and dedicated S3 bucket for your AWS Account. Further on you will link this bucket to you RDS instance.


Create Aurora Instance

Aurora DB Details

  1. From AWS Console Home, upper right corner (next to you name) be sure to select the closest region to your location.
  2. From AWS Console Home, search RDS.
  3. From RDS Dashboard, click Instances.
  4. Launch DB Instance, blue button.
  5. Select Engine: Amazon Aurora, click select.
  6. DB Instance Class: for testing purposes select the smallest available, currently t2.small
  7. Multi-AZ Deployment: for testing purposes select No
  8. DB Instance Identifier: assign a name, lower-case and no special characters.
  9. Master Username: root
  10. Master Password: assign a hard password and store it in a secure place.
  11. Confirm Password.
  12. On your left pane it is displayed an estimated monthly cost. For further information check On-Demand Pricing: RDS Pricing
  13. Click Next Step, blue button.

Screenshot

Aurora Network & Security

  1. VPC: Create new VPC
  2. Subnet Group: Create new DB Subnet Group
  3. Publicly Accessible: Yes
  4. Availability Zone: No Preference
  5. VPC Security Group: Create new Security Group

Screenshot

Aurora Database Options

  1. DB Cluster Identifier: leave blank
  2. Database Name: leave blank
  3. Database Port: 3306
  4. DB Parameter Group: default.aurora5.6
  5. DB Cluster Parameter Group: default.aurora5.6
  6. Option Group: leave default
  7. Enable Encryption: No

Aurora Failover

  • Priority: tier-0

Aurora Backup

  • Backup Retention Period: 1 day

Aurora Monitoring

  • Enable Enhanced Monitoring: No

Aurora Maintenance

  1. Auto Minor Version Upgrade: Yes
  2. Maintenance Windows: No Preference

Screenshot

Launch DB Instance

  • Click Launch DB Instance blue button.

  • This process may take a while, sometimes 30 minutes or more.

  • You can check Status of your instance by going to Instances on left navigation pane.


RDS Instance Security Group

Once the instance has Status: available proceed:

  1. Click check box way left of your DB Instance name.
  2. Click Instance Actions \ See Details gray button, on top.
  3. Lookup for Security Groups and click the blue string to the right, it may appear as

    default (sg-XXXXXXXX)

    Screenshot

  4. You are now on EC2 Dashboard and Security Group ID is already selected.

  5. Click Actions \ Edit inbound rules
  6. Click Add Rule, under Type select MYSQL/Aurora
  7. Source Custom and type value: 0.0.0.0/0
  8. Repeat steps 6 & 7, and type value ::/0

    Screenshot

  9. Click Save blue button.

  10. Click Actions \ Edit outbound rules
  11. Verify if Type: All traffic, Destination: Custom and value: 0.0.0.0/0 is already set, if not, add the rule.
  12. Go back to RDS Dashboard, select your instance, click Instance Actions \ Reboot, confirm with blue button on the right.
  13. Wait until Status is available and check if Security Groups are ( active )

Create IAM Policy to Grant Access to S3

From this point on you need the newly S3 bucket ARN that we provided over email.

If you haven't emailed us with your Canonical User ID, please follow these steps.

  1. Open IAM Console.
  2. In the left navigation pane choose Policies.
  3. Create policy blue button.
  4. Select Policy Generator

    Effect: Allow

    AWS Service: Amazon S3

    Actions: check GetObject and GetObjectVersion

    Amazon Resource Name (ARN): arn you received over email, example:

    arn:aws:s3:::bipostdata-123456789012

  5. Click Add Statement

  6. Repeat step 4 adding /* at the end of ARN bucket string, as follows:

    Effect: Allow

    AWS Service: Amazon S3

    Actions: check GetObject and GetObjectVersion

    Amazon Resource Name (ARN): example: arn:aws:s3:::bipostdata-123456789012/*

    Screenshot

  7. Click Next Step blue button.

  8. Policy Name: AllowAuroraToS3
  9. Optionally add Description.
  10. Policy Document: double check that JSON looks like this:

    {
        "Version": "2012-10-17",
        "Statement": [
            {
                "Sid": "Stmt9999999999999",
                "Effect": "Allow",
                "Action": [
                    "s3:GetObject",
                    "s3:GetObjectVersion"
                ],
                "Resource": [
                    "arn:aws:s3:::bipostdata-123456789012"
                ]
            },
            {
                "Sid": "Stmt9999999777999",
                "Effect": "Allow",
                "Action": [
                    "s3:GetObject",
                    "s3:GetObjectVersion"
                ],
                "Resource": [
                    "arn:aws:s3:::bipostdata-123456789012/*"
                ]
            }
        ]
    }
    
  11. Click Create Policy

Further information from AWS go to: Allowing Amazon Aurora to Access Amazon S3 Resources


Create IAM Role to Allow RDS Access to S3

  1. Open IAM Console.
  2. In the left navigation pane choose Roles.
  3. Create New Role blue button.
  4. Choose AWS Service Role, scroll down and select Amazon RDS

    Screenshot

  5. Attach Policy, leave blank and click Next Step blue button.

  6. Role name: RDSLoadFromS3
  7. Click Create role blue button.
  8. Click on your newly created role. This will open a Summary.
  9. Under Permissions, click Attach Policy blue button.

    Screenshot

  10. Use Filter and select Policy Type: Customer Managed

  11. Click the check box of your newly created Policy: AllowAuroraToS3
  12. Click Attach Policy blue button.
  13. Copy Role ARN string and save it for further use. It may look like this: arn:aws:iam::123456789012:role/RDSLoadFromS3

Further information from AWS go to: Creating an IAM Role to Allow Amazon Aurora to Access AWS Services


Set IAM Role to Aurora Cluster

  1. Open RDS console.
  2. Choose Clusters on left pane.
  3. Click check box of your newly cluster.
  4. Click Manage IAM Roles gray button, on top.
  5. Select the role you just created: RDSLoadFromS3 and click Done, blue button.

Screenshot


Create Cluster Parameter Group

If you are already using a custom DB Cluster Parameter Group, you can select that group instead of creating a new DB Cluster Parameter Group.

  1. Open RDS console.
  2. On left pane go to Parameter Groups.
  3. Click Create Parameter Group blue button on top.

    Parameter Group Family: aurora5.6

    Type: DB Cluster Parameter Group

    Group Name: AuroraClusterAllowAWSAccess

    Description: Allow cluster access to Amazon S3

  4. Click Create blue button.

  5. Click check box on your new auroraclusterallowawsaccess parameter group and click Edit Parameters gray button on top.
  6. Set the following:

    Name Edit Values Example
    aurora_load_from_s3_role paste Role ARN string arn:aws:iam::123456789012:role/RDSLoadFromS3
    aurora_select_into_s3_role paste Role ARN string arn:aws:iam::123456789012:role/RDSLoadFromS3
    aws_default_s3_role paste Role ARN string arn:aws:iam::123456789012:role/RDSLoadFromS3
  7. Click Save Changes blue button.

Further information from AWS go to: Associating an IAM Role with a DB Cluster


Create DB Parameter Group

If you are already using a custom DB Parameter Group, you can select that group instead of creating a new DB Parameter Group.

  1. Open RDS console.
  2. On left pane go to Parameter Groups.
  3. Click Create Parameter Group blue button on top.

    Parameter Group Family: aurora5.6

    Type: DB Parameter Group

    Group Name: AuroraInstanceAllowAWSAccess

    Description: Allow instance access to Amazon S3

  4. Click Create blue button.

  5. Click check box on your new aurorainstanceallowawsaccess parameter group and click Edit Parameters gray button on top.
  6. Set the following:

    Name Edit Values
    log_bin_trust_function_creators 1
    max_allowed_packet 1073741824
    max_connections 16000
    max_user_connections 4294967295
  7. Click Save Changes blue button.


Set Cluster Parameter Group

  1. Open RDS console.
  2. On left pane go to Clusters.
  3. Click check box on your new cluster.
  4. Click Modify Cluster gray button on top.
  5. Under Database Options, set DB Cluster Parameter Group to auroraclusterallowawsaccess.
  6. Click check box Apply Immediately and click Continue blue button.
  7. Review changes and click Modify Cluster blue button.

Set Instance Parameter Group

  1. Open RDS console.
  2. On left pane go to Instances.
  3. Click check box on your new instance.
  4. Click Instance Actions \ Modify gray button on top.
  5. Under Database Options, set DB Parameter Group to aurorainstanceallowawsaccess
  6. You may also notice that DB Cluster Parameter Group is set to auroraclusterallowawsaccess
  7. Click check box Apply Immediately and click Continue blue button.
  8. Review changes and click Modify DB Instance blue button.
  9. Click Instance Actions \ Reboot gray button on top.
  10. Confirm reboot with blue button.

Verify Instance Configuration

  1. Open RDS console.
  2. On left pane go to Instances.
  3. Click check box on your new instance.
  4. Click Instance Actions \ See Details gray button on top.
  5. Verify the following:

    Enpoint: ( authorized )

    Parameter Group: aurorainstanceallowawsaccess ( in-sync )

    DB Cluster Parameter Group: auroraclusterallowawsaccess ( in-sync )

    Security Groups: default (sg-XXXXXXXX) ( active )

    Publicly Accessible: Yes

    DB Instance Status: available


Test connection to your RDS Aurora

  1. Download and install any MySQL client of your preference:

    For Mac you may use "Sequel Pro" or "MySQL Workbench"
    For Windows you may use "MySQL Workbench" or "HeidiSQL"
    
  2. On your AWS Console go to RDS Dashboard, select your instance and copy the Cluster Endpoint, which is a blue string with more than 60 characters.

  3. Launch your MySQL client and configure a new connection:

    Name: type any name of your preference.

    Host: Paste the Cluster Endpoint and delete the suffix :3306

    Username: root

    Password: type the Master Password

    Port: 3306

    Database: Leave blank

    Connect using SSL: No

  4. Click Connect and verify that you can successfully connect to your RDS instance.


Send Instance Connection Details to Factor BI

Email all the information you used to Test connection to your RDS Aurora to jaime@factorbi.com so we can add your instance to our Bipost API.


Security of your RDS Instance for Production

If your are ready to use Bipost API for production, we highly recommend the following:

  1. Use MySQL client to create a new user.
  2. Set a strong password.
  3. Gran the new user with the following: GRANT LOAD FROM S3 ON *.* TO 'your-user-name';
  4. Set the following Global Privileges:

    Screenshot


Console Access to Bucket

Bipost synchronization uses S3 to upload the data that is extracted from the on-premises database. The bucket is located within Factor BI AWS account so we can efficiently handle API calls, patches and new releases.

Remember, we create a unique S3 bucket for each one of our customers, so nothing gets mixed up.

Sometimes you may want to access this bucket and review files and folders.

To accomplish this we provide an AWS Console access with a user, password and a direct link to your bucket.