Re: Identifying user-created objects

At Thu, 5 Mar 2020 18:06:26 +0900, Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote in
> 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.

I'm not sure those who wants to introduce PCI-DSS are under a normal
sitautation, though:p

That seems beside the point. pg_read_file is known to be usable for
drawing out database files. If you leave the function alone, the
security officer (designer?) have to consider the possibility that
someone draws out files in the database system using the function and
have to plan the action for the threat. In that context,
built-in-or-not distinction is useless.

In the first place, if you assume that someone may install malicious
functions in the server after beginning operation, distinction by OID
doesn't work at all because who can illegally install a malicious
function also be able to modify its OID with quite low odds.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center