https://github.com/osquery/osquery logo
Title
i

infomaniac

12/16/2020, 12:56 PM
is there a way to retrieve all available tables via the Thrift API?
to clarify, i see there is no such API available - is there some other way with IPC to retrieve this list, or plans to include this in the Thrift API?
turns out a regular old query works 🙂
select tbl_name FROM sqlite_temp_master WHERE type = "table";
and
pragma table_info("<table name>")
returns all the columns
t

theopolis

12/16/2020, 1:47 PM
Nice, you can also see all osquery components using the osquery_registry table. That won’t give you the schemas though.
👀 1
i

infomaniac

12/16/2020, 1:55 PM
hey @theopolis - interesting!
osquery_registry
returns a lot more tables though.
quick 'n dirty:
select sql.name AS 'from sqlite', reg.name as 'from osquery registry' FROM osquery_registry reg 
  LEFT JOIN sqlite_temp_master sql ON reg.name = sql.name 
  WHERE reg.active = 1 
  AND internal = 0
  AND registry = 'table'
good news is that
pragma table_info("<table>")
works for tables not included in
sqlite_temp_master
- so I'll just use
osquery_registry
then. thanks a lot!
t

theopolis

12/16/2020, 2:25 PM
Very cool, sorry you had to hack through that. We should invent a
osquery_tables
that structures the schema info.
👍 1
i

infomaniac

12/16/2020, 2:37 PM
in the interim, would you like me to contribute some documentation around this?
or open an issue around your suggestion
t

theopolis

12/16/2020, 2:39 PM
Maybe some documentation on the Extensions page of the readthedocs, mentioning that metadata can be accessed via the osquery_* tables and SQLite internals. Then show your examples.
The important connection we should make for folks IMO is that this information is available via queries and not a specific API
i

infomaniac

12/16/2020, 2:40 PM
agreed. that's what was missing for me in my use-case
t

theopolis

12/16/2020, 2:41 PM
Ah I forgot, there is a more rich API for explaining a query too. That allows you to inspect type information a query.
getQueryColumns
If you had a complex query, you can use that to see what columns would be returned, how they would be named, and their types as understood by SQLite.
i

infomaniac

12/16/2020, 2:43 PM
yes! i just discovered that today. i'm using Go which has unordered maps, so the columns were not being returned in a consistent order, so i'm using that
getQueryColumns
to solve that
t

theopolis

12/16/2020, 2:43 PM
Very cool
i

infomaniac

12/16/2020, 2:43 PM
just to be clear, your mention of `osquery_*`tables above just covers
osquery_registry
for now, but will cover
osquery_tables
(or whatever) in the future?
(i wish threads had threads)
scratch that, i see there are several available tables with the
osquery_
prefix
z

zwass

12/16/2020, 4:38 PM
Heads up https://github.com/osquery/osquery/commit/c3f9a3dae22d43ed3b4f6a403cbf89da4cba7c3c disables pragmas. We could re-enable them or just that one.
i

infomaniac

12/16/2020, 4:40 PM
@zwass i'd prefer to not use it, frankly - is there perhaps another way to describe a table in SQL?
z

zwass

12/16/2020, 4:53 PM
In the sqlite shell there's
.schema table
. I wonder if that works via the API?
i

infomaniac

12/16/2020, 4:54 PM
i don't believe it does
.schema
and
.tables
are exactly what i need, but those do not work via the Thrift API, so I have to revert to plain ol' SQL
s

seph

12/16/2020, 5:13 PM
.table
and
.schema
can be queried.
Baking something like that into the thrift API would be interesting. I'd love to hear about the use case driving it
i

infomaniac

12/16/2020, 5:31 PM
@seph when you say they can be queried, what do you mean? outside of the CLI?
in my use-case, i need to list all the available tables and constituent columns to provide autocompletion
z

zwass

12/16/2020, 5:40 PM
I think they are saying that particular "query" doesn't work. I think it might be a sqlite shell builtin.
👍 1
i

infomaniac

12/16/2020, 5:40 PM
we're missing each other. i'm issuing SQL queries over the API - but I can't use
.tables
or
.schema
over that API unless i'm mistaken
s

seph

12/16/2020, 5:42 PM
Huh. I thought it worked, but it’s been awhile since I know I’ve tried. (And my usual fleet manager now parses and doesn’t allow things that don’t look like sql)
For completion, I’ve seen implementations that use the spec files from source. But that won’t get you extensions
goquery uses
select name from osquery_registry where registry = 'table' and active = 1
to fetch the active tables for tab completion. Not sure it uses colunmns
i

infomaniac

12/16/2020, 5:48 PM
@seph indeed; the tables aspect is solved - it's just the columns i'm trying to figure out now
@zwass from my readings,
PRAGMA
seems to be the only way to describe tables without using the shell builtins. how feasible is it to whitelist the
table_info
pragma in light of that PR you linked earlier? if it weakens the security stance then i can live without it
s

seph

12/16/2020, 5:49 PM
I think it’s reasonable to allow some pragmas.
z

zwass

12/16/2020, 5:49 PM
Agreed, and it wouldn't be difficult. It just won't make the 4.6.0 release which I believe was already cut.
i

infomaniac

12/16/2020, 5:51 PM
fine by me - i'll add some error handling to account for the absense of
pragma
👍 thanks a lot!
s

seph

12/16/2020, 5:58 PM
Okay, y’all are correct.
.schema
does not work on the thrift socket.
z

zwass

12/16/2020, 5:58 PM
Can you please file this as an issue and link me? I can handle it.
i

infomaniac

12/16/2020, 5:59 PM
to be clear: the
pragma
whitelistings - or the fact that the shell builtins don't work via Thrift?
s

seph

12/16/2020, 5:59 PM
The pragma whitelistings should be an issue.
The shell builtins are probably just me being confused
i

infomaniac

12/16/2020, 6:00 PM
thought so, will do 👍
s

seph

12/16/2020, 6:01 PM
Poking around… It looks like
sqlite_schema
should contain this. But does not. OTOH, I’m not really a sqlite expert.
ah right. it’s been awhile. The osquery tables are all “eponymous virtual tables” which means they don’t show up in the
sqlite_schema
table
👍 1
i

infomaniac

12/16/2020, 6:12 PM