Re: Identifying user-created objects

On Thu, 5 Mar 2020 at 13:23, Fujii Masao <masao.fujii@oss.nttdata.com> wrote:
>
>
>
> On 2020/03/05 12:32, Kyotaro Horiguchi wrote:
> > At Wed, 4 Mar 2020 21:07:05 +0900, Fujii Masao <masao.fujii@oss.nttdata.com> wrote in
> >>>>>>>> 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?
> >>
> >> Yes.
> >>
> >> My opinion is that, if malious users are not allowed to log in
> >> as superusers and the admin give no permission on the system
> >> schema/catalog to them, checking whether the object is defined
> >> under pg_catalog schema or not is enough for your purpose.
> >> Because they are also not allowed to create the object under
> >> pg_catalog. pg_is_user_object() seems not necessary.
> >>
> >> OTOH, if you address the case where malicious users can create
> >> the object under pg_catalog, of course, checking whether
> >> the object is defined under pg_catalog schema or not is enough
> >> for the purpose. But pg_is_user_object() is also not enough
> >> because such users can change oid.
> >
> > The discussion seems assuming the feature is related to some security
> > measure.  But I think I haven't seen the objective or use case for the
> > feature. I don't see how we should treat them according the result
> > from the "user-defined objects detection" feature.
> >
> > For example, we could decide a function whether to be pushed-out or
> > not to remote server on postgres_fdw. In this case, we need to ask "is
> > the behavior of this function known to us?", in short, "is this
> > function is predefined?".  In this use case, we have no concern if DBA
> > have added some functions as "not user-defined", since it's their own
> > risk.
> >
> > I don't come up with another use cases but, anyway, I think we need to
> > clarify the scope of the feature.
>
> Agreed. Also we would need to consider that the existing approach
> (e.g., checking whether the object is defined under pg_catalog or not,
> or seeing pg_stat_user_functions, _indexes, and _tables) is enough
> for the use cases. If enough, new function might not be necessary.
> If not enough, we might also need to reconsider the definitions of
> pg_stat_user_xxx after considering the function.
>

Originally the motivation of this feature is that while studying PCI
DSS 2.2.5 I thought that a running PostgreSQL server is not able to
prove that there is no  malicious function in database. PCI DSS 2.2.5
states "Remove all unnecessary functionality, such as scripts,
drivers, features, subsystems, file systems, and unnecessary web
servers." If we want to clarify unnecessary or malicious functions we
can check public schema and user schema but once a function is created
on pg_proc we cannot distinguish whether it's a built-in (i.g. safe)
function or not. I totally agree that if malicious someone logs in as
a superuser he/she can do anything more serious than changing catalog
contents but I wanted to have a way to prove soundness of running
database.

Regards,

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