pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSchema's, roles and privileges
4+ messages / 3 participants
[nested] [flat]
* Schema's, roles and privileges
@ 2009-11-30 14:50 Michael Gould <mgould@intermodalsoftwaresolutions.net>
2009-11-30 15:08 ` Re: Schema's, roles and privileges Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Gould @ 2009-11-30 14:50 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Schema's, roles and privileges
2009-11-30 14:50 Schema's, roles and privileges Michael Gould <mgould@intermodalsoftwaresolutions.net>
@ 2009-11-30 15:08 ` Adrian Klaver <aklaver@comcast.net>
2009-11-30 15:25 ` Re: Schema's, roles and privileges Michael Gould <mgould@intermodalsoftwaresolutions.net>
2009-11-30 17:51 ` Re: Schema's, roles and privileges Harald Fuchs <hari.fuchs@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Adrian Klaver @ 2009-11-30 15:08 UTC (permalink / raw)
To: pgsql-sql; +Cc: Michael Gould <mgould@intermodalsoftwaresolutions.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 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@comcast.net
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Schema's, roles and privileges
2009-11-30 14:50 Schema's, roles and privileges Michael Gould <mgould@intermodalsoftwaresolutions.net>
2009-11-30 15:08 ` Re: Schema's, roles and privileges Adrian Klaver <aklaver@comcast.net>
@ 2009-11-30 15:25 ` Michael Gould <mgould@intermodalsoftwaresolutions.net>
1 sibling, 0 replies; 4+ messages in thread
From: Michael Gould @ 2009-11-30 15:25 UTC (permalink / raw)
To: Adrian Klaver <aklaver@comcast.net>; pgsql-sql
Thanks
Mike Gould
"Adrian Klaver" <aklaver@comcast.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@comcast.net
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Schema's, roles and privileges
2009-11-30 14:50 Schema's, roles and privileges Michael Gould <mgould@intermodalsoftwaresolutions.net>
2009-11-30 15:08 ` Re: Schema's, roles and privileges Adrian Klaver <aklaver@comcast.net>
@ 2009-11-30 17:51 ` Harald Fuchs <hari.fuchs@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Harald Fuchs @ 2009-11-30 17:51 UTC (permalink / raw)
To: pgsql-sql
In article <200911300708.12397.aklaver@comcast.net>,
Adrian Klaver <aklaver@comcast.net> writes:
> 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.
FWIW:
I also manage some databases needing one or more contrib/pgfoundry
modules. By habit, I install each module in its own schema (with read
access to everybody and write access to nobody) - this helps to
distinguish between your own DDL and the DDL of helper modules. For
example:
CREATE SCHEMA prefix;
SET search_path = mymodule;
\i path_to/mymodule.sql
ALTER DATABASE mydb SET search_path = public, mymodule;
The SQL files of a few modules mess with the search_path themselves;
this can be fixed by editing a copy of <module>.sql and \inserting the
copy.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2009-11-30 17:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-11-30 14:50 Schema's, roles and privileges Michael Gould <mgould@intermodalsoftwaresolutions.net>
2009-11-30 15:08 ` Adrian Klaver <aklaver@comcast.net>
2009-11-30 15:25 ` Michael Gould <mgould@intermodalsoftwaresolutions.net>
2009-11-30 17:51 ` Harald Fuchs <hari.fuchs@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox