Обсуждение: 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.