This is a very slick little utility - I have a similar need ... accessing osQuery endpoints. osQuery has its own, special, access points but it would much nicer to have a PostgreSQL wire compatible interface to osQuery endpoints.

Since osQuery is built on top of SQLite, does anyone know if a PostgreSQL wire compatible interface to osQuery endpoints is possible with postlite?

This project looks very interesting and promising! Thanks for bringing this idea out.

I have a couple of questions after going through the README.

• Is this only targeted at making remote management easier, or can it be used as a drop in replacement for Postgres?
• How would the performance be like?
• Can multiple clients connect and issue transactions?
• How is user authorization taken care of?

Sqlite does not perform well over NFS due to file locking issues that is present with NFS. Using postlite on the NFS server to mitigate that problem would be a humongous value addition from this project. Edit: I see that you also have worked on Litestream! You don't need me to tell you this :)

If it can perform reasonably well (not expecting production grade due to the backing store being sqlite), I can see this being used as a central database server (or even a DBaaS) for many homelabs too :)

Hi and thanks for a great project. I tried using postlite/sqlite when running some integration tests locally. Sadly they fail because postlite does not support the pg_range catalog table. Would it be possible to add this feature?

received ssl request message: &pgproto3.SSLRequest{} received startup message: &pgproto3.StartupMessage{ProtocolVersion:0x30000, Parameters:map[string]string{"database":"data.db3", "user":"postgres"}} [recv] &pgproto3.Query{String:"SET DateStyle TO 'ISO, MDY'"} received query: "SET DateStyle TO 'ISO, MDY'" [recv] &pgproto3.Terminate{} connection closed: xxxx

Postico 2 (2.0beta (9418)) does not work with postlite. The error is: no such function: current_schemas

The failing query:

SELECT
	oid
	,nspname
	,nspname = ANY (current_schemas(true)) AS is_on_search_path

	 ,obj_description(oid, 'pg_namespace') AS comment

FROM pg_namespace;

Connecting with psql works fine.

It looks like a remote denial-of-service (via stack overflow) on the server can be triggered by continuously sending SSLRequest, since handleSSLRequestMessage recursively calls serveConnStartup and the call from serveConnStartup to handleSSLRequestMessage isn't a tail call.

This PR introduces a startupState type for managing the lifecycle of the startup phase.

Using TablePlus client connecting does not show the databases or if directly input the database name it doesn't show any tables. PSQL command line does work fine.

Latest version of tableplus

[recv] &pgproto3.Query{String:"SELECT nspname FROM pg_catalog.pg_namespace;"} received query: "SELECT nspname FROM pg_catalog.pg_namespace;" [recv] &pgproto3.Query{String:"SELECT pg_catalog.pg_get_userbyid(p.proowner) as owner,p.oid AS oid,pg_get_function_identity_arguments(p.oid)AS args,n.nspname AS function_schema,p.proname AS function_name,CASE WHEN p.proisagg THEN'aggregate'WHEN p.proiswindow THEN'window'WHEN p.prorettype='pg_catalog.trigger'::pg_catalog.regtype THEN'trigger'ELSE'function'END AS function_type FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid=p.pronamespace WHERE n.nspname<>'pg_catalog'AND n.nspname<>'information_schema';"} received query: "SELECT pg_catalog.pg_get_userbyid(p.proowner) as owner,p.oid AS oid,pg_get_function_identity_arguments(p.oid)AS args,n.nspname AS function_schema,p.proname AS function_name,CASE WHEN p.proisagg THEN'aggregate'WHEN p.proiswindow THEN'window'WHEN p.prorettype='pg_catalog.trigger'::pg_catalog.regtype THEN'trigger'ELSE'function'END AS function_type FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid=p.pronamespace WHERE n.nspname<>'pg_catalog'AND n.nspname<>'information_schema';" [recv] &pgproto3.Query{String:"(SELECT table_name, table_schema, table_type FROM information_schema.tables) UNION (SELECT c.relname AS table_name, n.nspname AS table_schema, 'MATERIALIZED VIEW' FROM pg_catalog.pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'm');"} received query: "(SELECT table_name, table_schema, table_type FROM information_schema.tables) UNION (SELECT c.relname AS table_name, n.nspname AS table_schema, 'MATERIALIZED VIEW' FROM pg_catalog.pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'm');" [recv] &pgproto3.Query{String:"SELECT p.oid AS oid, p.relname AS table_name, n.nspname as table_schema FROM pg_class AS p JOIN pg_namespace AS n ON p.relnamespace=n.oid WHERE p.relkind='r';"} received query: "SELECT p.oid AS oid, p.relname AS table_name, n.nspname as table_schema FROM pg_class AS p JOIN pg_namespace AS n ON p.relnamespace=n.oid WHERE p.relkind='r';"

can't connect with dbeaver postgres - can connect with psql

dbeaver config: localhost, 5432, nc_timebilling.db

error: can't connect with dbeaver postgres - no databases found on the server

works: $ psql --host localhost nc_timebilling.db

Hi,

I'm likely doing something wrong, but unable to build.

    ~/code/postlite  on   main  go install -tags vtable ./cmd/postlite                                     
    ~/code/postlite  on   main  go build                                                                    
# github.com/benbjohnson/postlite
./pg_class.go:11:79: undefined: sqlite3.VTab
./pg_class.go:54:80: undefined: sqlite3.VTab
./pg_class.go:62:40: undefined: sqlite3.VTabCursor
./pg_class.go:66:48: undefined: sqlite3.InfoConstraint
./pg_class.go:66:77: undefined: sqlite3.InfoOrderBy
./pg_class.go:66:100: undefined: sqlite3.IndexResult
./pg_database.go:11:82: undefined: sqlite3.VTab
./pg_database.go:35:83: undefined: sqlite3.VTab
./pg_database.go:43:43: undefined: sqlite3.VTabCursor
./pg_database.go:47:51: undefined: sqlite3.InfoConstraint
./pg_database.go:47:51: too many errors

Any hints appreciated.

Thannks!