No title

carter ck wrote:

Hi all,

I was trying to create function in postgres, but it returns error mentioning the language is NOT defined.

The function is as following:

CREATE OR REPLACE FUNCTION test_word_count(TEXT, TEXT) RETURNS INTEGER AS $$
DECLARE
d_word ALIAS FOR $1;
d_phrase ALIAS FOR $2;
BEGIN
IF d_word IS NULL OR d_phrase IS NULL THEN RETURN 0;

RETURN 1;

END;

$$ LANGUAGE plpgsql;

ERROR: language "plpgsql" does not exist
HINT: Use CREATE LANGUAGE to load the language into the database.

I was wonderring why it is not included by default? Or have I missed out something in the configuration!

According to the documentation, you have to explicitly create the
language in order to register the language with the database:

CREATE LANGUAGE plpgsql;

Assuming everything else is set up properly, this will allow you to use
the plpgsql language.

Ron

On Wed, Oct 31, 2007 at 11:07:36AM +0800, carter ck wrote:

Hi all,

I was trying to create function in postgres, but it returns error mentioning the language is NOT defined.

<snip>

ERROR: language "plpgsql" does not exist
HINT: Use CREATE LANGUAGE to load the language into the database.

I was wonderring why it is not included by default? Or have I missed out something in the configuration!

It's included by default, just not enabled by default. Try "create
language plpgsql" as administrator.

Also, how to do a better text search? I have come across the bad performance of LIKE statement.

See tsearch2.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230

On Wed, Oct 31, 2007 at 4:07 AM, in message

<BAY101-W1511B7A0AA605D61D8630BD5930@phx.gbl>, carter ck
<carterck32@hotmail.com> wrote:

Hi all,

I was trying to create function in postgres, but it returns error

mentioning

the language is NOT defined.

The function is as following:

CREATE OR REPLACE FUNCTION test_word_count(TEXT, TEXT) RETURNS

INTEGER AS $$

DECLARE
d_word ALIAS FOR $1;
d_phrase ALIAS FOR $2;
BEGIN
IF d_word IS NULL OR d_phrase IS NULL THEN RETURN 0;

RETURN 1;

END;

$$ LANGUAGE plpgsql;

ERROR: language "plpgsql" does not exist
HINT: Use CREATE LANGUAGE to load the language into the database.

I was wonderring why it is not included by default? Or have I missed

out

something in the configuration!

Also, how to do a better text search? I have come across the bad

performance

of LIKE statement.

Have you tried TSearch2 to do a text search?

You can also create the language in template1 and then you'll have it in any
other database you'll create (from template1).

Il Wednesday 31 October 2007 08:21:08 Martijn van Oosterhout ha scritto:

On Wed, Oct 31, 2007 at 11:07:36AM +0800, carter ck wrote:

Hi all,

I was trying to create function in postgres, but it returns error
mentioning the language is NOT defined.

<snip>

ERROR: language "plpgsql" does not exist
HINT: Use CREATE LANGUAGE to load the language into the database.

I was wonderring why it is not included by default? Or have I missed out
something in the configuration!

It's included by default, just not enabled by default. Try "create
language plpgsql" as administrator.

Also, how to do a better text search? I have come across the bad
performance of LIKE statement.

See tsearch2.

Have a nice day,

--
Reg me Please

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

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

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

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

Thanks all. In the open source community there seems to be more talent to "hack" than 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. 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.

Best Regards,

Michael Gould
All Coast Intermodal Services, Inc.
904-376-7030
_____

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