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, Pennsylvania 19026
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
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
</head>
<body>
Bruce Momjian wrote:<br>
<blockquote type="cite" cite="mid:200201082333.g08NXuo16483@candle.pha.pa.us">
<pre wrap="">Thomas Swan 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/owner information.<br></pre>
</blockquote>
<pre wrap=""><!----><br>Why delete just those? I don't see a compelling usefulness.<br><br></pre>
</blockquote>
You have to explicitly name all of them. Also, some of those are unnamed
if they were auto created. <br>
<br>
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.
<br>
<br>
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.<br>
<br>
</body>
</html>
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 :)
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
</head>
<body>
Marc G. Fournier wrote:<br>
<blockquote type="cite" cite="mid:20020109010848.D21205-100000@earth.hub.org">
<pre wrap="">On Tue, 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>
<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/owner information.<br></pre>
</blockquote>
<pre wrap="">What permissions/owner information? There won't be any left if we<br>remove everything in the database.<br><br>Also, given the possibility that the database has been created from<br>a nonempty template, it's less than clear exactly what should be<br>removed.<br><br>I'd say DROP and CREATE DATABASE is a 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 have access 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 environment is the user and access controls that are currently
available in PostgreSQL.<br>
<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. 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 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. <br>
<br>
The proposed drop 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 for me. This
is why I mentioned the above ideas.<br>
<br>
<br>
<pre><br></pre>
------------<br>
</body>
</html>
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.
Import Notes
Resolved by subject fallback