X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 4F3F952A50
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat,  5 Mar 2005 03:33:33 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 98388-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat,  5 Mar 2005 03:33:24 +0000 (GMT)
Received: from mxsf32.cluster1.charter.net (mxsf32.cluster1.charter.net
	[209.225.28.156])
	by svr1.postgresql.org (Postfix) with ESMTP id 2532A52AA2
	for <pgsql-sql@postgresql.org>; Sat,  5 Mar 2005 03:33:19 +0000 (GMT)
Received: from mxip04.cluster1.charter.net (mxip04a.cluster1.charter.net
	[209.225.28.134])
	by mxsf32.cluster1.charter.net (8.12.11/8.12.11) with ESMTP id
	j253XMrF022538
	for <pgsql-sql@postgresql.org>; Fri, 4 Mar 2005 22:33:23 -0500
Received: from pc-68-118-180-13.will.ct.charter.com (HELO [192.168.116.102])
	(68.118.180.13)
	by mxip04.cluster1.charter.net with ESMTP; 04 Mar 2005 22:33:24 -0500
X-Ironport-AV: i="3.90,137,1107752400";
	d="scan'208"; a="17496182:sNHT1179955190"
Message-ID: <42292914.6000209@NarrowPathInc.com>
Date: Fri, 04 Mar 2005 22:35:48 -0500
From: Keith Worthington <KeithW@NarrowPathInc.com>
Reply-To: KeithW@NarrowPathInc.com
Organization: Narrow Path, Inc.
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Lynwood Stewart <lynwood@nwlink.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: truncating table permissions
References: <112ctegjgsa7a5f@corp.supernews.com>
In-Reply-To: <112ctegjgsa7a5f@corp.supernews.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.151 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200503/73
X-Sequence-Number: 20552

Lynwood Stewart wrote:

>I was expecting "truncate table <tablename>" to truncate a table if I had 
>delete permissions.  This does not appear to be the case.
>
>Would someone confirm this for me, or let me know what I am doing wrong.
>
>  
>
This is the case.  You are not doing anything wrong.  There was a 
discussion on this on the NOVICE list beginning on 2/22.  The subject 
was "Question on TRUNCATE privileges"

At the end of the day the answer is to have the table owner create a 
truncate function with SECURITY DEFINER privilege.

The following is from Tom Lane.

   See CREATE FUNCTION.  Something like (untested)

   create function truncate_my_table() returns void as
   $$ truncate my_table $$ language sql security definer;

   You'd probably then revoke the default public EXECUTE
   rights on this function, and grant EXECUTE only to
   selected users.

-- 
Kind Regards,
Keith