psycopg2.errors.CardinalityViolation: ON CONFLICT DO UPDATE command cannot affect row a second time #18
Comments
|
I tried this after reading that support for sub-accounts was implemented in #4. I didn't find any documentation for it, though. Let me know if I'm doing something wrong here. |
|
Hmm, I'll have to look into this. There are still a few sharp edges on sub-accounts. |
|
To reproduce:
If I swap the order of the accounts I get the same error on the second import. |
|
I was not able to reproduce this with the latest release, although it could be the result of a difference with our respective data sets. Can you try with the latest, and if it's still happening, we can dig in? |
|
Yes, I see you just made a new release: https://github.com/goldfiglabs/introspector/releases/tag/v0.1.3 I think I'll have time to try it over the weekend. |
|
I get the same error when using the latest version. It looks like the problem is in the aws_config_configurationrecorder table.
I tried to rule that out by setting the service flag to import just the ec2 service. ./introspector account aws import --service ec2But I got the same error result. I didn't see any examples of how to use the service flag, so I might be doing that wrong. How can we dig into this? |
|
Some quick background first: introspector has a series of tables for doing an import. First is After running an import, even if it's just a single service (the We can verify this by running the Can you post the results of that query (or email them) ? |
I haven't had time to pick apart the query to discover why, but I found a lot of duplicates. Read on for details. The analysis is performed after starting with a fresh introspector database and reproducing the import error. raw_importThe relation raw_import is empty. resourceThe relation resource has two rows for the config service. Presumably they reprresent the configuration recorder in each account. Surprisingly the path contains the organization ID instead of the AWS account ID. The uri and provider_account_id values are the same for the two rows. SELECT * FROM resource WHERE service = 'config';resource_attributeThe relation resource_attribute is empty. resource_relationThe relation resource_relation has four rows for each the configuration recorders. It looks like the intent is to describe the related IAM roles (relation="acts-as") and AWS accounts (relation="in"). It looks like there is some duplication here. Each recorder is "in" the same account twice. Each recorder "acts-as" two IAM roles, and those roles are in different accounts. The provider_account_id is the same for all rows. SELECT * FROM resource_relation WHERE resource_ID IN (6, 8);aws_config_configuration_recorderThe relation aws_config_configurationrecorder has two rows. I'm not sure how to interpret this data. The _id column coincidentally has the same values as the resource relation. The uri and provider_account_id is the same for both rows. In fact the two rows are identical except for the values in _id, includeglobalresources, laststarttime, and laststatuschangetime. SELECT * FROM aws_config_configurationrecorder;0074-aws_config_configurationrecorder.sqlThe query outputs four rows. There are duplicates in all the columns you indicated. The uri and the provider_account_id are the same for all rows. The _id values 6 and 8 are repeated. The output of the query in 0074-aws_config_configurationrecorder.sql looks like this: |
|
Got it, I should be able to dig in on this. Thanks for the data. |
|
Thanks. Let me know if you need anything else. The accounts are just demo accounts, so I can freeze the configuration for now. |
|
Hmm, so the culprit appears to be that each config recorder has a link to two different IAM roles ( So the next question is how did that happen? If these are demo accounts, would you be comfortable sharing a pg_dump over email? If not, probably the next step is to look for the resources 151 and 365. If those are duplicates, then something is likely messed up with mapping the IAM roles properly. SELECT
*
FROM
resource AS R
INNER JOIN resource_raw AS Raw
ON R.id = Raw.resource_id
WHERE
R.id IN (151, 365)I will also keep looking into a way to reproduce the issue. |
Sure, I'll try to get that to you before next week. |
|
@gsoltis , I just emailed you the dump. Let me know if you need anything else. I created the dump like this. pg_dump --host=localhost --dbname=introspector --user=postgres --file=introspector_dump.sql
tar --create --bzip2 --file=introspector_dump.sql.tar.bzip2 -- introspector_dump.sql
rm introspector_dump.sqlYou can restore the dump like this. # Reinitialize introspector
docker-compose down --volumes
docker-compose up --detach
introspector init
# Prepare database for import
dropdb --host=localhost --user=postgres introspector
createdb --host=localhost --user=postgres introspector
# Load dump into database
tar --extract --bzip2 --file=introspector_dump.sql.tar.bzip2
psql --host=localhost --user=postgres --dbname=introspector --file=introspector_dump.sql --quiet --output=/dev/null |
I'm guessing that resources 151 and 365 are those referenced in the _iam_role_id colum of the output of query 74. What I'm seeing is that they are not duplicates, but something stranger. Resource 365 is not an IAM role but a VPC route table. So, yes, something has messed up somewhere :-) I got the following two rows from the query in your last comment. Do you get the same from my dump? Resource 151 is an IAM role. Resource 365 is a VPC route table. |
|
I found the problem. Configuration recorders do not have an arn (see config resources). So, we made one up. Unfortunately, I forgot to include the account id in the made-up uri. Your import data has two recorders in different accounts, both with the same name, and so they conflicted, resulting in some assumptions being violated. Most resources don't have this problem, since they supply their own URIs, but some do not. I've updated the uri function to include the account id, and verified that it no longer causes a crash. Will merge soon. |
|
Thanks for providing the db dump, it made tracking down the issue much faster. |
Maybe it's because there can only be one recorder per account per region, but still, how strange that there is no ARN!
Incidentally, they were created using nozaq's terraform-aws-secure-baseline module. It will create a recorder in all regions with the same name. I ran the module in multiple accounts to test AWS Config's resource aggregation.
No problem, happy to help!
I'll try it over here when you tell me it's ready :-) |
|
Can you try with image |
|
Hi, @gsoltis , The new v2.1.4 is working well. Running with account 1: Running with account 2: (Sorry for the delay! Had to catch up with other things.) |
|
Awesome! Glad to hear it! |
I'm trying to import a second AWS account to my introspector database.
The first account is the organization management account. The second acount is a member of that organization.
I created the introspector IAM user in the member acount and set its credentials via the AWS_PROFILE environment variable.
I got the following error:
Error in context:
I'm using Docker image e934e4551c83 and release v0.1.2.
The text was updated successfully, but these errors were encountered: