As OFA tech lead, I need a new permissions group for OFA data analysts

[ADPennington]

Description: As captured in #888, we need a permissions group for OFA's data analysts. group name = DIGIT Team.

Acceptance Criteria:

• [ ] User group should be able to access all raw data files in S3 via: /admin/data_files/*
• [ ] User group should be able to access the most recent version of parsed and validated data that has been accepted into the Elastisearch postgres db

Tasks:

• [ ] Add user group
• [ ] Define permissions migration steps
• [ ] test parsed data filtering (show only most recent submission, etc)
• [ ] Update tests from #1387 to account for user transitions
• [ ] Update boundary diagram and system architecture diagrams to reflect access to elastic/kibana/postgres

Supporting documentation

• 1178

Open questions

• what kind of interface makes sense to give DIGIT Team members access to postgres data? (in absence of elastic/kibana access)
• can we limit group access to read only? or only certain operations, separate postgres user, control access via nginx, etc.

[valcollignon]

@abottoms-coder and @jtwillis92 to sync on this issue and refine further per backlog refinement 11.16.21

[valcollignon]

@abottoms-coder will sync with @jtwillis92 per backlog refinement 11.30.21

[valcollignon]

@abottoms-coder to refine further at a later time. Maybe belongs in v3 per @ADPennington. Backlog refinement 12.21

[ADPennington]

@jtimpe this ticket has info on how ofa pulls data down from legacy system: https://github.com/raft-tech/TANF-app/issues/1178

also note: the db returns data for most recent data submission (not all versions).

[ADPennington]

Notes about how OFA access data from legacy system:

• OFA staff must be using gov-provided laptops, which requires PIV for login
• OFA staff must be connected to VPN to connect to Oracle db

• OFA staff have a unique user/pass, and are provided with host, port and service identifier to connect to db. See example below:

  # set env vars
  prod_dbuid = glob.os.environ.get('ap_uid')
  prod_dbpw = glob.os.environ.get('ap_pwd')
  prod_host = glob.os.environ.get('host')
  prod_port= glob.os.environ.get('port')
  prod_sid = glob.os.environ.get('sid')
  
  # create data source name
  dsn_tns = cx_Oracle.makedsn(prod_host, prod_port, prod_sid)
  
  # establish connection to db
  connection = cx_Oracle.connect(prod_dbuid, prod_dbpw, dsn_tns)

  • OFA staff do not have the ability to change data in the db. the only way that data can change is if STTs re-submit data files (or in the rare scenario where the system admin makes a change to the db). Therefore, OFA staff work with data only from the most recent submission. STTs are expected to submit complete data files
  • OFA staff also have access to the raw data files that sit on ACFTitan server prior to being parsed/validated.
  • OFA staff generally extract the data from db by STT type, record type, and fiscal period (e.g. all active family (i.e. T1) records for tribes in FY23Q1). Example queries below:

    
    -- select all T1 records for FY23 for California
    SELECT *
    FROM tanf.active_family
    WHERE fips_code = 6 and rpt_month_year BETWEEN 202210 AND 202309;

  -- select all T6 records for FY23 for tribes SELECT * FROM tanf.aggregate WHERE tribe_code > 0 and rpt_month_year BETWEEN 202210 AND 202309;

[elipe17]

Follow-on ticket created for Kibana related features. #2840

[elipe17]

Note, CSV export of queries is only supported in AWS ES 7.9. However, Cloud.gov only supports AWS ES 7.4. Thus, we can generate equivalent KQL/Elastic queries, but there will not be an easy way to export them until cloud.gov supports a newer version of the AWS ES service, or we are no longer bound to cloud.gov.

[ADPennington]

Note, CSV export of queries is only supported in AWS ES 7.9. However, Cloud.gov only supports AWS ES 7.4. Thus, we can generate equivalent KQL/Elastic queries, but there will not be an easy way to export them until cloud.gov supports a newer version of the AWS ES service, or we are no longer bound to cloud.gov.

@elipe17 what are the export options with 7.4 ? how would OFA staff retrieve the data to read into our programming tools?

[elipe17]

Note, CSV export of queries is only supported in AWS ES 7.9. However, Cloud.gov only supports AWS ES 7.4. Thus, we can generate equivalent KQL/Elastic queries, but there will not be an easy way to export them until cloud.gov supports a newer version of the AWS ES service, or we are no longer bound to cloud.gov.

@elipe17 what are the export options with 7.4 ? how would OFA staff retrieve the data to read into our programming tools?

@ADPennington We can accomplish this using cURL. I will provide instructions in an MD along with some example queries that have been converted and are verified to work.