Thread: 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 >