Обсуждение: What object types should be in schemas?
The current hierarchy of object types is like this: database access method event trigger extension foreign data wrapper foreign server language publication schema aggregate collation conversion domain function/procedure index operator operator class operator family sequence statistics table/view policy rule trigger text search configuration text search dictionary text search parser text search template type subscription role tablespace special: - cast - transform - user mapping How does one decide whether something should be in a schema or not? The current state feels intuitively correct, but I can't determine any firm way to decide. Over in the column encryption thread, the patch proposes to add various key types as new object types. For simplicity, I just stuck them directly under database, but I don't know whether that is correct. Thoughts?
Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes: > The current hierarchy of object types is like this: > ... > How does one decide whether something should be in a schema or not? Roughly speaking, I think the intuition was "if there are not likely to be a lot of objects of type X, maybe they don't need to be within schemas". Extensions might be raised as a counterexample, but in that case I recall that there was a specific consideration: extensions can contain (own) schemas, so it would be very confusing if they could also be within schemas. I'm not sure about whether that holds for foreign data wrappers and foreign servers, but isn't that case mandated by the SQL spec? Roles and tablespaces aren't within schemas because they aren't within databases. > Over in the column encryption thread, the patch proposes to add various > key types as new object types. For simplicity, I just stuck them > directly under database, but I don't know whether that is correct. Is it reasonable for those to be per-database rather than cluster-wide? I don't immediately see a reason to have encrypted columns in shared catalogs, but there would never be any chance of supporting that if the keys live in per-database catalogs. (OTOH, perhaps there are security reasons to keep them per-database, so I'm not insisting that this is the right way.) If we did make them cluster-wide then of course they'd be outside schemas too. If we don't, I'd lean slightly towards putting them within schemas, because that seems to be the default choice if you're not sure. There probably aren't a huge number of text search parsers either, but they live within schemas. regards, tom lane
On 2023-Jan-11, Peter Eisentraut wrote: > How does one decide whether something should be in a schema or not? The > current state feels intuitively correct, but I can't determine any firm way > to decide. > > Over in the column encryption thread, the patch proposes to add various key > types as new object types. For simplicity, I just stuck them directly under > database, but I don't know whether that is correct. I think one important criterion to think about is how does encryption work when you have per-customer (or per-whatever) schemas. Is the concept of a column encryption [objtype] a thing that you would like to set up per customer? In that case, you will probably want that object to live in that customer's schema. Otherwise, you'll force the DBA to come up with a naming scheme that includes the customer name in the column encryption object. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "En las profundidades de nuestro inconsciente hay una obsesiva necesidad de un universo lógico y coherente. Pero el universo real se halla siempre un paso más allá de la lógica" (Irulan)
On 12.01.23 18:41, Alvaro Herrera wrote: > I think one important criterion to think about is how does encryption work > when you have per-customer (or per-whatever) schemas. Is the concept of > a column encryption [objtype] a thing that you would like to set up per > customer? In that case, you will probably want that object to live in > that customer's schema. Otherwise, you'll force the DBA to come up with > a naming scheme that includes the customer name in the column encryption > object. Makes sense. In my latest patch I have moved these key objects into schemas.