Thread: Securing stored procedures and triggers

Il Wednesday 31 October 2007 15:39:55 mgould ha scritto:
> We are currently migrating from Sybase's ASA 9/10 to PostGres 8.2.4.  One
> of the features that is really nice in ASA is the ability to add the
> attribute hidden to a Create procedure, Create function and Create trigger.
>  Essentially what this does is encrypt the code so that if anyone or any
> utility gets into the database they cannot see any of the actual code.
> This is a great feature for protecting intellectual processing techniques.
> I don't know if there is anyway to do this in PostGres.  Before the hidden
> feature was added, we had a competitor  steal some of our stored procedure
> processing code.  Is there anyway to protect this from happening in
> PostGres?
>
> Best Regards,
>
> Michael Gould
> All Coast Intermodal Services, Inc.
> 904-376-7030

I'm not sure about that feature being supported by PGSQL.
Don't think so, anyway.

But if your competitor needs to steal your code in order to compete,
then you are ahead of him: you think, he copies.

--
Reg me Please

mgould wrote:
> We are currently migrating from Sybase's ASA 9/10 to PostGres
> 8.2.4.  One of the features that is really nice in ASA is the
> ability to add the attribute hidden to a Create procedure,
> Create function and Create trigger.  Essentially what this
> does is encrypt the code so that if anyone or any utility
> gets into the database they cannot see any of the actual
> code.  This is a great feature for protecting intellectual
> processing techniques.  I don't know if there is anyway to do
> this in PostGres.  Before the hidden feature was added, we
> had a competitor  steal some of our stored procedure
> processing code.  Is there anyway to protect this from
> happening in PostGres?

Not with PL/pgSQL.

That's probably not what you want, but you can write
stored procedures in C and ship the shared library.

Yours,
Laurenz Albe

mgould <mgould@allcoast.net> writes:

> We are currently migrating from Sybase's ASA 9/10 to PostGres 8.2.4.
> One of the features that is really nice in ASA is the ability to add
> the attribute hidden to a Create procedure, Create function and
> Create trigger.  Essentially what this does is encrypt the code so
> that if anyone or any utility gets into the database they cannot see
> any of the actual code.  This is a great feature for protecting
> intellectual processing techniques.  I don't know if there is anyway
> to do this in PostGres.  Before the hidden feature was added, we had
> a competitor steal some of our stored procedure processing code.  Is
> there anyway to protect this from happening in PostGres?

The only bulletproof way to do this currently is to write all your
stored functions in C and load them as a shared library.

-Doug

On 10/31/07, mgould <mgould@allcoast.net> wrote:
>
>
> We are currently migrating from Sybase's ASA 9/10 to PostGres 8.2.4.  One of
> the features that is really nice in ASA is the ability to add the attribute
> hidden to a Create procedure, Create function and Create trigger.
> Essentially what this does is encrypt the code so that if anyone or any
> utility gets into the database they cannot see any of the actual code.  This
> is a great feature for protecting intellectual processing techniques.  I
> don't know if there is anyway to do this in PostGres.  Before the hidden
> feature was added, we had a competitor  steal some of our stored procedure
> processing code.  Is there anyway to protect this from happening in
> PostGres?

I know to the untrained eye this looks like security, but honestly,
even the most junior of hackers could likely break into it and get
whatever code is supposedly being protected.

You are MUCH better off having your legal department work on this with
the proper paperwork.

You can, of course, write all your stored procs in C and compile them.
 Then they're also "encoded" in such a way that joe six pack can't
read them.  But again, it's not real protection.  If the database can
run it, it can be decompiled and examined, whether it's in sybase or
postgresql.

If you don't want your customers to see what's happening under the
hood, you have one, and exactly one, option.  Host the databases
locally and do not allow the users admin powers.  Once you start
handing out code, in whatever format, the genie is simply waiting for
someone to open the bottle.

On 10/31/07, Douglas McNaught <doug@mcnaught.org> wrote:

> The only bulletproof way to do this currently is to write all your
> stored functions in C and load them as a shared library.

