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!

Not familiar with AWS or just want to skip creating AWS related services? Write us.


Check closest AWS Region to you location

cloudping.info

Click the above link 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 (12-digit) and Canonical User ID (64-digit).

    Screenshot

  6. Email these numbers to info@factorbi.com so we can setup your dedicated Bucket.

Q: How these numbers are used?

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 IAM Policy to Grant Access to S3

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

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

    Screenshot

  6. Copy and paste the following.

    {
        "Version": "2012-10-17",
        "Statement": [
            {
                "Effect": "Allow",
                "Action": [
                    "s3:GetObject",
                    "s3:GetObjectVersion"
                ],
                "Resource": [
                    "arn:aws:s3:::bipostdata-123456789012"
                ]
            },
            {
                "Effect": "Allow",
                "Action": [
                    "s3:GetObject",
                    "s3:GetObjectVersion"
                ],
                "Resource": [
                    "arn:aws:s3:::bipostdata-123456789012/*"
                ]
            }
        ]
    }
    
  7. Replace the text arn:aws:s3:::bipostdata-123456789012 with the bucket ARN you received from us over email.

  8. Double check the JSON text is using your own ARN bucket and click Review policy blue button on the lower right.

  9. Enter the following on the Review policy screen.

    Name: AllowAuroraToS3

    Name: Connection to Factor BI bucket.

  10. Click Create policy blue button.

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 role blue button.
  4. Choose AWS service, then RDS

    Screenshot

  5. Select your use case click RDS - CloudHSM and Directory Service, click Next: Permissions blue button.

  6. Click Next:Review.
  7. Set Role name: RDSLoadFromS3 and click Create role.
  8. Now from the navigation details, click the role you just created.
  9. Under permissions tab, detach by clicking X the following:

    AmazonRDSDirectoryServiceAccess

    RDSCloudHsmAuthorizationRole

  10. Now click Attach policy blue button.

    Screenshot

  11. Select AllowAuroraToS3 and click Attach policy blue button.

  12. 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


Create Aurora Instance

Instance specifications

  1. From AWS Console Home, upper right corner (next to you name) be sure to select the closest region to your location.
  2. Under menu Services search RDS.
  3. From Amazon RDS click Instances.
  4. Launch DB Instance, orange button.
  5. Select Engine: Amazon Aurora, scroll down, Edition: MySQL 5.7-compatible click Next orange button.
  6. DB instance class: for testing purposes select the smallest available, currently t2.small
  7. Multi-AZ deployment: for testing purposes select No
  8. Settings, DB Instance identifier: assign a name, lower-case and no special characters.
  9. Master username: root
  10. Master Password: assign a strong password and store it in a secure place.

    Password can't contain spaces and the following characters:

    /
    "
    @
    
  11. Click Next orange button.

Screenshot

Network & Security

  1. Virtual Private Cloud (VPC): Create new VPC
  2. Subnet group: Create new DB Subnet Group
  3. Publicly accessible: Yes
  4. Availability zone: No Preference
  5. VPC security groups: Create new VPC security groups

Screenshot

Database options

  1. DB Cluster Identifier: leave blank
  2. Database name: leave blank
  3. Database port: 3306
  4. DB parameter group: default.aurora-mysql5.7
  5. DB cluster parameter group: default.aurora-mysql5.7
  6. Option group: leave default

Encryption

  • Encryption: Disable encryption

Failover

  • Priority: tier-0

Backup

  • Backup retention period: 1 day

Monitoring

  • Enhanced Monitoring: Disable enhanced monitoring

Maintenance

  1. Auto minor version upgrade: Enable auto minor version upgrade
  2. Maintenance windows: No preference

Launch DB Instance

  • Click Launch DB Instance orange button.

  • This process may take a few minutes.

  • Click View DB Instance details.


RDS Instance Security Group

Click Instances left pane.

Once the new instance has Status: available proceed:

  1. Click your new instance.
  2. Scroll down to Connect section.
  3. Under Security group rules click the blue string that looks like this

    rds-launch-wizard (sg-XXXXXXXX)

    Screenshot

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

  5. Click Actions \ Edit inbound rules
  6. Remove the default Custom TCP rule created.
  7. Click Add Rule, under Type select MYSQL/Aurora
  8. Source Custom and enter this value: 0.0.0.0/0

  9. Repeat steps 7 & 8, and type value ::/0

    Screenshot

  10. Click Save blue button.

  11. Click Actions \ Edit outbound rules
  12. Verify if Type: All traffic, Destination: Custom and value: 0.0.0.0/0 is already set, if not, add the rule.
  13. Go back to RDS Console, select your instance, click Instance actions \ Reboot, confirm with orange button on the right.
  14. Wait until Status is available

    Screenshot

  15. Click your DB Instance, scroll down to Details section and check if Security groups are ( active )


