Re: Schema's, roles and privileges

Thanks

Mike Gould

"Adrian Klaver" <aklaver(at)comcast(dot)net> wrote:
> 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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

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