Closed byt3bl33d3r closed 1 year ago
@byt3bl33d3r byt3bl33d3r I suspect that even if we granted permissions, this would not work as you expect on the foreign tables. As a test, can you connect to steampipe as root from hasura and see how it behaves?
The issue is around Postgresql triggers on writes to plugin tables. Steampipe plugin tables are virtual foreign tables. Data for these tables are fetched in realtime when a SELECT for a table is done.
Is your feature request related to a problem? Please describe
I'm trying to hook up Hasura so I can have a "drop-in" GraphQL API to Steampipe. I'm running steampipe in service mode through docker. Initially everything seemed to work out of the box, however, the underlying postgres db that steampipe uses has some funky permission stuff going on that doesn't allow Hasura to create triggers which it uses for Event Triggers. When attempting to create a Grahpql Subscriptions instead, the plugins throw a segmentation violation.
GraphQL queries work just fine out of the box which in itself is dang cool:
Taking a look at the steampipe logs at
/home/steampipe/.steampipe/logs
in the docker container I see two different errors.Error on Event Trigger Creation
It seems like the steampipe user doesn't have permissions to create additional schemas which result in the following error
Expand to see error (lots of text)
``` 2023-06-14 12:57:48.817 UTC [2343] LOG: disconnection: session time: 0:03:00.975 user=steampipe database=steampipe host=172.28.0.3 port=38448 2023-06-14 12:57:48.819 UTC [2345] LOG: disconnection: session time: 0:03:00.948 user=steampipe database=steampipe host=172.28.0.3 port=38452 2023-06-14 12:57:56.829 UTC [2344] LOG: disconnection: session time: 0:03:08.958 user=steampipe database=steampipe host=172.28.0.3 port=38454 2023-06-14 12:58:33.620 UTC [2347] ERROR: permission denied for database steampipe 2023-06-14 12:58:33.620 UTC [2347] STATEMENT: CREATE SCHEMA hdb_catalog 2023-06-14 12:58:43.624 UTC [2347] ERROR: permission denied for database steampipe 2023-06-14 12:58:43.624 UTC [2347] STATEMENT: CREATE SCHEMA hdb_catalog 2023-06-14 12:58:53.630 UTC [2347] ERROR: permission denied for database steampipe 2023-06-14 12:58:53.630 UTC [2347] STATEMENT: CREATE SCHEMA hdb_catalog 2023-06-14 12:59:03.637 UTC [2347] ERROR: permission denied for database steampipe 2023-06-14 12:59:03.637 UTC [2347] STATEMENT: CREATE SCHEMA hdb_catalog 2023-06-14 12:59:03.641 UTC [2347] ERROR: schema "hdb_catalog" does not exist 2023-06-14 12:59:03.641 UTC [2347] STATEMENT: CREATE OR REPLACE function hdb_catalog."notify_hasura_test_steampipe_INSERT"() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE _old record; _new record; _data json; BEGIN IF TG_OP = 'UPDATE' THEN _old := row((SELECT "e" FROM (SELECT OLD."type" , OLD."_ctx" , OLD."referenced_group_id" , OLD."prefix_list_id" , OLD."pair_user_id" , OLD."referenced_vpc_peering_connection_id" , OLD."group_id" , OLD."pair_group_id" , OLD."owner_id" , OLD."ip_protocol" , OLD."vpc_id" , OLD."description" , OLD."is_egress" , OLD."from_port" , OLD."pair_vpc_peering_connection_id" , OLD."partition" , OLD."pair_peering_status" , OLD."referenced_peering_status" , OLD."region" , OLD."pair_vpc_id" , OLD."security_group_rule_id" , OLD."title" , OLD."akas" , OLD."referenced_user_id" , OLD."pair_group_name" , OLD."cidr_ip" , OLD."referenced_vpc_id" , OLD."to_port" , OLD."cidr_ipv6" , OLD."group_owner_id" , OLD."group_name" , OLD."account_id" , OLD."cidr_ipv4" ) AS "e" ) ); _new := row((SELECT "e" FROM (SELECT NEW."type" , NEW."_ctx" , NEW."referenced_group_id" , NEW."prefix_list_id" , NEW."pair_user_id" , NEW."referenced_vpc_peering_connection_id" , NEW."group_id" , NEW."pair_group_id" , NEW."owner_id" , NEW."ip_protocol" , NEW."vpc_id" , NEW."description" , NEW."is_egress" , NEW."from_port" , NEW."pair_vpc_peering_connection_id" , NEW."partition" , NEW."pair_peering_status" , NEW."referenced_peering_status" , NEW."region" , NEW."pair_vpc_id" , NEW."security_group_rule_id" , NEW."title" , NEW."akas" , NEW."referenced_user_id" , NEW."pair_group_name" , NEW."cidr_ip" , NEW."referenced_vpc_id" , NEW."to_port" , NEW."cidr_ipv6" , NEW."group_owner_id" , NEW."group_name" , NEW."account_id" , NEW."cidr_ipv4" ) AS "e" ) ); ELSE /* initialize _old and _new with dummy values for INSERT and UPDATE events*/ _old := row((select 1)); _new := row((select 1)); END IF; _data := json_build_object( 'old', NULL, 'new', row_to_json((SELECT "e" FROM (SELECT NEW."type" , NEW."_ctx" , NEW."referenced_group_id" , NEW."prefix_list_id" , NEW."pair_user_id" , NEW."referenced_vpc_peering_connection_id" , NEW."group_id" , NEW."pair_group_id" , NEW."owner_id" , NEW."ip_protocol" , NEW."vpc_id" , NEW."description" , NEW."is_egress" , NEW."from_port" , NEW."pair_vpc_peering_connection_id" , NEW."partition" , NEW."pair_peering_status" , NEW."referenced_peering_status" , NEW."region" , NEW."pair_vpc_id" , NEW."security_group_rule_id" , NEW."title" , NEW."akas" , NEW."referenced_user_id" , NEW."pair_group_name" , NEW."cidr_ip" , NEW."referenced_vpc_id" , NEW."to_port" , NEW."cidr_ipv6" , NEW."group_owner_id" , NEW."group_name" , NEW."account_id" , NEW."cidr_ipv4" ) AS "e" ) ) ); BEGIN /* NOTE: formerly we used TG_TABLE_NAME in place of tableName here. However in the case of partitioned tables this will give the name of the partitioned table and since we use the table name to get the event trigger configuration from the schema, this fails because the event trigger is only created on the original table. */ IF (TG_OP <> 'UPDATE') OR (_old <> _new) THEN PERFORM hdb_catalog.insert_event_log(CAST('aws' AS text), CAST('aws_vpc_security_group_rule' AS text), CAST('test_steampipe' AS text), TG_OP, _data); END IF; EXCEPTION WHEN undefined_function THEN IF (TG_OP <> 'UPDATE') OR (_old *<> _new) THEN PERFORM hdb_catalog.insert_event_log(CAST('aws' AS text), CAST('aws_vpc_security_group_rule' AS text), CAST('test_steampipe' AS text), TG_OP, _data); END IF; END; RETURN NULL; END; $$; 2023-06-14 13:02:04.424 UTC [2347] LOG: disconnection: session time: 0:07:16.104 user=steampipe database=steampipe host=172.28.0.3 port=38480 2023-06-14 13:07:05.995 UTC [2384] LOG: connection received: host=172.28.0.3 port=37404 2023-06-14 13:07:06.005 UTC [2384] LOG: connection authenticated: identity="steampipe" method=scram-sha-256 (/home/steampipe/.steampipe/db/14.2.0/data/pg_hba.conf:30) ```Error on Graphql Subscription
I used the following graphql to attempt to create the subscription:
This made the
aws
plugin throw a segmentation violation:Expand to see error (lots of text)
``` 2023-06-14 13:07:12.929 UTC [2389] LOG: connection authorized: user=steampipe database=steampipe SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256) fatal error: unexpected signal during runtime execution [signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x7f4b08] runtime stack: runtime.throw({0x7ff0ae4823d6?, 0x1d2da6d?}) /opt/hostedtoolcache/go/1.19.9/x64/src/runtime/panic.go:1047 +0x5f fp=0x7ffc3cce7f08 sp=0x7ffc3cce7ed8 pc=0x7ff0ad7255df runtime.sigpanic() /opt/hostedtoolcache/go/1.19.9/x64/src/runtime/signal_unix.go:819 +0x369 fp=0x7ffc3cce7f58 sp=0x7ffc3cce7f08 pc=0x7ff0ad73bce9 goroutine 17 [syscall, locked to thread]: runtime.cgocall(0x7ff0ae4351b0, 0xc000715a70) /opt/hostedtoolcache/go/1.19.9/x64/src/runtime/cgocall.go:158 +0x5c fp=0xc000715a48 sp=0xc000715a10 pc=0x7ff0ad6ef1dc main._Cfunc_ExecEvalExpr(0x1d2da68, 0x14b7aa0, 0xc000913ba8) _cgo_gotypes.go:2946 +0x51 fp=0xc000715a70 sp=0xc000715a48 pc=0x7ff0ae4239f1 main.getQualValue.func4(0x7ff0ae493a06?, 0x7ff000000077?, 0xc000715b10?) /home/runner/work/steampipe-postgres-fdw/steampipe-postgres-fdw/quals.go:350 +0x74 fp=0xc000715ab8 sp=0xc000715a70 pc=0x7ff0ae42f354 main.getQualValue(0x1d1ce68, 0x26220d0, 0x2633068) /home/runner/work/steampipe-postgres-fdw/steampipe-postgres-fdw/quals.go:350 +0x149 fp=0xc000715b40 sp=0xc000715ab8 pc=0x7ff0ae42ef69 main.qualFromOpExpr(0x26342a8, 0x26220d0, 0xc000715ca0?) /home/runner/work/steampipe-postgres-fdw/steampipe-postgres-fdw/quals.go:110 +0x1b4 fp=0xc000715bf0 sp=0xc000715b40 pc=0x7ff0ae42d894 main.restrictionsToQuals(0x26220d0, 0x1b?) /home/runner/work/steampipe-postgres-fdw/steampipe-postgres-fdw/quals.go:46 +0x232 fp=0xc000715ce8 sp=0xc000715bf0 pc=0x7ff0ae42cfd2 main.goFdwBeginForeignScan(0x26220d0, 0x10) /home/runner/work/steampipe-postgres-fdw/steampipe-postgres-fdw/fdw.go:230 +0x2c5 fp=0xc000715e10 sp=0xc000715ce8 pc=0x7ff0ae428fc5 _cgoexp_15c14303aa49_goFdwBeginForeignScan(0x7ffc3cce7ea0?) _cgo_gotypes.go:4055 +0x25 fp=0xc000715e30 sp=0xc000715e10 pc=0x7ff0ae431ea5 runtime.cgocallbackg1(0x7ff0ae431e80, 0xc000715fe0?, 0x0) /opt/hostedtoolcache/go/1.19.9/x64/src/runtime/cgocall.go:316 +0x2c2 fp=0xc000715f00 sp=0xc000715e30 pc=0x7ff0ad6ef702 runtime.cgocallbackg(0x0?, 0x0?, 0x0?) /opt/hostedtoolcache/go/1.19.9/x64/src/runtime/cgocall.go:235 +0x109 fp=0xc000715f90 sp=0xc000715f00 pc=0x7ff0ad6ef389 runtime.cgocallbackg(0x7ff0ae431e80, 0x7ffc3cce815c, 0x0)Describe the solution you'd like
Have steampipe work with Hasura "out of the box" would be pretty epic. Comboing these two up you'd have a minimal and near real-time cloud monitoring tool by using Graphql Subscriptions and/or Event triggers to push down notifications of changes in the cloudz and important security stuff. The world's your oyster.
Describe alternatives you've considered
I haven't tried this with Hasura alternatives like postgraphile but i suspect i'd run into the same issues.
This would probably (?) work with Postgrest but then i'd have to poll for changes client side instead of the "push" approach which is more desired here.
https://github.com/vbalasu/pg-notify-webhook https://github.com/diogob/postgres-websockets
might be worth exploring as well but Hasura provides a one stop shop for all of this and the UI makes it more convenient.
Additional context
N/A