Thread: Truncate Permission

Hi,

I was looking to start development on the following TODO entry.

Add a separate TRUNCATE permission

Currently only the owner can TRUNCATE a table because triggers are not called, and the table is locked in exclusive
mode.


Does anyone have any objections? Looks like there is a change freeze on 
at the moment, so I assume my patch will not get reviewed until 8.4 
starts development?

Now some functional questions...

1. I assume you want something like the following?

grant truncate on [table] bla to user;
revoke truncate on [table] bla from user;

Are there any other statements that need to be included?

2. When executing a truncate command, the owner permission check is 
replaced by a truncate privilege check. Would you prefer both privileges 
to be checked?

3. Can I reuse the old ACL_RULE bit position and display character?

src/include/nodes/parsenodes.h - 1<<4
src/include/utils/acl.h - character 'R'

Will this break dump/restores? I would have preferred to use 't' or 'T' 
but these are both used.

4. Should the truncate permission be contained within the all 
privileges? If a user does

grant all [privileges] on [table] bla to user;
revoke all [privileges] on [table] bla from user;

If everyone is too busy at the moment with the 8.3 release, let me know 
and I will resubmit this stuff in a couple of months when things have 
calmed down.

Ta

Nick

Nick Barr <nicky@chuckie.co.uk> writes:
> I was looking to start development on the following TODO entry.
> Add a separate TRUNCATE permission

Is there actually a use-case for that?  It seems like mostly pointless
complication to me.  (Note that in the role world, one can effectively
have a table owned by a group, so the real issue here is only whether
there is a point in letting someone truncate a table without having any
other owner-grade permissions for it.)
        regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Nick Barr <nicky@chuckie.co.uk> writes:
> > I was looking to start development on the following TODO entry.
> > Add a separate TRUNCATE permission
>
> Is there actually a use-case for that?  It seems like mostly pointless
> complication to me.  (Note that in the role world, one can effectively
> have a table owned by a group, so the real issue here is only whether
> there is a point in letting someone truncate a table without having any
> other owner-grade permissions for it.)

Yes, there is a use-case for it.  If you don't have triggers or
transactional concerns on the table and you want users to be able to
truncate tables while not allowing them to do things like change the
table structure.  I proposed a patch a while ago to implement a seperate
permission for truncate but it was turned down because of concern over
using the few remaining bits in the ACL structure.

My counter-proposal is that the ACLs be split up into two integers
instead of having just one- one for the 'use' bits and a seperate one
for the 'GRANTABLE' bits.  This would double the space available for
permission bits and we could support truncate, vacuum, analyze all as
seperate grantable permissions rather than forcing them to be lumped in
with the ownership/ALTER TABLE permissions.

