Re: Grant issues

On Thu, Dec 12, 2002 at 15:51:52 -0800,
  Steve Crawford <scrawford@pinpointresearch.com> wrote:
> I have an automated application that creates and updates tables in a database
> and I have a "readonly" group containing all the users who should be allowed
> to query the data. I am now a bit stuck. Can I:
>
> 1. Grant to users in the readonly group permission to do unrestricted queries
> on the database (I know I can give this permission on a table but the
> application creates tables as needed and I just want to give blanket
> read-only rights to the users who need to query the data)?

When tables are created only the owner has access. You need to do something
to give people in the group select access. Options include a script that runs
periodicly that examines table permissions and changes them, modifying the
application to add grant commands when creating tables, and modifying
postgresql to create tables with different access rights.

> 2. Grant permission to create/alter views and temporary tables but not to
> create nor alter functions, triggers, rules, indexes, etc. in the database.

In 7.3 you can control being able to create temporary tables and objects in
schemas separately. However if you can create views, then you can create
other objects as well. You can restrict creating functions by removing
the usage rights for all languages.

> I'm setting up this database on 7.3. Will schemas be of any use here (ie. can
> I set up read-only rights on the schema that the application uses but set up
> users with their own schemas where they can do whatever they want)?

Before 7.3 there was no way to restrict who could create tables in a database
(for people allowed to access the database). In 7.3 restricting creation of
objects is done by restricting who can create schemas and who is allowed to
create objects in existing schemas.