Обсуждение: viewing source code

Tom Lane indicated this thread should be moved here.  Instead of asking for
what I consider the solution, let me propose a real business case and you
guys tell me how best to handle it.  

I am building an Enterprise Data Warehouse with PostgreSQL.  BTW, I love
this database.  I will have data from all over the company housed in it and
a variety of business users from all over the company as well.

Users Communities     Call Center     Finance     Sales     IT     Marketing     HRSecurity

Major Feature 1: These users will be using common dimensions and fact tables
that are unique to each department.  PostgreSQL security will handle this
just fine.

Major Feature 2: Users will be able to load their own data into the data
warehouse and secure this to their department.  PostgreSQL security will
handle this just fine.

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.

The user community is not highly technical and they are not looking for an
obfuscation solution.  They are looking for simple security to their code
they write in pl/pgsql that is similar if not the same as the security for
protecting their data.

How best can I achieve Major Feature 3?

Examples of what would be in the functions that need to be secured:
1.  HR could have pay and performance calculations that they don't want
shared with other departments.
2.  Finance could have earnings calculations they don't want to share with
marketing.
3.  Security could have functions to identify network abusers.
4.  Finance could have fraud and abuse calculations they don't want to share
with the call center.

Building a database per department isn't feasible.  It would require a vast
amount of data replication and duplication.  We want an Enterprise solution.

One solution already proposed is to create a view in place of the pg_proc
table.  The source code column would be protected in a similar fashion to
pg_stat_activity.current_query.  This seems like a great solution to me and
it would meet our Major Feature 3.


Jon

* 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

"Roberts, Jon" <Jon.Roberts@asurion.com> writes:

> 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.

Incidentally, are you really wedded to pl/pgsql functions?

It seems like it would be far more powerful to allow them to build up a
library of perl or python or whatever and put them in files in a blessed
directory. Then you could have a few standard functions which they could use
to call functions from those libraries.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!