Re: Identifying user-created objects

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.

This behavior is introduced in PG12 where we made oid column
non-system column. A table having oid = 0 is shown in pg_class but we
cannot drop it.

Regards,

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