Feedback-ID: i0c21471d:Fastmail
MIME-Version: 1.0
Date: Thu, 23 Oct 2025 10:56:02 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: "Chao Li" <li.evan.chao@gmail.com>,
 "Matheus Alcantara" <matheusssilv97@gmail.com>,
 "Quan Zongliang" <quanzongliang@yeah.net>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Message-Id: <5aaee744-342c-4ec8-a2ef-ffa0969cc51c@app.fastmail.com>
In-Reply-To: <0DDF33A5-4F8C-4725-A124-B301147FA1FA@gmail.com>
References: <DCTSKQAVOXIG.3A05JM1YORARK@gmail.com>
 <0DDF33A5-4F8C-4725-A124-B301147FA1FA@gmail.com>
Subject: Re: Include extension path on pg_available_extensions
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/5aaee744-342c-4ec8-a2ef-ffa0969cc51c%40app.fastmail.com>
Precedence: bulk

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.


-- 
Euler Taveira
EDB   https://www.enterprisedb.com/