Thread: Feature Request: DROP ALL FROM DATABASE database_name
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.
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, Pennsylvania19026
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
Bruce Momjian wrote:<br /><blockquote cite="mid:200201082333.g08NXuo16483@candle.pha.pa.us" type="cite"><pre wrap="">ThomasSwan wrote:<br /></pre><blockquote type="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/ownerinformation.<br /></pre></blockquote><pre wrap=""><br />Why delete just those? I don't see a compellingusefulness.<br /><br /></pre></blockquote> You have to explicitly name all of them. Also, some of those are unnamedif they were auto created. <br /><br /> Also, the SERIAL datatype creates an auto_named sequence that's not alwayseasy to figure out. And, dropping a table leaves that sequence still intact. <br /><br /> On the owner permissionsfront. If someone is developing and wants to wipe their database and start fresh, the easiest way is to doa drop database, and create database. But, that would require the createdatabase right. But, in a situation wheremultiple developers are working on different databases, this is awkward. The proposed request always a normal userto clean that database and start fresh, so to speak.<br /><br />
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 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 />
>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. Funny you should mention this ... my roomate is sys admin at a colocation company that is in the process of swicthing over to using postgresql now. He was asking if there was a way to do exactly this sort of thing. He ended up going doing \dt pasting into another file and then doing GRANTs on all tables. He was mainly just looking for GRANT ALL ON DATABASE not thinking about future tables just a way to GRANT on all tables currently in database. It would be nice to extend object somehow to include all db objects. Best Regards, Carl Garland _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.