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:
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!
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.
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