Marc G. Fournier wrote:
On Tue, 8 Jan 2002, Tom Lane wrote:

Thomas Swan <tswan-lst@ics.olemiss.edu> writes:
This could be useful for cleaning out a database (sequences, triggers,
rules) without deleting it and without having to create it, thus
preserving permissions/owner information.
What permissions/owner information?  There won't be any left if we
remove everything in the database.

Also, given the possibility that the database has been created from
a nonempty template, it's less than clear exactly what should be
removed.

I'd say DROP and CREATE DATABASE is a perfectly presentable way of
handling this.

>From an ISP standpoint, this is something that I'd definitely love to see
... would save getting requests from users that don't have access to
anything but their database from having to ask me to DROP/CREATE their
database :)
Marc, I agree.   One of the disadvantages of using PostgreSQL in a multiuser multidatabase environment is the user and access controls that are currently available in PostgreSQL.

I have dreamed of the ability to go GRANT ALL ON DATABASE database_name TO USER username and then add a table and them be able to have permissions to access that table.   It would almost be worth having a pg_access system table that had the OID or database_name, uid/gid, and rights as an array that could be a gateway to the database. 

 pg_access
+----------+--------+--------------------------+
| database | userid | rights                   |
+----------+--------+--------------------------+
| foo      | 101    | select,update            |
| foo      | 102    | select,update,create,drop|
+----------+--------+--------------------------+
I'm not sure of the actual information to store, i.e. OID or name, but I think it would useful from a DBA standpoint and from an ISP standpoint as well.   Currently a GRANT ALL ON only does current objects, but will not include future objects (an added table for example).   I do think an  overall database level permission would be most advantageous.

The proposed drop command would be a step in making this more useable and hopefully increase the userbase and popularity of PostgreSQL.

Personally, I think the developer group has done an incredible and commendable job so far.   And, there's no other database that I would prefer to use.   However, the user and rights management is a bit awkward for me.   This is why I mentioned the above ideas.



------------