freol35241 / ltss

Long time state storage (LTSS) custom component for Home Assistant using Timescale DB
MIT License
80 stars 21 forks source link

Add init commands to execute as postgres user (admin) to docu #35

Open FireEmerald opened 2 years ago

FireEmerald commented 2 years ago

At the moment you write in the README:

(...) During the initial startup of the component, the extensions will be created on the specified database. This requires superuser priviligies on the PostgreSQL instance (...) Once the extensions are created, a user without superuser rights can be used!

Executing commands on startup as super db admin user postgres and then switch to user B may lead to permission problems and is not really a good idea. For example user A could create a table which user B can't access afterwards.

I think there are only two commands to execute manually before editing the HA configuration.yaml file:

CREATE EXTENSION IF NOT EXISTS postgis CASCADE;
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

I would include these statements in the README and maybe let the user by default execute them and only check for their existence inside your startup scripts.

freol35241 commented 2 years ago

@FireEmerald Thanks for opening this issue, you bring up an interesting point here and I think I agree with you.

Currently, there is some logic in the component setup that revolves around the availability of timescaledb and postgis extensions (i.e. NOT that they are actually installed, only that they are available for installation) to support LTSS on vanilla postgres and with timescaledb and/or postgis extensions available (see #29 for details).

Maybe this logic should be changed to check for installed extensions rather than available extensions such that the user runs none/any/all of the below during the initial setup and thus also configures LTSS implicitly.

CREATE EXTENSION IF NOT EXISTS postgis CASCADE;
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Do you have any further input/opinions?