Set IAM Role to Aurora Cluster

  1. Open RDS console.
  2. Choose Clusters on left pane.
  3. Click radio button of your newly cluster.
  4. Click Actions then Manage IAM roles.

    Screenshot

  5. Under Add IAM roles to this cluster select the role you just created: RDSLoadFromS3 and click Add role button.

    Screenshot

  6. Wait until you see Status active under Current IAM roles for this cluster.

  7. Click Done.

Create Cluster Parameter Group

  1. Open RDS console.
  2. On left pane go to Parameter Groups.
  3. Click Create parameter group orange button on top.

    Parameter group family: aurora-mysql5.7

    Type: DB Cluster Parameter Group

    Group name: AuroraClusterAllowAWSAccess

    Description: Allow cluster access to Amazon S3

  4. Click Create orange button and refresh browser.

  5. Click check box on your new auroraclusterallowawsaccess parameter group and click Parameter group actions and then Edit button on top.
  6. Make sure you have your ARN role string (step 12 here) and replace it below.
  7. 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
  8. Click Save Changes orange button.

  9. Click Preview changes and it should look like this:

    Screenshot

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


Create DB Parameter Group

  1. Open RDS console.
  2. On left pane go to Parameter Groups.
  3. Click Create parameter group orange button on top.

    Parameter group family: aurora-mysql5.7

    Type: DB Parameter Group

    Group name: AuroraInstanceAllowAWSAccess

    Description: Allow instance access to Amazon S3

  4. Click Create orange button and refresh browser.

  5. Click check box on your new aurorainstanceallowawsaccess parameter group and click Parameter group actions and then Edit 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 Preview changes and double check.

  8. Click Save changes orange button.

Set Cluster Parameter Group

  1. Open RDS console.
  2. On left pane go to Clusters.
  3. Click radio button on your new cluster.
  4. Click Actions then Modify cluster button on top.
  5. Under Database options, set DB cluster parameter group to auroraclusterallowawsaccess.
  6. Scroll down and click Continue.
  7. Click option Apply immediately and click Modify cluster.

Set Instance Parameter Group

  1. Open RDS console.
  2. On left pane go to Instances.
  3. Click radio button on your new instance.
  4. Click Instance Actions \ Modify button on top.
  5. Scroll down and under Database options, set DB parameter group to aurorainstanceallowawsaccess
  6. You may also notice that DB cluster parameter group is already set to auroraclusterallowawsaccess
  7. Click Continue orange button.
  8. Select Apply Immediately and click Modify DB Instance.
  9. Use refresh icon and wait until Status is available on your instance.
  10. Click Instance actions, select Reboot and confirm.
  11. Use refresh icon and wait until Status is available on your instance.

Verify Instance Configuration

  1. Open RDS console.
  2. On left pane go to Instances.
  3. Click your new instance.
  4. Verify the following:

    DB instance status: available

    Parameter group: aurorainstanceallowawsaccess (in-sync)

    DB cluster parameter group: auroraclusterallowawsaccess (in-sync)

    Security groups: rds-launch-wizard (sg-XXXXXXXX) ( active )

    Publicly accessible: Yes


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, then Clusters, select your new cluster 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.

    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.


Configure Instance Connection Details on Factor BI Console

  1. Log in to Factor BI Console.
  2. Go to RDS Instances and then click under Hostname.
  3. Complete all fields on the form with instance connection information.

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


Security for Downloading Data

If you plan to download data from Aurora to your on-premises databases, there are some settings to make on your AWS account.

  1. Open IAM console.
  2. Click Add user blue button on top left corner.
  3. User name: outFromS3
  4. Access type: Programmatic access
  5. Click Next: Permissions blue button lower right corner.

    Screenshot

  6. Select Attach existing policies directly

  7. On the search box type S3 and select AmazonS3FullAccess
  8. Click Next: Review blue button lower right corner.

    Screenshot

  9. Click Download .csv.

  10. Email the CSV to info@factorbi.com so we can setup the downloading process.

    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.