Unable to create Model due to "MySQL has gone away" storing G Suite group members

[ogunz]

First, help us help you by providing the following information when opening an issue.

• Which version of Forseti Security you're using (look in /home/ubuntu/forseti-security/google/cloud/forseti/init.py
  on the client).
  """Google Cloud Forseti."""
  version = '2.25.1'
  package_name = 'forseti-security'

• Which module(s) (inventory, scanner, enforcer, explain) you're having trouble with.
  I believe its inventory therefor causing me not to be able to create a model to use any other module

• Include errors, log output, and host operating system, including installed packages.
  Ubuntu 18.04.4

Error that returns on forseti-server-vm when trying to run command "forseti model create --inventory_index_id <INVENTORY_INDEX_ID> <MODEL_NAME>"
"Error occurred on the server side, message: <_Rendezvous of RPC that terminated with:
status = StatusCode.UNKNOWN
details = "Exception calling application: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invali
d transaction is rolled back [SQL: 'SELECT model.name AS model_name, model.handle AS model_handle, model.descriptio
n AS model_description, model.watchdog_timer_datetime AS model_watchdog_timer_datetime, model.created_at_datetime A
S model_created_at_datetime, model.etag_seed AS model_etag_seed \nFROM model \nWHERE model.name = %(param_1)s'] [pa
rameters: [{'%(140219278530544 param)s': 'modelrulestest002'}]]"
debug_error_string = "{"created":"@1593458490.581113361","description":"Error received from peer ipv6:[::1]
:50051","file":"src/core/lib/surface/call.cc","file_line":1052,"grpc_message":"Exception calling application: (sqla
lchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: 'SELECT model.name A
S model_name, model.handle AS model_handle, model.description AS model_description, model.watchdog_timer_datetime A
S model_watchdog_timer_datetime, model.created_at_datetime AS model_created_at_datetime, model.etag_seed AS model_e
tag_seed \nFROM model \nWHERE model.name = %(param_1)s'] [parameters: [{'%(140219278530544 param)s': 'modelrulestes
t002'}]]","grpc_status":2}"

"

• Note how you deployed (via Deployment Manager, Terraform, local system, etc.).
  Deployed using terraform

Secondly, if the issue is a feature request, be descriptive regarding what needs to be
changed and why, e.g. what to scan for, what would be a 'good' state and what would be
a 'violation' etc.

Finally, be sure to see the FAQ and Get Help
for assistance.

Thanks!

[auto-comment]

Thank you for opening an issue. Our team's interrupts engineer will review your issue shortly.

Issue Resolution:

• [Interrupts Engineer] Triage / apply categorization labels
• [Interrupts Engineer] Verify / Reproduce the reported issue
• [Forseti Engineer] Perform root cause analysis
• Forseti Engineer] Add tasks and next steps to resolve this issue.

[ogunz]

Hi Forseti team, can i please get some help here.

[gkowalski-google]

While troubleshooting the model creation today, we noticed that Forseti is getting some 400 errors for getting G Suite groups. Forseti is able to get most of the users and groups successfully it appears. This G Suite domain has a lot of users/groups, which is why the inventory/model is taking so long. @ogunz Can you supply a sample error message from the G Suite errors?

We are going to wait for the inventory to create (which is taking 24+ hours), and then will see if we can create a model. We noticed that the server today had an error message in the console logs about "too many open files". We verified the ubuntu ulimit's were set at reasonable levels, and watched an inventory to see if a lot of open files/sockets were being left open. It didn't appear like a lot (about 1,000) after 10-15 minutes, however it might get worse.

Some ideas to troubleshoot further:

• try using a machine type bigger than n1-standard-8. It didn't seem like there were any resource constraints, but can't hurt.
• come up with some sql commands that can be used to monitor the inventory/model processes
• add some more helpful logging to inventory/model creation

[gkowalski-google]

