PostgREST configuration available in db_root_spec session

[wayland]

Hi!

I'm helping with the effort to rewrite the OpenAPI stuff in SQL, in a separate module. I wanted to bring to the attention of the PostgREST devs one of the features that we'll need to have added to PostgREST to complete this work.

We need access to some of the configuration parameters from SQL. Our thought is that PostgREST can run some set_config statements in SQL before calling pgrst.db_root_spec. The parameters we currently need are:

Need:

• pgrst.server_host and pgrst.server_port (or alternately server_proxy_uri -- just let us know which)
• pgrst.db_schemas
• pgrst.version

Thanks!

[steve-chavez]

@wayland Would you like to help with this?

The configs are set here:

postgrest/src/PostgREST/Query.hs

Lines 236 to 258 in c3301a1

	-- | Set transaction scoped settings
	setPgLocals :: AppConfig -> KM.KeyMap JSON.Value -> BS.ByteString -> [(ByteString, ByteString)] ->
	ApiRequest -> Maybe Text -> DbHandler ()
	setPgLocals AppConfig{..} claims role roleSettings ApiRequest{..} tout = lift $
	SQL.statement mempty $ SQL.dynamicallyParameterized
	-- To ensure `GRANT SET ON PARAMETER <superuser_setting> TO authenticator` works, the role settings must be set before the impersonated role.
	-- Otherwise the GRANT SET would have to be applied to the impersonated role. See https://github.com/PostgREST/postgrest/issues/3045
	("select " <> intercalateSnippet ", " (searchPathSql : roleSettingsSql ++ roleSql ++ claimsSql ++ [methodSql, pathSql] ++ headersSql ++ cookiesSql ++ timezoneSql ++ timeoutSql ++ appSettingsSql))
	HD.noResult configDbPreparedStatements
	where
	methodSql = setConfigWithConstantName ("request.method", iMethod)
	pathSql = setConfigWithConstantName ("request.path", iPath)
	headersSql = setConfigWithConstantNameJSON "request.headers" iHeaders
	cookiesSql = setConfigWithConstantNameJSON "request.cookies" iCookies
	claimsSql = [setConfigWithConstantName ("request.jwt.claims", LBS.toStrict $ JSON.encode claims)]
	roleSql = [setConfigWithConstantName ("role", role)]
	roleSettingsSql = setConfigWithDynamicName <$> roleSettings
	appSettingsSql = setConfigWithDynamicName <$> (join bimap toUtf8 <$> configAppSettings)
	timezoneSql = maybe mempty (\(PreferTimezone tz) -> [setConfigWithConstantName ("timezone", tz)]) $ preferTimezone iPreferences
	timeoutSql = maybe mempty ((\t -> [setConfigWithConstantName ("statement_timeout", t)]) . encodeUtf8) tout
	searchPathSql =
	let schemas = escapeIdentList (iSchema : configDbExtraSearchPath) in
	setConfigWithConstantName ("search_path", schemas)

[wayland]

Thanks! Hopefully I'll get to this eventually.

My goal is to hook jsonforms-editor ( https://jsonforms-editor.netlify.app/ ) to this, and hopefully, I'll be able to add queries to Postgres, and forms will appear in the webapp.

The pieces I still need to hook together are:

• A build (docker-compose) that includes PostgREST, DataWharf, and some kind of ETL (maybe Apache Nifi?)
• Get OpenAPI 3.1+ working in Postgres
• A jsonforms-editor based app (to add to the build above)
• This ticket here (the last piece, because I've almost no experience with Haskell -- as a Perl programmer (with some Raku), though, I'm closer to functional languages than the average coder)

If I get the OpenAPI stuff finished, I might dive into this before the jsonforms stuff, though; hopefully it'll be a smaller task.

[plentydone]

Does anyone mind if I take a swing at this? This seems like a fairly small change. Am I missing any requirements beyond getting setPgLocals to set the new fields and seeing if I can scrounge together a test?

[wolfgangwalther]

I'm personally not convinced that set_config is the right approach. I think we should do better for the root endpoint and use arguments to the respective function.

[laurenceisla]

I'm personally not convinced that set_config is the right approach. I think we should do better for the root endpoint and use arguments to the respective function.

Agree. Not discarding this option altogether, but I'd like to try something like this first:

create or replace function root(
  server_host text,
  server_port int,
  db_schemas text[],
  version text
) as $$
  ...
$$;

# postgrest.conf
db-root-spec = "root"

PostgREST would need to pass those values to the function.

[steve-chavez]

Hardcoding a function signature at this stage (db-root-spec is still experimental) seems more cumbersome than just passing the GUCs only for the root spec. Once postgrest-openapi stabilizes we can commit to a signature or perhaps just a json param.