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.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Sat, Jan 30, 2010 at 7:59 AM, Ray Stell <stellr [at] cns.vt.edu> wrote:
> Running 8.2.15 on RHEL4 the log reports this from autovacuum along with
> other pg_ tables:
>
> VACUUM WARNING: =A0skipping "pg_database" --- only table or database owne=
r 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 ...)

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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 [at] 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


--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Sat, Jan 30, 2010 at 7:00 PM, Ray Stell <stellr [at] 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: =A0skipping "pg_database" --- only table or database o=
wner can vacuum it
>>
>> Huh, that's pretty weird ... autovacuum is supposed to connect as
>> superuser internally. =A0Did you do something funny to the system role
>> (typically called "postgres")?
>
>
> agreed. =A0If 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?

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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 [at] 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.


--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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.


--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin