Truncate Permission

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

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.

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/

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