search

zodb / relstorage

A backend for ZODB that stores pickles in a relational database.
Other
54 stars 46 forks source link

PostgreSql namespace with static name #423

Open NicolasGoeddel opened 3 years ago

NicolasGoeddel commented 3 years ago

Hi,

after a customers moved its PostgreSql database server from localhost to an external host they also changed the namespace of the database Plone connects to. After that Plone did not want to start anymore. The reason was the following line which change the output of that method to an empty dict.

https://github.com/zodb/relstorage/blob/master/src/relstorage/adapters/postgresql/schema.py#L195

I am not sure if it is enough to simply remove the WHERE clause here. In my case it worked. But in general I guess it should be changed to the real namespace the Relstorage is working in.

jamadden commented 3 years ago

RelStorage doesn't support creating the database in a different namespace, so changing it after the fact is also not supported. I think there would need to be a number of changes to make that reliable.

NicolasGoeddel commented 3 years ago

I don't think I can convince our customer to use a table space with the name "public". What could possibly go wrong? I was able to start Plone as usual and I can work with it.

jamadden commented 3 years ago

Note that "public" doesn't imply anything about security beyond the owner of the database having write access; it's simply the name PostgreSQL uses to mean "default." It can be permissioned in any way you choose.

In general, RelStorage knows nothing about PostgreSQL schemas (or schemas in any other database). No schemas or procedure invocations are qualified by schema name. RelStorage generally assumes that the current connection is enough of a qualifier. Thus, when multiple schemas are in use for the same database, properly configuring the "search path" is key, or very wrong results can occur (unexpected/incorrect tables or procedures can hide the expected tables or procedures).

If you change the search_path after the database has been created and initialized by RelStorage, you may wind up with some RelStorage objects in some schemas and others (potentially older and out of date versions) in others. That's confusing at best. Note that using current_schema in the query you cited to replace "public" doesn't solve this problem, because there really isn't a "current schema", just a first item in a search path, and once multiple schemas are on the table, there's no guarantee that the first item in the search path is actually the desired home of RelStorage objects.

The RelStorage drivers do not provide a standard way to set the required search_path configuration value. If you rely on the way one driver does it, you'll have to find a different way if you choose to use a different driver. The only portable way (across PostgreSQL drivers, anyway) is to assign the search_path to particular roles, and then you're back to managing multiple users anyway.

The same goes for migrating to other RDBMS backends. Since schema usage isn't standardized, anything beyond the one user == one database == one RelStorage installation is going to complicate that.

Those are some of the potential complications I don't have the time to support.