Re: Include extension path on pg_available_extensions

On Thu Oct 23, 2025 at 10:56 AM -03, Euler Taveira wrote:
> On Wed, Oct 22, 2025, at 10:28 PM, Chao Li wrote:
>>> On 9/16/25 8:18 AM, Matheus Alcantara wrote:
>>>
>>>> Any opinions on this?
>>>> [1]
https://www.postgresql.org/message-id/CAKFQuwbR1Fzr8yRuMW%3DN1UMA1cTpFcqZe9bW_-ZF8%3DBa2Ud2%3Dw%40mail.gmail.com
>>> Just as the discussion here. Adding extension location is a good idea.
>>
>>
>> +1. I like the ideal.
>>
>
> Exposing useful information might be a good idea except if it doesn't
> compromise security. IIRC there is no function or view that exposes absolute
> path to regular users.
>
> The view pg_available_extensions has PUBLIC access. Check similar functions
> using a query like:
>
> SELECT proname,
>        x.unnest AS argname
> FROM
>   (SELECT proname,
>           unnest(proargnames)
>    FROM pg_proc) AS x
> WHERE x.unnest ~ 'file'
>   OR x.unnest ~ 'path';
>
> Some of the functions that return absolute path revoked PUBLIC access for
> security reason. See pg_show_all_file_settings, pg_hba_file_rules, and
> pg_ident_file_mappings. (All of these functions have a view that returns its
> content similar to pg_available_extensions.) See system_views.sql.
>
> Do we want to use a similar pattern (revoke PUBLIC access from the function)?
> It breaks the compatibility but perhaps using an existent pre-defined role
> (pg_read_all_settings?) may be less harmful.
>
> There are at least 2 alternatives:
>
> * separate function: add a new function that returns the absolute path. Don't
>   grant PUBLIC access. It doesn't break compatibility but you need to modify
>   your query.
>
> * insufficient privilege: if the role doesn't have the sufficient privileges,
>   return NULL or '<insufficient privilege>' (similar to pg_stat_activity). I
>   don't have a strong preference but the latter can impose more effort to use
>   if you don't know the role has sufficient privilege. However, it is clear why
>   the absolute path is not returned.
>
Yeah, I agree. I've implemented the first version in a way it doesn't
show the real value of the $system macro because of security reasons but
I agree that we don't want that any user can see the configured path of
custom extensions too. I would prefer to go with the '<insufficient privilege>'
since it's something that we already have today in other views and users
may already know how to deal with it.

--
Matheus Alcantara