Closed weitzj closed 4 days ago
weitzj
commented
3 weeks ago I am still investigating, whether the underlying OS had a change.
The OS is fine. Bisecting between 0.139 - 0.145 resulted that:
Version 0.144 is working. Version 0.145 is broken
Hints:
Might be related to a new postgres-fdw-standalone release. Need to test with an older version without this bugfix: https://github.com/turbot/steampipe-postgres-fdw/pull/487
weitzj
commented
2 weeks ago The bug is gone if I use an older postgres-fdw wrapper and use the newest steampipe aws plugin, e.g.do the following:
git clone https://github.com/turbot/steampipe-postgres-fdw.git
cd steampipe-postgres-fdw.git
make standalone plugin="aws"
# this downloads the latest tag, here 0.145.0Final test revealed:
Using the git tag 1.11.5 (as well as 1.11.6) of steampipe-postgres-fdw with the steampipe-plugin-aws git:tag 0.145.0 will trigger this bug.
Using git tag 1.11.4 of steampipe-postgres-fdw with the steampipe-plugin-aws git tag: 0.145.0 will NOT trigger this bug.
weitzj
commented
2 weeks ago Example SQL statement, which creates an aws schema with a partiion key by the aws account_id given an aws profile:
# ~/.aws/config
[profile default]
web_identity_token_file = /var/run/secrets/eks.amazonaws.com/serviceaccount/token
role_arn=arn:aws:iam::111111111:role/steampipe
[profile myaccount_01234567890]
source_profile = default
role_arn=arn:aws:iam::0123467890:role/steampipe_readonly
-- create users and roles
CREATE ROLE steampipe_users;
GRANT CONNECT ON DATABASE postgres TO steampipe_users;
GRANT TEMPORARY ON DATABASE postgres TO steampipe_users;
CREATE USER steampipe_user WITH PASSWORD 'steampipe_password';
GRANT steampipe_users TO steampipe_user;
-- load steampipe extension
DROP EXTENSION IF EXISTS steampipe_postgres_aws CASCADE;
CREATE EXTENSION IF NOT EXISTS "steampipe_postgres_aws";
-- create foreign data server and attach to awsprofile as mentioned abovee
DROP SERVER IF EXISTS steampipe_aws_myaccount_01234567890;
CREATE SERVER steampipe_aws_myaccount_01234567890 FOREIGN DATA WRAPPER steampipe_postgres_aws OPTIONS (config 'profile = "myaccount_01234567890"
regions = [
"eu-central-1",
"eu-west-1",
]
ignore_error_codes = ["AccessDenied", "AccessDeniedException", "NotAuthorized", "UnauthorizedOperation", "UnrecognizedClientException", "AuthorizationError"]
');
DROP SCHEMA IF EXISTS aws_myaccount_01234567890 CASCADE;
CREATE SCHEMA aws_myaccount_01234567890;
COMMENT ON SCHEMA aws_myaccount_01234567890 IS 'steampipe aws fdw';
GRANT USAGE ON SCHEMA aws_myaccount_01234567890 TO steampipe_users;
ALTER DEFAULT PRIVILEGES IN SCHEMA aws_myaccount_01234567890 GRANT SELECT ON TABLES TO steampipe_users;
GRANT SELECT ON ALL TABLES IN SCHEMA aws_myaccount_01234567890 TO steampipe_users;
IMPORT FOREIGN SCHEMA aws_myaccount_01234567890 FROM SERVER steampipe_aws_myaccount_01234567890 INTO aws_myaccount_01234567890;
ALTER USER steampipe_user SET search_path = public,aws,aws_myaccount_01234567890;
ALTER USER postgres SET search_path = public,aws,aws_myaccount_01234567890;
DROP SCHEMA IF EXISTS internal_aws CASCADE;
CREATE SCHEMA internal_aws;
COMMENT ON SCHEMA internal_aws IS 'internal_aws steampipe partition';
GRANT USAGE ON SCHEMA internal_aws TO steampipe_users;
ALTER DEFAULT PRIVILEGES IN SCHEMA internal_aws GRANT SELECT ON TABLES TO steampipe_users;
GRANT SELECT ON ALL TABLES IN SCHEMA internal_aws TO steampipe_users;
DROP SCHEMA IF EXISTS aws CASCADE;
CREATE SCHEMA aws;
COMMENT ON SCHEMA aws IS 'aws steampipe partition';
GRANT USAGE ON SCHEMA aws TO steampipe_users;
ALTER DEFAULT PRIVILEGES IN SCHEMA aws GRANT SELECT ON TABLES TO steampipe_users;
GRANT SELECT ON ALL TABLES IN SCHEMA aws TO steampipe_users;
-- cluster partition the aws schema based on each aws account_id
-- import each table schema of the steampipe information_schema.
-- Use the myaccount schema as a source how the table looks like.
-- e.g. you will have an intern_aws.aws_account table, which has the same format
-- as from an explicit schema when the foreign data wrapper for the steampipe plugin
-- has created.
-- internal_aws.* is the partitioned schema by sp_connection_name
-- aws.* is just a view ontop of internal_aws (idea is here actually to introduce a materialized view
-- or some other sort of caching. Do not know how to do it.
CREATE OR REPLACE FUNCTION create_aws_partition() RETURNS void AS
$$
DECLARE
aggregate_table_name TEXT;
BEGIN
FOR aggregate_table_name IN SELECT distinct(foreign_table_name)
FROM information_schema.foreign_tables
WHERE left(foreign_table_name, 4) = 'aws_'
ORDER BY foreign_table_name ASC
LOOP
EXECUTE format('DROP VIEW IF EXISTS aws.%I', aggregate_table_name);
EXECUTE format('DROP TABLE IF EXISTS internal_aws.%I', aggregate_table_name);
EXECUTE format(
'CREATE TABLE internal_aws.%I (LIKE %I.%I) PARTITION BY LIST (sp_connection_name)',
aggregate_table_name,
'aws_myaccount_01234567890', aggregate_table_name
);
EXECUTE format('CREATE VIEW aws.%I AS SELECT * FROM internal_aws.%I', aggregate_table_name, aggregate_table_name);
END LOOP;
END
$$ LANGUAGE plpgsql;
-- attach partition to aws schema
CREATE OR REPLACE FUNCTION attach_aws_account(schema_current_name text) RETURNS void AS
$$
DECLARE
table_name TEXT;
BEGIN
FOR table_name IN EXECUTE format(
'SELECT foreign_table_name FROM information_schema.foreign_tables WHERE foreign_table_schema = ''%I'' ORDER BY foreign_table_name ASC',
schema_current_name
)
LOOP
EXECUTE format(
'ALTER TABLE internal_aws.%I ATTACH PARTITION %I.%I FOR VALUES IN (''%I'')',
table_name,
schema_current_name, table_name, schema_current_name
);
END LOOP;
END
$$ LANGUAGE plpgsql;
-- create an aws schema where the partition key is the aws account_id. This way
-- we can attach multiple AWS accounts with their own AWS config to the general
-- AWS schema and mimic the internal steampipe behaviour to have an aws search path
SELECT create_aws_partition();
-- which searches accross all accounts.
SELECT attach_aws_account('aws_myaccount_01234567890');To clarify:
only the Postgres standalone AWS fdw fails
pskrbasu
commented
4 days ago @weitzj Could you try building the steampipe-postgres-fdw with the FDW latest released tag(v1.11.8) and the latest plugin version? Let us know if you still see this issue.
weitzj
commented
4 days ago I will try.
weitzj
commented
4 days ago The issue is fixed and caching seems to work. Thank you!
weitzj
commented
4 days ago Version v1.11.8 with the latest steampipe-aws plugin from master 0.146.0 works.
The new release 0.147.0 of stampipe-plugin-aws will get built/wrapped by version 1.11.8 of the postgres-fdw - so the bugfix should be finally available in version 0.147.0
Describe the bug
Using steampipe-plugin-aws version 0.145 with an steampipe-postgres-fdw version 1.11.6 breaks the AWS assumeRole session.
Using steampipe-plugin-aws version 0.145.0 with steampipe-postgres-fdw versoin 1.11.4 works as expected.
Be aware: I am talking about the release version 0.145.0 and the git-tag version 0.145.0 (as they are different depending on which steampipe-postgres-fdw release was used to build the steampipe-postgres-aws standalone plugins.
Something between a working steampipe-plugin-aws release
0.144.0(as you have published it with steampipe-postgres-fdw 1.11.4) and the version 0.145.0 version (which was published with steamppe-postgres-fdw 1.11.6) has changed regarding parsing the AWS config file.Bisecting:
release 0.144 works release 0.145 does not work
continuing....
I am testing this explicitly with:
steampipe_postgres_aws.pg15.linux_amd64Versionv0.145.0Steampipe version (
steampipe -v) Example: v0.145.0To reproduce Use steampipe-plugin-aws 0.144.0 with a working AWS config file and a steampipe config file and a Kubernetes Cluster where 2 ENV vars are set:
~/.aws/config~/.steampipe/aws.spcExpected behavior
Querying steampipe should work as in Version 0.144.0 but fails in version 0.145.0
Additional context
My guess is that the aws-go-sdk-v2 was updated in the meantime. Another guess is that, if the two exported ENV-vars mentioned above are unset, that it could work. The ENV vars are automatically injected by AWS EKS IRSA.
A sample Go program to simulate the STS assume role behaviour regarind exporting env-vars or not:
main.go
go.mod
References: