Обсуждение: VACUUM WARNING: skipping "pg_statistic" --- only table or database owner can vacuum it

Running 8.2.15 on RHEL4 the log reports this from autovacuum along with
other pg_ tables:

VACUUM WARNING:  skipping "pg_database" --- only table or database owner can vacuum it

Any ideas how I can clean this up or how I got into this hole?
Vacuum works as superuser.

Thanks.

On Sat, Jan 30, 2010 at 7:59 AM, Ray Stell <stellr@cns.vt.edu> wrote:
> Running 8.2.15 on RHEL4 the log reports this from autovacuum along with
> other pg_ tables:
>
> VACUUM WARNING:  skipping "pg_database" --- only table or database owner can vacuum it
>
> Any ideas how I can clean this up or how I got into this hole?
> Vacuum works as superuser.

Make yourself the owner of the database? (i.e. alter database ...)

On Sat, Jan 30, 2010 at 10:28:37AM -0700, Scott Marlowe wrote:
> On Sat, Jan 30, 2010 at 7:59 AM, Ray Stell <stellr@cns.vt.edu> wrote:
> > Running 8.2.15 on RHEL4 the log reports this from autovacuum along with
> > other pg_ tables:
> Make yourself the owner of the database? (i.e. alter database ...)


autovacuum is throwing these.  If I vacuum as the current owner it seems to
work:

template1=# VACUUM VERBOSE ANALYZE pg_statistic;
INFO:  vacuuming "pg_catalog.pg_statistic"
INFO:  index "pg_statistic_relid_att_index" now contains 298 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_statistic": found 0 removable, 298 nonremovable row versions in 9 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 2 unused item pointers.
2 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_2619"
INFO:  index "pg_toast_2619_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_2619": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Ray Stell wrote:
> Running 8.2.15 on RHEL4 the log reports this from autovacuum along with
> other pg_ tables:
>
> VACUUM WARNING:  skipping "pg_database" --- only table or database owner can vacuum it

Huh, that's pretty weird ... autovacuum is supposed to connect as
superuser internally.  Did you do something funny to the system role
(typically called "postgres")?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Sat, Jan 30, 2010 at 10:35:27PM -0300, Alvaro Herrera wrote:
> Ray Stell wrote:
> > Running 8.2.15 on RHEL4 the log reports this from autovacuum along with
> > other pg_ tables:
> >
> > VACUUM WARNING:  skipping "pg_database" --- only table or database owner can vacuum it
>
> Huh, that's pretty weird ... autovacuum is supposed to connect as
> superuser internally.  Did you do something funny to the system role
> (typically called "postgres")?


agreed.  If I did, I don't know what it was. I suppose I'm reduced
to creating a new cluster and restoring the apps into it, unless
somebody can suggest an audit method that would uncover the injury.

On Sat, Jan 30, 2010 at 7:00 PM, Ray Stell <stellr@cns.vt.edu> wrote:
> On Sat, Jan 30, 2010 at 10:35:27PM -0300, Alvaro Herrera wrote:
>> Ray Stell wrote:
>> > Running 8.2.15 on RHEL4 the log reports this from autovacuum along with
>> > other pg_ tables:
>> >
>> > VACUUM WARNING:  skipping "pg_database" --- only table or database owner can vacuum it
>>
>> Huh, that's pretty weird ... autovacuum is supposed to connect as
>> superuser internally.  Did you do something funny to the system role
>> (typically called "postgres")?
>
>
> agreed.  If I did, I don't know what it was. I suppose I'm reduced
> to creating a new cluster and restoring the apps into it, unless
> somebody can suggest an audit method that would uncover the injury.

Whoa, don't burn down the village just yet.

What does \du postgres say?

On Sat, Jan 30, 2010 at 07:18:52PM -0700, Scott Marlowe wrote:
> On Sat, Jan 30, 2010 at 7:00 PM, Ray Stell <stellr@cns.vt.edu> wrote:
> > On Sat, Jan 30, 2010 at 10:35:27PM -0300, Alvaro Herrera wrote:
> >> Ray Stell wrote:
> >> > Running 8.2.15 on RHEL4 the log reports this from autovacuum along with
> >> > other pg_ tables:
> >> >
> >> > VACUUM WARNING: ?skipping "pg_database" --- only table or database owner can vacuum it
> >>
> >> Huh, that's pretty weird ... autovacuum is supposed to connect as
> >> superuser internally. ?Did you do something funny to the system role
> >> (typically called "postgres")?
> >
> >
> > agreed. ?If I did, I don't know what it was. I suppose I'm reduced
> > to creating a new cluster and restoring the apps into it, unless
> > somebody can suggest an audit method that would uncover the injury.
>
> Whoa, don't burn down the village just yet.

no worries, fairly small cluster, but if somebody knows
how to weed this out that would be a great help.


> What does \du postgres say?

I used "-U pgadmin" on my initdb, so I don't have postgres user:

template1=# \du
                               List of roles
 Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+-----------
 admin     | no        | no          | no        | no limit    |
 pgadmin   | yes       | yes         | yes       | no limit    |
(2 rows)

but you see she does own the farm:

template1=# \l
       List of databases
   Name    |  Owner  | Encoding
-----------+---------+----------
 fms       | pgadmin | UTF8
 postgres  | pgadmin | UTF8
 template0 | pgadmin | UTF8
 template1 | pgadmin | UTF8
(4 rows)

Thanks for your time.

On Sat, Jan 30, 2010 at 09:59:36AM -0500, Ray Stell wrote:
> Running 8.2.15 on RHEL4 the log reports this from autovacuum along with
> other pg_ tables:
>
> VACUUM WARNING:  skipping "pg_database" --- only table or database owner can vacuum it


More logging shows the user generating the msg is not superuser.  The user
is named "admin," as it is an application administrator and it chokes
on all the rels in information_schema and pg_catalog.

fms=# select * from pg_user where usename = 'admin';
 usename | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
---------+----------+-------------+----------+-----------+----------+----------+-----------
 admin   |    16385 | f           | f        | f         | ******** |          |
(1 row)

Superuser owns these rels:

fms=# select * from pg_tables where tablename = 'sql_sizing';
     schemaname     | tablename  | tableowner | tablespace | hasindexes | hasrules | hastriggers
--------------------+------------+------------+------------+------------+----------+-------------
 information_schema | sql_sizing | postgres   |            | f          | f        | f
(1 row)

This vacuum.c if clause that gets to the warning msg:

        if (!(pg_class_ownercheck(RelationGetRelid(onerel), GetUserId()) ||
                  (pg_database_ownercheck(MyDatabaseId, GetUserId()) && !onerel->rd_rel->relisshared)))

The following printf in the vacuum.c clause shows all is well, but
raises some questions:

  1  pg_class_ownercheck(RelationGetRelid(onerel), GetUserId())=0
  2  RelationGetRelid(onerel)=sql_sizing
  3  GetUserId()=16385
  4  pg_database_ownercheck(MyDatabaseId, GetUserId())=0
  5  MyDatabaseId=16384
  6  onerel->rd_rel->relisshared=0

Is autovacuum handing all users off to vacuum all rels?  Even those
it does not own?  Perhaps "admin" an unfortunate usename choice?

Thanks.

On Tue, Feb 09, 2010 at 05:39:52PM -0500, Ray Stell wrote:
> On Sat, Jan 30, 2010 at 09:59:36AM -0500, Ray Stell wrote:
>
> Is autovacuum handing all users off to vacuum all rels?  Even those
> it does not own?  Perhaps "admin" an unfortunate usename choice?


doh...the light comes on.

reading through postinit.c and others makes it clear autovacuum is not the source
of the vacuum.  No doubt the app, Cisco Fabric Manager Server, is doing something
stupid.  Yep:

/var/local/cisco_mds9000
# strings ./jboss/server/default/deploy/dcm.ear/dcm.jar/com/cisco/dcbu/sm/server/db/PostgresWrapper.class | grep -i
vacuum
 vacuum_analyze
vacuum analyze
 vacuum_analyze failed:

2.     Irrelephant
Anything that is unrelated to an elephant.
http://www.urbandictionary.com/define.php?term=irrelephant