Re: Identifying user-created objects

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.

Regards,

-- 
Fujii Masao
NTT DATA CORPORATION
Advanced Platform Technology Group
Research and Development Headquarters