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