@ogunz I have found an issue with the Forseti model creation. It appears you have seen this issue Can't reconnect until invali d transaction is rolled back, so hopefully this fix will help. See: #3774

[ogunz]

Can you give more clarity on "Update model creation to ignore the case of the permission.". How to achieve this task?

…

On Thu, Jul 16, 2020 at 11:21 PM Gregg Kowalski ***@***.***> wrote: @ogunz <https://github.com/ogunz> I have found an issue with the Forseti model creation. It appears you have seen this issue Can't reconnect until invali d transaction is rolled back, so hopefully this fix will help. See: #3774 <#3774> — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#3767 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AJFJO5R6HXEPJJEDFIXQA2DR37UWTANCNFSM4OMSHDQA> .

-- Oscar Gonzalez | Application Engineer

[gkowalski-google]

I will be providing a fix. Working on a couple options, and hope to have this merged into the main branch shortly. You can switch to use that branch/version of Forseti until a patch release comes out.

[ogunz]

Hi Gregg, How can I switch to use the working branch/version of forseti until a patch release comes out? otherwise whats the eta on the patch fix?

…

On Fri, Jul 17, 2020 at 8:53 PM Gregg Kowalski ***@***.***> wrote: I will be providing a fix. Working on a couple options, and hope to have this merged into the main branch shortly. You can switch to use that branch/version of Forseti until a patch release comes out. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#3767 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AJFJO5TLRXWI3YZQ3ZRA5PDR4EMEHANCNFSM4OMSHDQA> .

-- Oscar Gonzalez | Application Engineer

[gkowalski-google]

Hi @ogunz, the fix has been merged into the master branch. You can deploy this version with Terraform by setting forseti_version = "master"'. It is recommended to use the master branch version of the Forseti Terraform module as well, which can be done by using the module sourced from GitHub source = "git::https://github.com/forseti-security/terraform-google-forseti.git"`.

I am working on a patch release that will be out next week. If you do test the new version, please let me know if it resolves the issue for you.

[gkowalski-google]

@ogunz The latest Forseti release is out to fix the model creation issue. Please upgrade at your convenience and let me know if the issue is resolved for you. You can upgrade with Terraform by setting the Forseti module version to 5.2.2 or ~> 5.2.0.

[gkowalski-google]

The model creation issue related to the duplicate permissions has been resolved, but there is an issue happening during model creation to save the G Suite group members:

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2006, "MySQL server has gone away (ConnectionResetError(104, 'Connection reset by peer'))") [SQL: 'INSERT INTO {{ MODEL_HANDLE }}_group_members

We have tried setting cloudsql_net_write_timeout = 600 with no luck and are exploring other MySQL flags including max_allowed_packet.

[gkowalski-google]

Neither of the MySQL flags helped, the model creation still fails here while storing the G Suite memberships. I believe this could be resolved by using one of the SQLAlchemy bulk operations or by adding the rows to the sessions and flushing after x number of items; the latter is how other places in the code typically deal with this.

[gkowalski-google]

Latest changes are on branch: feature/store-gsuite-membership-in-batch

There seems to be more success with this branch, however now hitting this error:

Field 'group_name' doesn't have a default value\") [SQL: 'INSERT INTO `{{ MODEL }}_group_members

...

File \"/usr/local/lib/python3.6/dist-packages/forseti_security-2.25.1-py3.6.egg/google/cloud/forseti/services/model/importer/importer.py\", line 357, in run\n    post_action=self._store_gsuite_membership_post\n  File \"/usr/local/lib/python3.6/dist-packages/forseti_security-2.25.1-py3.6.egg/google/cloud/forseti/services/model/importer/importer.py\", line 444, in model_action_wrapper\n    post_action()\n  File \"/usr/local/lib/python3.6/dist-packages/forseti_security-2.25.1-py3.6.egg/google/cloud/forseti/services/model/importer/importer.py\", line 504, in _store_gsuite_membership_post\n