search

fr-ser / grafana-sqlite-datasource

Grafana Plugin to enable SQLite as a Datasource
Apache License 2.0
124 stars 17 forks source link

error checking existence: stat {path to db}: permission denied - for database in /home directory #73

Closed jpgpi250 closed 2 years ago

jpgpi250 commented 2 years ago

Trying to create a datasource

save & test says: error checking existence: stat /home/pi/redirect/sqlite3/redirect.db: permission denied

What I tried (permissions):

none of the above change the result.

I have other sqlite3 resources, moved the db into the folder where the other db is located, reconfigured -> success, so the db is not damaged, the permissions warning appears to be accurate.

The application does not allow to relocate the db, it needs to be /home/pi/redirect/sqlite3/redirect.db

The error message says "stat", meaning (I think) you use stat to check the permissions.

Thanks for your time and effort.

fr-ser commented 2 years ago

Can you log in to the Raspberry as the Grafana user and try to use the stat command with the absolute path

su grafana
stat  /home/pi/redirect/sqlite3/redirect.db
sqlite3 /home/pi/redirect/sqlite3/redirect.db

Does it work from the terminal?

jpgpi250 commented 2 years ago

using the grafana package from https://dl.grafana.com/oss/release/grafana_8.2.2_armhf.deb. The user and group are automatically created by the package, the user grafana is configured "no login allowed".

according to this, running sudo -u grafana test -r /home/pi/redirect/sqlite3/redirect.db; echo "$?", which returns 0, indicates the user grafana has read permission -r (read) -> 0 (read OK) -w (write) ->1 (write NOK) -x (execute) -> (execute OK)

which matches 0755

jpgpi250 commented 2 years ago

tried to execute the requested command, using runuser

#!/bin/bash
whoami
stat  /home/pi/redirect/sqlite3/redirect.db`

sudo runuser -u grafana -- "./test-stat.sh"

grafana
    File: /home/pi/redirect/sqlite3/redirect.db
    Size: 151552          Blocks: 304        IO Block: 4096   regular file
    Device: b302h/45826d    Inode: 428965      Links: 1
    Access: (0755/-rwxr-xr-x)  Uid: ( 1000/      pi)   Gid: ( 1000/      pi)
    Access: 2021-10-29 23:32:59.855884250 +0200
    Modify: 2021-11-02 07:13:57.333827635 +0100
    Change: 2021-11-02 07:13:57.333827635 +0100
    Birth: -
#!/bin/bash
whoami
sqlite3 "/home/pi/redirect/sqlite3/redirect.db" ".tables"

sudo runuser -u grafana -- "./test-sqlite3.sh"

grafana
    info     queries

edit
added whoami 
/edit
jordipalet commented 2 years ago

I just got exactly the same problem. I'm trying to use grafana to access /home/pi/domoticz/domoticz.db. I tried everything (adding to the same group, root, changed file/dir permissions, etc.) nothing worked.

sudo -u grafana test -r /home/pi/domoticz/domoticz.db; echo "$?" 0

cat test-stat.sh

!/bin/bash

whoami stat /home/pi/domoticz/domoticz.db sqlite3 "/home/pi/domoticz/domoticz.db" ".tables"

sudo runuser -u grafana -- "./test-stat.sh" grafana File: /home/pi/domoticz/domoticz.db Size: 40341504 Blocks: 78800 IO Block: 4096 regular file Device: 805h/2053d Inode: 4720390 Links: 1 Access: (0644/-rw-r--r--) Uid: ( 1000/ pi) Gid: ( 1000/ pi) Access: 2021-03-06 14:49:42.728259507 +0100 Modify: 2021-11-03 11:00:00.357798231 +0100 Change: 2021-11-03 11:00:00.357798231 +0100 Birth: - BackupLog MobileDevices Scenes
Cameras MultiMeter SetpointTimers
CamerasActiveDevices MultiMeter_Calendar SharedDevices
CustomImages MySensors Temperature
DeviceStatus MySensorsChilds Temperature_Calendar DeviceToPlansMap MySensorsVars TimerPlans
EnoceanSensors Notifications Timers
EventMaster Percentage ToonDevices
EventRules Percentage_Calendar UV
Fan Plans UV_Calendar
Fan_Calendar Preferences UserSessions
Floorplans PushLink UserVariables
Hardware Rain Users
LightSubDevices Rain_Calendar WOLNodes
LightingLog SceneDevices Wind
Meter SceneLog Wind_Calendar
Meter_Calendar SceneTimers ZWaveNodes

jpgpi250 commented 2 years ago

did you try, like I did, just to test, to move the database into another location? I copied the database to a subfolder of /etc, and was able to create / access the datasource succesfully. Just as a test (database integrity - required permissions, ...)

jordipalet commented 2 years ago

just tried (did a copy of the database to /etc), it works (copied as sudo, so it is root owned)!

I also tested into /etc/testing/domoticz/domoticz.db and also works, so is not a path length problem?

jordipalet commented 2 years ago

Trying a query with the copy of the database, grafana complains about lack of write permission, so granting w permission to all in the dir, resolved the problem, but I'm not convinced this is the best way and if this is related to the problem with my original path.

jpgpi250 commented 2 years ago

Why would grafana need write permissions on a database to execute queries? I have other sqlite3 datasources (different databases), running (as before):

/etc/pihole/pihole-FTL.db (permissions 0664), this datasource works as expected.

sudo -u grafana test -w /etc/pihole/pihole-FTL.db; echo "$?"

result (see here): -w (write) ->1 (write NOK)

fr-ser commented 2 years ago

Why would grafana need write permissions on a database to execute queries?

Some queries change the state of the database and for those you need write permissions. In the plugin we do not differentiate between pure "reading" and "editing" queries so maybe this is part of the SQLite library that write permissions are required 🤔

But at least adding the database should be possible even without write permissions ... I'll have to investigate this a bit further..

But are you saying that providing write permissions solved the issue?

jpgpi250 commented 2 years ago

For me, in the original location, it didn't solve the problem. As indicated earlier, I tried:

no result, same error.

jordipalet commented 2 years ago

No, in the original location of the database doesn't work. I copied the database (just for testing) into /etc/testing/domoticz/domoticz.db, then it works (with write permission only), but I fail to understand why it doesn't work in the original location.

jordipalet commented 2 years ago

I keep trying many options, and I believe the problem is that the plugin or grafana is somehow requiring the rw permissions in the full path

fr-ser commented 2 years ago

I don't think the plugin needs to rw permission but it needs the folder access permission in the full path. Important: You need the "folder execute" (execute is "access" for folders) permission for every folder in the path to use items inside this folder.

For an explanation about the "execute" permission on folders see https://unix.stackexchange.com/questions/21251/execute-vs-read-bit-how-do-directory-permissions-in-linux-work

To illustrate:

This works (only read permission on the file and execute permission on all parents)

root@546a3b965ba4:/woot/hi# ls -lah
total 80K
drwx--x--x 2 root root 4.0K Nov  3 17:54 .
drwx--x--x 3 root root 4.0K Nov  3 17:54 ..
-r--r--r-- 1 root root  72K Nov  3 17:54 sth.db

This does not work. And the only difference is the execute permission

root@546a3b965ba4:/woot/hi# ls -lah
total 80K
drwx--x--x 2 root root 4.0K Nov  3 17:54 .
drwx------ 3 root root 4.0K Nov  3 17:54 ..
-r--r--r-- 1 root root  72K Nov  3 17:54 sth.db

So can you please attach the permission of every part of the path in your directory to check the permissions? For example via ls -lah

jpgpi250 commented 2 years ago

all directories, and the database have permissions 755, see below, listed with stat

pi@raspberrypi:~ $ stat /home File: /home Size: 4096 Blocks: 8 IO Block: 4096 directory Device: b302h/45826d Inode: 16321 Links: 3 Access: (0755/drwxr-xr-x) Uid: ( 0/ root) Gid: ( 0/ root) Access: 2021-05-07 16:59:42.794110194 +0200 Modify: 2021-11-03 13:50:57.347665188 +0100 Change: 2021-11-03 13:50:57.347665188 +0100 Birth: - pi@raspberrypi:~ $ stat /home/pi File: /home/pi Size: 4096 Blocks: 8 IO Block: 4096 directory Device: b302h/45826d Inode: 825 Links: 24 Access: (0755/drwxr-xr-x) Uid: ( 1000/ pi) Gid: ( 1000/ pi) Access: 2021-05-07 16:59:42.794110194 +0200 Modify: 2021-10-31 15:18:45.211034646 +0100 Change: 2021-10-31 15:18:45.211034646 +0100 Birth: - pi@raspberrypi:~ $ stat /home/pi/redirect File: /home/pi/redirect Size: 4096 Blocks: 8 IO Block: 4096 directory Device: b302h/45826d Inode: 275760 Links: 3 Access: (0755/drwxr-xr-x) Uid: ( 1000/ pi) Gid: ( 1000/ pi) Access: 2021-10-29 22:03:19.055803857 +0200 Modify: 2021-10-29 22:05:25.295338007 +0200 Change: 2021-10-30 13:35:22.663362529 +0200 Birth: - pi@raspberrypi:~ $ stat /home/pi/redirect/sqlite3 File: /home/pi/redirect/sqlite3 Size: 4096 Blocks: 8 IO Block: 4096 directory Device: b302h/45826d Inode: 428963 Links: 2 Access: (0755/drwxr-xr-x) Uid: ( 1000/ pi) Gid: ( 1000/ pi) Access: 2021-10-29 22:03:47.095700372 +0200 Modify: 2021-11-03 19:08:54.554381452 +0100 Change: 2021-11-03 19:08:54.554381452 +0100 Birth: - pi@raspberrypi:~ $ stat /home/pi/redirect/sqlite3/redirect.db File: /home/pi/redirect/sqlite3/redirect.db Size: 200704 Blocks: 400 IO Block: 4096 regular file Device: b302h/45826d Inode: 428965 Links: 1 Access: (0755/-rwxr-xr-x) Uid: ( 1000/ pi) Gid: ( 1000/ pi) Access: 2021-10-29 23:32:59.855884250 +0200 Modify: 2021-11-03 07:15:40.628857302 +0100 Change: 2021-11-03 07:15:40.628857302 +0100 Birth: -

fr-ser commented 2 years ago

Hmm. Then I have no clue...

Do you get a similar permission error, when you run queries? (after saving the path with permission errors)

I somehow need to reproduce the error to investigate further... Can you provide me a docker image with such an issue for example?

jpgpi250 commented 2 years ago

as soon as you create a new panel, select the datasource, a triangle shows up in the upper left corner, message: unable to open database file: permission denied

sorry don't do docker, don't know how, never use it...

replace user with the actual user name

content of redirect.sql:

PRAGMA foreign_keys=OFF; BEGIN TRANSACTION;

CREATE TABLE info ( property TEXT PRIMARY KEY, value TEXT NOT NULL );

INSERT INTO "info" VALUES('version','1'); INSERT INTO 'info' VALUES('latest_timestamp','0');

CREATE TABLE queries ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp INTEGER NOT NULL, serial INTEGER NOT NULL, type INTEGER NOT NULL, domain TEXT NOT NULL, client TEXT NOT NULL, UNIQUE (timestamp, serial) ); COMMIT;

to retrieve info from the database: sqlite3 "/home/user/redirect/sqlite3/redirect.db" "SELECT * FROM "info" WHERE property = 'version';"

jpgpi250 commented 2 years ago

optionally, if you don't find the cause, add a flag (selection) to the datastore configuration: ignore file permissions

fr-ser commented 2 years ago

as soon as you create a new panel, select the datasource, a triangle shows up in the upper left corner, message: unable to open database file: permission denied

sorry don't do docker, don't know how, never use it...

I followed your steps and it works on my machine 🤷🏻

optionally, if you don't find the cause, add a flag (selection) to the datastore configuration: ignore file permissions

The plugin does not enforce file permissions. What you can check, however, (as I recommended above) is trying to run a query with this database, even after the health check for the data source failed.

Because when you run the query, we simply run the query against the database. Nothing else. If you still get a permission error for running the query, then there is a permission issue.

jpgpi250 commented 2 years ago

you've seen all the relevant permissions listed above, clearly there is no permissions problem to solve. All tests have clearly indicated the permissions on directories and the database are correct. Why not simply remove the permissions test, other linux components do not check this, it is up to the administrator to ensure the permissions are set correctly, in order for things to work.

jordipalet commented 2 years ago

What you can check, however, (as I recommended above) is trying to run a query with this database, even after the health check for the data source failed.

Tried that, doesn't work.

fr-ser commented 2 years ago

you've seen all the relevant permissions listed above, clearly there is no permissions problem to solve. All tests have clearly indicated the permissions on directories and the database are correct. Why not simply remove the permissions test, other linux components do not check this, it is up to the administrator to ensure the permissions are set correctly, in order for things to work.

There is no permission test. Not for the database settings nor for the queries.

The database settings only try to check that the file exists (which fails due to permission issues). The queries themselves, don't even do that, they directly run the query (which also fails due to permission issues).

Without a reproducible example, I am sorry to say that I cannot locate and therefore investigate the bug further

fr-ser commented 2 years ago

The only other thing which comes to mind is to test against different versions of the plugin or Grafana. Maybe an update in one of those caused some issues. It is far fetched but without having a reproducible example I can't think of anything else 🤷🏻

jpgpi250 commented 2 years ago

tried older version 1.2.1 -> no joy

seriouslly dirty workaround (probably not appriciated by the linux admin):

mkdir -p /grafana mkdir -p /grafana/redirect sudo mount -r --bind /home/pi/redirect/sqlite3 /grafana/redirect

define data source as /grafana/redirect/redirect.db -> works

My thoughts: the plugin cannot handle the long path, shortening the path, using the mount, solves the problem (quick and dirty). Both /grafana and /grafana/redirect are owned by root, permissions 0755, no change required to make things work.

fstab entry: /home/pi/redirect/sqlite3 /grafana/redirect none bind 0 0

tried this on another sqlite database, manifesting the same problem, works (another mount point - fstab entry is required).

fr-ser commented 2 years ago

My thoughts: the plugin cannot handle the long path

I haven't seen such a constraint before but also have not actively checked it. I'll try some longer path on my end and see if this is the issue 👌🏻

jordipalet commented 2 years ago

I'm not really sure it is the length of the path the cause of the problem.

My original path to the db is:

/home/pi/domoticz/domoticz.db

Yesterday I tried a copy of the db in a longer path:

/test-db-path/another-level/domoticz/domoticz.db

And it worked. I used sudo to create the dir and cp the db, so all by default

also, right now I tried moving this path to /home, so it becomes:

/home/test-db-path/another-level/domoticz/domoticz.db

even shortening: /home/t/domoticz.db

And it doesn't work! Same error: error checking existence: stat /home/t/domoticz.db: permission denied

and the queries report: unable to open database file: permission denied

I tried to make sudo chmod -R 777 t

so t and below have all the possible rights ...

and same problem

so it looks like something at /home permissions is the problem ...

The strange thing is that if I do

!/bin/bash

whoami test -r /home/t/domoticz.db; echo "$?" test -w /home/t/domoticz.db; echo "$?" test -x /home/t/domoticz.db; echo "$?" stat /home/t/domoticz.db sqlite3 "/home/t/domoticz.db" ".tables"

running it as sudo runuser -u grafana -- "/home/pi/test-stat.sh"

it seems to confirm that grafana can rwx to it:

grafana 0 0 0 File: /home/t/domoticz.db Size: 40341504 Blocks: 78792 IO Block: 4096 regular file Device: 805h/2053d Inode: 1572868 Links: 1 Access: (0777/-rwxrwxrwx) Uid: ( 0/ root) Gid: ( 0/ root) Access: 2021-11-04 09:44:37.982919283 +0100 Modify: 2021-11-04 10:04:42.091267517 +0100 Change: 2021-11-04 10:04:42.091267517 +0100 Birth: - BackupLog MobileDevices Scenes
Cameras MultiMeter SetpointTimers
CamerasActiveDevices MultiMeter_Calendar SharedDevices
CustomImages MySensors Temperature
DeviceStatus MySensorsChilds Temperature_Calendar DeviceToPlansMap MySensorsVars TimerPlans
EnoceanSensors Notifications Timers
EventMaster Percentage ToonDevices
EventRules Percentage_Calendar UV
Fan Plans UV_Calendar
Fan_Calendar Preferences UserSessions
Floorplans PushLink UserVariables
Hardware Rain Users
LightSubDevices Rain_Calendar WOLNodes
LightingLog SceneDevices Wind
Meter SceneLog Wind_Calendar
Meter_Calendar SceneTimers ZWaveNodes

jpgpi250 commented 2 years ago

my dirty workaround does move things away from /home so your idea may be correct (so it looks like something at /home permissions is the problem ...)

fr-ser commented 2 years ago

I installed Grafana on a raspberry and I have no problems. I checked the folder permissions and even with read only access everything works...

pi@raspberrypi:~/very/long/folder/name/but/it/still/works $ pwd
/home/pi/very/long/folder/name/but/it/still/works

pi@raspberrypi:~/very/long/folder/name/but/it/still/works $ ls -lah
total 80K
drwxr-xr-x 2 pi pi 4.0K Nov  8 16:04 .
drwxr-xr-x 3 pi pi 4.0K Nov  8 16:04 ..
-r--r--r-- 1 pi pi  72K Nov  8 16:04 data.db

pi@raspberrypi:~/very/long/folder/name/but/it/still/works $ ls -lah /home/
total 12K
drwxr-xr-x  3 root root 4.0K Mar  4  2021 .
drwxr-xr-x 18 root root 4.0K May  7  2021 ..
drwxr-xr-x  6 pi   pi   4.0K Nov  8 16:04 pi

I checked the user groups of Grafana and the pi user. The only interesting thing here is that the pi user belongs to the Grafana group. I don't know if that makes a difference

pi@raspberrypi:~/very/long/folder/name/but/it/still/works $ groups grafana
grafana : grafana

pi@raspberrypi:~/very/long/folder/name/but/it/still/works $ groups pi
pi : pi adm dialout cdrom sudo audio video plugdev games users input netdev spi i2c gpio docker grafana

Currently, I see nothing more that I can try.

jpgpi250 commented 2 years ago

Thanks for (not yet) letting this go

stale[bot] commented 2 years ago

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

mmaga commented 2 years ago

I installed Grafana on a raspberry and I have no problems. I checked the folder permissions and even with read only access everything works...

pi@raspberrypi:~/very/long/folder/name/but/it/still/works $ pwd
/home/pi/very/long/folder/name/but/it/still/works

pi@raspberrypi:~/very/long/folder/name/but/it/still/works $ ls -lah
total 80K
drwxr-xr-x 2 pi pi 4.0K Nov  8 16:04 .
drwxr-xr-x 3 pi pi 4.0K Nov  8 16:04 ..
-r--r--r-- 1 pi pi  72K Nov  8 16:04 data.db

pi@raspberrypi:~/very/long/folder/name/but/it/still/works $ ls -lah /home/
total 12K
drwxr-xr-x  3 root root 4.0K Mar  4  2021 .
drwxr-xr-x 18 root root 4.0K May  7  2021 ..
drwxr-xr-x  6 pi   pi   4.0K Nov  8 16:04 pi

I checked the user groups of Grafana and the pi user. The only interesting thing here is that the pi user belongs to the Grafana group. I don't know if that makes a difference

pi@raspberrypi:~/very/long/folder/name/but/it/still/works $ groups grafana
grafana : grafana

pi@raspberrypi:~/very/long/folder/name/but/it/still/works $ groups pi
pi : pi adm dialout cdrom sudo audio video plugdev games users input netdev spi i2c gpio docker grafana

Currently, I see nothing more that I can try.

Dear fr-ser,

Congrats on the sqlite3 plugin and all the efforts on helping us :-)

I'm totally newbie on grafana.

I'm using a local sqlite3 database with local machine stats in /tmp folder (raspbian 5.10.63-v7l+). The sqlite3 plugin raises no error on accessing the file, but returns "no such table" error when querying select statement from the tables.

I moved the database file to /mnt, no changes on its permissions (644), neither /mnt (755), and the select query was successful for all tables, no stats or table access errors.

I do confirm it doesn't get access from /home, nor from /home/pi no matter the rwx permissions all along the path. It doesn't work also from folders like /var/tmp, /var/shm (ramdisk).

I'm available to contribute on testing and giving feedback to improve your valuable work :-)

fr-ser commented 2 years ago

I'm using a local sqlite3 database with local machine stats in /tmp folder (raspbian 5.10.63-v7l+). The sqlite3 plugin raises no error on accessing the file, but returns "no such table" error when querying select statement from the tables.

I moved the database file to /mnt, no changes on its permissions (644), neither /mnt (755), and the select query was successful for all tables, no stats or table access errors.

That sounds like in the first case a wrong path was provided and an empty sqlite file was created

fr-ser commented 2 years ago

I do confirm it doesn't get access from /home, nor from /home/pi no matter the rwx permissions all along the path. It doesn't work also from folders like /var/tmp, /var/shm (ramdisk).

Then it seems to be related to some permissions of the file path 🤷🏻 But I see no further avenues of debugging on my end. The only last option I see would maybe be a remote call or something to take a look at the specific raspberrypi instance where it does not work...

jordipalet commented 2 years ago

Thanks for (not yet) letting this go

  • added pi to the grafana group
  • stoppped / started grafana-server service
  • test (new sqlite data source)-> same problem (permissions)

I did the same test again, adding user pi to the grafana group, no luck.

mmaga commented 2 years ago

I'm using a local sqlite3 database with local machine stats in /tmp folder (raspbian 5.10.63-v7l+). The sqlite3 plugin raises no error on accessing the file, but returns "no such table" error when querying select statement from the tables.

I moved the database file to /mnt, no changes on its permissions (644), neither /mnt (755), and the select query was successful for all tables, no stats or table access errors.

That sounds like in the first case a wrong path was provided and an empty sqlite file was created

The hypothesis about creating an empty file would be a good one if the file wasn't there before. I do confirm that there was no problems with the path because the plugin found the file at all the places:

/tmp/stats.db: the file was found, no error accessing, but it raises error 'no such table' /tmp is a Ramdisk mount, permissions for tmp are 755, permissions for stats.db are 644

/mnt/stats.db: the file was found, no error accessing, and select statement returned all the records :-) /mnt is a regular directory, permissions for mnt are 755, permissions for stats.db are 644

It looks like it doesn't follow symlinks also.

One hypothesis to be added is that folders like /home and /tmp can have special ACLs and maybe grafana user can't go through. I didn't check.

Yes, I can make myself available for a call after December 17th. It would be nice to work on understanding better Grafana's platform and its plugins.

fr-ser commented 2 years ago

Just give me some way to contact you and we can try to set something up. Other than that I see nothing I can do right now

mmaga commented 2 years ago

We can book a google meeting (teams or zoom) using my email: [removed for data privacy and spam protection]

ssnkhan commented 2 years ago

Just wanted to chime in with a +1 -- got the error after upgrading Grafana. Saw this post here: https://community.grafana.com/t/sqlite-datasource-and-grafana-8-2-1/54694/2, suggesting that the DB cannot reside within the home folder, and why @jpgpi250's solution works.

fr-ser commented 2 years ago

Thanks a lot @ssnkhan for the link!

The issue seems to be indeed a new feature of Grafana v8.2.0. It is a systemd setting ProtectHome. Some more information can be found here: https://github.com/grafana/grafana/issues/41299

Since this is a Grafana setting it can be overridden but the plugin itself cannot change the behavior. I will close this issue for now. if someone has problems outside of the home directory or despite the Grafana settings let's open a new issue

fr-ser commented 2 years ago

A similar issue applies to the var directory. I added a section in the readme to make this clearer for new users https://github.com/fr-ser/grafana-sqlite-datasource/commit/676385e239d7469c0c8e79da31a5b1423a571749

Thanks @mmaga for the help in identifying the /var issue 👌🏻