postgres_fdw: restore_stats uses current user's mapping instead of table owner's during ANALYZE
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t139640psql -h localhost -U postgresBuilt from patchset v1 (message #1), August 25, 2026 at 12:37 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t139640_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t139640_1 && git checkout t139640_1Patchset v1 (message #1) is on t139640_1
Hi,
I found that ANALYZE on a foreign table with postgres_fdw uses different user
mappings depending on the restore_stats option. When restore_stats is disabled,
it uses the foreign table owner's user mapping to sample remote data. However,
when restore_stats is enabled, it instead uses the user mapping of the role
running ANALYZE to fetch remote statistics.
As a result, ANALYZE can fail with "user mapping not found" for users who are
allowed to analyze the foreign table but do not have their own user mapping,
even if the table owner has a valid one. Using different mappings depending on
restore_stats also seems confusing and inconsistent to me.
Was this behavior intentional?
The attached patch fixes this by making ANALYZE always use the foreign table
owner's user mapping, regardless of the restore_stats setting. It also adds
regression tests covering both restore_stats enabled and disabled cases.
Thoughts?
Regards,
--
Fujii Masao
Fujii-san,
On Thu, May 14, 2026 at 11:30 PM Fujii Masao <masao.fujii@gmail.com> wrote:
I found that ANALYZE on a foreign table with postgres_fdw uses different user
mappings depending on the restore_stats option. When restore_stats is disabled,
it uses the foreign table owner's user mapping to sample remote data. However,
when restore_stats is enabled, it instead uses the user mapping of the role
running ANALYZE to fetch remote statistics.
That's right.
As a result, ANALYZE can fail with "user mapping not found" for users who are
allowed to analyze the foreign table but do not have their own user mapping,
even if the table owner has a valid one. Using different mappings depending on
restore_stats also seems confusing and inconsistent to me.Was this behavior intentional?
That's intentional. The reason for that is: 1) unlike sampling, stats
import only scans pg_class and pg_stats on the remote server, so there
is no need to use the owner's user mapping, and 2) considering that
the remote user mapped to the owner has stronger privileges in most
cases, it's secure to avoid doing so without a valid reason.
Sorry, I don't think that consistency is a good enough reason for that.
Thanks for reviewing this feature!
Best regards,
Etsuro Fujita
On Fri, May 15, 2026 at 9:56 AM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Fujii-san,
On Thu, May 14, 2026 at 11:30 PM Fujii Masao <masao.fujii@gmail.com> wrote:
I found that ANALYZE on a foreign table with postgres_fdw uses different user
mappings depending on the restore_stats option. When restore_stats is disabled,
it uses the foreign table owner's user mapping to sample remote data. However,
when restore_stats is enabled, it instead uses the user mapping of the role
running ANALYZE to fetch remote statistics.That's right.
As a result, ANALYZE can fail with "user mapping not found" for users who are
allowed to analyze the foreign table but do not have their own user mapping,
even if the table owner has a valid one. Using different mappings depending on
restore_stats also seems confusing and inconsistent to me.Was this behavior intentional?
That's intentional. The reason for that is: 1) unlike sampling, stats
import only scans pg_class and pg_stats on the remote server, so there
is no need to use the owner's user mapping, and 2) considering that
the remote user mapped to the owner has stronger privileges in most
cases, it's secure to avoid doing so without a valid reason.Sorry, I don't think that consistency is a good enough reason for that.
I think this inconsistency is confusing for users, especially because
if ANALYZE with restore_stats enabled fails to fetch remote statistics,
it falls back to sampling remote data using the table owner's user mapping.
That means users enabling restore_stats may need to create two separate
user mappings: one for the table owner and another for the role executing
ANALYZE, just to handle the fallback case. This implicit switch in the user
mapping being used seems unnecessarily confusing to me.
Regards,
--
Fujii Masao
On Fri, May 15, 2026 at 1:55 PM Fujii Masao <masao.fujii@gmail.com> wrote:
On Fri, May 15, 2026 at 9:56 AM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
On Thu, May 14, 2026 at 11:30 PM Fujii Masao <masao.fujii@gmail.com> wrote:
I found that ANALYZE on a foreign table with postgres_fdw uses different user
mappings depending on the restore_stats option. When restore_stats is disabled,
it uses the foreign table owner's user mapping to sample remote data. However,
when restore_stats is enabled, it instead uses the user mapping of the role
running ANALYZE to fetch remote statistics.That's right.
As a result, ANALYZE can fail with "user mapping not found" for users who are
allowed to analyze the foreign table but do not have their own user mapping,
even if the table owner has a valid one. Using different mappings depending on
restore_stats also seems confusing and inconsistent to me.Was this behavior intentional?
That's intentional. The reason for that is: 1) unlike sampling, stats
import only scans pg_class and pg_stats on the remote server, so there
is no need to use the owner's user mapping, and 2) considering that
the remote user mapped to the owner has stronger privileges in most
cases, it's secure to avoid doing so without a valid reason.Sorry, I don't think that consistency is a good enough reason for that.
I think this inconsistency is confusing for users, especially because
if ANALYZE with restore_stats enabled fails to fetch remote statistics,
it falls back to sampling remote data using the table owner's user mapping.That means users enabling restore_stats may need to create two separate
user mappings: one for the table owner and another for the role executing
ANALYZE, just to handle the fallback case. This implicit switch in the user
mapping being used seems unnecessarily confusing to me.
I'm not sure users really care about using the two user mappings in
the fallback case. But even if it's true, the case would be rather
exceptional, so I don't think that that would be a big problem. Am I
wrong? Anyway, I still don't think it's reasonable to use the owner's
user mapping even when doing stats import, just for such an
exceptional case. (Note: future work on stats import is to prevent
fallback as much as possible, so the issue you are concerned about
will be further reduced in the future.)
Best regards,
Etsuro Fujita