STEAMPIPE_WORKSPACE_DATABASE is not honored

[electriquo]

Describe the bug
The documentation says that Steampipe can connect to an external database by using STEAMPIPE_WORKSPACE_DATABASE environment variable

STEAMPIPE_WORKSPACE_DATABASE=postgresql://myusername:[email protected]:9193/aaa000

But Steampipe seems not to honor the value since when the service is up, regardless of the value of STEAMPIPE_WORKSPACE_DATABASE , Steampipe still prints

Host(s):            localhost, 127.0.0.1, 172.20.0.2
Port:               9193
Database:           steampipe
User:               steampipe
Password:           ********* [use --show-password to reveal]
Connection string:  postgres://steampipe@localhost:9193/steampipe

While there is no options set in neither workspace configuration, nor database configuration

Steampipe version (steampipe -v)

$ steampipe --version
Steampipe v0.20.5

To reproduce
Set a connection string for STEAMPIPE_WORKSPACE_DATABASE environment variable and start Steampipe service

$ export STEAMPIPE_LOG_LEVEL=trace
$ export STEAMPIPE_WORKSPACE_DATABASE=postgres://steampipe:[email protected]:5432/steampipe
$ steampipe service start --dashboard --dashboard-listen=network
...
2023-06-08 09:56:45.764 UTC [TRACE] steampipe: StartDB invoker service
2023-06-08 09:56:45.765 UTC [TRACE] steampipe: Trying to create client with:  host=localhost port=9193 user=root dbname=postgres sslmode=disable
...

Steampipe service is running:

Database:

  Host(s):            localhost, 127.0.0.1, 172.20.0.2
  Port:               9193
  Database:           steampipe
  User:               steampipe
  Password:           ********* [use --show-password to reveal]
  Connection string:  postgres://steampipe@localhost:9193/steampipe

Then connect to the database and list the relations

$ psql postgres://steampipe:[email protected]:5432/steampipe -c '\d'
Did not find any relations.

And when looking at the processes, Steampipe did launch the local Postgres server that holds the tables and data

$ ps xo user:10,cmd | grep /bin/postgres
steampipe  /home/steampipe/.steampipe/db/14.2.0/postgres/bin/postgres -p 9193 -c listen_addresses="*" -c application_name=steampipe -c cluster_name=steampipe -c log_directory=/home/steampipe/.steampipe/logs -c ssl=on -c ssl_cert_file=/home/steampipe/.steampipe/db/14.2.0/data/server.crt -c ssl_key_file=/home/steampipe/.steampipe/db/14.2.0/data/server.key -D /home/steampipe/.steampipe/db/14.2.0/data

$ psql $(steampipe service status --show-password | grep -o 'postgres://.*') -c 'select count(*) from information_schema.tables'
 count
-------
   617
(1 row)

Expected behavior
Steampipe should honor the database connection string provided by STEAMPIPE_WORKSPACE_DATABASE environment variable.
Plus, it would be lovely if Steampipe would not start a local Postgres database if external one is provided.

Additional context
Note that the external database is not a part of Steampipe Cloud but the issue is the same when providing database connection string to Steampipe Cloud

[binaek]

@foolioo the service commands in steampipe are specifically meant to startup the underlying postgresql and dashboard servers.

STEAMPIPE_WORKSPACE_DATABASE is more of a client side thing - which steampipe uses to resolve and connect to a remote (or local) steampipe service.

More or less every steampipe command is designed to interact with a remote steampipe service. However, two command sets only operate as servers. These are:

• steampipe service *
• steampipe plugin *

The STEAMPIPE_WORKSPACE_DATABASE environment variable is ignored in this case.

If I am understanding your use case correctly, the following should get you unblocked:

$ export STEAMPIPE_WORKSPACE_DATABASE=postgres://steampipe:[email protected]:5432/steampipe
$ steampipe dashboard --dashboard-listen=network

It looks like it is not clear in our documentation on how the STEAMPIPE_WORKSPACE_DATABASE environment variable works.

We have opened an issue at #3542 to put it on the roadmap.

[electriquo]

@binaek Thank you for the clarification. When using an external database, the relations (tables) do not exist and it causes Steampipe to fail. When using an external database, what needs to be done for the relations to be created? Is there a way to tell Steampipe to prepare\initialize\migrate the database?

[electriquo]

@binaek Any idea how to solve #3537 (comment)?

[binaek]

Hi @foolioo

Could you expand a bit on what you mean by an external database?

Is it a remote steampipe service?

The steampipe tables cannot be initialised in a standard installation of postgresql. This is because the steampipe service has a special purpose FDW which is used to communicate with the API plugins.

If you could expand on what you are trying to achieve, maybe we can suggest alternative approaches?

[electriquo]

@binaek Basically, trying to decouple between Steampipe and Postgres, such that Steampipe runs on one node and Postgres run on other node, where Steampipe can access the Postgres.

Utilizing STEAMPIPE_WORKSPACE_DATABASE and following your guidance at #3537 (comment) did not connect to the the Postgres node pointed by the connection string in STEAMPIPE_WORKSPACE_DATABASE`.

All I want is to make Steampipe work while providing it with an external Postgres, but it fails since the tables do not exist.

[electriquo]

@binaek Any guidance?

[electriquo]

@binaek friendly ping

[binaek]

Hi @foolioo apologies for the tardiness.

Steampipe is not designed to work with an external Postgresql instance.

The core of steampipe is an FDW along with a plugin and connection manager daemon.

Having said that, you can start steampipe as a service.

When you start steampipe as a service, it behaves as a postgresql database that you can connect to using any postgresql client.

https://steampipe.io/docs/managing/service

[electriquo]

@binaek Any plans to support an external database?

[binaek]

Not that I am aware of.

Having said that, bear in mind that under the hood steampipe is just your neighbourly Postgresql instance.

It has it's own public schema which is made available for writes.

Unless you are trying to use a cloud specific database (Aurora and sorts), steampipe should fit most other use cases.

[binaek]

@binaek Basically, trying to decouple between Steampipe and Postgres, such that Steampipe runs on one node and Postgres run on other node, where Steampipe can access the Postgres.

Utilizing STEAMPIPE_WORKSPACE_DATABASE and following your guidance at #3537 (comment) did not connect to the the Postgres node pointed by the connection string in STEAMPIPE_WORKSPACE_DATABASE`.

All I want is to make Steampipe work while providing it with an external Postgres, but it fails since the tables do not exist.

Looking back at this, you can run Steampipe as a service (steampipe service start) in Node1 and then connect to it using steampipe query --workspace-database postgresql://steampipe@node1:9193/steampipe.

That should work for you

[electriquo]

Looking back at this, you can run Steampipe as a service (steampipe service start) in Node1 and then connect to it using steampipe query --workspace-database postgresql://steampipe@node1:9193/steampipe.

@binaek There is only a 1 node in your proposal (Node1), while I would like to have 2 different nodes

decouple between Steampipe and Postgres, such that Steampipe runs on one node and Postgres run on other node, where Steampipe can access the Postgres.

Please clarify

[electriquo]

@binaek friendly ping

[binaek]

@foolioo

You run steampipe service start in node1 and connect to it with steampipe query --workspace-database postgresql://steampipe@node1:9193/steampipe in node2

[github-actions]

This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.