pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedowner of data type "areas" appears to be invalid ?
3+ messages / 2 participants
[nested] [flat]
* owner of data type "areas" appears to be invalid ?
@ 2005-10-14 15:53 george young <gry@ll.mit.edu>
2005-10-14 16:04 ` Re: owner of data type "areas" appears to be invalid ? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: george young @ 2005-10-14 15:53 UTC (permalink / raw)
To: pgsql-sql
[PostgreSQL 7.4RC2, x86 linux]
Several tables are giving the errors like:
pg_dump: WARNING: owner of data type "areas" appears to be invalid
from pg_dump.
This is my production database, (50 users, 18 hours/day, 21MB compressed dump).
The output of "pg_dump -t areas" starts with:
REVOKE ALL ON TABLE areas FROM PUBLIC;
REVOKE ALL ON TABLE areas FROM geoyou;
SET SESSION AUTHORIZATION "101";
GRANT ALL ON TABLE areas TO "101" WITH GRANT OPTION;
RESET SESSION AUTHORIZATION;
SET SESSION AUTHORIZATION "101";
GRANT ALL ON TABLE areas TO PUBLIC;
RESET SESSION AUTHORIZATION;
GRANT ALL ON TABLE areas TO PUBLIC;
But "101" is the numerical group id of the "operator" group! (see dump from pg_group below). The table owner is "geoyou" as seen from the pg_class select below. User "geoyou" is in pg_user with usesysid=501. There is no row in pg_user with usesysid=101, and there is none with usename "101".
How can I fix this? I must be able to get clean dumps that can be reloaded in case of a crash.
-- George Young
pig5=> select * from pg_class where relname='areas';
relname | relnamespace | reltype | relowner | relam | relfilenode | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl
---------+--------------+----------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+---------------------------------------
areas | 2200 | 14745246 | 501 | 0 | 14745245 | 1 | 41 | 14745247 | 0 | f | f | r | 2 | 0 | 0 | 0 | 0 | 0 | t | f | f | f | {101=a*r*w*d*R*x*t*/101,=arwdRxt/101}
pig5=> select * from pg_user where usesysid=501;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig
---------+----------+-------------+----------+-----------+----------+----------+-----------
geoyou | 501 | f | f | f | ******** | |
select * from pg_group where grosysid=101;
groname | grosysid | grolist
----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
operator | 101 | {602,616,509,525,614,514,617,539,517,558,581,567,506,609,94,511,573,17115,327,17422,512,537,626,503,519,583,547,570,584,78,10980,518,557,564,528,546,592,599,613,510,513,536,554,500,530,594,608,524,17114,533,17116,17289,17290,17292,17294,17345,17347,17421,17423,17425,214,17430,17427,17428,574,11,391,17431,17667,17703,8309,17769,17842,17773,17874,17877,13283,12758,17966,18888,17902,18099,18117,18129,18170,18173,18163,32766,18195,18202,18208,17786,17704,18375,18322,18399,18410,17904,18438,18424,28424,18437,102137,9877,502,32768,18553,13065,10681,8245,17049,15885,15886,8977,18706,18717}
select * from pg_tables where tablename='areas';
schemaname | tablename | tableowner | hasindexes | hasrules | hastriggers
------------+-----------+------------+------------+----------+-------------
public | areas | geoyou | f | f | f
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: owner of data type "areas" appears to be invalid ?
2005-10-14 15:53 owner of data type "areas" appears to be invalid ? george young <gry@ll.mit.edu>
@ 2005-10-14 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-14 16:19 ` Re: owner of data type "areas" appears to be invalid ? george young <gry@ll.mit.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2005-10-14 16:04 UTC (permalink / raw)
To: gry@ll.mit.edu; +Cc: pgsql-sql
george young <gry@ll.mit.edu> writes:
> How can I fix this?
Re-create the owning user (which you evidently dropped), assigning it
sysid 101.
PG 8.1 will make it impossible to drop users who still own objects or
have permissions ... although that will bring its own set of gotchas ...
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: owner of data type "areas" appears to be invalid ?
2005-10-14 15:53 owner of data type "areas" appears to be invalid ? george young <gry@ll.mit.edu>
2005-10-14 16:04 ` Re: owner of data type "areas" appears to be invalid ? Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-10-14 16:19 ` george young <gry@ll.mit.edu>
0 siblings, 0 replies; 3+ messages in thread
From: george young @ 2005-10-14 16:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Yes, that worked. Thank you very much!
-- George
On Fri, 14 Oct 2005 12:04:13 -0400
Tom Lane <tgl@sss.pgh.pa.us> threw this fish to the penguins:
> george young <gry@ll.mit.edu> writes:
> > How can I fix this?
>
> Re-create the owning user (which you evidently dropped), assigning it
> sysid 101.
>
> PG 8.1 will make it impossible to drop users who still own objects or
> have permissions ... although that will bring its own set of gotchas ...
>
> regards, tom lane
>
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-10-14 16:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-14 15:53 owner of data type "areas" appears to be invalid ? george young <gry@ll.mit.edu>
2005-10-14 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-14 16:19 ` george young <gry@ll.mit.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox