Re: [HACKERS] what is the meaning of schema?
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] what is the meaning of schema?
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 .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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
