Re: pg_get_first_normal_oid()?

On Wed, Feb 10, 2021 at 4:43 PM Joel Jacobson <joel(at)compiler(dot)org> wrote:
>
> Hi,
>
> I need to filter out any system catalog objects from SQL,
> and I've learned it's not possible to simply filter based on namespace name,
> since there are objects such as pg_am that don't have any namespace belonging,
> except indirectly via their handler, but since you can define a new access method
> using an existing handler from the system catalog, there is no way to distinguish
> your user created access handler from the system catalog access handlers
> only based on namespace name based filtering.
>
> After some digging I found this
>
> #define FirstNormalObjectId 16384
>
> in src/include/access/transam.h, which pg_dump.c and 14 other files are using at 27 different places in the sources.
>
> Seems to be a popular and important fellow.
>
> I see this value doesn't change often, it was added back in 2005-04-13 in commit 2193a856a229026673cbc56310cd0bddf7b5ea25.
>
> Is it safe to just hard-code in application code needing to know this cut-off value?
>
> Or will we have a Bill Gates "640K ought to be enough for anybody" moment in the foreseeable future,
> where this limit needs to be increased?
>
> If there is a risk we will, then maybe we should add a function such as $SUBJECT to expose this value to SQL users who needs it?
>
> I see there has been a related discussion in the thread "Identifying user-created objects"
>
> https://www.postgresql.org/message-id/flat/CA%2Bfd4k7Zr%2ByQLYWF3O_KjAJyYYUZFBZ_dFchfBvq5bMj9GgKQw%40mail.gmail.com

As mentioned in that thread, it's still hard to distinguish between
user objects and system objects using only OID since we can create
objects with OID lower than FirstNormalObjectId by creating objects in
single-user mode. It was not enough for security purposes. I think
providing concrete use cases of the function would support this
proposal.

>
> However, this thread focused on security and wants to know if a specific oid is user defined or not.
>
> I think pg_get_first_normal_oid() would be more useful than pg_is_user_object(oid),
> since with pg_get_first_normal_oid() you could do filtering based on oid indexes.
>
> Compare e.g.:
>
> SELECT * FROM pg_class WHERE oid >= pg_get_first_normal_oid()
>
> with..
>
> SELECT * FROM pg_class WHERE pg_is_user_object(oid) IS TRUE
>
> The first query could use the index on pg_class.oid,
> whereas I'm not mistaken, the second query would need a seq_scan to evaluate pg_is_user_object() for each oid.

Yes. I've also considered the former approach but I prioritized
readability and extensibility; it requires prior knowledge for users
that OIDs greater than the first normal OID are used during normal
multi-user operation. Also in a future if we have similar functions
for other OID bounds such as FirstGenbkiObjectId and
FirstBootstrapObjectId we will end up doing like 'WHERE oid >=
pg_get_first_bootstrap_oid() and oid < pg_get_first_normal_oid()',
which is not intuitive.

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/