At SEAS and CETs request: public access DB should be behind an API or something else to secure access

[markwhiting]

Message from CETs is something like:

RDS DB Instances should prohibit public access, as determined by the PubliclyAccessible configuration

Account Name | Account Number aws-csslab-commonsense-seas | 012585330321 Event Details Description: This AWS control checks whether RDS instances are publicly accessible by evaluating the > publiclyAccessible field in the instance configuration item.   Remediation: For information on how to correct this issue, consult the AWS Security Hub controls > documentation. https://docs.aws.amazon.com/console/securityhub/RDS.2/remediation   Severity: | CRITICAL Region: | us-east-1 Control Id: | RDS.2

There's more information in the email that includes ARNs and IPs, which we don't need here. As far as I know, this impacts all collections on the main DB for this platform.

This is done when:

1. we can reach out to CETs and they confirm that we are in compliance with their policy
2. we can get secured access via observable or other systems we want to set up in that way.

[markwhiting]

@amirrr — you might have thoughts on the setup for this, feel free to add notes, but I think perhaps the students can help with executing if that works. Further, just wondering, can we adhere to this by adding an IP whitelist on the DB that includes the observable IP? That might simplify things instead of implementing the API

[markwhiting]

@amirrr you had a few thoughts last time we talked about this. I think we were saying we could shift everything to a common API so that the internal processes and external data access were relatively uniform.

Can you jot down more about your thoughts on that? Is there anything else to decide about it or can we start implementing it?

[amirrr]

@amirrr you had a few thoughts last time we talked about this. I think we were saying we could shift everything to a common API so that the internal processes and external data access were relatively uniform.

Can you jot down more about your thoughts on that? Is there anything else to decide about it or can we start implementing it?

Certainly doable. The easiest way would be to leverage our current implementation of our api service for the statements and survey to expose some of the data to secure endpoint for example commonsense.seas.penn.edu/api/data/{whatever}

We just have to configure our database with user roles and the rest could work out of the box.

[markwhiting]

OK, and then would observable just get a token or something, so it can access the API?

[amirrr]

Yeah we currently support json web tokens so we can have a token for observable that doesn't expire.

[JamesPHoughton]

Current barriers to entry:

• learning more about the tools
• learning more about the specific goals/requirements

Tasks:

• @acao22 talk with @markwhiting or @amirrr about requirements
• collect all the different ways that the data is used via different services:
  • dashboards on observable
  • experiment interface
• @acao22 look at how database is being used in observable notebooks to help understand access requirements

[JamesPHoughton]

Done a lot of the learning, implementing a potential solution in the next week or two for evaluation.

One desire is to try and complete all the setup using terraform, whenever possible.

[JamesPHoughton]

Getting access permissions is a blocker, this may be resolved now by @amirrr adding permissions for @acao22. Works on @acao22's personal AWS account.

@markwhiting can also give access to stuff. Also can give permission to adjust permissions.

[JamesPHoughton]

• Working still on Lambda connection to AWS, need to work on API Gateway.
• Probably something that can't be complete this week.
• @acao22 Will carve out a chunk that can be completed this week and close that with this issue
• @acao22 Will document steps for the remainder in a new issue, that would be written appropriately for a new RA to take on, with enough detail that they know the goal, what will tell them they are successful, and where they should get started

@markwhiting may need to give @acao22 more direct access to the DB?