Title
#general
infomaniac

infomaniac

12/16/2020, 12:56 PM
is there a way to retrieve all available tables via the Thrift API?
1:15 PM
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?
1:37 PM
turns out a regular old query works 🙂
select tbl_name FROM sqlite_temp_master WHERE type = "table";
1:40 PM
and
pragma table_info("<table name>")
returns all the columns
theopolis

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.
infomaniac

infomaniac

12/16/2020, 1:55 PM
hey @theopolis - interesting!
osquery_registry
returns a lot more tables though.
1:55 PM
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'
2:02 PM
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!
theopolis

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.
infomaniac

infomaniac

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

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.
2:40 PM
The important connection we should make for folks IMO is that this information is available via queries and not a specific API
infomaniac

infomaniac

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

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.
2:41 PM
getQueryColumns
2:43 PM
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.
infomaniac

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
theopolis

theopolis

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

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?
2:46 PM
(i wish threads had threads)
3:20 PM
scratch that, i see there are several available tables with the
osquery_
prefix
zwass

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.
infomaniac

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?
zwass

zwass

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

infomaniac

12/16/2020, 4:54 PM
i don't believe it does
4:54 PM
.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.
5:14 PM
Baking something like that into the thrift API would be interesting. I'd love to hear about the use case driving it
infomaniac

infomaniac

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

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.
infomaniac

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)
5:45 PM
For completion, I’ve seen implementations that use the spec files from source. But that won’t get you extensions
5:47 PM
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
infomaniac

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
5:48 PM
@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.
zwass

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.
infomaniac

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.
zwass

zwass

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

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.
5:59 PM
The shell builtins are probably just me being confused
infomaniac

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.
6:06 PM
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
infomaniac

infomaniac

12/16/2020, 6:12 PM