Groups and schemas (was: You're on SecurityFocus.com...)

Tom Lane wrote:
>> Also this will be pretty tricky to get to work for groups. (That
>> pg_group table really needs a redesign.)
>
>True. I'm inclined to think that should be looked at in the context
>of the schema support that people have been muttering about --- maybe
>groups can be replaced by schemas somehow? (Just a thought, maybe a
>half-baked one.)

Please don't lose group support; it is a separate issue from schemas.

If schemas were available, I would use them to separate or subdivide
projects. For example, a financial accounting system would logically
divide into modules, some of which would be universally required
(general ledger, base support, etc.) and some of which would be
of interest to smaller groups of customers (sales ledger, order processing,
inventory control, payroll). Each of these modules would contain a
logically separable set of data elements (tables, views, etc.).
However, add-on modules are very likely to need to access data from
other modules. I would use schemas to implement these modules and
they could cross-refer to each other with the specification
`schema.table.column'.

It should be possible for separate schemas to be housed on different
machines, linked by networking.

Different sets of users need to access different parts of the data, in
different ways. This is not necessarily related to the separation of
modules. For example, the directors of a company might have private
loan accounts in the general ledger module, and their pay would be
handled by the payroll module; it is likely that access to their
records would be heavily restricted. This is more easily done by
group privileges, by giving restricted access to a "directors" group,
than by trying to split the database schema down further. Similarly,
where there is extensive separation of duties, one group may
have write access for payments but not for invoices.

Finally, use of groups makes it far easier to handle the privileges of
new employees. It is much easier to make a new employee a member of
the "buyers" group than to grant access rights individually to each of
a number of tables, spread across several schemas.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"Therefore, my beloved brethren, be ye stedfast,
unmoveable, always abounding in the work of the Lord,
forasmuch as ye know that your labour is not in vain
in the Lord." I Corinthians 15:58