Re: Feature Request: DROP ALL FROM DATABASE database_name - Mailing list pgsql-hackers

Marc G. Fournier wrote:<br /><blockquote cite="mid:20020109010848.D21205-100000@earth.hub.org" type="cite"><pre
wrap="">OnTue, 8 Jan 2002, Tom Lane wrote:<br /><br /></pre><blockquote type="cite"><pre wrap="">Thomas Swan <a
class="moz-txt-link-rfc2396E"href="mailto:tswan-lst@ics.olemiss.edu"><tswan-lst@ics.olemiss.edu></a> writes:<br
/></pre><blockquotetype="cite"><pre wrap="">This could be useful for cleaning out a database (sequences, triggers,<br
/>rules)without deleting it and without having to create it, thus<br />preserving permissions/owner information.<br
/></pre></blockquote><prewrap="">What permissions/owner information?  There won't be any left if we<br />remove
everythingin the database.<br /><br />Also, given the possibility that the database has been created from<br />a
nonemptytemplate, it's less than clear exactly what should be<br />removed.<br /><br />I'd say DROP and CREATE DATABASE
isa perfectly presentable way of<br />handling this.<br /></pre></blockquote><pre wrap=""><br />>From an ISP
standpoint,this is something that I'd definitely love to see<br />... would save getting requests from users that don't
haveaccess to<br />anything but their database from having to ask me to DROP/CREATE their<br />database
:)</pre></blockquote>Marc, I agree.   One of the disadvantages of using PostgreSQL in a multiuser multidatabase
environmentis the user and access controls that are currently available in PostgreSQL.<br /><br /> I have dreamed of
theability to go GRANT ALL ON DATABASE database_name TO USER username and then add a table and them be able to have
permissionsto 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. <br /><br /><pre> pg_access<br
/>+----------+--------+--------------------------+<br/>| database | userid | rights                   |<br
/>+----------+--------+--------------------------+<br/>| foo      | 101    | select,update            |<br />| foo
|102    | select,update,create,drop|<br />+----------+--------+--------------------------+<br /></pre> I'm not sure of
theactual information to store, i.e. OID or name, but I think it would useful from a DBA standpoint and from an ISP
standpointas well.   Currently a GRANT ALL ON only does current objects, but will not include future objects (an added
tablefor example).   I do think an  overall database level permission would be most advantageous. <br /><br /> The
proposeddrop command would be a step in making this more useable and hopefully increase the userbase and popularity of
PostgreSQL.<br/><br /> 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
forme.   This is why I mentioned the above ideas.<br /><br /><br /><pre><br /></pre> ------------<br />