REASSIGN OWNED doesn't for all objects, ALTER FUNCTION seems to fix it

L.S.

# select version();
version
---------------------------------------------------------------------------------------
PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.4.4, 64-bit
(1 row)

In a production database we experienced the following:

* the database contains a number of tables and functions owned by user A

* one is logged in as postgres

* upon issuing 'REASSIGN OWNED BY A TO postgres', all tables and _most_
functions changed ownership, but not all.... a number of functions stay marked
as owned by A, nothing weird in the logs, the reassign looked like it
completed successfully

* there doesn't seem to be anything 'special' about the functions that did not
change ownership

* changing ownership for one of these functions by using 'alter function'
succeeds without any problem

Mind you, we tested the reassign in a trial database first, a freshly restored
dump, and there the reassign worked properly / completely.

Any hints as to what could cause this? We left the production db as-is, so we
still have a number of these functions 'still owned by A' for which we could
check things in pg_* tables or something.

--

Best,

Frank.

Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl> writes:
> * upon issuing 'REASSIGN OWNED BY A TO postgres', all tables and _most_
> functions changed ownership, but not all.... a number of functions stay marked
> as owned by A, nothing weird in the logs, the reassign looked like it
> completed successfully

Hmmm .... look into pg_shdepend to see if there are entries linking
those functions to an owner. For instance, after

regression=# create user joe;
CREATE ROLE
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> create function foo() returns int as 'select 1' language sql;
CREATE FUNCTION

I get

regression=> select * from pg_shdepend;
dbid | classid | objid | objsubid | refclassid | refobjid | deptype
--------+---------+--------+----------+------------+----------+---------
...
123822 | 1255 | 148691 | 0 | 1260 | 148690 | o
...

1255 = pg_proc, 1260 = pg_authid, and the other numbers are the OIDs of
the database, function, and role (user) respectively. This data is what
REASSIGN OWNED works off of, and I suppose that some rows must be wrong
or missing in your pg_shdepend.

regards, tom lane

Hi Tom,

Op maandag 18 april 2011, schreef Tom Lane:
> Hmmm .... look into pg_shdepend to see if there are entries linking
> those functions to an owner.

mmm, indeed it seems that some things are our of sync here

the following is coming from the production database, thus after the 'reassign
from A to postgres' was run

****
1.
****

SELECT
n.nspname as "Schema",
p.proname as "Name",
pg_catalog.pg_get_userbyid(p.proowner) as "Owner"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND pg_catalog.pg_get_userbyid(p.proowner) != 'postgres';

This returns all 60 functions that were not reassigned, 'Owner' here still is
user 'A'.

****
2.
****

select
s.deptype,
p.proname,
pg_catalog.pg_get_userbyid(p.proowner) as proc_owner,
pg_catalog.pg_get_userbyid(s.refobjid) as sh_dep_owner
from
pg_shdepend s
full outer join pg_proc p on p.oid = s.objid
where
(
coalesce(
(select datname from pg_database where oid = s.dbid) = 'megafox'
and s.classid::regclass::text = 'pg_proc'
and pg_catalog.pg_get_userbyid(refobjid) != 'postgres', false)
or
coalesce(
pg_catalog.pg_get_userbyid(p.proowner) != 'postgres', false))
order by
s.deptype, p.proname;

This confirms that these 60 functions do not have a 'o' (owner) record in
pg_shdepend, it therefor matches what you seemed to expect: no records in
pg_shdepend, so "reassign owned" does not do anything.

Our obvious questions now are:

- how did we get into this

and

- how do we get out

How is it possible that a function had a pg_catalog.pg_proc.proowner other
than postgres while there are no corresponding records in pg_shdepend? Fyi,
the last major upgrade (for which a pg_restore was done) was in july 2009.

****
3.
****

The query above returns 10 other suspicious rows, suspicious to us at least.
These rows are about functions which according to pg_catalog.pg_proc.proowner
are owned by postgres (the last-but-one column), while in pg_shdepend they
still have an 'o' record with owner 'A' (the last column).

So again, pg_catalog.pg_proc.proowner and pg_shdepend are not in sync.

-----

For what its worth, nothing special was noticed about postgresql nor the
hardware. Postgresql for us has been and still is rock stable for almost ten
years now ;)

We did try some scenarios of changing ownership of things, but were not able
to generate a situation with pg_proc.proowner not in sync with pg_shdepend. I
guess this was to be expected, since a newly restored dump also does not show
the problem, it's only in the production database, which ofcourse has moved
through a number of minor upgrades without a restore.

--
Best,

Frank.

Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl> writes:
> mmm, indeed it seems that some things are our of sync here
> ...
> This confirms that these 60 functions do not have a 'o' (owner) record in
> pg_shdepend, it therefor matches what you seemed to expect: no records in
> pg_shdepend, so "reassign owned" does not do anything.

> Our obvious questions now are:
> - how did we get into this
> and
> - how do we get out

I wonder whether the pg_shdepend data is actually wrong, or just the
indexes on it are at fault. Did you try forcing that query to be done
with a seqscan, or see if reindexing pg_shdepend fixes things up?

The reason I'm wondering is that I've just found a failure mechanism
that could account for significant lossage of index entries for a system
catalog:
http://archives.postgresql.org/pgsql-hackers/2011-04/msg01070.php

To explain your problem that way would require assuming that somebody
was REINDEX'ing pg_shdepend at approximately the same time that somebody
else was rolling back DDL that had modified these same pg_shdepend
entries --- which in this case would probably mean a failed REASSIGN
OWNED for this same user ID. Have you got background tasks that try to
REINDEX everything in sight?

regards, tom lane

Hi,

Op woensdag 20 april 2011, schreef Tom Lane:
> I wonder whether the pg_shdepend data is actually wrong, or just the
> indexes on it are at fault. Did you try forcing that query to be done
> with a seqscan

Just did by setting enable_indexscan to false and verifying that all is used
are seq_scans by running explain first.

Both queries return the exact same result, so it seems the indexes are not the
problem in this case?

> or see if reindexing pg_shdepend fixes things up?

Didn't do that now, given the above result, but should you prefer it, just let
me know.

> The reason I'm wondering is that I've just found a failure mechanism
> that could account for significant lossage of index entries for a system
> catalog:
> http://archives.postgresql.org/pgsql-hackers/2011-04/msg01070.php
>
> To explain your problem that way would require assuming that somebody
> was REINDEX'ing pg_shdepend at approximately the same time that somebody
> else was rolling back DDL that had modified these same pg_shdepend
> entries --- which in this case would probably mean a failed REASSIGN
> OWNED for this same user ID. Have you got background tasks that try to
> REINDEX everything in sight?

Nope, nothing like that running in the background. We basically never reindex
manually. The only DDL related stuff that does get used a fair bit, is
creating / using / dropping temp table stuff. During the period since the last
major postgresql update, numerous functions have been updated on numerous
moments in time, but this is mainly done during maintenance windows. Recently
we started a cleanup to 'correct wrong ownership and/or permissions', which
basically was what made this show up.

--
Best,

Frank.

Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl> writes:
> Op woensdag 20 april 2011, schreef Tom Lane:
>> To explain your problem that way would require assuming that somebody
>> was REINDEX'ing pg_shdepend at approximately the same time that somebody
>> else was rolling back DDL that had modified these same pg_shdepend
>> entries --- which in this case would probably mean a failed REASSIGN
>> OWNED for this same user ID. Have you got background tasks that try to
>> REINDEX everything in sight?

> Nope, nothing like that running in the background.

Actually, now that I think about it, 8.4 didn't allow on-the-fly
reindexing of shared catalogs anyway. So that couldn't be your problem
even if the test had shown the indexes didn't match the catalog. But
it seems the rows actually disappeared from the catalog, and I have no
idea what would've caused that.

regards, tom lane

Hi,

Op woensdag 20 april 2011, schreef Tom Lane:
> Actually, now that I think about it, 8.4 didn't allow on-the-fly
> reindexing of shared catalogs anyway. So that couldn't be your problem
> even if the test had shown the indexes didn't match the catalog. But
> it seems the rows actually disappeared from the catalog, and I have no
> idea what would've caused that.

ok, clear

should we even try to get them back by generating them somehow?

we're planning to upgrade to v9 in a month or so....

also: would there be any reason you can think of why using alter function in
the current version in order to correct this situation would have a negative
side-effect?

--
Best,

Frank.

Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl> writes:
> Op woensdag 20 april 2011, schreef Tom Lane:
>> Actually, now that I think about it, 8.4 didn't allow on-the-fly
>> reindexing of shared catalogs anyway. So that couldn't be your problem
>> even if the test had shown the indexes didn't match the catalog. But
>> it seems the rows actually disappeared from the catalog, and I have no
>> idea what would've caused that.

> should we even try to get them back by generating them somehow?

It's not really important --- the only difficulty with not having them
is the one you already hit, that REASSIGN OWNED misses things it should
do. If you're planning an update or dump-and-reload shortly, I wouldn't
worry about it. What's much more worrisome is the question of whether
the same kind of data loss happened anywhere else, and that isn't
something that the available info tells me anything about.

> also: would there be any reason you can think of why using alter function in
> the current version in order to correct this situation would have a negative
> side-effect?

If you do want to go around and do retail ALTER OWNER commands, you
certainly could. I thought for a moment that changeDependencyOnOwner
would complain about the lack of a pre-existing pg_shdepend entry, but I
see it won't, so it should work fine.

regards, tom lane