Unfortunately, I havn't had time to actually implement this.  I started
on it but all the macros are pretty hard-wired towards the current
implementation and their API isn't suited towards splitting the
GRANTABLE parts out (iirc anyway, it's been a while).

If we could agree on this approach perhaps we could add it as a TODO
with a dependency on it before the seperate TRUNCATE permission is
implemented.

In fact, I brought this up previously in this thread that came up last
year:
http://archives.postgresql.org/pgsql-hackers/2006-04/msg00964.php

Looking back farther, my patch originally added seperate permissions for
TRUNCATE, VACUUM and ANALYZE here:

http://archives.postgresql.org/pgsql-patches/2006-01/msg00028.php

And the concern was about adding too many extra seperate permissions and
about using up too many of the remaining privilege bits.  There was an
alternative suggestion to have one bit for
'truncate/vacuum/analyze/whatever-else-we-want' which might be alright
in some cases but I don't like it because it's implied that it could
change over time as we add things and that doesn't really sit very well
with me.  Also, it sounds an awful lot like 'almost owner' and I'm not
exactly sure what we'd call it anyway.

With auto-vacuum I'm a bit less concerned about having seperately
grantable 'analyze' and 'vacuum' permissions (though I'd still like) so
perhaps we can agree to use one bit up for a specific 'TRUNCATE'
permission?  Doesn't matter to me who implements it, but my patch might
be a decent place to start looking at the areas which have to be
touched.  I'd also be happy to update/change it for current HEAD and to
just implement the 'TRUNCATE' permission.

I dunno, given that it's on the TODO and that we've had a patch (at
least in part) for it for over a year, could it get into 8.4? ;)
Thanks,
    Stephen

I second this proposal
My problem is that some users don't have access to change the structure but they wanted to delete all data from the table
they try truncate - does not work because not the owner
so they make a delete from a really big table

So I would like to see a truncate permission - makes some things easier in my opinion
--
Ewald Geschwinde
http://www.postgresql.at

On Mon, Jun 11, 2007 at 09:40:08AM +0200, Ewald Geschwinde wrote:
> My problem is that some users don't have access to change the structure but
> they wanted to delete all data from the table
> they try truncate - does not work because not the owner
> so they make a delete from a really big table

Wouldn't it be far more logical to decide that if a user has the
permissions to do a DELETE FROM table; then they have permission to do
a TRUNCATE? Why make an additional permission?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout wrote:
> On Mon, Jun 11, 2007 at 09:40:08AM +0200, Ewald Geschwinde wrote:
>> My problem is that some users don't have access to change the structure but
>> they wanted to delete all data from the table
>> they try truncate - does not work because not the owner
>> so they make a delete from a really big table
> 
> Wouldn't it be far more logical to decide that if a user has the
> permissions to do a DELETE FROM table; then they have permission to do
> a TRUNCATE? Why make an additional permission?

Truncate doesn't fire ON DELETE triggers.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com

> > Wouldn't it be far more logical to decide that if a user has the
> > permissions to do a DELETE FROM table; then they have permission to
do
> > a TRUNCATE? Why make an additional permission?
>
> Truncate doesn't fire ON DELETE triggers.

Yes, but it would imho be ok if there are'nt any on delete triggers on
the table.

Andreas

* Zeugswetter Andreas ADI SD (ZeugswetterA@spardat.at) wrote:
>
> > > Wouldn't it be far more logical to decide that if a user has the
> > > permissions to do a DELETE FROM table; then they have permission to
> do
> > > a TRUNCATE? Why make an additional permission?
> >
> > Truncate doesn't fire ON DELETE triggers.
>
> Yes, but it would imho be ok if there are'nt any on delete triggers on
> the table.

Nope, it doesn't follow MVCC rules properly either.  It really needs to
be a seperate permission.
Thanks,
    Stephen

> * Zeugswetter Andreas ADI SD (ZeugswetterA@spardat.at) wrote:
>>
>> > > Wouldn't it be far more logical to decide that if a user has the
>> > > permissions to do a DELETE FROM table; then they have permission to
>> do
>> > > a TRUNCATE? Why make an additional permission?
>> >
>> > Truncate doesn't fire ON DELETE triggers.
>>
>> Yes, but it would imho be ok if there are'nt any on delete triggers on
>> the table.
>
> Nope, it doesn't follow MVCC rules properly either.  It really needs to
> be a seperate permission.
>
>     Thanks,
>
>         Stephen

Hi,

Thanks for all the replies. I was primarily looking for some development
to do in my spare time, and have since produced a patch for this. I assume
this patch will be put on hold, which is fine.

Would the core developers accept a patch that extended the ACL types to
support more possible permissions?

At the moment it seems as if a single 32 bit integer is used for the
permissions, with the top half being the grantable rights. I assume I
would need to extend this into two 32 bit integers, or one 64 bit integer?

Would it be worth making this two 64 bit integers whilst we are at it, or
is that just silly? I agree that making a permission for every possible
command would be overkill and somewhat time consuming, so I assume that
two 64 bit integers would also be overkill.


Nick

"Nicholas Barr" <nicky@chuckie.co.uk> writes:
> At the moment it seems as if a single 32 bit integer is used for the
> permissions, with the top half being the grantable rights. I assume I
> would need to extend this into two 32 bit integers, or one 64 bit integer?

Two 32-bit please.  We are still trying not to depend on 64-bit
arithmetic for any core functionality.
        regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> "Nicholas Barr" <nicky@chuckie.co.uk> writes:
> > At the moment it seems as if a single 32 bit integer is used for the
> > permissions, with the top half being the grantable rights. I assume I
> > would need to extend this into two 32 bit integers, or one 64 bit integer?
>
> Two 32-bit please.  We are still trying not to depend on 64-bit
> arithmetic for any core functionality.

Agreed.  Also, most of the time you'll only be pulling in the first one
(for a permissions check).  The second would only ever be used when a
'GRANT' is done.
Thanks,
    Stephen