Re: Identifying user-created objects

On Thu, 5 Mar 2020 at 16:36, Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> wrote:
>
> At Thu, 5 Mar 2020 15:21:49 +0900, Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote in
> > > > 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.
>
> Thanks for the elaboration. It doesn't seem to me as the
> resposibility of PostgreSQL program. The same can be said to OSes.
>
> I think the section is not saying that "keep you system only with
> defaultly installed components", but "remove all features unncecessary
> to your system even if it is defaultly installed as far as you can".

Agreed.

> And whether A system is needing a feature or not cannot be the matter
> of PostgreSQL or OSes.
>
> So you need to remove some system-admistrative functions if you know
> it is not required by your system in order to comform the
> requirement. But they would be "non-user-defined" objects.

I think normally users don't want to remove built-in functions because
they think these functions are trusted and it's hard to restore them
when they want later. So I thought user want to search functions that
is unnecessary but not a built-in function.

Regards,

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