Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > In general, I do prefer that permissions be seperably grantable. Being
> > able to grant 'truncate' permissions would be really nice. Is the only
> > reason such permission doesn't exist due to no one working on it, or is
> > there other disagreement about it?
>
> Lack of appetite for having forty nonstandard kinds of privilege,
> I suppose ;-)
>
> Given that we now have roles, it's fairly easy to grant "table owner"
> to trusted people, so the use-case for special privilege types has
> dropped off dramatically IMHO.

The problem is that you might want to grant 'truncate' to people who
*aren't* particularly trusted. For truncate, at least I have a
real-world use-case for it. I've got a number of users who are required
to fill in on the order of 300 seperate tables. We provide a number of
different ways of doing the uploads:
ODBC
phppgadmin
Our own phppgadmin-like interface
Web-based streaming CSV uploader

The CSV uploader is by far the most popular because it allows them to
easily reload a table from the files they have. The uploader starts off
with a 'delete from x' right now. I've been looking at (but don't
particularly like) writing a setuid pl/pgsql function so that I can make
the uploader be able to truncate the tables. This would almost entirely
eliminate the need to do vacuums on the tables. It's very rare for
there to be multiple things happening on a given database at a given
time too.

What does happen though, is that these tables are often used immediately
after they've been uploaded which means they needs to be analyzed too.
Again, something which would be very nice if the uploader could do.
Same with vacuums, in fact, if the uploader could vacuum the tables I
probably wouldn't need truncate as badly (though it'd still be nice),
since I could just delete from table; vacuum;

What these users *can't* do, by any means, is drop tables, or change the
structure or types or keys or anything else having to do with the table
definitions.

Writing setuid pl/pgsql functions for each of these is circumventing the
ACL and permission system of the database; working *around* it instead
of *with* it, which makes me somewhat nervous and feels like a lack in
the database. :/

Thanks!

Stephen