I noticed after creating a database that I wasn’t able to change any configuration settings via the set command, due to insufficient privileges. For example:
neondb=> alter system set default_transaction_isolation = serializable;
ERROR: permission denied to set parameter "default_transaction_isolation"
Based on this comment, it seems like this is the intended behavior, however there’s also no other way to change these settings. For example, Google’s Cloud SQL for PostgreSQL has database flags.
Looking through the docs, GitHub issues, and the community forum, I don’t see any reference to this kind of functionality. Is this something that Neon is planning to implement?
1 Like
I was having the same question. Hope there’s an answer.
Hi,
We expose some GUCs (a.k.a. flags or settings) such that you can modify them through the console’s API, but that doesn’t (yet) include default_transaction_isolation. I’ll forward that issue to our console team for consideration.
ALTER SYSTEM does not work because that requires superuser permissions, which isn’t compatible with our current security and configuration model: superusers misconfiguring settings could break several components in the system, and we don’t currently have a path for the PostgreSQL node to notify Console that the system configuration has been updated.
We’re working on adjacent features that should make it easier to improve the situation, but it’ll take a little more time before we can expose this to users.
1 Like
@Daniel’s post in a different thread made me remember that ALTER DATABASE $mydb SET default_transaction_isolation = serializable is a potential workaround.
System-level configuration updates with ALTER SYSTEM are still not supported, but most use cases can be covered by updating the database-level settings instead, using ALTER DATABASE.
Thanks for your answers Matthias!
Can you add a documentation link to where these settings are located please? Because when I searched before creating this post, I couldn’t find anything like that. Not saying it doesn’t exist, just that I couldn’t find it so maybe the same thing could happen to others.
As I understand it from your comments, the eventual plan is to allow superuser access, as opposed to some ui/api abstraction?
Regarding the alter database workaround, thanks for that; it’ll definitely help.
Can you add a documentation link to where these settings are located please? Because when I searched before creating this post, I couldn’t find anything like that.
I don’t think we have documentation on which settings we allow users to set through the API, no, so it’s no wonder you couldn’t find it.
The documentation on how to configure the settings for an endpoint is available in the API docs for updating an endpoint, and project-level defaults can be configured at the project level in its top-level default_endpoint_settings field.
As I understand it from your comments, the eventual plan is to allow superuser access, as opposed to some ui/api abstraction?
Not quite. We’re planning on streamlining the flow of our processes so that users can configure most of their instances just like any other PostgreSQL instance where they have SUPERUSER permissions, but with some limitations.
One such limitation is that users are unable (and will remain unable) to use COPY [TO / FROM] [FILE / PROGRAM] in Neon - executing arbitrary programs on the host node or reading and writing arbitrary files as the PostgreSQL user is not great from a security perspective, so we’ll keep that locked down for now.
But, we’re working on making user administration available through PostgreSQL, as well as database creation and other administrative tasks that currently require users to use the console UI or API.
Do you think you could list those options either here or in the docs, or are they not meant to be publicly accessible?
That sounds like a good compromise to me, regarding superuser access. Thanks for your detailed response!
I’ll forward the request to the docs team, as global configuration limitations should indeed be part of the documentation.