public inbox for [email protected]
help / color / mirror / Atom feedFrom: Shin Berg <[email protected]>
To: Amit Khandekar <[email protected]>
Cc: [email protected]
Subject: Re: Inconsistency in owner assignment between INDEX and STATISTICS
Date: Wed, 11 Mar 2026 18:54:01 +0900
Message-ID: <CACSdjfPor9NRN4WiNZ5HmpdFn7q6dERHuZvaMxHMirXUYDC6_A@mail.gmail.com> (raw)
In-Reply-To: <CAJ3gD9eJE_c=eSbBjqAAg+-x2XOSgZ5uYqyAiy4TrJJwxEXyng@mail.gmail.com>
References: <CACSdjfMWcR7aJUDf6XNt_yaDi+riz3Ku4EQ-x3xA=Xx1209n2g@mail.gmail.com>
<CAJ3gD9eJE_c=eSbBjqAAg+-x2XOSgZ5uYqyAiy4TrJJwxEXyng@mail.gmail.com>
Thank you for the detailed feedback, Amit.
You're right on both points. I had been comparing STATISTICS against INDEX
and treating the difference as an inconsistency, but as you point out,
INDEX ownership is special — it's tied to the table and intentionally not
user-adjustable. STATISTICS follows the same ownership model as VIEW (the
creator becomes the owner), which is consistent and by design.
I also verified locally that my reproduction script was flawed: the
"must be owner" error was caused by a schema search path issue, not an
ownership restriction. The script did not demonstrate what I claimed.
I'm withdrawing this proposal. Thanks again for taking the time to review
it.
On Tue, Mar 10, 2026 at 11:07 PM Amit Khandekar <[email protected]>
wrote:
> On Sat, 14 Feb 2026 at 14:18, Shin Berg <[email protected]> wrote:
> >
> > Hi,
> >
> > I'd like to raise a small consistency issue between how INDEX and
> extended STATISTICS handle object ownership, and ask whether aligning them
> would be desirable.
> >
> > Current behavior (tested on REL_17_STABLE):
> >
> > - When a superuser creates an INDEX on another user's table, the index
> is owned by the *table owner* (see catalog/index.c: index relation's
> relowner is set from the heap relation's relowner).
> > - When a superuser creates STATISTICS on another user's table, the
> statistics object is owned by the *current user* (statscmds.c: stxowner =
> GetUserId()).
>
> I will try to divide the problem into two questions:
>
> 1. Should the statistics object's owner be permanently associated with
> the table owner?
>
> From the docs, it does look like the current behaviour is intentional.
>
> https://www.postgresql.org/docs/current/sql-createstatistics.html :
> "You must be the owner of a table to create a statistics object
> reading it. Once created, however, the ownership of the statistics
> object is independent of the underlying table(s)."
>
> So I think we should not change the behaviour where the statistics
> object is created with independent ownership.
>
> With indexes, the behaviour has always been that it is associated with
> the table:
>
> postgres=# alter INDEX shared_schema.idx_bob owner to bob1;
> WARNING: cannot change owner of index "idx_bob"
> HINT: Change the ownership of the index's table instead.
>
> 2. Regardless of that, should the "create statistics" create the stat
> object with the same ownership as the table's, if it's the superuser
> who is creating the statistics?
>
> I think, since there is no permanent association of ownership between
> the table and the statistics, it makes sense for the user who is
> running the create command to own the statistics, regardless of who
> the user is, provided that the user has privileges.
>
> >
> > So in a scenario where a DBA creates both an index and extended
> statistics on a user's table, the table owner can DROP the index (because
> they own it) but cannot DROP the statistics object (they get "does not
> exist" when lacking ownership, which hides the real permission issue).
>
> The permission error should be emitted if the DROP is on the right
> schema. See below.
>
> > That can cause operational friction in multi-tenant or shared-schema
> setups (e.g. the table owner cannot drop the statistics to resolve
> dependency issues before altering the table).
>
> Maybe, make sure the table owner (and not the superuser) is creating
> the statistics?
>
> >
> > Reproduction (as superuser, then as table owner):
> >
> > CREATE SCHEMA shared_schema;
> > CREATE USER bob;
> > GRANT USAGE, CREATE ON SCHEMA shared_schema TO bob;
> >
> > SET ROLE bob;
> > CREATE TABLE shared_schema.bob_table (a int, b int);
> > RESET ROLE;
> >
> > CREATE INDEX idx_bob ON shared_schema.bob_table(a);
> > CREATE STATISTICS stat_bob ON a, b FROM shared_schema.bob_table;
> >
> > SELECT 'INDEX', c.relname, pg_get_userbyid(c.relowner) FROM pg_index i
> > JOIN pg_class c ON c.oid = i.indexrelid
> > WHERE indrelid = 'shared_schema.bob_table'::regclass
> > UNION ALL
> > SELECT 'STATISTICS', stxname, pg_get_userbyid(stxowner) FROM
> pg_statistic_ext
> > WHERE stxrelid = 'shared_schema.bob_table'::regclass;
> > -- INDEX owner = bob, STATISTICS owner = superuser
> >
> > SET ROLE bob;
> > DROP INDEX shared_schema.idx_bob; -- succeeds
> > DROP STATISTICS shared_schema.stat_bob; -- ERROR: statistics object
> "..." does not exist
>
> The statistics object is created in the default schema, not the table's
> schema.
> postgres=> DROP STATISTICS public.stat_bob;
> ERROR: must be owner of statistics object public.stat_bob
>
> Thanks
> -Amit Khandekar
>
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Inconsistency in owner assignment between INDEX and STATISTICS
In-Reply-To: <CACSdjfPor9NRN4WiNZ5HmpdFn7q6dERHuZvaMxHMirXUYDC6_A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox