Thread: what is the meaning of "schema"?
My understanding of "schema" that I discovered in 7.3 (I don't think they were available before) is that you can have two tables with the same name if they are in different schemas. I have done a google search, as well as archive search but pg_dump and pg_dumpall are broken if a database contains schemas. First of all if there are two tables with the same name in different schemas pg_dump only dumps out one table. There is no way to dump other tables and I have checked pg_dump man page Restoring a pg_dumpall is now a nightmare because I had as superuser # create schema test authorization httpd on a database not owned by database owner. And it works merrily until the time to dump and restore. pg_dumpall answers to above create authorization is \connect - httpd create schema test Hell breaks lose with that! Because httpd cannot create schema on a database that it does not own. Why couldn't pg_dumpall does create schema test authorization httpd as superuser when the schema was created in that fashion? I really don't think anyone is going to pay attention to this rant since these list does not like/answer anonymous posts but I have to post just so some poor soul might find it in the archive and be warned. My current versions are 7.3.2 and 7.3.3 and I have been using posgres since 7.1 and consider myself experienced with postgres Schemas are the best thing since slice breads but the baker decided to poison the bread. Nice! Thanks __________________________________ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com
On Friday 20 Jun 2003 4:19 pm, _ wrote: > My understanding of "schema" that I discovered > in 7.3 (I don't think they were available before) > is that you can have two tables with the same name > if they are in different schemas. > > I have done a google search, as well as archive search > but > > pg_dump and pg_dumpall are broken if a database > contains schemas. > > First of all if there are two tables with the same > name in different schemas pg_dump only dumps out > one table. There is no way to dump other tables > and I have checked pg_dump man page Works here - v7.3.2: SET search_path = public, pg_catalog; [begin pg_dump extract] -- -- Data for TOC entry 25 (OID 2263656) -- Name: bar; Type: TABLE DATA; Schema: public; Owner: richardh -- COPY bar (m) FROM stdin; 1.00 2345.00 99999999.00 \. SET search_path = richardh, pg_catalog; -- -- Data for TOC entry 26 (OID 2275041) -- Name: bar; Type: TABLE DATA; Schema: richardh; Owner: richardh -- COPY bar (a, b) FROM stdin; 1 aaa \. [end pg_dump extract] Did the user you pg_dumped as have visibility on your second schema? > Restoring a pg_dumpall is now a nightmare because > > I had as superuser > > # create schema test authorization httpd > > on a database not owned by database owner. > And it works merrily until the time to > dump and restore. > > pg_dumpall answers to above create authorization is > > \connect - httpd > > create schema test > > Hell breaks lose with that! Because httpd cannot > create schema on a database that it does not own. > Why couldn't pg_dumpall does > > create schema test authorization httpd Did you GRANT CREATE ON DATABASE for user httpd? That looks like what you need. > as superuser when the schema was created in that > fashion? > > I really don't think anyone is going to pay attention > to this rant since these list does not like/answer anonymous posts > but I have to post just so some poor soul might find > it in the archive and be warned. Always thought of the lists as welcoming myself, although I must admit anonymous posting is a bit odd. Especially when you could call yourself John Smith and no-one would be any the wiser. > My current versions are 7.3.2 and 7.3.3 and I have been using > posgres since 7.1 and consider myself experienced with postgres > > Schemas are the best thing since slice breads but > the baker decided to poison the bread. Nice! Let me know if this reply helps -- Richard Huxton
On Saturday 21 Jun 2003 11:04 pm, _ wrote: > Hi > > Thanks for the reply especially since I have resigned > myself not to use schema anymore and > unsubscribed from the list. (I subscribed just to post) I've CCd this back to the hackers list, since others may have something to contribute here. > I think that when a schema is created as > > # create schema test authorization httpd > > pg_dump should do exactly that. > > Notice that it works perfectly since super user > is creating schema until it comes > to restoring the dump. I'm guessing (and that's all it is - I've not looked at the source) that PG doesn't know that the schema was created that way. So - basically I think we have two choices: 1. All schemas owned by "foo" should be built using: \connect - foo CREATE SCHEMA AUTHORIZATION foo; 2. All schemas owned by foo should use: \connect - postgres CREATE SCHEMA foo AUTHORIZATION foo; Both produce the same result, but the one requires superuser permissions. I think this certainly needs thinking about - it's only going to occur when you have a schema owned by neither the superuser or the database owner. > httpd does not have any specail privilege except > schema usage (either granted as "authorization" at > schema creation time by super user or > explicitly granted by postgres) and table level > permissions. I take it the explicit grant works OK? If so, that's the workaround I'd use for the moment. Must admit, I'd never considered having schemas owned by a user without other access to a database I don't suppose you've got the time to put together a small demo script for this - creates two users, creates a database for user1, creates schemas, one table then dumps the db? That would make for a quick test against 7.4 CVS - I don't think a fix would take long to produce then. -- Richard Huxton