When using the HTTP JSON API, one can pass a JDBC configuration which is used to connect to a Postgres database for the query store. This config includes a database user and an option to create the schema or not (if it already exists). How do users typically interact with this from a database role perspective?
Specifically, the database user when creating the schema needs to have permissions to drop and create tables (DDL operations), but when running the service afterwards the user only needs permissions to read and write to existing tables. In the latter scenario, is it normal to inspect the schema created and create a new user with the refined permissions to those tables, or does one typically just use a single user with liberal permissions for both modes?
The sandbox docs seem to suggest always passing the same user with DDL privileges, in case migrations are required.
I’m wondering what the common usage is here for designing the trigger service.
2 Likes
With respect to the JSON API, it’s best to think of the database as a DB-admin-configurable cache, which is updated in accordance with the invariant a token will only be used within the scope of a request. So, for example, it by no means reflects a consistent view of the active contract set, except with respect to specific subsets the JSON API is capable of exploiting. It would be extremely unwise to build a separate application around JSON API’s data store, without also incorporating the whole of JSON API’s data management code and DB ownership.
The way we expect admins to interact with this database is by attaching indices. We cannot index for every possible query, so instead, admins can index in accordance with the template-specific queries that are actually being made to their JSON API deployments, in exactly the way a DBA might normally be responsible for indexing for applications. For example, if you commonly query on {"foo": {"bar": ...}}, it might be wise for your DBA to index the foo.bar JSON property.
There is only one user of core interest: the JSON API user. There is no interesting way in which we could segment the access this user needs with respect to read and write; I suppose it would be possible to deny DDL to the JSON API after the initial create, especially because it deliberately excludes the possibility of migrations, but that would be niche at best.
I can imagine that DBAs might want to grant abilities to manage the aforementioned custom indices to different users, but that is a concern JSON API leaves to them.
5 Likes
That makes sense, thanks!
1 Like