* Roberts, Jon (Jon.Roberts@asurion.com) wrote:
> Major Feature 3: Users will build their own functions to manipulate their
> own data and share the output with their department. PostgreSQL security
> currently does not secure the functions they write so the feature is not
> fully met.
Alright, this isn't *completely* thought out, and I know it's not a
perfect solution, but to get something going in a released version of
PG...
Store the function definitions in a table, just per usual, and use PG
permissions and whatnot to handle keeping it safe. Write a simple
'helper' function which wouldn't have anything sensetive in it which
will take the function name as an argument. Then, in a transaction, it
could create the appropriate function, run it, and then drop it.
Probably not practical in an on-line/heavy-usage transactional system
due to the added overhead, but doing it for a data warehouse type
solution where the functions do alot of heavy lifting but aren't run
very often might be alright. Yes, it would also get ugly if you had
alot of functions building on other functions and whatnot. It also
wouldn't work too well if the person running the function isn't supposed
to be able to see it, but that doesn't sound like the case here.
While I've never really had a reason to secure my functions, I'm not
against providing a way to do it in PG. I'm not sure if what you've
outlined is the best approach or it might be better to come up with a
generic, system-table-wide, way to limit who can see what (for example,
prevent users from being able to see table definitions in schemas they
don't have usage rights on).
Enjoy,
Stephen
