On Mon, Sep 23, 2024 at 8:57 AM jian he <jian.universal...@gmail.com> wrote:
>
> database owners do not necessarily have schema USAGE privilege.
> -------------<<<>>>------------------
> create role alice NOSUPERUSER LOGIN;
> create role bob NOSUPERUSER LOGIN;
> create database test;
> alter database test owner to alice;
> GRANT CONNECT, CREATE on database test to bob;
> \c test bob
> create schema one;
> create table one.t(a int);
> \c test alice
>
> analyze one.t;
>
> with cte as (
> select oid as the_t
> from pg_class
> where relname = any('{t}') and relnamespace = 'one'::regnamespace)
> SELECT
> pg_catalog.pg_set_relation_stats(
> relation => the_t,
> relpages => 17::integer,
> reltuples => 400.0::real,
> relallvisible => 4::integer)
> from cte;
>
>
> In the above case, alice cannot do "analyze one.t;",
> but can do pg_set_relation_stats, which seems not ok?sorry for the noise. what you stats_lock_check_privileges about privilege is right. database owner cannot do "ANALYZE one.t;" but it can do "ANALYZE;" to indirect analyzing one.t which seems to be the expected behavior per https://www.postgresql.org/docs/17/sql-analyze.html << To analyze a table, one must ordinarily have the MAINTAIN privilege on the table. However, database owners are allowed to analyze all tables in their databases, except shared catalogs. <<
