Message-ID: <3DF7D077.9060904@klaster.net>
Date: Thu, 12 Dec 2002 00:55:35 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US;
	rv:1.2b) Gecko/20021016
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: multi-user and multi-level database access
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hi
I want to protect my database against unauthorised data destruction 
(Postgresql 7.2) I have some applications accesing database, each one 
does other things and there is a lot of users using these applications.

Here is my solution:
- each application has one postgresql group (create group...)
- i have a lot of grants/revokes for these groups
- there is a table named "users" which contains logins, user names and 
other useful information.
- after inserting a row to table "users", inside plpgsql function
I "create user <login>"
- for each application I "alter group application_group add user <login>"

My questions are:
- how to check if some postgresql user exists? I found them in table 
pg_shadow, but selecting this table is legal?
- how to change postgresql user login? Do I have to drop/create user, or 
I can update table pg_shadow?
- is it possible to revoke some privileges to all tables without listing 
them? I want to revoke all trigger/references/rule to all users without 
database owner.

And one additional question - not exactly to this list:
- did anyone compile libpq under MsWindows with ssl?

Regards,
Tomasz Myrta