Calculate home-assistant_V2.db size without whitelisting entire config dir

Share your Projects!
1

A lot of users are searching how to calculate te filesize of the home-assistant_V2.db without using the filesize attribute in the configuration and whitelisting their entire /config folder as a security issue

so here it is:

sensor:
  - platform: sql
    db_url: sqlite:////config/home-assistant_v2.db
    queries:
      - name: db size
        query: 'SELECT SUM("pgsize") as "size"  FROM "dbstat";'
        column: 'size'

It wil result a byte specified object in your entities
you can make this in megabyte by changing the querry to:

 'SELECT SUM("pgsize")*0.0000009999999999999999 as "size"  FROM "dbstat";'

i checked this with a filesize attribute and the corresponding Mb’s are identical

For those who want to whitelist their config and use the filesize attrib:

homeassistant:
  whitelist_external_dirs:
    - /config
sensor:
  - platform: filesize
    file_paths:
      - /config/home-assistant_v2.db
3 Likes
2

Please DON’T USE constructs like these—they might return incorrect results (you’d need the page_count multiplied by page_size) and are horribly slow. Your version runs several minutes on my system (2.3GB DB) and returns ~648MB instead of 2.3GB.

Since sqlite v3.16, we have pragmas that return results and don’t have side effects. So here is my suggestion for improvement:

sensor:
  # HA database size
  - platform: sql
    # Not needed when using recorder, because we already have a connection.
    # Also, socket connections on Linux systems are MUCH faster.
    # Absolute path begins after the 3rd "/" (location for Hassbian).
    #db_url: sqlite:////home/homeassistant/.homeassistant/home-assistant_v2.db
    scan_interval: 300
    queries:
      - name: DB Size
        # This is the fastest solution, available since sqlite 3.16.
        # It’s the same info as returned by ".dbinfo" in the sqlite3 cmdline client.
        query: 'SELECT ROUND(page_count * page_size / 1024 / 1024, 1) as size FROM pragma_page_count(), pragma_page_size();'
        column: 'size'
        unit_of_measurement: 'MiB'

Hey, and thanks for the idea for another nice sensor! :slight_smile:

3 Likes
3

Dont have your problem on multiple hassio…

4

Pure chance … You might want to check your page size, my system uses a page size of 4096.

5

Can someone clarify for me why whitelisting /config is a security issue? Is everything exposed somehow publicly if you do that? I can’t figure out anyway to get to files outside of the www folder whitelisted or not and I don’t see anything in the documentation that says whitelisted dirs are exposed. If this is a security risk I’d like to update the doc to specify that and explain why.

6

Exposing your entire config dir is for example Exposing your config with password in :wink: and thats a security risk in My opinion

7

Sure that would be bad but how is it exposed? Let’s say for example /config was whitelisted on a given HA instance. What URL would you be able to go to then to see the secrets.yaml file? {HA base URL}/{what goes here?}/secrets.yaml

8

I pm Ed you about this :wink:

1 Like