Re: why use SCHEMA? any real-world examples?

Miles Keaton wrote:
> Since the manual says HOW, could anyone here who has used schemas take
> a minute to describe to a newbie like me why you did? What benefits
> did they offer you? Any drawbacks?

Well - it's a namespace feature, so at its simplest it lets you have two
objects with the same name. It also lets you have permission control
over them, and provides a convenient way to group items together. For
example, I usually have a "util" schema where I keep utility
functions/views for dba use rather than general users.

For a longer example, you might have a database with two sets of users -
sales and accounts. They both need to print reports, but not the same
set of reports. So - you create a "reports" table with an access code of
S=sales, A=accounts, *=everyone.

You wrap this with a view "my_reports" where you supply your user-type
(S/A) and get a list of reports you can access. However, your
application needs to supply the user-type and if someone can inject the
right SQL into your connection, they can gain access to any report.

So - you create 3 schemas: core, sales, accounts. You put the "reports"
table into core and two views into "sales" and "accounts", both named
"my_reports" and rewritten appropriately. You deny access to "reports"
directly, and make sure your application sets its "search_path" to
contain the relevant sales/accounts schema. Then "SELECT * FROM
my_reports" will show only those reports your login allows.

HTH
--
Richard Huxton
Archonet Ltd