Re: Identifying user-created objects

On Wed, 4 Mar 2020 at 18:57, Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
>
>
>
> On 2020/03/04 18:36, Masahiko Sawada wrote:
> > On Wed, 4 Mar 2020 at 18:02, Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
> >>
> >>
> >>
> >> On 2020/03/04 17:05, Masahiko Sawada wrote:
> >>> On Wed, 4 Mar 2020 at 16:43, Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
> >>>>
> >>>>
> >>>>
> >>>> On 2020/02/05 20:26, Masahiko Sawada wrote:
> >>>>> 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.
> >>>>
> >>>> The function that you are proposing is really enough for this use case?
> >>>> What if malicious users directly change the oid of function
> >>>> to < FirstNormalObjectId? Or you're assuming that malicious users will
> >>>> never log in as superuser and not be able to change the oid?
> >>>
> >>> That's a good point! I'm surprised that user is allowed to update an
> >>> oid of database object. In addition, surprisingly we can update it to
> >>> 0, which in turn leads the assertion failure:
> >>
> >> Since non-superusers are not allowed to do that by default,
> >> that's not so bad? That is, to avoid such unexpected change of oid,
> >> admin just should prevent malicious users from logging in as superusers
> >> and not give the permission on system catalogs to such users.
> >>
> >
> > I think there is still insider threats. As long as we depend on
> > superuser privilege to do some DBA work, a malicious DBA might be able
> > to log in as superuser and modify oid.
>
> Yes. But I'm sure that DBA has already considered the measures
> againt such threads. Otherwise malicious users can do anything
> more malicious rather than changing oid.

Agreed. So that's not a serious problem in practice but we cannot say
the checking by pg_is_user_object() is totally enough for checking
whether malicious object exists or not. Is that right?

Regards,

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