https://github.com/osquery/osquery logo
Join Slack
Powered by
Hi everyone. Trying to use ATC feature, and hittin...
# general
s
Hi everyone. Trying to use ATC feature, and hitting following error:
Copy code
I1207 13:47:10.155495 48644 virtual_sqlite_table.cpp:111] ATC table: Could not prepare database at path: "/etc/osquery/quarantine.db"
I1207 13:47:10.155580 48644 auto_constructed_tables.cpp:38] ATC Table: Unable to detect journal mode, applying default locking policy for path /etc/osquery/quarantine.db
I1207 13:47:10.155865 48644 virtual_sqlite_table.cpp:111] ATC table: Could not prepare database at path: "/etc/osquery/quarantine.db"
W1207 13:47:10.155910 48644 auto_constructed_tables.cpp:47] ATC Table: Error Code: 26 Could not generate data: Could not prepare database for path /etc/osquery/quarantine.db
I suspect that that's due to: https://github.com/osquery/osquery/issues/5225 since error message does mention 
journal mode
detection problem, but I'm not sure how to fix this from my side. Does anyone know how to fix\prevent such issues ?
f
@slevchenko in the past I have seen that error due to a malformed ATC config file, iirc it was a misnamed column the last time I saw it.
Actually, I take that back, it was a misnamed 
FROM
statement
Here's the last time someone raised this: https://osquery.slack.com/archives/C08V7KTJB/p1631826420144600
s
Thanks @fritz. I've resolved this issue. In my case following requirements had to be met: 1. db file had to be sqlite3 on some distros 3-rd version is not default 2. journal_mode had to be set 3. sqlite table had to be created with a single line statement, no linebreaks, tabs or any fancy formatting
in my case journal_mode = 'delete' works
f
ah yes, #3 is super important
s
That almost drove me nuts, since other tools, browsers and python libs worked fine even with that formatting. So it's super important to be mentioned somewhere in docs
s
Hrm. That feels like a bug.
Would you file it? I can't promise to fix it, but it should be fixable...
s
Will try to do it. I'll need some time to reproduce this behavior by removing changes I made to make it work
f
I doubt it is a bug, json does not like line-breaks generally speaking iirc? I thought you had to use 
\n
newlines
s
@fritz Thank you very much. As you said this feature is awesome and I'm already using it 🙂
❤️ 1
s
json? I thought we were talking about the underlying sql databases
s
@seph Yes, we are. In a blogpost above json syntax was used,my case it was python library which was relying on (pseudo code follows):
Copy code
CREATE table quarantine(
  ...
  ...
);
In my case statements were formatted by IDE, and worked in any python, sqlite client and IDE itself. That's why it was very hard to notice what was actually wrong.
So regardless of initial source, be it json or just text sql if statements end up containing newline chars, these statements are not recognized by osquery ATC.
s
Seems like either a bug, or some subtle error in what you're doing.
Testing this quickly, I cannot replicate you're issue:
Copy code
read -r -d '' SQL <<'EOF'
CREATE TABLE testatc (
  astring TEXT,
  anum INT
);

INSERT INTO testatc(astring, anum)
VALUES
('a', 1),
('b', 2);
EOF

rm -f newlines.db
printf "$SQL" | sqlite3 newlines.db

rm -f nolines.db
printf "$SQL" | tr -d "\n" | sqlite3 nolines.db
will make two sqlite DBs
You can see their schemas as:
Copy code
pemberton:osquery-atc-bug seph$ sqlite3 nolines.db .schema
CREATE TABLE testatc (  astring TEXT,  anum INT);
pemberton:osquery-atc-bug seph$ sqlite3 newlines.db .schema
CREATE TABLE testatc (
  astring TEXT,
  anum INT
);
Using:
Copy code
pemberton:osquery-atc-bug seph$ cat osq.conf 
{
  "auto_table_construction": {
    "newlines": {
      "query": "select astring, anum from testatc",
      "path": "newlines.db",
      "columns": [
        "astring",
        "anum"
      ]
    },
    "nolines": {
      "query": "select astring, anum from testatc",
      "path": "nolines.db",
      "columns": [
        "astring",
        "anum"
      ]
    }
  }
}
ATC tables work fine:
Copy code
osquery> select * from nolines;
+----------------------------------------+---------+------+
| path                                   | astring | anum |
+----------------------------------------+---------+------+
| /Users/seph/osquery-atc-bug/nolines.db | a       | 1    |
| /Users/seph/osquery-atc-bug/nolines.db | b       | 2    |
+----------------------------------------+---------+------+
osquery> select * from newlines;
+-----------------------------------------+---------+------+
| path                                    | astring | anum |
+-----------------------------------------+---------+------+
| /Users/seph/osquery-atc-bug/newlines.db | a       | 1    |
| /Users/seph/osquery-atc-bug/newlines.db | b       | 2    |
+-----------------------------------------+---------+------+
s
@seph I can't reproduce this either on version 5.0.1. Well I hope this bug is gone for good.
7 Views
Open in Slack
PreviousNext
Powered by