Well, as I pointed out in my post, even that's not bullet-proof.  As
long as decompilers / debuggers etc... exist, then compiling is just a
small barrier to a good hacker.  The only way to win is not to play,
i.e. keep the code in house.

There's not bulletproof way, in my opinion.

If they copy the whole DB structure *and* the object binaries they'll
have the very same functionalities!

Il Wednesday 31 October 2007 16:13:23 Douglas McNaught ha scritto:
> mgould <mgould@allcoast.net> writes:
> > We are currently migrating from Sybase's ASA 9/10 to PostGres 8.2.4.
> > One of the features that is really nice in ASA is the ability to add
> > the attribute hidden to a Create procedure, Create function and
> > Create trigger.  Essentially what this does is encrypt the code so
> > that if anyone or any utility gets into the database they cannot see
> > any of the actual code.  This is a great feature for protecting
> > intellectual processing techniques.  I don't know if there is anyway
> > to do this in PostGres.  Before the hidden feature was added, we had
> > a competitor steal some of our stored procedure processing code.  Is
> > there anyway to protect this from happening in PostGres?
>
> The only bulletproof way to do this currently is to write all your
> stored functions in C and load them as a shared library.
>
> -Doug
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

--
Reg me Please

"Scott Marlowe" <scott.marlowe@gmail.com> writes:

> On 10/31/07, Douglas McNaught <doug@mcnaught.org> wrote:
>
>> The only bulletproof way to do this currently is to write all your
>> stored functions in C and load them as a shared library.
>
> Well, as I pointed out in my post, even that's not bullet-proof.  As
> long as decompilers / debuggers etc... exist, then compiling is just a
> small barrier to a good hacker.  The only way to win is not to play,
> i.e. keep the code in house.

Very true, I should have said "semi-bulletproof".  :)

In addition, the "interesting" parts of a C function would be the SPI
queries, which unless further obfuscated would be easily extractable
from the library using strings(1).

-Doug

---

From: Reg Me Please [mailto:regmeplease@gmail.com]
To: pgsql-general@postgresql.org
Sent: Wed, 31 Oct 2007 14:26:51 -0400
Subject: Re: [GENERAL] Securing stored procedures and triggers

There's not bulletproof way, in my opinion.

If they copy the whole DB structure *and* the object binaries they'll
have the very same functionalities!

Il Wednesday 31 October 2007 16:13:23 Douglas McNaught ha scritto:
> mgould <mgould@allcoast.net> writes:
> > We are currently migrating from Sybase's ASA 9/10 to PostGres 8.2.4.
> > One of the features that is really nice in ASA is the ability to add
> > the attribute hidden to a Create procedure, Create function and
> > Create trigger. Essentially what this does is encrypt the code so
> > that if anyone or any utility gets into the database they cannot see
> > any of the actual code. This is a great feature for protecting
> > intellectual processing techniques. I don't know if there is anyway
> > to do this in PostGres. Before the hidden feature was added, we had
> > a competitor steal some of our stored procedure processing code. Is
> > there anyway to protect this from happening in PostGres?
>
> The only bulletproof way to do this currently is to write all your
> stored functions in C and load them as a shared library.
>
> -Doug
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

--
Reg me Please

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Wed, Oct 31, 2007 at 05:18:58PM -0400, mgould wrote:
> Thanks all.  In the open source community there seems to be more
> talent to "hack" than in other environments.

I think we're just much more honest about what the technology is
really capable of.  None of us is likely to actually bother breaking
into anything, it's just that once you understand the fundamental
building blocks of computers it's reasonably easy to determine specific
properties.  People involved in FOSS projects generally have a much
better understanding of this that in other environments.

> Once I told ASA to set
> the "hidden" attribute, I've not had any problems with this, at least
> that I've heard of.

Which, almost by definition in security, you're not going to hear about.

> I was hoping that I'd be able to keep others out
> of the database totally but I can't host these applications for all of
> my customers.

In absolute terms you can't protect code.  The whole point of computers
and information is that it's very difficult to lock down.  Witness the
trouble that the big media companies are having with trying to "protect"
the contents of their DVD/CD's.


  Sam