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.


Tue, 08 Jan 2002 16:19:54 -0600
Thomas Swan <tswan-lst@ics.olemiss.edu>
Feature Request: DROP ALL FROM DATABASE database_name
Thomas Swan wrote:
> 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.

Why delete just those?  I don't see a compelling usefulness.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 8 Jan 2002 18:33:56 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Feature Request: DROP ALL FROM DATABASE database_name
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.

			regards, tom lane

Tue, 08 Jan 2002 18:50:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Feature Request: DROP ALL FROM DATABASE database_name 





Bruce Momjian wrote:

Thomas Swan wrote:

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.

Why delete just those?  I don't see a compelling usefulness.

You have to explicitly name all of them.   Also, some of those are unnamed
if they were auto created.  

Also, the SERIAL datatype creates an auto_named sequence that's not always
easy to figure out.    And, dropping a table leaves that sequence still intact.
    

On the owner permissions front.    If someone is developing and wants to
wipe their database and start fresh,  the easiest way is to do a drop database,
and create database.   But, that would require the createdatabase right.
   But, in a situation where multiple developers are working on different
databases, this is awkward.     The proposed request always a normal user
to clean that database and start fresh, so to speak.




Tue, 08 Jan 2002 17:57:34 -0600
Thomas Swan <tswan-lst@ics.olemiss.edu>
Re: Feature Request: DROP ALL FROM DATABASE database_name
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 :)



Wed, 9 Jan 2002 01:11:11 -0400 (AST)
"Marc G. Fournier" <scrappy@hub.org>
Re: Feature Request: DROP ALL FROM DATABASE database_name





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, thuspreserving permissions/owner information.

What permissions/owner information?  There won't be any left if weremove everything in the database.Also, given the possibility that the database has been created froma nonempty template, it's less than clear exactly what should beremoved.I'd say DROP and CREATE DATABASE is a perfectly presentable way ofhandling 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 toanything but their database from having to ask me to DROP/CREATE theirdatabase :)

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.



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



Wed, 09 Jan 2002 00:06:02 -0600
Thomas Swan <tswan-lst@ics.olemiss.edu>
Re: Feature Request: DROP ALL FROM DATABASE database_name