Thread: Schema's versus tablespace

Hi list,

We have developed a system that is running as an ASP application, currently we
are using Postgresql 7.4. For every customer we have a separate schema, and
this works OK. Now in version 8, tablespaces were introduced. This summer we
are planning to migrate to version 8.1. Question is if there are advantages
to use tablespaces instead of schema's?

Keep up the good work

Cheers,
Dick Kniep

On Tue, Mar 28, 2006 at 09:41:46AM +0200, Dick Kniep wrote:
> Hi list,
>
> We have developed a system that is running as an ASP application, currently we
> are using Postgresql 7.4. For every customer we have a separate schema, and
> this works OK. Now in version 8, tablespaces were introduced. This summer we
> are planning to migrate to version 8.1. Question is if there are advantages
> to use tablespaces instead of schema's?

Schemas related to how your tables are logically divided up.
Tablespaces are about where the tables are actually stored on disk. As
such they have nothing to do with eachother. You can mix and match as
you please.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

OK, that's cleared, thanks,

Now another question related to this. In 7.4.8 I have a schema with the
following authorization:

CREATE SCHEMA adeuxproductie
  AUTHORIZATION postgres;
GRANT ALL ON SCHEMA adeuxproductie TO postgres;
GRANT ALL ON SCHEMA adeuxproductie TO GROUP adeuxproductie;
COMMENT ON SCHEMA adeuxproductie IS 'Standard production schema';

Now when I try to access the tables/views in the schema as a user that is NOT
a member of the group adeuxproductie, I expect it to fail. However, access is
granted as if there is no authorization specified.

Is this a bug, or do I misunderstand something?

Cheers,
Dick

On Tuesday 28 March 2006 09:48, Martijn van Oosterhout wrote:
> On Tue, Mar 28, 2006 at 09:41:46AM +0200, Dick Kniep wrote:
> > Hi list,
> >
> > We have developed a system that is running as an ASP application,
> > currently we are using Postgresql 7.4. For every customer we have a
> > separate schema, and this works OK. Now in version 8, tablespaces were
> > introduced. This summer we are planning to migrate to version 8.1.
> > Question is if there are advantages to use tablespaces instead of
> > schema's?
>
> Schemas related to how your tables are logically divided up.
> Tablespaces are about where the tables are actually stored on disk. As
> such they have nothing to do with eachother. You can mix and match as
> you please.
>
> Have a nice day,

Dick Kniep <dick@kniep.nl> writes:
> Now when I try to access the tables/views in the schema as a user that
> is NOT a member of the group adeuxproductie, I expect it to
> fail. However, access is granted as if there is no authorization
> specified.

Perhaps you tested this as a superuser?  For ordinary users it
definitely would fail.

            regards, tom lane