Re: Identifying user-created objects

On Thu, 5 Mar 2020 at 18:39, Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> wrote:
>
> 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.

So how do you check if unnecessary, malicious or unauthorized function
exists in database after that, for example when periodical security
check? Functions defined after initdb must be checked under user's
responsibility but since normally there are many built-in functions in
pg_proc the check in pg_proc could be cumbersome. So the idea of this
feature is to make that check easier by marking built-in functions.

>
> 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.

Yes, that's what Fujii-san also pointed out. It's better to find a way
to distinct functions while not relying on OID.

Regards,

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