Identifying user-created objects

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
41 messages Options
123
Reply | Threaded
Open this post in threaded view
|

Identifying user-created objects

Masahiko Sawada-2
Hi,

User can create database objects such as functions into pg_catalog.
But if I'm not missing something, currently there is no
straightforward way to identify if the object is a user created object
or a system object which is created during initdb. If we can do that
user will be able to check if malicious functions are not created in
the database, which is important from the security perspective.

I've attached PoC patch to introduce a SQL function
pg_is_user_object() that returns true if the given oid is user object
oid, that is greater than or equal to FirstNormalObjectId. Feedback is
very welcome.

Regards,

--
Masahiko Sawada            http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

identify_user_object.patch (3K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Amit Langote
On Wed, Feb 5, 2020 at 8:27 PM Masahiko Sawada
<[hidden email]> wrote:

> User can create database objects such as functions into pg_catalog.
> But if I'm not missing something, currently there is no
> straightforward way to identify if the object is a user created object
> or a system object which is created during initdb. If we can do that
> user will be able to check if malicious functions are not created in
> the database, which is important from the security perspective.
>
> I've attached PoC patch to introduce a SQL function
> pg_is_user_object() that returns true if the given oid is user object
> oid, that is greater than or equal to FirstNormalObjectId. Feedback is
> very welcome.

+1.

About the implementation, how about defining a static inline function,
say is_user_object(), next to FirstNormalObjectId's definition and
make pg_is_user_object() call it?  There are a few placed in the
backend code that perform the same computation as pg_is_user_object(),
which could be changed to use is_user_object() instead.

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Michael Paquier-2
On Thu, Feb 06, 2020 at 04:25:47PM +0900, Amit Langote wrote:
> About the implementation, how about defining a static inline function,
> say is_user_object(), next to FirstNormalObjectId's definition and
> make pg_is_user_object() call it?  There are a few placed in the
> backend code that perform the same computation as pg_is_user_object(),
> which could be changed to use is_user_object() instead.

FWIW, if we bother adding SQL functions for that, my first impression
was to have three functions, each one of them returning:
- FirstNormalObjectId
- FirstGenbkiObjectId
- FirstNormalObjectId
Perhaps you should add a minimal set of regression tests in the
patch.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Amit Langote
On Thu, Feb 6, 2020 at 4:31 PM Michael Paquier <[hidden email]> wrote:

> On Thu, Feb 06, 2020 at 04:25:47PM +0900, Amit Langote wrote:
> > About the implementation, how about defining a static inline function,
> > say is_user_object(), next to FirstNormalObjectId's definition and
> > make pg_is_user_object() call it?  There are a few placed in the
> > backend code that perform the same computation as pg_is_user_object(),
> > which could be changed to use is_user_object() instead.
>
> FWIW, if we bother adding SQL functions for that, my first impression
> was to have three functions, each one of them returning:
> - FirstNormalObjectId
> - FirstGenbkiObjectId
> - FirstNormalObjectId

Did you miss FirstBootstrapObjectId by any chance?

I see the following ranges as defined in transam.h.

1-(FirstGenbkiObjectId - 1): manually assigned OIDs
FirstGenbkiObjectId-(FirstBootstrapObjectId - 1): genbki.pl assigned OIDs
FirstBootstrapObjectId-(FirstNormalObjectId - 1): initdb requested
FirstNormalObjectId or greater: user-defined objects

Sawada-san's proposal covers #4.  Do we need an SQL function for the
first three?  IOW, would the distinction between OIDs belonging to the
first three ranges be of interest to anyone except core PG hackers?

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Julien Rouhaud
On Thu, Feb 6, 2020 at 8:53 AM Amit Langote <[hidden email]> wrote:

>
> On Thu, Feb 6, 2020 at 4:31 PM Michael Paquier <[hidden email]> wrote:
> > On Thu, Feb 06, 2020 at 04:25:47PM +0900, Amit Langote wrote:
> > > About the implementation, how about defining a static inline function,
> > > say is_user_object(), next to FirstNormalObjectId's definition and
> > > make pg_is_user_object() call it?  There are a few placed in the
> > > backend code that perform the same computation as pg_is_user_object(),
> > > which could be changed to use is_user_object() instead.
> >
> > FWIW, if we bother adding SQL functions for that, my first impression
> > was to have three functions, each one of them returning:
> > - FirstNormalObjectId
> > - FirstGenbkiObjectId
> > - FirstNormalObjectId
>
> Did you miss FirstBootstrapObjectId by any chance?
>
> I see the following ranges as defined in transam.h.
>
> 1-(FirstGenbkiObjectId - 1): manually assigned OIDs
> FirstGenbkiObjectId-(FirstBootstrapObjectId - 1): genbki.pl assigned OIDs
> FirstBootstrapObjectId-(FirstNormalObjectId - 1): initdb requested
> FirstNormalObjectId or greater: user-defined objects
>
> Sawada-san's proposal covers #4.  Do we need an SQL function for the
> first three?  IOW, would the distinction between OIDs belonging to the
> first three ranges be of interest to anyone except core PG hackers?

+1 for #4, but I'm not sure that the other 3 are really interesting to
have at SQL level.


Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Michael Paquier-2
In reply to this post by Amit Langote
On Thu, Feb 06, 2020 at 04:52:48PM +0900, Amit Langote wrote:
> On Thu, Feb 6, 2020 at 4:31 PM Michael Paquier <[hidden email]> wrote:
>> FWIW, if we bother adding SQL functions for that, my first impression
>> was to have three functions, each one of them returning:
>> - FirstNormalObjectId
>> - FirstGenbkiObjectId
>> - FirstNormalObjectId
>
> Did you miss FirstBootstrapObjectId by any chance?

Yep, incorrect copy-pasto.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Masahiko Sawada-2
In reply to this post by Amit Langote
On Thu, 6 Feb 2020 at 16:53, Amit Langote <[hidden email]> wrote:

>
> On Thu, Feb 6, 2020 at 4:31 PM Michael Paquier <[hidden email]> wrote:
> > On Thu, Feb 06, 2020 at 04:25:47PM +0900, Amit Langote wrote:
> > > About the implementation, how about defining a static inline function,
> > > say is_user_object(), next to FirstNormalObjectId's definition and
> > > make pg_is_user_object() call it?  There are a few placed in the
> > > backend code that perform the same computation as pg_is_user_object(),
> > > which could be changed to use is_user_object() instead.
> >
> > FWIW, if we bother adding SQL functions for that, my first impression
> > was to have three functions, each one of them returning:
> > - FirstNormalObjectId
> > - FirstGenbkiObjectId
> > - FirstNormalObjectId
>
> Did you miss FirstBootstrapObjectId by any chance?
>
> I see the following ranges as defined in transam.h.
>
> 1-(FirstGenbkiObjectId - 1): manually assigned OIDs
> FirstGenbkiObjectId-(FirstBootstrapObjectId - 1): genbki.pl assigned OIDs
> FirstBootstrapObjectId-(FirstNormalObjectId - 1): initdb requested
> FirstNormalObjectId or greater: user-defined objects
>
> Sawada-san's proposal covers #4.  Do we need an SQL function for the
> first three?  IOW, would the distinction between OIDs belonging to the
> first three ranges be of interest to anyone except core PG hackers?

Yeah I thought of these three values but I'm also not sure it's worth for users.

If we have these functions returning the values respectively, when we
want to check if an oid is assigned during initdb we will end up with
doing something like 'WHERE oid >= pg_first_bootstrap_oid() and oid <
pg_first_normal_oid()', which is not intuitive, I think. Users have to
remember the order of these values.

Regards,

--
Masahiko Sawada            http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Masahiko Sawada-2
On Thu, 6 Feb 2020 at 17:18, Masahiko Sawada
<[hidden email]> wrote:

>
> On Thu, 6 Feb 2020 at 16:53, Amit Langote <[hidden email]> wrote:
> >
> > On Thu, Feb 6, 2020 at 4:31 PM Michael Paquier <[hidden email]> wrote:
> > > On Thu, Feb 06, 2020 at 04:25:47PM +0900, Amit Langote wrote:
> > > > About the implementation, how about defining a static inline function,
> > > > say is_user_object(), next to FirstNormalObjectId's definition and
> > > > make pg_is_user_object() call it?  There are a few placed in the
> > > > backend code that perform the same computation as pg_is_user_object(),
> > > > which could be changed to use is_user_object() instead.
> > >
> > > FWIW, if we bother adding SQL functions for that, my first impression
> > > was to have three functions, each one of them returning:
> > > - FirstNormalObjectId
> > > - FirstGenbkiObjectId
> > > - FirstNormalObjectId
> >
> > Did you miss FirstBootstrapObjectId by any chance?
> >
> > I see the following ranges as defined in transam.h.
> >
> > 1-(FirstGenbkiObjectId - 1): manually assigned OIDs
> > FirstGenbkiObjectId-(FirstBootstrapObjectId - 1): genbki.pl assigned OIDs
> > FirstBootstrapObjectId-(FirstNormalObjectId - 1): initdb requested
> > FirstNormalObjectId or greater: user-defined objects
> >
> > Sawada-san's proposal covers #4.  Do we need an SQL function for the
> > first three?  IOW, would the distinction between OIDs belonging to the
> > first three ranges be of interest to anyone except core PG hackers?
>
> Yeah I thought of these three values but I'm also not sure it's worth for users.
>
> If we have these functions returning the values respectively, when we
> want to check if an oid is assigned during initdb we will end up with
> doing something like 'WHERE oid >= pg_first_bootstrap_oid() and oid <
> pg_first_normal_oid()', which is not intuitive, I think. Users have to
> remember the order of these values.
>
Attached the updated version patch that includes regression tests. And
I have registered this to the next commit fest.

Regards,

--
Masahiko Sawada            http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

identify_user_object_v2.patch (5K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Amit Langote
Sawada-san,

On Mon, Feb 10, 2020 at 12:25 PM Masahiko Sawada
<[hidden email]> wrote:
> > > > On Thu, Feb 06, 2020 at 04:25:47PM +0900, Amit Langote wrote:
> > > > > About the implementation, how about defining a static inline function,
> > > > > say is_user_object(), next to FirstNormalObjectId's definition and
> > > > > make pg_is_user_object() call it?  There are a few placed in the
> > > > > backend code that perform the same computation as pg_is_user_object(),
> > > > > which could be changed to use is_user_object() instead.
>
> Attached the updated version patch that includes regression tests. And
> I have registered this to the next commit fest.

Thank you.

Any thoughts on the above suggestion?

Regards,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Masahiko Sawada-2
On Mon, 10 Feb 2020 at 12:54, Amit Langote <[hidden email]> wrote:

>
> Sawada-san,
>
> On Mon, Feb 10, 2020 at 12:25 PM Masahiko Sawada
> <[hidden email]> wrote:
> > > > > On Thu, Feb 06, 2020 at 04:25:47PM +0900, Amit Langote wrote:
> > > > > > About the implementation, how about defining a static inline function,
> > > > > > say is_user_object(), next to FirstNormalObjectId's definition and
> > > > > > make pg_is_user_object() call it?  There are a few placed in the
> > > > > > backend code that perform the same computation as pg_is_user_object(),
> > > > > > which could be changed to use is_user_object() instead.
> >
> > Attached the updated version patch that includes regression tests. And
> > I have registered this to the next commit fest.
>
> Thank you.
>
> Any thoughts on the above suggestion?

Oops, I had overlooked it. I agree with you.

How about having it as a macro like:

#define ObjectIdIsUserObject(oid) ((Oid)(oid) >= FirstNormalObjectId)

Regards,

--
Masahiko Sawada            http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Amit Langote
On Mon, Feb 10, 2020 at 1:06 PM Masahiko Sawada
<[hidden email]> wrote:

> On Mon, 10 Feb 2020 at 12:54, Amit Langote <[hidden email]> wrote:
> >
> > Sawada-san,
> >
> > On Mon, Feb 10, 2020 at 12:25 PM Masahiko Sawada
> > <[hidden email]> wrote:
> > > > > > On Thu, Feb 06, 2020 at 04:25:47PM +0900, Amit Langote wrote:
> > > > > > > About the implementation, how about defining a static inline function,
> > > > > > > say is_user_object(), next to FirstNormalObjectId's definition and
> > > > > > > make pg_is_user_object() call it?  There are a few placed in the
> > > > > > > backend code that perform the same computation as pg_is_user_object(),
> > > > > > > which could be changed to use is_user_object() instead.
> > >
> > > Attached the updated version patch that includes regression tests. And
> > > I have registered this to the next commit fest.
> >
> > Thank you.
> >
> > Any thoughts on the above suggestion?
>
> Oops, I had overlooked it. I agree with you.
>
> How about having it as a macro like:
>
> #define ObjectIdIsUserObject(oid) ((Oid)(oid) >= FirstNormalObjectId)

I'm fine with a macro.

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Michael Paquier-2
On Mon, Feb 10, 2020 at 01:16:30PM +0900, Amit Langote wrote:
> On Mon, Feb 10, 2020 at 1:06 PM Masahiko Sawada
> <[hidden email]> wrote:
>> How about having it as a macro like:
>>
>> #define ObjectIdIsUserObject(oid) ((Oid)(oid) >= FirstNormalObjectId)
>
> I'm fine with a macro.

I am not sure that it is worth having one extra abstraction layer for
that.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Masahiko Sawada-2
On Mon, 10 Feb 2020 at 14:09, Michael Paquier <[hidden email]> wrote:

>
> On Mon, Feb 10, 2020 at 01:16:30PM +0900, Amit Langote wrote:
> > On Mon, Feb 10, 2020 at 1:06 PM Masahiko Sawada
> > <[hidden email]> wrote:
> >> How about having it as a macro like:
> >>
> >> #define ObjectIdIsUserObject(oid) ((Oid)(oid) >= FirstNormalObjectId)
> >
> > I'm fine with a macro.
>
> I am not sure that it is worth having one extra abstraction layer for
> that.

Hmm I'm not going to insist on that but I thought that it could
somewhat improve readability at places where they already compares an
oid to FirstNormalObjectId as Amit mentioned:

src/backend/catalog/pg_publication.c:       relid >= FirstNormalObjectId;
src/backend/utils/adt/json.c:               if (typoid >= FirstNormalObjectId)
src/backend/utils/adt/jsonb.c:              if (typoid >= FirstNormalObjectId)

Regards,

--
Masahiko Sawada            http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Amit Langote
On Mon, Feb 10, 2020 at 2:23 PM Masahiko Sawada
<[hidden email]> wrote:

> On Mon, 10 Feb 2020 at 14:09, Michael Paquier <[hidden email]> wrote:
> >
> > On Mon, Feb 10, 2020 at 01:16:30PM +0900, Amit Langote wrote:
> > > On Mon, Feb 10, 2020 at 1:06 PM Masahiko Sawada
> > > <[hidden email]> wrote:
> > >> How about having it as a macro like:
> > >>
> > >> #define ObjectIdIsUserObject(oid) ((Oid)(oid) >= FirstNormalObjectId)
> > >
> > > I'm fine with a macro.
> >
> > I am not sure that it is worth having one extra abstraction layer for
> > that.
>
> Hmm I'm not going to insist on that but I thought that it could
> somewhat improve readability at places where they already compares an
> oid to FirstNormalObjectId as Amit mentioned:
>
> src/backend/catalog/pg_publication.c:       relid >= FirstNormalObjectId;
> src/backend/utils/adt/json.c:               if (typoid >= FirstNormalObjectId)
> src/backend/utils/adt/jsonb.c:              if (typoid >= FirstNormalObjectId)

Agree that ObjectIsUserObject(oid) is easier to read than oid >=
FirstNormalObject.  I would have not bothered, for example, if it was
something like oid >= FirstUserObjectId to begin with.

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Kyotaro Horiguchi-4
At Mon, 10 Feb 2020 14:32:44 +0900, Amit Langote <[hidden email]> wrote in

> On Mon, Feb 10, 2020 at 2:23 PM Masahiko Sawada
> <[hidden email]> wrote:
> > On Mon, 10 Feb 2020 at 14:09, Michael Paquier <[hidden email]> wrote:
> > >
> > > On Mon, Feb 10, 2020 at 01:16:30PM +0900, Amit Langote wrote:
> > > > On Mon, Feb 10, 2020 at 1:06 PM Masahiko Sawada
> > > > <[hidden email]> wrote:
> > > >> How about having it as a macro like:
> > > >>
> > > >> #define ObjectIdIsUserObject(oid) ((Oid)(oid) >= FirstNormalObjectId)
> > > >
> > > > I'm fine with a macro.
> > >
> > > I am not sure that it is worth having one extra abstraction layer for
> > > that.
> >
> > Hmm I'm not going to insist on that but I thought that it could
> > somewhat improve readability at places where they already compares an
> > oid to FirstNormalObjectId as Amit mentioned:
> >
> > src/backend/catalog/pg_publication.c:       relid >= FirstNormalObjectId;
> > src/backend/utils/adt/json.c:               if (typoid >= FirstNormalObjectId)
> > src/backend/utils/adt/jsonb.c:              if (typoid >= FirstNormalObjectId)
>
> Agree that ObjectIsUserObject(oid) is easier to read than oid >=
> FirstNormalObject.  I would have not bothered, for example, if it was
> something like oid >= FirstUserObjectId to begin with.

Aside from the naming, I'm not sure it's sensible to use
FirstNormalObjectId since I don't see a clear definition or required
characteristics for "user created objects" is.  If we did CREATE
TABLE, FUNCTION or maybe any objects during single-user mode before
the first object is created during normal multiuser operation, the
"user-created(or not?)" object has an OID less than
FirstNormalObjectId. If such objects are the "user created object", we
need FirstUserObjectId defferent from FirstNormalObjectId.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center


Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Amit Langote
On Thu, Feb 13, 2020 at 10:30 AM Kyotaro Horiguchi
<[hidden email]> wrote:

> At Mon, 10 Feb 2020 14:32:44 +0900, Amit Langote <[hidden email]> wrote in
> > Agree that ObjectIsUserObject(oid) is easier to read than oid >=
> > FirstNormalObject.  I would have not bothered, for example, if it was
> > something like oid >= FirstUserObjectId to begin with.
>
> Aside from the naming, I'm not sure it's sensible to use
> FirstNormalObjectId since I don't see a clear definition or required
> characteristics for "user created objects" is.  If we did CREATE
> TABLE, FUNCTION or maybe any objects during single-user mode before
> the first object is created during normal multiuser operation, the
> "user-created(or not?)" object has an OID less than
> FirstNormalObjectId. If such objects are the "user created object", we
> need FirstUserObjectId defferent from FirstNormalObjectId.

Interesting observation.  Connecting to database in --single mode,
whether done using initdb or directly, is always considered
"bootstrapping", so the OIDs from the bootstrapping range are
consumed.

$ postgres --single -D pgdata postgres

PostgreSQL stand-alone backend 13devel
backend> create table a (a int);
backend> select 'a'::regclass::oid;
         1: oid (typeid = 26, len = 4, typmod = -1, byval = t)
        ----
         1: oid = "14168"       (typeid = 26, len = 4, typmod = -1, byval = t)

Here, FirstBootstrapObjectId < 14168 < FirstNormalObjectId

Maybe we could document that pg_is_user_object() and its internal
counterpart returns true only for objects that are created during
"normal" multi-user database operation.

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Julien Rouhaud
On Thu, Feb 13, 2020 at 8:32 AM Amit Langote <[hidden email]> wrote:

>
> On Thu, Feb 13, 2020 at 10:30 AM Kyotaro Horiguchi
> <[hidden email]> wrote:
> > At Mon, 10 Feb 2020 14:32:44 +0900, Amit Langote <[hidden email]> wrote in
> > > Agree that ObjectIsUserObject(oid) is easier to read than oid >=
> > > FirstNormalObject.  I would have not bothered, for example, if it was
> > > something like oid >= FirstUserObjectId to begin with.
> >
> > Aside from the naming, I'm not sure it's sensible to use
> > FirstNormalObjectId since I don't see a clear definition or required
> > characteristics for "user created objects" is.  If we did CREATE
> > TABLE, FUNCTION or maybe any objects during single-user mode before
> > the first object is created during normal multiuser operation, the
> > "user-created(or not?)" object has an OID less than
> > FirstNormalObjectId. If such objects are the "user created object", we
> > need FirstUserObjectId defferent from FirstNormalObjectId.
>
> Interesting observation.  Connecting to database in --single mode,
> whether done using initdb or directly, is always considered
> "bootstrapping", so the OIDs from the bootstrapping range are
> consumed.
>
> $ postgres --single -D pgdata postgres
>
> PostgreSQL stand-alone backend 13devel
> backend> create table a (a int);
> backend> select 'a'::regclass::oid;
>          1: oid (typeid = 26, len = 4, typmod = -1, byval = t)
>         ----
>          1: oid = "14168"       (typeid = 26, len = 4, typmod = -1, byval = t)
>
> Here, FirstBootstrapObjectId < 14168 < FirstNormalObjectId

FTR it's also possible to get the same result using binary mode and
binary_upgrade_set_next_XXX functions.

> Maybe we could document that pg_is_user_object() and its internal
> counterpart returns true only for objects that are created during
> "normal" multi-user database operation.

+1


Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Masahiko Sawada-2
On Thu, 13 Feb 2020 at 17:13, Julien Rouhaud <[hidden email]> wrote:

>
> On Thu, Feb 13, 2020 at 8:32 AM Amit Langote <[hidden email]> wrote:
> >
> > On Thu, Feb 13, 2020 at 10:30 AM Kyotaro Horiguchi
> > <[hidden email]> wrote:
> > > At Mon, 10 Feb 2020 14:32:44 +0900, Amit Langote <[hidden email]> wrote in
> > > > Agree that ObjectIsUserObject(oid) is easier to read than oid >=
> > > > FirstNormalObject.  I would have not bothered, for example, if it was
> > > > something like oid >= FirstUserObjectId to begin with.
> > >
> > > Aside from the naming, I'm not sure it's sensible to use
> > > FirstNormalObjectId since I don't see a clear definition or required
> > > characteristics for "user created objects" is.  If we did CREATE
> > > TABLE, FUNCTION or maybe any objects during single-user mode before
> > > the first object is created during normal multiuser operation, the
> > > "user-created(or not?)" object has an OID less than
> > > FirstNormalObjectId. If such objects are the "user created object", we
> > > need FirstUserObjectId defferent from FirstNormalObjectId.
> >
> > Interesting observation.  Connecting to database in --single mode,
> > whether done using initdb or directly, is always considered
> > "bootstrapping", so the OIDs from the bootstrapping range are
> > consumed.
> >
> > $ postgres --single -D pgdata postgres
> >
> > PostgreSQL stand-alone backend 13devel
> > backend> create table a (a int);
> > backend> select 'a'::regclass::oid;
> >          1: oid (typeid = 26, len = 4, typmod = -1, byval = t)
> >         ----
> >          1: oid = "14168"       (typeid = 26, len = 4, typmod = -1, byval = t)
> >
> > Here, FirstBootstrapObjectId < 14168 < FirstNormalObjectId
>
> FTR it's also possible to get the same result using binary mode and
> binary_upgrade_set_next_XXX functions.
>
> > Maybe we could document that pg_is_user_object() and its internal
> > counterpart returns true only for objects that are created during
> > "normal" multi-user database operation.
>
> +1
Agreed.

Attached updated version patch.

Regards,

--
Masahiko Sawada            http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

identify_user_object_v3.patch (8K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

Amit Langote
On Wed, Feb 26, 2020 at 4:48 PM Masahiko Sawada
<[hidden email]> wrote:

> On Thu, 13 Feb 2020 at 17:13, Julien Rouhaud <[hidden email]> wrote:
> > On Thu, Feb 13, 2020 at 8:32 AM Amit Langote <[hidden email]> wrote:
> > > Maybe we could document that pg_is_user_object() and its internal
> > > counterpart returns true only for objects that are created during
> > > "normal" multi-user database operation.
> >
> > +1
>
> Agreed.
>
> Attached updated version patch.

Thanks for updating the patch.  Some comments:

+      <row>
+       <entry><literal><function>pg_is_user_object(<parameter>oid</parameter>)</function></literal></entry>
+       <entry><type>bool</type></entry>
+       <entry>
+        true if <parameter>oid</parameter> is the object which is
created during
+        normal multi-user database operation.
+       </entry>
+      </row>

How about clarifying the description further as follows:

"true for objects created while database is operating in normal
multi-user mode, as opposed to single-user mode (see <xref
linkend="app-postgres"/>)."

Term "multi-user operation" is not mentioned elsewhere in the
documentation, so better to clarify what it means.

Also, maybe a minor nitpick, but how about adding the new function's
row at the end of the table (Table 9.72) instead of in the middle?

Other than that, patch looks to be in pretty good shape.

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: Identifying user-created objects

vignesh C
In reply to this post by Masahiko Sawada-2
On Wed, Feb 26, 2020 at 1:18 PM Masahiko Sawada
<[hidden email]> wrote:

>
> On Thu, 13 Feb 2020 at 17:13, Julien Rouhaud <[hidden email]> wrote:
> >
> > On Thu, Feb 13, 2020 at 8:32 AM Amit Langote <[hidden email]> wrote:
> > >
> > > On Thu, Feb 13, 2020 at 10:30 AM Kyotaro Horiguchi
> > > <[hidden email]> wrote:
> > > > At Mon, 10 Feb 2020 14:32:44 +0900, Amit Langote <[hidden email]> wrote in
> > > > > Agree that ObjectIsUserObject(oid) is easier to read than oid >=
> > > > > FirstNormalObject.  I would have not bothered, for example, if it was
> > > > > something like oid >= FirstUserObjectId to begin with.
> > > >
> > > > Aside from the naming, I'm not sure it's sensible to use
> > > > FirstNormalObjectId since I don't see a clear definition or required
> > > > characteristics for "user created objects" is.  If we did CREATE
> > > > TABLE, FUNCTION or maybe any objects during single-user mode before
> > > > the first object is created during normal multiuser operation, the
> > > > "user-created(or not?)" object has an OID less than
> > > > FirstNormalObjectId. If such objects are the "user created object", we
> > > > need FirstUserObjectId defferent from FirstNormalObjectId.
> > >
> > > Interesting observation.  Connecting to database in --single mode,
> > > whether done using initdb or directly, is always considered
> > > "bootstrapping", so the OIDs from the bootstrapping range are
> > > consumed.
> > >
> > > $ postgres --single -D pgdata postgres
> > >
> > > PostgreSQL stand-alone backend 13devel
> > > backend> create table a (a int);
> > > backend> select 'a'::regclass::oid;
> > >          1: oid (typeid = 26, len = 4, typmod = -1, byval = t)
> > >         ----
> > >          1: oid = "14168"       (typeid = 26, len = 4, typmod = -1, byval = t)
> > >
> > > Here, FirstBootstrapObjectId < 14168 < FirstNormalObjectId
> >
> > FTR it's also possible to get the same result using binary mode and
> > binary_upgrade_set_next_XXX functions.
> >
> > > Maybe we could document that pg_is_user_object() and its internal
> > > counterpart returns true only for objects that are created during
> > > "normal" multi-user database operation.
> >
> > +1
>
> Agreed.
>
> Attached updated version patch.
>

Should we add some check if object exists or not here:
+Datum
+pg_is_user_object(PG_FUNCTION_ARGS)
+{
+    Oid oid = PG_GETARG_OID(0);
+
+    PG_RETURN_BOOL(ObjectIsUserObject(oid));
+}

I was trying some scenarios where we pass an object which does not exist:
postgres=# SELECT pg_is_user_object(0);
 pg_is_user_object
-------------------
 f
(1 row)
postgres=# SELECT pg_is_user_object(222222);
 pg_is_user_object
-------------------
 t
(1 row)
SELECT pg_is_user_object('pg_class1'::regclass);
ERROR:  relation "pg_class1" does not exist
LINE 1: SELECT pg_is_user_object('pg_class1'::regclass);
                                 ^
I felt these behavior seems to be slightly inconsistent.
Thoughts?

Regards,
Vignesh
EnterpriseDB: http://www.enterprisedb.com


123