Re: Schema's, roles and privileges

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 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

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

From 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
default. It can be dropped, too. "

--
Adrian Klaver
aklaver(at)comcast(dot)net