Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id CFB0B633570 for ; Mon, 30 Nov 2009 11:26:05 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 83104-04 for ; Mon, 30 Nov 2009 15:25:45 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from serv01.siteground178.com (ns1.siteground178.com [67.15.255.19]) by mail.postgresql.org (Postfix) with ESMTP id 4351F6334AF for ; Mon, 30 Nov 2009 11:25:54 -0400 (AST) Received: from [67.15.255.19] (port=36608 helo=intermodalsoftwaresolutions.net) by serv01.siteground178.com with esmtpa (Exim 4.69) (envelope-from ) id 1NF88P-00039O-0r; Mon, 30 Nov 2009 09:25:53 -0600 Received: from phpmailer ([76.106.235.204]) by intermodalsoftwaresolutions.net with HTTP (PHPMailer); Mon, 30 Nov 2009 09:25:52 -0600 Date: Mon, 30 Nov 2009 09:25:52 -0600 To: Adrian Klaver , pgsql-sql@postgresql.org From: Michael Gould Subject: Re: Schema's, roles and privileges Message-ID: X-Priority: 3 X-Mailer: PHPMailer [version 1.71] X-Mailer: FeLaMiMail In-Reply-To: <200911300708.12397.aklaver@comcast.net> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="iso-8859-1" X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - serv01.siteground178.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - intermodalsoftwaresolutions.net X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.135 tagged_above=-10 required=5 tests=AWL=0.134, BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200911/82 X-Sequence-Number: 33433 Thanks Mike Gould "Adrian Klaver" 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 >=20 > From a quick look it would seem the easiest solution would be to change the=20 > search_path in: > citext.sql.in=20 > uuid-ossp.sql.in > These files are found in the respective contrib directories. Uninstall = the > modules. Rerun make and then reinstall. >=20 > 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. " >=20 > --=20 > Adrian Klaver > aklaver@comcast.net >=20 > --=20 > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >=20 -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax