Re: viewing source code

> -----Original Message-----
> From: Trevor Talbot [mailto:quension(at)gmail(dot)com]
> Sent: Wednesday, December 19, 2007 9:45 AM
> To: Joshua D. Drake
> Cc: Roberts, Jon; Kris Jurka; Merlin Moncure; Jonah H. Harris; Bill Moran;
> pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] viewing source code
>
> On 12/18/07, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
>
> > On Tue, 18 Dec 2007 10:05:46 -0600
> > "Roberts, Jon" <Jon(dot)Roberts(at)asurion(dot)com> wrote:
>
> > > If we are talking about enhancement requests, I would propose we
> > > create a role that can be granted/revoked that enables a user to see
> > > dictionary objects like source code. Secondly, users should be able
> > > to see their own code they write but not others unless they have been
> > > granted this dictionary role.
>
> > You are likely not going to get any support on an obfuscation front.
> > This is an Open Source project :P
>
> Wait, what? This is a DBMS, with some existing security controls
> regarding the data users are able to access, and the proposal is about
> increasing the granularity of that control. Arbitrary function bodies
> are just as much data as anything else in the system.
>
> Obfuscation would be something like encrypting the function bodies so
> that even the owner or administrator cannot view or modify the code
> without significant reverse engineering. I mean, some people do want
> that sort of thing, but this proposal isn't even close.

Trevor, thank you for making the proposal clearer.

The more I thought about a counter proposal to put views on pg_proc, I
realized that isn't feasible either. It would break functionality of
pgAdmin because users couldn't view their source code with the tool.

>
> Where on earth did "obfuscation" come from?

Don't know. :)

This really is a needed feature to make PostgreSQL more attractive to
businesses. A more robust security model that better follows commercial
products is needed for adoption.

Jon

Roberts, Jon escribió:

> The more I thought about a counter proposal to put views on pg_proc, I
> realized that isn't feasible either. It would break functionality of
> pgAdmin because users couldn't view their source code with the tool.

What's wrong with patching pgAdmin?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Roberts, Jon wrote:
>
>
> This really is a needed feature to make PostgreSQL more attractive to
> businesses. A more robust security model that better follows commercial
> products is needed for adoption.
>

I would argue that commercial products need to get a clue and stop
playing bondage with their users to help stop their imminent and frankly
obvious downfall from the Open Source competition.

This "feature" as it is called can be developed externally and has zero
reason to exist within PostgreSQL. If the feature has the level of
demand that people think that it does, then the external project will be
very successful and that's cool.

Sincerely,

Joshua D. Drake

On 12/20/07, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> Roberts, Jon wrote:

> > This really is a needed feature to make PostgreSQL more attractive to
> > businesses. A more robust security model that better follows commercial
> > products is needed for adoption.

> I would argue that commercial products need to get a clue and stop
> playing bondage with their users to help stop their imminent and frankly
> obvious downfall from the Open Source competition.

I'm still not seeing where your comments are actually coming from, and
I can't decipher your argument as a result. Exactly what is it about
fine-grained security controls that is "playing bondage with their
users"?

> This "feature" as it is called can be developed externally and has zero
> reason to exist within PostgreSQL. If the feature has the level of
> demand that people think that it does, then the external project will be
> very successful and that's cool.

I'm unsure of what you consider "external" here. Is SE-PostgreSQL the
type of thing you mean?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 20 Dec 2007 10:47:53 -0800
"Trevor Talbot" <quension(at)gmail(dot)com> wrote:

>
> > This "feature" as it is called can be developed externally and has
> > zero reason to exist within PostgreSQL. If the feature has the
> > level of demand that people think that it does, then the external
> > project will be very successful and that's cool.
>
> I'm unsure of what you consider "external" here. Is SE-PostgreSQL the
> type of thing you mean?

I don't know that it needs to be that extensive. I noted elsewhere in
the thread the idea of a plpgsql_s. I think that is an interesting
idea. I just don't think it needs to be incorporated into
postgresql-core.

If we were to remove viewing source from postgresql-core an interesting
possibility would be to remove prosrc from pg_proc altogether. Instead
prosrc becomes a lookup field to the prosrc table.

The prosrc table would only be accessible from a called function (thus
you can't grab source via select). Of course this wouldn't apply to
superusers but any normal user would not be able to so much as look
sideways at the prosrc table.

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHasUbATb/zqfZUUQRAqINAJsFpvPkUJ6oL/gH7dX4YLsbldIC4gCfdujh
/S2b/ZmQU+R54MlO5ATelns=
=+2Ut
-----END PGP SIGNATURE-----

Joshua D. Drake escribió:

> I don't know that it needs to be that extensive. I noted elsewhere in
> the thread the idea of a plpgsql_s. I think that is an interesting
> idea. I just don't think it needs to be incorporated into
> postgresql-core.

I don't think that makes any kind of sense. Hiding prosrc should happen
on a entirely different level from the language on which the function is
written. It's a completely orthogonal decision. Besides, you probably
don't want prosrc to be encrypted -- just not accesible to everyone, and
it doesn't make sense to have a different _language_ to do that.

Also, having an encrypted source code means there must be a decryption
key somewhere, which is a pain on itself. And if you expose the crypted
prosrc, you are exposing to brute force attacks (to which you are not if
prosrc is hidden).

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Dec 20, 2007 3:07 PM, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> I don't think that makes any kind of sense. Hiding prosrc should happen
> on a entirely different level from the language on which the function is
> written. It's a completely orthogonal decision. Besides, you probably
> don't want prosrc to be encrypted -- just not accesible to everyone, and
> it doesn't make sense to have a different _language_ to do that.

I kinda agree, kinda disagree on this point. You may recall the huge
debate a while back where AndrewSN and others were promoting a revised
set of views to expose the system catalogs. I thought this was a good
idea because the internal catalogs could be hidden from all but the su
and the views could be much easier to manipulate in that fashion. The
proposal was however shot down for other reasons.

I don't really agree that wrapping pl/pgsql with encryptor/decryptor
is a bad idea. It's fairly easy to do and very flexible (you don't
have to stop at encryption...for example you could run the code
through a pre-processor for token substitution). We are not adding a
language in the semantic sense, wrapping an existing one. Could
probably be extended to multiple languages if desired without too much
effort...I think it's only worthwhile bringing in core if you want to
hide the internals inside the syntax (CREATE ENCRYPTED FUNCTION
foo...)

Key management is an issue but easily solved. Uber simple solution is
to create a designated table holding the key(s) and use classic
permissions to guard it. So I don't agree with your negative comments
in this direction but I'm not saying this is the only way to solve
this. It is, however the only realistic way to do it without changes
to the project or breaking pgadmin.

> Also, having an encrypted source code means there must be a decryption
> key somewhere, which is a pain on itself. And if you expose the crypted
> prosrc, you are exposing to brute force attacks (to which you are not if
> prosrc is hidden).

i don't buy the brute force argument at all...aes256 or blowfish are
perfectly safe. The purpose of encryption is to move sensitive data
through public channels...otherwise, why encrypt?

merlin

On Thu, Dec 20, 2007 at 03:35:42PM -0500, Merlin Moncure wrote:
>
> Key management is an issue but easily solved. Uber simple solution is
> to create a designated table holding the key(s) and use classic
> permissions to guard it.

Any security expert worth the title would point and laugh at that
suggestion. If the idea is that the contents have to be encrypted to
protect them, then it is just not acceptable to have the encryption keys
online. That's the sort of "security" that inevitably causes programs to
get a reputation for ill-thought-out protections.

A

On Dec 20, 2007 3:52 PM, Andrew Sullivan <ajs(at)crankycanuck(dot)ca> wrote:
> On Thu, Dec 20, 2007 at 03:35:42PM -0500, Merlin Moncure wrote:
> >
> > Key management is an issue but easily solved. Uber simple solution is
> > to create a designated table holding the key(s) and use classic
> > permissions to guard it.
>
> Any security expert worth the title would point and laugh at that
> suggestion. If the idea is that the contents have to be encrypted to
> protect them, then it is just not acceptable to have the encryption keys
> online. That's the sort of "security" that inevitably causes programs to
> get a reputation for ill-thought-out protections.

right, right, thanks for the lecture. I am aware of various issues
with key management.

I said 'simple' not 'good'. there are many stronger things, like
forcing the key to be passed in for each invocation, hmac, etc. etc.
I am not making a proposal here and you don't have to denigrate my
broad suggestion on a technical detail which is quite distracting from
the real issue at hand, btw. I was just suggesting something easy to
stop casual browsing. If you want to talk specifics, we can talk
specifics...

merlin

Merlin Moncure escribió:

> I don't really agree that wrapping pl/pgsql with encryptor/decryptor
> is a bad idea.

Right. But do you agree that it is separate from having hidden prosrc?
If we can complete a design then let's shot that way, and aim at
encryption sometime in the future :-)

I have to note that I would probably not be the one to actually produce
a patch in this direction, or even to work on a working, detailed design
:-) You just read Joshua's opinion on this issue and I don't think I
need to say more :-)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Thu, Dec 20, 2007 at 05:04:33PM -0500, Merlin Moncure wrote:
> right, right, thanks for the lecture. I am aware of various issues
> with key management.

Sorry to come off that way. It wasn't my intention to lecture, but rather
to try to stop dead a cure that, in my opinion, is rather worse than the
disease.

> I said 'simple' not 'good'.

I think this is where we disagree. It's simple only because it's no
security at all. It's not that it's "not good for some purposes". I'm
arguing that it's the sort of approach that shouldn't be used ever, period.

We have learned, over and over again, that simple answers that might have
been good enough for a very narrow purpose inevitably get used for a
slightly wider case than that for which they're appropriate. Anything that
involves storing the keys in the same repository as the encrypted data is
just begging to be misused that way.

> I am not making a proposal here and you don't have to denigrate my
> broad suggestion on a technical detail which is quite distracting from
> the real issue at hand, btw.

This isn't a technical detail that I'm talking about: it's a very serious
mistake in the entire approach to which you alluded, and goes to the heart
of why I think any talk of somehow encrypting or otherwise obfuscating the
contents of pg_proc are a bad idea. Column controls based on user roles are
another matter, because they'd be part of the access control system in the
DBMS.

Best,

A

On Dec 20, 2007 5:28 PM, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> > I don't really agree that wrapping pl/pgsql with encryptor/decryptor
> > is a bad idea.
>
> Right. But do you agree that it is separate from having hidden prosrc?
> If we can complete a design then let's shot that way, and aim at
> encryption sometime in the future :-)
>
> I have to note that I would probably not be the one to actually produce
> a patch in this direction, or even to work on a working, detailed design
> :-) You just read Joshua's opinion on this issue and I don't think I
> need to say more :-)

it is separate. doing it hiding prosrc way requires, as i see it a)
row/col security, or b) view switcheroo
row/col security is great but views (IMO) are a better approach to
this generally. archives is of course replete with numerous generally
fruitless treatments of both topics.

view switcheroo is more of a 'do the ends justify the means' debate.
this could turn into a big discussion about what else could be done
with the system catalogs.

since its not really all that difficult to disable access to pg_proc,
and there are relatively few side effects outside of hosing pgadmin, i
don't think the ends do justify the means at least in terms of
internal server changes. If the necessary features get added in for
other reasons, then perhaps...

wrapping language handlers is interesting from other angles too. many
times I've wanted to do preprocessing on functions without sacrificing
ability of pasting from psql.

merlin

"Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> I don't really agree that wrapping pl/pgsql with encryptor/decryptor
> is a bad idea.

It's quite a good idea, because it has more than zero chance of
succeeding politically in the community.

The fundamental reason why preventing access to pg_proc.prosrc won't
happen is this: all the pain (and there will be plenty) will be
inflicted on people who get none of the benefit (because they don't give
a damn about hiding their own functions' code). The folks who want
function hiding can shout all they want, but as long as there is a very
sizable fraction of the community who flat out *don't* want it, it's
not going to get applied.

Encrypted function bodies avoid this problem because they inflict no
performance penalty, operational complexity, or client-code breakage
on people who don't use the feature. They are arguably also a better
solution because they can guard against more sorts of threats than
a column-hiding solution can.

I don't deny that the key-management problem is interesting, but it
seems soluble; moreover, the difficulties that people have pointed to
are nothing but an attempt to move the goalposts, because they
correspond to requirements that a column-hiding solution would never
meet at all.

So if you want something other than endless arguments to happen,
come up with a nice key-management design for encrypted function
bodies.

regards, tom lane

>
> wrapping pl/pgsql with encryptor/decryptor
>
> It's quite a good idea, because it has more than zero chance of
> succeeding politically in the community.
>

It's additionally a good idea because the other big database is using the
same approach. Easier sell to phb.

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

Is this a TODO?

---------------------------------------------------------------------------

Tom Lane wrote:
> "Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> > I don't really agree that wrapping pl/pgsql with encryptor/decryptor
> > is a bad idea.
>
> It's quite a good idea, because it has more than zero chance of
> succeeding politically in the community.
>
> The fundamental reason why preventing access to pg_proc.prosrc won't
> happen is this: all the pain (and there will be plenty) will be
> inflicted on people who get none of the benefit (because they don't give
> a damn about hiding their own functions' code). The folks who want
> function hiding can shout all they want, but as long as there is a very
> sizable fraction of the community who flat out *don't* want it, it's
> not going to get applied.
>
> Encrypted function bodies avoid this problem because they inflict no
> performance penalty, operational complexity, or client-code breakage
> on people who don't use the feature. They are arguably also a better
> solution because they can guard against more sorts of threats than
> a column-hiding solution can.
>
> I don't deny that the key-management problem is interesting, but it
> seems soluble; moreover, the difficulties that people have pointed to
> are nothing but an attempt to move the goalposts, because they
> correspond to requirements that a column-hiding solution would never
> meet at all.
>
> So if you want something other than endless arguments to happen,
> come up with a nice key-management design for encrypted function
> bodies.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Dec 21, 2007 9:34 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> Is this a TODO?
>

I don't think so, at least not yet (it's not clear what if anything
there is to do).

see: http://archives.postgresql.org/pgsql-hackers/2007-12/msg00788.php

merlin

Bruce Momjian wrote:
> Is this a TODO?
>
> ---------------------------------------------------------------------------
>
> Tom Lane wrote:
>> "Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
>>> I don't really agree that wrapping pl/pgsql with encryptor/decryptor
>>> is a bad idea.
>> It's quite a good idea, because it has more than zero chance of
>> succeeding politically in the community.
>>
>> The fundamental reason why preventing access to pg_proc.prosrc won't
>> happen is this: all the pain (and there will be plenty) will be
>> inflicted on people who get none of the benefit (because they don't give
>> a damn about hiding their own functions' code). The folks who want
>> function hiding can shout all they want, but as long as there is a very
>> sizable fraction of the community who flat out *don't* want it, it's
>> not going to get applied.
>>
>> Encrypted function bodies avoid this problem because they inflict no
>> performance penalty, operational complexity, or client-code breakage
>> on people who don't use the feature. They are arguably also a better
>> solution because they can guard against more sorts of threats than
>> a column-hiding solution can.
>>
>> I don't deny that the key-management problem is interesting, but it
>> seems soluble; moreover, the difficulties that people have pointed to
>> are nothing but an attempt to move the goalposts, because they
>> correspond to requirements that a column-hiding solution would never
>> meet at all.
>>
>> So if you want something other than endless arguments to happen,
>> come up with a nice key-management design for encrypted function
>> bodies.

I keep thinking the problem of keys is similar that of Apache servers
which use certificates that require passphrases. When the server is
started, the passphrase is entered on the command line.

--
Dan Langille - http://www.langille.org/

It seems like a lot of people only saw "hide source code" in the
original message, and then went off on tangents that don't have
anything to do with the request.

Again:

On 12/14/07, Roberts, Jon <Jon(dot)Roberts(at)asurion(dot)com> wrote:
> Is it possible yet in PostgreSQL to hide the source code of functions from
> users based on role membership? I would like to avoid converting the code
> to C to secure the source code and I don't want it obfuscated either.
>
> In an ideal world, if a user can't modify a function, he/she shouldn't be
> able to see the source code. If the user can execute the function, then the
> user should be able to see the signature of the function but not the body.

As a Role under PostgreSQL, I can create tables, views, functions,
etc. As the owner of those objects, I can control what other roles can
view data through them, and what roles can modify them.

However, unlike tables, I cannot control what roles can view the data
contained within my PL functions (body).

That's it. A very simple problem. One that has absolutely nothing
whatsoever to do with encrypted storage on disk or hiding things from
DBAs or superusers.

I'm surprised this group ended up so far off point. It's not as if
objecting to this requires a bunch of abstract hyperbole, just a
simple "it's not worth the effort and it's considered a bad idea to
put security-senstive data inside PL function bodies".

On 12/20/07, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> On Thu, 20 Dec 2007 10:47:53 -0800
> "Trevor Talbot" <quension(at)gmail(dot)com> wrote:

> > > This "feature" as it is called can be developed externally and has
> > > zero reason to exist within PostgreSQL. If the feature has the
> > > level of demand that people think that it does, then the external
> > > project will be very successful and that's cool.

> > I'm unsure of what you consider "external" here. Is SE-PostgreSQL the
> > type of thing you mean?

> I don't know that it needs to be that extensive. I noted elsewhere in
> the thread the idea of a plpgsql_s. I think that is an interesting
> idea. I just don't think it needs to be incorporated into
> postgresql-core.

I was trying to get a handle on whether you meant external as in
middleware, or external as in third-party patches to PostgreSQL. The
OP's request doesn't necessarily need something as extensive as
SE-PostgreSQL, but it needs to be on the same level: something that
affects the database surface clients see, not apps behind middleware.

On 12/20/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:

> > I don't really agree that wrapping pl/pgsql with encryptor/decryptor
> > is a bad idea.

> It's quite a good idea, because it has more than zero chance of
> succeeding politically in the community.

Something that looks a lot like encryption of the entire database is
more likely to succeed politically than a simple addition to
PostgreSQL's existing role-based security model? Really?

It's not like I can claim otherwise, I'm just wondering if I woke up
in an alternate universe this morning...

> The fundamental reason why preventing access to pg_proc.prosrc won't
> happen is this: all the pain (and there will be plenty) will be
> inflicted on people who get none of the benefit (because they don't give
> a damn about hiding their own functions' code). The folks who want
> function hiding can shout all they want, but as long as there is a very
> sizable fraction of the community who flat out *don't* want it, it's
> not going to get applied.

I don't understand. Can you give an example of pain you see coming?

I wrote:

> That's it. A very simple problem.

It was hinted to me off-list that my mail was fanning the flames, so
to clarify: when I say things like the above, I mean conceptually.

I think there might be a shared pool of knowledge that says it's
anything but simple in practical terms, but that hasn't been
communicated clearly in this thread. That's what I was getting at.

"Trevor Talbot" <quension(at)gmail(dot)com> writes:
> Something that looks a lot like encryption of the entire database is
> more likely to succeed politically than a simple addition to
> PostgreSQL's existing role-based security model? Really?

I guess that you have failed to understand any of the discussion.

Adding a GRANT SEESOURCECODE ON FUNCTION type of privilege would
perhaps be a "simple addition to PostgreSQL's existing security model",
but it would accomplish precisely zero, because anyone smart enough
to be using Postgres in the first place would simply look directly into
pg_proc to see the function body. In order to make it into a meaningful
restriction, we would have to restrict direct SQL access to the system
catalogs --- at least that one --- which would break vast quantities of
stuff. The fact that psql, pg_dump, and pgAdmin would all break is
daunting in itself, but those are likely just the tip of the iceberg.
Looking at the system catalogs has always been part of the culture
around here, and it's impossible to guess how many one-off client
programs do it. I'd bet on "a lot", though.

Another problem is that you're facing a cultural bias. You quote

> On 12/14/07, Roberts, Jon <Jon(dot)Roberts(at)asurion(dot)com> wrote:
>> In an ideal world, if a user can't modify a function, he/she shouldn't be
>> able to see the source code.

but what neither of you apparently grasp is that to most open source
programmers, that's not an "ideal world", that's a pretty good
description of hell on earth. There is no way that you will persuade
this project that hiding source code should be the default behavior,
or even especially easy.

We're willing to think about ways to hide source code where there is a
really serious commercial imperative to do it --- but in cases like
that, schemes that are as easily broken into as a SQL-level GRANT are
probably not good enough anyhow. And thus we arrive at encrypted source
text and discussions of where to keep the key.

Once again: this discussion is 100% off-topic for pgsql-performance.
If you want to keep talking about it, please join the child thread on
pgsql-hackers.

regards, tom lane