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
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