Date: Mon, 30 Nov 2009 08:50:27 -0600
Cc: pgsql-sql@postgresql.org
From: Michael Gould <mgould@intermodalsoftwaresolutions.net>
Subject: Schema's, roles and privileges
Message-ID: <803af4645647a59bcb56badac2b1cd8f@intermodalsoftwaresolutions.net>
In-Reply-To: <4B1389AE.2060305@postnewspapers.com.au>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="iso-8859-1"

I have a database with a schema called ISS. This is where all of our
application definitions are stored. We did add 2 contribute modules =
(citext)
and guid generator and both of these by default went to the public schema.
It is our intent to not allow any access to public by our users.

A few questions

1. Can I reinstall the contrib modules in the ISS schema only or do they
need to be in the public schema

2. If they need to stay in the public schema and I don't want to give any
insert, update, delete or select access to public, can I revoke those
privileges and just give execute on the functions that were added by the
contrib module.

3. If I can reinstall the contrib modules in the application schema, can I
delete the public schema or does it still need to be there and I would =
just
revoke all except for the superuser id which would be for our installer or
tech support if needed. We have a separate userid for the security
administrator. All of the functions that the security administrator needs
are provided by a application module and they will not be directly =
accessing
the database via a SQL utility at all.

Best Regards


--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax