Обсуждение: Autovacuum Issues?

I'm seeing the following warning repeatedly in my postgres log.  I'm
using PostgreSQL 8.4 on RHEL6.

Jan 29 04:49:57 myserver postgres[17308]: [3-1] user= db= host=
WARNING:  skipping "mytable" --- only table or database owner can
vacuum it

"mytable" is owned by a non-superuser role which cannot logon named "db_owner".
Permissions for "mytable" is as follows:
db_owner=arwdDxt/db_owner
app_user=arwd/db_owner

I did not perform a manual vacuum, so this must be autovacuum kicking in.

Does autovacuum automatically use the 'postgres' role?  If so, how can
I change what role autovacuum uses?  One of the security requirements
I've been required to implement removes superuser privileges from
postgres and assigns those privileges to a different role.  I didn't
see a config line for changing the role which performs autovacuum.

An alternative solution I've come up with is to assign ownership to
'postgres' and disable postgres from logon.  However, if I do that,
this is going to require a significant modification to the system
design, as well as modification to the software, and I'm going to have
some very unhappy developers.  So I'd like to avoid this route if
possible.

Thanks,

Ken

Kenneth Buckler <kenneth.buckler@gmail.com> writes:
> Does autovacuum automatically use the 'postgres' role?

It automatically uses the bootstrap superuser role.

> If so, how can I change what role autovacuum uses?

You can't.

> One of the security requirements
> I've been required to implement removes superuser privileges from
> postgres and assigns those privileges to a different role.

You can't mess around with the bootstrap superuser.  If you like, you
can cause it to be named something other than "postgres" --- just run
initdb as some other operating system user name.  (I think it would also
work to do ALTER USER RENAME after the fact, but haven't really
experimented with the consequences of that.)  But otherwise, this
"security requirement" seems pretty air-headed.  You have to have a
superuser.

            regards, tom lane

Well, that's good news and bad news.

Good news...the application developers' jobs just got a little easier.

Bad news...I get to document why we can't meet this security requirement.

And yes, I agree, it's a pretty air-headed requirement.  If I spent
less time chasing compliance, I might actually make the system more
secure.

Ken

On Mon, Jan 31, 2011 at 1:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Kenneth Buckler <kenneth.buckler@gmail.com> writes:
>> Does autovacuum automatically use the 'postgres' role?
>
> It automatically uses the bootstrap superuser role.
>
>> If so, how can I change what role autovacuum uses?
>
> You can't.
>
>> One of the security requirements
>> I've been required to implement removes superuser privileges from
>> postgres and assigns those privileges to a different role.
>
> You can't mess around with the bootstrap superuser.  If you like, you
> can cause it to be named something other than "postgres" --- just run
> initdb as some other operating system user name.  (I think it would also
> work to do ALTER USER RENAME after the fact, but haven't really
> experimented with the consequences of that.)  But otherwise, this
> "security requirement" seems pretty air-headed.  You have to have a
> superuser.
>
>                        regards, tom lane
>