Обсуждение: WARNING: owner of type appears to be invalid?
I recently installed postgresql 7.1.1, and now when I run pg_dumpall (which I do in a nightly script for backups), I get the following messages, duplicated for each database in the system: WARNING: owner of type 'pg_inherits' appears to be invalid WARNING: owner of type 'pg_index' appears to be invalid WARNING: owner of type 'pg_statistic' appears to be invalid WARNING: owner of type 'pg_operator' appears to be invalid WARNING: owner of type 'pg_opclass' appears to be invalid WARNING: owner of type 'pg_am' appears to be invalid WARNING: owner of type 'pg_amop' appears to be invalid WARNING: owner of type 'pg_amproc' appears to be invalid WARNING: owner of type 'pg_language' appears to be invalid WARNING: owner of type 'pg_largeobject' appears to be invalid WARNING: owner of type 'pg_aggregate' appears to be invalid WARNING: owner of type 'pg_ipl' appears to be invalid WARNING: owner of type 'pg_inheritproc' appears to be invalid WARNING: owner of type 'pg_rewrite' appears to be invalid WARNING: owner of type 'pg_listener' appears to be invalid WARNING: owner of type 'pg_description' appears to be invalid Does anyone know what's causing this, and how I can fix it? - Marc
"Marc Sherman" <msherman@projectile.ca> writes:
> I recently installed postgresql 7.1.1, and now when I run pg_dumpall
> (which I do in a nightly script for backups), I get the following
> messages, duplicated for each database in the system:
> WARNING: owner of type 'pg_inherits' appears to be invalid
> WARNING: owner of type 'pg_index' appears to be invalid
Sounds like you have no pg_shadow entry for the postgres user.
regards, tom lane
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > "Marc Sherman" <msherman@projectile.ca> writes: > > I recently installed postgresql 7.1.1, and now when I run pg_dumpall > > (which I do in a nightly script for backups), I get the following > > messages, duplicated for each database in the system: > > > WARNING: owner of type 'pg_inherits' appears to be invalid > > WARNING: owner of type 'pg_index' appears to be invalid > > Sounds like you have no pg_shadow entry for the postgres user. No, that's not it: template1=# select * from pg_shadow; usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd | passwd | valuntil ----------+----------+-------------+----------+----------+-----------+------ --+---------- postgres | 31 | t | t | t | t | | msherman | 1000 | t | f | f | f | | www-data | 33 | f | f | f | f | | (3 rows) This is strange, though -- the problem types have typowner set to 103 in pg_type, which is clearly invalid. 103 happens to be the Linux user ID (in passwd) for the postgres user. Strange. I suspect this may be a problem with the Debian install scripts; Debian is forcing postgres' user id to be 31 in the db, since it used to use a reserved user id for postgres in the os. Is it enough for me to log on to each of my databases as user postgres, and execute the query "update pg_type set typowner=31 where typowner=103;" to fix this problem? Are there any other hidden databases I should fix besides template1? Thanks for pointing me in the right direction, Tom. - Marc
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
> > Is it enough for me to log on to each of my databases as user
> > postgres, and execute the query "update pg_type set typowner=31
> > where typowner=103;" to fix this problem? Are there any other
> > hidden databases I should fix besides template1?
>
> Aside from pg_type, you probably have bogus values in the owner columns
> for pg_class, pg_function, pg_operator, etc etc. It'd be a lot easier
> to create another user with sysid = 103 ...
Yup, you were right. In fact, about half of the the system tables
are owned by 103 (the other half are owned by 31):
template1=> \dS
List of relations
Name | Type | Owner
----------------+---------+----------------
pg_aggregate | table | postgres-badid
pg_am | table | postgres-badid
pg_amop | table | postgres-badid
pg_amproc | table | postgres-badid
pg_attrdef | table | postgres
pg_attribute | table | postgres
pg_class | table | postgres
pg_database | table | postgres
pg_description | table | postgres-badid
pg_group | table | postgres
pg_index | table | postgres-badid
pg_indexes | view | postgres
pg_inheritproc | table | postgres-badid
pg_inherits | table | postgres-badid
pg_ipl | table | postgres-badid
pg_language | table | postgres-badid
pg_largeobject | table | postgres-badid
pg_listener | table | postgres-badid
pg_log | special | postgres
pg_opclass | table | postgres-badid
pg_operator | table | postgres-badid
pg_proc | table | postgres
pg_relcheck | table | postgres
pg_rewrite | table | postgres-badid
pg_rules | view | postgres
pg_shadow | table | postgres
pg_statistic | table | postgres-badid
pg_tables | view | postgres
pg_trigger | table | postgres
pg_type | table | postgres
pg_user | view | postgres
pg_variable | special | postgres
pg_views | view | postgres
pg_xactlock | special | postgres
(34 rows)
Thanks for the help, Tom.
- Marc
"Marc Sherman" <msherman@projectile.ca> writes:
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>> Sounds like you have no pg_shadow entry for the postgres user.
> No, that's not it:
> template1=# select * from pg_shadow;
> usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
> passwd | valuntil
> ----------+----------+-------------+----------+----------+-----------+------
> --+----------
> postgres | 31 | t | t | t | t |
> |
> msherman | 1000 | t | f | f | f |
> |
> www-data | 33 | f | f | f | f |
> |
> (3 rows)
> This is strange, though -- the problem types have typowner set
> to 103 in pg_type, which is clearly invalid. 103 happens to be
> the Linux user ID (in passwd) for the postgres user. Strange.
> I suspect this may be a problem with the Debian install scripts;
> Debian is forcing postgres' user id to be 31 in the db, since it
> used to use a reserved user id for postgres in the os.
Either that or a bug in initdb: it *should* substitute the same ID into
postgres' pg_shadow entry as it does into the owner columns of the
template1 system catalogs. Maybe it's getting confused. Oliver,
any thoughts?
> Is it enough for me to log on to each of my databases as user
> postgres, and execute the query "update pg_type set typowner=31
> where typowner=103;" to fix this problem? Are there any other
> hidden databases I should fix besides template1?
Aside from pg_type, you probably have bogus values in the owner columns
for pg_class, pg_function, pg_operator, etc etc. It'd be a lot easier
to create another user with sysid = 103 ...
regards, tom lane
Tom Lane wrote: >"Marc Sherman" <msherman@projectile.ca> writes: >> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] >>> Sounds like you have no pg_shadow entry for the postgres user. > >> No, that's not it: ... >> This is strange, though -- the problem types have typowner set >> to 103 in pg_type, which is clearly invalid. 103 happens to be >> the Linux user ID (in passwd) for the postgres user. Strange. > >> I suspect this may be a problem with the Debian install scripts; >> Debian is forcing postgres' user id to be 31 in the db, since it >> used to use a reserved user id for postgres in the os. Yes: this is a hangover from the old system which I can remove, since there is no longer any point in it. >Either that or a bug in initdb: it *should* substitute the same ID into >postgres' pg_shadow entry as it does into the owner columns of the >template1 system catalogs. Maybe it's getting confused. Oliver, >any thoughts? I can't see any bug in the initdb script. It looks straightforward. Did the user perhaps delete and recreate the postgres user at some stage? Then the hard-coded sysid in the installation would orphan user-created functions when the database was upgraded. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "But where shall wisdom be found? And where is the place of understanding? It cannot be gotten for gold, neither shall silver be weighed for the price thereof. Whence then cometh wisdom? and where is the place of understanding? ...Behold the fear of the Lord, that is wisdom; and to depart from evil is understanding." Job 12,15,20,28
From: Oliver Elphick [mailto:olly@lfix.co.uk] > > I can't see any bug in the initdb script. It looks straightforward. > > Did the user perhaps delete and recreate the postgres user at some stage? > Then the hard-coded sysid in the installation would orphan user-created > functions when the database was upgraded. No, that never happened. Also, there are no user-created functions (or types) in the database to begin with, just tables, indexes, and sequences. This is a brand new installation; I had 6.5 (potato's version) installed previously, but I did a pg_dumpall and purged the old install before installing 7.1.1-3 and importing the dumped data using psql. If you'd like to see any other queries on my template1 database, to see if you can figure out where the line is drawn between user ids 103 and 31, I'd be happy to oblige. One thing to note, I did have to manually execute the adduser call from the install script, before I could succesfully install 7.1.1-3, due to the shell=/bin/false bug I reported last week. Could that be part of the problem? Should we move this thread to BTS? - Marc
"Marc Sherman" <msherman@projectile.ca> writes:
> Yup, you were right. In fact, about half of the the system tables
> are owned by 103 (the other half are owned by 31):
Now that's *really* odd. I wonder how things got that way?
regards, tom lane