From: Adrian Klaver <aklaver@comcast.net>
To: pgsql-sql@postgresql.org
Subject: Re: Schema's, roles and privileges
Date: Mon, 30 Nov 2009 07:08:12 -0800
User-Agent: KMail/1.9.10
Cc: Michael Gould <mgould@intermodalsoftwaresolutions.net>
References: <803af4645647a59bcb56badac2b1cd8f@intermodalsoftwaresolutions.net>
In-Reply-To: 
 <803af4645647a59bcb56badac2b1cd8f@intermodalsoftwaresolutions.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200911300708.12397.aklaver@comcast.net>

On Monday 30 November 2009 6:50:27 am Michael Gould wrote:
> 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 publ=
ic
> 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 ju=
st
> 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

=46rom a quick look it would seem the easiest solution would be to change t=
he=20
search_path in:
citext.sql.in=20
uuid-ossp.sql.in
These files are found in the respective contrib directories. Uninstall the=
=20
modules. Rerun make and then reinstall.

=46rom here:
http://www.postgresql.org/docs/8.4/interactive/ddl-schemas.html
"There is nothing special about the public schema except that it exists by=
=20
default. It can be dropped, too. "

=2D-=20
Adrian Klaver
aklaver@comcast.net