Re: [GENERAL] Oracle to PostgreSQL

[Felix Kunde]

On Thu, 2017-11-09 at 17:01 +0530, Brahmam Eswar wrote:
> You can create a composite type in PostgreSQL:
> CREATE TYPE complex AS (r integer, i integer);
> You would use an array in this case:
> DECLARE
> carr complex[];

I've once faced the same thing, and did as Laurenz suggested.
You will like Postgres' array support.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL

[Laurenz Albe]

On Thu, 2017-11-09 at 17:01 +0530, Brahmam Eswar wrote:
> Here is the snippet of it.

>TYPE INV_LINES_RT IS RECORD(
>  VENDOR_NUM A.Datastore.VENDOR_NUM%TYPE,
>  VENDOR_SITE_CODE A.Datastore.VENDOR_SITE_CODE%TYPE,
>  INVOICE_NUM A.Datastore.INVOICE_NUM%TYPE,
>  TXN_CNT NUMBER
>);

You can create a composite type in PostgreSQL:

   CREATE TYPE complex AS (r integer, i integer);

>TYPE INV_LINES_T IS TABLE OF INV_LINES_RT;

You would use an array in this case:

   DECLARE
  carr complex[];

Yours,
Laurenz Albe


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL

[Brahmam Eswar]

Here is the snippet of it.

*Oracle :*

*Declaration part in Store Procedure*

 CREATE OR REPLACE PROCEDURE "A"."Datastore"
 (

 , In_Param1 IN VARCHAR2
 , In_Param2 IN VARCHAR2
 , In_Param3 IN VARCHAR2
 , Out_Param1 OUT VARCHAR2
 , ERROR_MSG OUT VARCHAR2
 ) AS
   TEMP_ERR_MSG VARCHAR2(4000);
   IS_FIRST CHAR(1);
   TYPE INV_LINES_RT *IS RECORD*(
 VENDOR_NUM A.Datastore.VENDOR_NUM%TYPE,
 VENDOR_SITE_CODE A.Datastore.VENDOR_SITE_CODE%TYPE,
 INVOICE_NUM A.Datastore.INVOICE_NUM%TYPE,
 TXN_CNT NUMBER
   );
   TYPE INV_LINES_T *IS TABLE OF* INV_LINES_RT;
   L_INV_LINES INV_LINES_T;


*Looping Part :*

 IS_FIRST:='Y';
 WITH Distinct_Invoices As ( select DISTINCT
VENDOR_NUM,VENDOR_SITE_CODE,INVOICE_NUM,DESCRIPTION,CURR_CODE,INVOICE_AMOUNT,IMAGE_NUMBER,
VENDOR_NAME, PAYMENT_METHOD, GL_DATE, TERMS_NAME, RETURN_ID, PAYGROUP,
INVOICE_TYPE, INVOICE_DATE from A.Datastore where CASE_ID = In_Param1 )
 Select VENDOR_NUM,VENDOR_SITE_CODE,INVOICE_NUM, count(*) as TXN_CNT * BULK
COLLECT INTO* L_INV_LINES from Distinct_Invoices group by
VENDOR_NUM,VENDOR_SITE_CODE,INVOICE_NUM having count(*) > 1;
   IF  L_INV_LINES.COUNT>0 THEN

 ERROR_COUNT:=0;
   FOR indx in L_INV_LINES.first .. L_INV_LINES.last LOOP
 IF IS_FIRST!='Y' THEN
  TEMP_ERR_MSG  :=  TEMP_ERR_MSG || ', ';
 END IF;
 IS_FIRST:='N';
 TEMP_ERR_MSG  :=  TEMP_ERR_MSG ||  ||
L_INV_LINES(indx).INVOICE_NUM || ;


How do i convert this into PostgreSQL.

On Thu, Nov 9, 2017 at 4:48 PM, Laurenz Albe 
wrote:

> Brahmam Eswar wrote:
> > How to migrate the Oracle collection types ( IsTableOF, IsRecord) to
> postgres.
>
> Are you talking about table definitions or PL/SQL code?
>
> Yours,
> Laurenz Albe
>



-- 
Thanks & Regards,
Brahmeswara Rao J.

Re: [GENERAL] Oracle to PostgreSQL

[Laurenz Albe]

Brahmam Eswar wrote:
> How to migrate the Oracle collection types ( IsTableOF, IsRecord) to postgres.

Are you talking about table definitions or PL/SQL code?

Yours,
Laurenz Albe


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Oracle to PostgreSQL

[Brahmam Eswar]

Hi ,

How to migrate the Oracle collection types ( IsTableOF, IsRecord) to
postgres.

-- 
Thanks & Regards,
Brahmeswara Rao J.

Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 7:17 PM, Adrian Klaver 
wrote:

> On 05/29/2017 06:40 AM, PAWAN SHARMA wrote:
>
>>
>>
> ya, but how can fix it.??
>>
>> because memory is not an issue on both the server.
>>
>>
>> Hi Chris,
>>
>> I am able to get the output script in output.sql but now data migration
>> is the big problem.
>>
>
> http://ora2pg.darold.net/documentation.html#postgresql_import
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


Thanks all,

now it's working After DATA_LIMIT15000 in ora2pg.conf.

test migration is successful.


C:\ora2pg>ora2pg -c ora2pg.conf
[>] 2/2 tables (100.0%) end of scanning.
[>] 0/2 tables (0.0%) end of scanning.
[>] 2/2 tables (100.0%) end of table export.
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=;sid=;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE mytab (
id bigint,
name varchar(30),
dt timestamp
) ;

CREATE TABLE test (
id bigint,
name varchar(30)
) ;
[>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
[>] 1/2 total rows (50.0%) - (4 sec., avg: 0
recs/sec).
[>] 1/1 rows (100.0%) Table TEST (0 recs/sec)
[>] 2/2 total rows (100.0%) - (261 sec., avg: 0
recs/sec).
[>] 2/2 rows (100.0%) on total estimated data (262
sec., avg: 0 recs/sec)
C:\ora2pg>

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Adrian Klaver]

On 05/29/2017 06:40 AM, PAWAN SHARMA wrote:





ya, but how can fix it.??

because memory is not an issue on both the server.


Hi Chris,

I am able to get the output script in output.sql but now data migration 
is the big problem.


http://ora2pg.darold.net/documentation.html#postgresql_import

--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Neil Anderson]

>>>
>>>
>> ya, but how can fix it.??
>>
>> because memory is not an issue on both the server.
>>
>

I've never used ora2pg but there is some documentation saying that
often an out of memory issue can be fixed by adjusting your DATA_LIMIT
setting. https://github.com/darold/ora2pg/blob/master/README#L307

> Hi Chris,
>
> I am able to get the output script in output.sql but now data migration is
> the big problem.

-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 6:54 PM, PAWAN SHARMA 
wrote:

>
>
> On Mon, May 29, 2017 at 6:51 PM, Chris Mair  wrote:
>
>> [>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
>>> [>] 1/2 total rows (50.0%) - (4 sec., avg: 0
>>> recs/sec).
>>> Out of memory!] 1/2 rows (50.0%) on total estimated data (4
>>> sec., avg: 0 recs/sec)
>>> Issuing rollback() due to DESTROY without explicit disconnect() of
>>> DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
>>> RT=1521)(PROTOCOL=tcp)(HOST=))(CONNECT_DATA=(SID=)))
>>> at C:/Strawberry/perl/vendor/
>>> lib/DBD/Oracle.pm line 348.
>>>
>>>
>>> So, It will show data enter in mytab 100% but in test it is 50%. but
>>> when I checked on PostgreSQL server their data only in one table.
>>>
>>
>> Did you notice the "Out of memory!" you got there?
>>
>> I guess that's the problem now...
>>
>> Bye,
>> Chris.
>>
>>
>>
>>
>>
>> ya, but how can fix it.??
>
> because memory is not an issue on both the server.
>
>
Hi Chris,

I am able to get the output script in output.sql but now data migration is
the big problem.

Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 6:51 PM, Chris Mair  wrote:

> [>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
>> [>] 1/2 total rows (50.0%) - (4 sec., avg: 0
>> recs/sec).
>> Out of memory!] 1/2 rows (50.0%) on total estimated data (4
>> sec., avg: 0 recs/sec)
>> Issuing rollback() due to DESTROY without explicit disconnect() of
>> DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
>> RT=1521)(PROTOCOL=tcp)(HOST=))(CONNECT_DATA=(SID=)))
>> at C:/Strawberry/perl/vendor/
>> lib/DBD/Oracle.pm line 348.
>>
>>
>> So, It will show data enter in mytab 100% but in test it is 50%. but when
>> I checked on PostgreSQL server their data only in one table.
>>
>
> Did you notice the "Out of memory!" you got there?
>
> I guess that's the problem now...
>
> Bye,
> Chris.
>
>
>
>
>
> ya, but how can fix it.??

because memory is not an issue on both the server.

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Chris Mair]

[>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
[>] 1/2 total rows (50.0%) - (4 sec., avg: 0 recs/sec).
Out of memory!] 1/2 rows (50.0%) on total estimated data (4 sec., 
avg: 0 recs/sec)
Issuing rollback() due to DESTROY without explicit disconnect() of 
DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
RT=1521)(PROTOCOL=tcp)(HOST=))(CONNECT_DATA=(SID=))) at 
C:/Strawberry/perl/vendor/
lib/DBD/Oracle.pm line 348.


So, It will show data enter in mytab 100% but in test it is 50%. but when I 
checked on PostgreSQL server their data only in one table.


Did you notice the "Out of memory!" you got there?

I guess that's the problem now...

Bye,
Chris.








--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 6:28 PM, Chris Mair  wrote:

> C:\ora2pg>ora2pg -c ora2pg.conf
>> [>] 2/2 tables (100.0%) end of scanning.
>> [>] 0/2 tables (0.0%) end of scanning.
>> [>] 2/2 tables (100.0%) end of table export.
>>
>
> Looks good so far.
> This means you could connect to Oracle DB now.
>
>
> DBD::Pg::st execute failed: ERROR:  relation "mytab" does not exist
>>
>
> This is coming from the Postgres side.
>
> In ora2pg.conf go to the section
>
> OUTPUT SECTION (Control output to file or PostgreSQL database)
>
> I suggest you comment out (prefix with #) the part
>
> #PG_DSN dbi:Pg:dbname=test_db;host=localhost;port=5432
> #PG_USERtest
> #PG_PWD test
>
> and just have ora2pg write its ouput to a file by setting OUTPUT like this:
>
> OUTPUT  output.sql
>
> This way you have your oputput for Postgres in a file that you can check
> out
> and try importing step by step. I guess you are running this on some test
> data, so the file will be small enough to open it with an editor.
> You cap paste piece by piece into a Postgres prompt (psql or pgadmin or
> whatever
> you're using).
>
> You can then see at what point you get an error (and hopefully understand
> what's happening).
>
> Bye,
> Chris.
>
>
>
Hi Chris,

Thanks for suggestion.!!!

here in ora2pg.conf, I have used below type in ora2pg.conf and create the
table manually on PostgreSQL server.


*TYPETABLE*


*output after this*
-
C:\ora2pg>ora2pg -c ora2pg.conf
Ora2Pg version: 18.1
Trying to connect to database:
dbi:Oracle:host=;sid=;port=1521
Isolation level: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Retrieving table information...
[1] Scanning table MYTAB (1 rows)...
[2] Scanning table TEST (1 rows)...
Dumping table TEST...
Dumping table MYTAB...
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=;sid=;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE test (
id bigint,
name varchar(30)
) ;

CREATE TABLE mytab (
id bigint,
name varchar(30),
dt timestamp
) ;


but, when i am trying to insert data using

TYPE   TABLE, INSERT


C:\ora2pg>ora2pg -c ora2pg.conf
[>] 2/2 tables (100.0%) end of scanning.
[>] 0/2 tables (0.0%) end of scanning.
[>] 2/2 tables (100.0%) end of table export.
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=;sid=;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE mytab (
id bigint,
name varchar(30),
dt timestamp
) ;

CREATE TABLE test (
id bigint,
name varchar(30)
) ;
[>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
[>] 1/2 total rows (50.0%) - (4 sec., avg: 0
recs/sec).
Out of memory!] 1/2 rows (50.0%) on total estimated data (4
sec., avg: 0 recs/sec)
Issuing rollback() due to DESTROY without explicit disconnect() of
DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
RT=1521)(PROTOCOL=tcp)(HOST=))(CONNECT_DATA=(SID=))) at
C:/Strawberry/perl/vendor/
lib/DBD/Oracle.pm line 348.


So, It will show data enter in mytab 100% but in test it is 50%. but when I
checked on PostgreSQL server their data only in one table.

test=# select * from mytab ;
 id | name | dt
+--+-
  1 | aa   | 2017-05-29 06:05:46
(1 row)

test=# select * from test;
 id | name
+--
(0 rows)




So, I am stuck here..!!! Please suggest.

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Chris Mair]

C:\ora2pg>ora2pg -c ora2pg.conf
[>] 2/2 tables (100.0%) end of scanning.
[>] 0/2 tables (0.0%) end of scanning.
[>] 2/2 tables (100.0%) end of table export.


Looks good so far.
This means you could connect to Oracle DB now.



DBD::Pg::st execute failed: ERROR:  relation "mytab" does not exist


This is coming from the Postgres side.

In ora2pg.conf go to the section

OUTPUT SECTION (Control output to file or PostgreSQL database)

I suggest you comment out (prefix with #) the part

#PG_DSN dbi:Pg:dbname=test_db;host=localhost;port=5432
#PG_USERtest
#PG_PWD test

and just have ora2pg write its ouput to a file by setting OUTPUT like this:

OUTPUT  output.sql

This way you have your oputput for Postgres in a file that you can check out
and try importing step by step. I guess you are running this on some test
data, so the file will be small enough to open it with an editor.
You cap paste piece by piece into a Postgres prompt (psql or pgadmin or whatever
you're using).

You can then see at what point you get an error (and hopefully understand
what's happening).

Bye,
Chris.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 2:49 PM, Chris Mair  wrote:

> I am facing below issue while running below command.
>>
>> *C:\ora2pg>ora2pg -c ora2pg.conf*
>> DBI connect('host=;sid=;port=',',...)
>> failed: ORA-01017: invalid username/p
>> assword; logon denied (DBD ERROR: OCISessionBegin) at
>> C:/Strawberry/perl/site/lib/Ora2Pg.pm line 1376.
>> FATAL: 1017 ... ORA-01017: invalid username/password; logon denied (DBD
>> ERROR: OCISessionBegin)
>> Aborting export...
>>
>> even I am able to login in Oracle with same  and 
>>
>
> If you can login from the same host using sqlplus like this:
>
> sqlplus myuser/mypass@myhost:1521/mysid
>
> then you likely can have ora2pg connect too. Just make sure that in
> ora2pg.conf you put the lines:
>
> ORACLE_DSN  dbi:Oracle:host=myhost;sid=mysid
> ORACLE_USER myuser
> ORACLE_PWD  mypass
>
> Bye,
> Chris.
>
>
>
Hi Chris,

C:\ora2pg>ora2pg -c ora2pg.conf
[>] 2/2 tables (100.0%) end of scanning.
[>] 0/2 tables (0.0%) end of scanning.
[>] 2/2 tables (100.0%) end of table export.
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=;sid=;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE test (
id bigint,
name varchar(30)
) ;

CREATE TABLE mytab (
id bigint,
name varchar(30),
dt timestamp
) ;
DBD::Pg::st execute failed: ERROR:  relation "mytab" does not exist
LINE 1: INSERT INTO mytab (id,name,dt) VALUES ($1,$2,$3)
^ at C:/Strawberry/perl/site/lib/Ora2Pg.pm line 11574.
FATAL: ERROR:  relation "mytab" does not exist
LINE 1: INSERT INTO mytab (id,name,dt) VALUES ($1,$2,$3)
^
Aborting export...

C:\ora2pg>

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Chris Mair]

I am facing below issue while running below command.

*C:\ora2pg>ora2pg -c ora2pg.conf*
DBI connect('host=;sid=;port=',',...) failed: 
ORA-01017: invalid username/p
assword; logon denied (DBD ERROR: OCISessionBegin) at 
C:/Strawberry/perl/site/lib/Ora2Pg.pm line 1376.
FATAL: 1017 ... ORA-01017: invalid username/password; logon denied (DBD ERROR: 
OCISessionBegin)
Aborting export...

even I am able to login in Oracle with same  and 


If you can login from the same host using sqlplus like this:

sqlplus myuser/mypass@myhost:1521/mysid

then you likely can have ora2pg connect too. Just make sure that in ora2pg.conf 
you put the lines:

ORACLE_DSN  dbi:Oracle:host=myhost;sid=mysid
ORACLE_USER myuser
ORACLE_PWD  mypass

Bye,
Chris.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

Hi All,

I am going to migrate Oracle database into PostgreSQL using ora2pg tools.

I have installed Strawberry Perl.

C:\ora2pg>perl -v
This is perl 5, version 24, subversion 1 (v5.24.1) built for
MSWin32-x64-multi-thread
Copyright 1987-2017, Larry Wall


I am facing below issue while running below command.

*C:\ora2pg>ora2pg -c ora2pg.conf*
DBI connect('host=;sid=;port=',',...)
failed: ORA-01017: invalid username/p
assword; logon denied (DBD ERROR: OCISessionBegin) at
C:/Strawberry/perl/site/lib/Ora2Pg.pm line 1376.
FATAL: 1017 ... ORA-01017: invalid username/password; logon denied (DBD
ERROR: OCISessionBegin)
Aborting export...

even I am able to login in Oracle with same  and 

*entry in sqlnet.ora*

SQLNET.AUTHENTICATION_SERVICES = (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

Please suggest.!!

-Pawan

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[William Dunn]

On Wed, Jul 8, 2015 at 5:20 PM, Joshua D. Drake j...@commandprompt.com
 wrote:


 On 07/08/2015 12:47 PM, John McKown wrote:


 ​Why are they converting?

 Would EnterpriseDB (a commercial version of PostgreSQL which has
 extensions to make it a drop in replacement for Oracle) be a
 possibility?
 http://www.enterprisedb.com/solutions/oracle-compatibility-technology


 Because EDB is expensive. Why go from one closed source solution to
 another when you can go to the best Open Source database and forgo all of
 that?

 Yes, EDB has some nice tools (no denying that) but porting from Oracle to
 PostgreSQL proper is not difficult in the least (although time consuming).


I would suggest refrain from dismissing EnterpriseDB's PostgreSQL Advanced
Server like that. It is not free like the community version of Postgres but
the cost pays for developer time spent adding the additional features which
make it capable of being a drop-in replacement of Oracle. For an
organization migrating off of Oracle paying for that would make a lot of
sense because of the developer time (and cost) saved by porting to that
rather than the additional effort of migration to community Postgres. It
also has the additional benefit of providing all the programming features
that their app developers have become used to when working with Oracle.

The EnterpriseDB developers are some of the most active contributors to the
community version of Postgres and the advancements made in the community
version are included in EnterpriseDB Advanced Server as well.

*Will J. Dunn*
*willjdunn.com http://willjdunn.com*

On Wed, Jul 8, 2015 at 5:20 PM, Joshua D. Drake j...@commandprompt.com
wrote:


 On 07/08/2015 12:47 PM, John McKown wrote:


 ​Why are they converting?

 Would EnterpriseDB (a commercial version of PostgreSQL which has
 extensions to make it a drop in replacement for Oracle) be a
 possibility?
 http://www.enterprisedb.com/solutions/oracle-compatibility-technology


 Because EDB is expensive. Why go from one closed source solution to
 another when you can go to the best Open Source database and forgo all of
 that?

 Yes, EDB has some nice tools (no denying that) but porting from Oracle to
 PostgreSQL proper is not difficult in the least (although time consuming).

 Sincerely,

 JD


 --
 Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
 PostgreSQL Centered full stack support, consulting and development.
 Announcing I'm offended is basically telling the world you can't
 control your own emotions, so everyone else should do it for you.



 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[Julien Rouhaud]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Le 08/07/2015 22:25, CS DBA a écrit :
 
 
 On 07/08/2015 02:20 PM, John R Pierce wrote:
 On 7/8/2015 1:16 PM, dinesh kumar wrote:
 We recently done the similar migration for one of our customer.
 We used all opensource tools to achieve this migration
 process.
 
 We used Pentaho Data Integration tool for doing Online DB
 migration, which took minimal downtime with CDC{Change Data
 Capture} approach. Also, we used Ora2Pg tool to migrate the DB
 objects with some manual syntax modifications.
 
 
 thats the easy part.
 
 now what about the massive code base of pl/sql and triggers he 
 mentioned ?
 
 
 
 Have you considered using ora2pg? http://ora2pg.darold.net/
 
 We've done several client migrations with it, quite successfully
 
 
 

ora2pg is definitely the tool you need.  It can give you a total
migration estimated time (in man-day unit), with a call like

ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html --estimate_cost

You can check a sample report here: http://ora2pg.darold.net/report.html

- -- 
Julien Rouhaud
http://dalibo.com - http://dalibo.org
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.17 (GNU/Linux)

iQEcBAEBAgAGBQJVnimjAAoJELGaJ8vfEpOqTKAH/0+uGEXUmGm6tfagpJqU7kB+
2c+jooW/MKokDcgifvmUTy+fKb8iDoF8CUffActFyX5YyrCFfb4Bjw9P6wuJfF6S
WXhzWXQ//AFiApqNPknfHWnYeqe4jJlLq2fHN7qCQvItEWuKFiHpWcEi1zVBPnMm
e6NLxePm0WzjpigbwhT2X0Ziena8CxxdencPQvO81clsR8Fgtq4B//6KQ3GEsIL2
aUtj4k+wBCmRywiMgiSYiQzLUXUp2HWOp6qLLYpwifr4BgY2X+CQNSHlAK2KXecf
fQ+rm3tyo5QEtMxHQPO/NUsl+zSIllZjPYG1Wa81RwsQpWKhNNB/+reDTSgC5ws=
=9pb/
-END PGP SIGNATURE-


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[dinesh kumar]

Hi,

We recently done the similar migration for one of our customer. We used all
opensource tools to achieve this migration process.

We used Pentaho Data Integration tool for doing Online DB migration, which
took minimal downtime with CDC{Change Data Capture} approach. Also, we used
Ora2Pg tool to migrate the DB objects with some manual syntax modifications.

Regards,
Dinesh
manojadinesh.blogspot.com

On Wed, Jul 8, 2015 at 12:24 PM, Tim Clotworthy 
tclotwor...@bluestonelogic.com wrote:

 Hello,

 I have a customer that is about to undertake a migration of an Oracle 11g
 database to PostgreSQL 9.x (exact version to be determined). I am talking
 not only of the migration of schemas and data, but also of a substantial
 codebase of Pl/SQL stored procedures, as well as many triggers.

 I don't think they know yet what they are up against. Everything I have
 read is that this is a very substantial effort. At this stage, they would
 be particularly interested in realistic and practical information on how
 to
 estimate the effort required as well as any best-practices or guidance on
 transition strategies.

 I have found official documentation on the PostgreSQL site for porting
 Pl/SQL to PL/pgSQL. This is excellent technical documentation. However,
 there success will require that they are well prepared realistically
 understanding the scope of the effor they are asbout to undertake.

 Thanks for any response!

[GENERAL] Oracle to PostgreSQL Migration - Need Information

[Tim Clotworthy]

Hello,

I have a customer that is about to undertake a migration of an Oracle 11g
database to PostgreSQL 9.x (exact version to be determined). I am talking
not only of the migration of schemas and data, but also of a substantial
codebase of Pl/SQL stored procedures, as well as many triggers.

I don't think they know yet what they are up against. Everything I have
read is that this is a very substantial effort. At this stage, they would
be particularly interested in realistic and practical information on how to
estimate the effort required as well as any best-practices or guidance on
transition strategies.

I have found official documentation on the PostgreSQL site for porting
Pl/SQL to PL/pgSQL. This is excellent technical documentation. However,
there success will require that they are well prepared realistically
understanding the scope of the effor they are asbout to undertake.

Thanks for any response!

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[John R Pierce]

On 7/8/2015 1:16 PM, dinesh kumar wrote:
We recently done the similar migration for one of our customer. We 
used all opensource tools to achieve this migration process.


We used Pentaho Data Integration tool for doing Online DB migration, 
which took minimal downtime with CDC{Change Data Capture} approach. 
Also, we used Ora2Pg tool to migrate the DB objects with some manual 
syntax modifications.




thats the easy part.

now what about the massive code base of pl/sql and triggers he mentioned ?



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[Joshua D. Drake]

On 07/08/2015 12:47 PM, John McKown wrote:



​Why are they converting?

Would EnterpriseDB (a commercial version of PostgreSQL which has
extensions to make it a drop in replacement for Oracle) be a possibility?
http://www.enterprisedb.com/solutions/oracle-compatibility-technology


Because EDB is expensive. Why go from one closed source solution to 
another when you can go to the best Open Source database and forgo all 
of that?


Yes, EDB has some nice tools (no denying that) but porting from Oracle 
to PostgreSQL proper is not difficult in the least (although time 
consuming).


Sincerely,

JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing I'm offended is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[CS DBA]

On 07/08/2015 02:20 PM, John R Pierce wrote:
 On 7/8/2015 1:16 PM, dinesh kumar wrote:
 We recently done the similar migration for one of our customer. We
 used all opensource tools to achieve this migration process.

 We used Pentaho Data Integration tool for doing Online DB migration,
 which took minimal downtime with CDC{Change Data Capture} approach.
 Also, we used Ora2Pg tool to migrate the DB objects with some manual
 syntax modifications.


 thats the easy part.

 now what about the massive code base of pl/sql and triggers he
 mentioned ?



Have you considered using ora2pg?
http://ora2pg.darold.net/

We've done several client migrations with it, quite successfully



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[Tim Clarke]

This is almost exactly what we did around 8 years ago; obviously the
version numbers have changed. The reason we chose Postgres was the
enormous similarity between the two languages plus the overwhelming ROI
on the migration; my CEO had a spontaneous nosebleed when the Oracle
licensing costs were revealed one year.

Migration requires some thought, investigation, a clear plan and testing
- but I'm probably preaching to the converted.

We did it, we've never looked back and have had at least 8 long very
happy, stable and productive years and are looking forward to many more
(raises a glass to the developers and maintainers).

Tim Clarke

On 08/07/15 20:24, Tim Clotworthy wrote:
 Hello, 
  
 I have a customer that is about to undertake a migration of an Oracle 11g 
 database to PostgreSQL 9.x (exact version to be determined). I am talking 
 not only of the migration of schemas and data, but also of a substantial 
 codebase of Pl/SQL stored procedures, as well as many triggers. 
  
 I don't think they know yet what they are up against. Everything I have 
 read is that this is a very substantial effort. At this stage, they would 
 be particularly interested in realistic and practical information on
 how to 
 estimate the effort required as well as any best-practices or guidance on 
 transition strategies. 
  
 I have found official documentation on the PostgreSQL site for porting 
 Pl/SQL to PL/pgSQL. This is excellent technical documentation. However, 
 there success will require that they are well prepared realistically 
 understanding the scope of the effor they are asbout to undertake. 
  
 Thanks for any response! 

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[dinesh kumar]

On Wed, Jul 8, 2015 at 1:20 PM, John R Pierce pie...@hogranch.com wrote:

 On 7/8/2015 1:16 PM, dinesh kumar wrote:

 We recently done the similar migration for one of our customer. We used
 all opensource tools to achieve this migration process.

 We used Pentaho Data Integration tool for doing Online DB migration,
 which took minimal downtime with CDC{Change Data Capture} approach. Also,
 we used Ora2Pg tool to migrate the DB objects with some manual syntax
 modifications.


 thats the easy part.

 now what about the massive code base of pl/sql and triggers he mentioned ?


Yeah, we need to rewrite the business logic if there are any un-supported
features like autonomous transactions, packages, nested procedures, e.t.c.

Regards,
Dinesh
manojadinesh.blogspot.com



 --
 john r pierce, recycling bits in santa cruz



 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[John McKown]

On Wed, Jul 8, 2015 at 2:24 PM, Tim Clotworthy 
tclotwor...@bluestonelogic.com wrote:

 Hello,

 I have a customer that is about to undertake a migration of an Oracle 11g
 database to PostgreSQL 9.x (exact version to be determined). I am talking
 not only of the migration of schemas and data, but also of a substantial
 codebase of Pl/SQL stored procedures, as well as many triggers.

 I don't think they know yet what they are up against. Everything I have
 read is that this is a very substantial effort. At this stage, they would
 be particularly interested in realistic and practical information on how
 to
 estimate the effort required as well as any best-practices or guidance on
 transition strategies.

 I have found official documentation on the PostgreSQL site for porting
 Pl/SQL to PL/pgSQL. This is excellent technical documentation. However,
 there success will require that they are well prepared realistically
 understanding the scope of the effor they are asbout to undertake.

 Thanks for any response!


​Why are they converting?

Would EnterpriseDB (a commercial version of PostgreSQL which has extensions
to make it a drop in replacement for Oracle) be a possibility?
http://www.enterprisedb.com/solutions/oracle-compatibility-technology
​


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! 
John McKown

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[Melvin Davidson]

I would start by looking at how many databases, schemas, tables and views
are involved. Then look at how many individual Oracle functions need to be
converted to plpgsql. You also need to investigate if there are any custom
data types. I do not have the formula, but I am sure there is a general
time factor involved in converting x databases, y schemas and z tables. You
did not mention what hardware is involved, but I am also sure there is a
time factor involved in copying / converting data from Oracle to
PostgreSQL, and you need to consider how much data you need to move and the
order, as there are probably foreign keys involved also.

You might want to do a small test to see how long it takes to dump 10k rows
of data from 1 tOracle table and load to PostgreSQL. That will at least
give you a general idea of how long it will take to move all data.

On Wed, Jul 8, 2015 at 5:20 PM, Joshua D. Drake j...@commandprompt.com
wrote:


 On 07/08/2015 12:47 PM, John McKown wrote:


 ​Why are they converting?

 Would EnterpriseDB (a commercial version of PostgreSQL which has
 extensions to make it a drop in replacement for Oracle) be a
 possibility?
 http://www.enterprisedb.com/solutions/oracle-compatibility-technology


 Because EDB is expensive. Why go from one closed source solution to
 another when you can go to the best Open Source database and forgo all of
 that?

 Yes, EDB has some nice tools (no denying that) but porting from Oracle to
 PostgreSQL proper is not difficult in the least (although time consuming).

 Sincerely,

 JD


 --
 Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
 PostgreSQL Centered full stack support, consulting and development.
 Announcing I'm offended is basically telling the world you can't
 control your own emotions, so everyone else should do it for you.


 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general




-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Oracle to PostgreSQL migration

[sridhar bamandlapally]

Hi

With minimal application code changes we are able to migrate database from
Oracle to PostgreSQL successfully (including performance issues) on
production

This we did on high read intensive database sized 900+GB

Thanks to PostgreSQL

Thanks
Sridhar BN

Re: [GENERAL] Oracle to PostgreSQL replication

[Rajni Baliyan]

I downloaded symmetricDS but I did not found any installer
(symmetric-pro-x.x.x.jar)
Can any one please help me in this

Thanks

Best Regards,

*Rajni Baliyan | Database - Consultant*
*ASHNIK PTE. LTD.*101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M:+65 87294147 T: +65 6438 3504 | www.ashnik.com

*[image: icons]*

[image: Email patch] http://www.ashnik.com/
This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).


On Fri, May 9, 2014 at 4:11 AM, Anand Kumar, Karthik 
karthik.anandku...@classmates.com wrote:

  We use symmetricDS for this. Works pretty well.

  http://www.symmetricds.org/


   From: Serge Fonville serge.fonvi...@gmail.com
 Date: Wednesday, May 7, 2014 at 2:49 AM
 To: Geoff Montee geoff.mon...@gmail.com
 Cc: Sameer Kumar sameer.ku...@ashnik.com, PostgreSQL General Discussion
 Forum pgsql-general@postgresql.org
 Subject: Re: [GENERAL] Oracle to PostgreSQL replication

   Hi,

  I need to setup a replication process for continuously replicating
 changes happening in an Oracle Database to a PostgreSQL database.

 My Oracle Database is version 11.2 and setup as a cluster with RAC
 My Postgres database version is 9.2

 Oracle Database is running in Solaris and PostgreSQL is running on RHEL.

 Is there any commercial or open source tool available to achieve this?


  How about EnterpriseDB XDB 
 replicationhttp://www.enterprisedb.com/products-services-training/products/complementary-enterprisedb-products/xdb-replication-server
 ?

 Kind regards/met vriendelijke groet,

  Serge Fonville

  http://www.sergefonville.nl


 2014-05-07 11:44 GMT+02:00 Geoff Montee geoff.mon...@gmail.com:


  On Wed, May 7, 2014 at 12:31 AM, Sameer Kumar 
 sameer.ku...@ashnik.comwrote:

  Hi,


  I need to setup a replication process for continuously replicating
 changes happening in an Oracle Database to a PostgreSQL database.


  My Oracle Database is version 11.2 and setup as a cluster with RAC
 My Postgres database version is 9.2

  Oracle Database is running in Solaris and PostgreSQL is running on
 RHEL.

  Is there any commercial or open source tool available to achieve this?



  Continuent's Tungsten Replicator apparently offers Oracle to MySQL
 replication. There's a wiki page that suggests PostgreSQL support was in
 development at one time. I'm not sure how far they got, or if they are
 still working on it.

  http://www.continuent.com/solutions/replication

  https://wiki.postgresql.org/wiki/Tungsten

  Geoff Montee

Re: [GENERAL] Oracle to PostgreSQL replication

[Thomas Kellerer]

Rajni Baliyan, 09.05.2014 09:46:
 I downloaded symmetricDS but I did not found any installer 
 (symmetric-pro-x.x.x.jar)
 Can any one please help me in this

This might help: 
http://www.symmetricds.org/doc/3.5/html/tutorial.html#tutorial-install






-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL replication

[Albe Laurenz]

Sameer Kumar wrote:
 I guess I will be exploring more on oracle foreign data wrapper.
 
 Has anyone tried using oracle_fdw with Oracle RAC? I am wondering how would 
 it handle failovers.

I have not tried it, but it should work as follows:

- You'll have to use a connect string that is correctly defined for
  transparent application failover, see
  http://docs.oracle.com/cd/E11882_01/network.112/e10836/advcfg.htm#NETAG455

- Failover will happen automatically.

- SELECTs should be able to complete without error message.

- INSERT, UPDATE and DELETE will fail with an error message and
  will have to be retried.  I did not code oracle_fdw to support
  TAF for DML statements.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL replication

[tuanhoanganh]

You can test with SymmetricDS (www.*symmetricds*.org)


On Thu, May 8, 2014 at 10:35 PM, tuanhoanganh hatua...@gmail.com wrote:

 You can test with SymmetricDS (www.*symmetricds*.org)


 On Thu, May 8, 2014 at 12:53 PM, Sameer Kumar sameer.ku...@ashnik.comwrote:


 Thanks alot everyone!

 I guess I will be exploring more on oracle foreign data wrapper.

 Has anyone tried using oracle_fdw with Oracle RAC? I am wondering how
 would it handle failovers.

Re: [GENERAL] Oracle to PostgreSQL replication

[Anand Kumar, Karthik]

We use symmetricDS for this. Works pretty well.

http://www.symmetricds.org/


From: Serge Fonville serge.fonvi...@gmail.commailto:serge.fonvi...@gmail.com
Date: Wednesday, May 7, 2014 at 2:49 AM
To: Geoff Montee geoff.mon...@gmail.commailto:geoff.mon...@gmail.com
Cc: Sameer Kumar sameer.ku...@ashnik.commailto:sameer.ku...@ashnik.com, 
PostgreSQL General Discussion Forum 
pgsql-general@postgresql.orgmailto:pgsql-general@postgresql.org
Subject: Re: [GENERAL] Oracle to PostgreSQL replication

Hi,

I need to setup a replication process for continuously replicating changes 
happening in an Oracle Database to a PostgreSQL database.

My Oracle Database is version 11.2 and setup as a cluster with RAC
My Postgres database version is 9.2

Oracle Database is running in Solaris and PostgreSQL is running on RHEL.

Is there any commercial or open source tool available to achieve this?

How about EnterpriseDB XDB 
replicationhttp://www.enterprisedb.com/products-services-training/products/complementary-enterprisedb-products/xdb-replication-server?

Kind regards/met vriendelijke groet,

Serge Fonville

http://www.sergefonville.nl


2014-05-07 11:44 GMT+02:00 Geoff Montee 
geoff.mon...@gmail.commailto:geoff.mon...@gmail.com:

On Wed, May 7, 2014 at 12:31 AM, Sameer Kumar 
sameer.ku...@ashnik.commailto:sameer.ku...@ashnik.com wrote:
Hi,


I need to setup a replication process for continuously replicating changes 
happening in an Oracle Database to a PostgreSQL database.


My Oracle Database is version 11.2 and setup as a cluster with RAC
My Postgres database version is 9.2

Oracle Database is running in Solaris and PostgreSQL is running on RHEL.

Is there any commercial or open source tool available to achieve this?



Continuent's Tungsten Replicator apparently offers Oracle to MySQL replication. 
There's a wiki page that suggests PostgreSQL support was in development at one 
time. I'm not sure how far they got, or if they are still working on it.

http://www.continuent.com/solutions/replication

https://wiki.postgresql.org/wiki/Tungsten

Geoff Montee

Re: [GENERAL] Oracle to PostgreSQL replication

[Albe Laurenz]

Sameer Kumar wrote:
 I need to setup a replication process for continuously replicating changes 
 happening in an Oracle
 Database to a PostgreSQL database.
 
 
 My Oracle Database is version 11.2 and setup as a cluster with RAC
 My Postgres database version is 9.2
 
 Oracle Database is running in Solaris and PostgreSQL is running on RHEL.
 
 Is there any commercial or open source tool available to achieve this?
 
 I was wondering has anyone used foreign data  wrapper or

There is no ready-made solution for this from the PostgreSQL side.
You could check with Oracle if they provide something like that
with their Golden Gate.

What you could do is to have a trigger record all changes to the Oracle
table in a separate log table and regularly run a program that pulls those
changes from the log table and applies them to a PostgreSQL table,
deleting the log entries as it goes.

You could write such a thing as PostgreSQL function using oracle_fdw,
but you need PostgreSQL 9.3 if you want to update Oracle data that way.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL replication

[Geoff Montee]

On Wed, May 7, 2014 at 12:31 AM, Sameer Kumar sameer.ku...@ashnik.comwrote:

 Hi,


 I need to setup a replication process for continuously replicating changes
 happening in an Oracle Database to a PostgreSQL database.


 My Oracle Database is version 11.2 and setup as a cluster with RAC
 My Postgres database version is 9.2

 Oracle Database is running in Solaris and PostgreSQL is running on RHEL.

 Is there any commercial or open source tool available to achieve this?



Continuent's Tungsten Replicator apparently offers Oracle to MySQL
replication. There's a wiki page that suggests PostgreSQL support was in
development at one time. I'm not sure how far they got, or if they are
still working on it.

http://www.continuent.com/solutions/replication

https://wiki.postgresql.org/wiki/Tungsten

Geoff Montee

Re: [GENERAL] Oracle to PostgreSQL replication

[Serge Fonville]

Hi,

I need to setup a replication process for continuously replicating changes
 happening in an Oracle Database to a PostgreSQL database.

 My Oracle Database is version 11.2 and setup as a cluster with RAC
 My Postgres database version is 9.2

 Oracle Database is running in Solaris and PostgreSQL is running on RHEL.

 Is there any commercial or open source tool available to achieve this?


How about EnterpriseDB XDB
replicationhttp://www.enterprisedb.com/products-services-training/products/complementary-enterprisedb-products/xdb-replication-server
?

Kind regards/met vriendelijke groet,

Serge Fonville

http://www.sergefonville.nl


2014-05-07 11:44 GMT+02:00 Geoff Montee geoff.mon...@gmail.com:


 On Wed, May 7, 2014 at 12:31 AM, Sameer Kumar sameer.ku...@ashnik.comwrote:

 Hi,


 I need to setup a replication process for continuously replicating
 changes happening in an Oracle Database to a PostgreSQL database.


 My Oracle Database is version 11.2 and setup as a cluster with RAC
 My Postgres database version is 9.2

 Oracle Database is running in Solaris and PostgreSQL is running on RHEL.

 Is there any commercial or open source tool available to achieve this?



 Continuent's Tungsten Replicator apparently offers Oracle to MySQL
 replication. There's a wiki page that suggests PostgreSQL support was in
 development at one time. I'm not sure how far they got, or if they are
 still working on it.

 http://www.continuent.com/solutions/replication

 https://wiki.postgresql.org/wiki/Tungsten

 Geoff Montee

Re: [GENERAL] Oracle to PostgreSQL replication

[Sameer Kumar]

Thanks alot everyone!

I guess I will be exploring more on oracle foreign data wrapper.

Has anyone tried using oracle_fdw with Oracle RAC? I am wondering how would
it handle failovers.

[GENERAL] Oracle to PostgreSQL replication

[Sameer Kumar]

Hi,


I need to setup a replication process for continuously replicating changes
happening in an Oracle Database to a PostgreSQL database.


My Oracle Database is version 11.2 and setup as a cluster with RAC
My Postgres database version is 9.2

Oracle Database is running in Solaris and PostgreSQL is running on RHEL.

Is there any commercial or open source tool available to achieve this?

I was wondering has anyone used foreign data  wrapper or

Best Regards,

*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

M: *+65 8110 0350*  T: +65 6438 3504 | www.ashnik.com

*[image: icons]*



[image: Email patch] http://www.ashnik.com/



This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).

[GENERAL] Oracle to PostgreSQL transition?

[Roy Anderson]

Hey all,

We have a very robust Oracle and SQL Server presence at work but we're
looking to farm out some of the load to PostgreSQL to limit costs. I'm
curious if there are any DBAs out there who have gone down this route
before. Any tips, tricks, failures, successes, etc.? I would just like to
hear some first-hand commentary on this topic.

Thank you so much!

Roy

Re: [GENERAL] Oracle to PostgreSQL transition?

[Szymon Guz]

On 4 April 2013 16:16, Roy Anderson roy.ander...@gmail.com wrote:

 Hey all,

 We have a very robust Oracle and SQL Server presence at work but we're
 looking to farm out some of the load to PostgreSQL to limit costs. I'm
 curious if there are any DBAs out there who have gone down this route
 before. Any tips, tricks, failures, successes, etc.? I would just like to
 hear some first-hand commentary on this topic.

 Thank you so much!

 Roy


Hi Roy,
maybe this would be helpful:

http://wiki.postgresql.org/wiki/PostgreSQL_for_Oracle_DBAs
http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion
http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Oracle

regards
Szymon

Re: [GENERAL] Oracle to PostgreSQL transition?

[Michael Paquier]

On Thu, Apr 4, 2013 at 11:16 PM, Roy Anderson roy.ander...@gmail.comwrote:

 Hey all,

 We have a very robust Oracle and SQL Server presence at work but we're
 looking to farm out some of the load to PostgreSQL to limit costs. I'm
 curious if there are any DBAs out there who have gone down this route
 before. Any tips, tricks, failures, successes, etc.? I would just like to
 hear some first-hand commentary on this topic.

Here is a tool that can be used to migrate an Oracle DB into a Postgres DB:
https://github.com/darold/ora2pg

Hope it is useful.
-- 
Michael

Re: [GENERAL] Oracle to PostgreSQL transition?

[Robert Treat]

On Fri, Apr 5, 2013 at 2:12 AM, Szymon Guz mabew...@gmail.com wrote:
 On 4 April 2013 16:16, Roy Anderson roy.ander...@gmail.com wrote:

 Hey all,

 We have a very robust Oracle and SQL Server presence at work but we're
 looking to farm out some of the load to PostgreSQL to limit costs. I'm
 curious if there are any DBAs out there who have gone down this route
 before. Any tips, tricks, failures, successes, etc.? I would just like to
 hear some first-hand commentary on this topic.

 Thank you so much!

 Roy


 Hi Roy,
 maybe this would be helpful:

 http://wiki.postgresql.org/wiki/PostgreSQL_for_Oracle_DBAs
 http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion
 http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Oracle


Yeah, it's worth reading through those links to get an idea of things;
you'll find much less literature (and tools) for MSSQL, but the
principals are mostly the same. One thing to decide on is if you are
going to port applications wholesale, or try to run some kind of
hybrid oracle/mssql - postgres mix. If it's the latter, you'll
probably need to write your own tools; at least we've always done that
as we've never found anything that worked with both Oracle and MSSQL
into Postgres reliably; but really it shouldn't be too difficult;
basically just ETL or some home brew replication scripting to glue
things together.

Robert Treat
conjecture: xzilla.net
consulting: omniti.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL transition?

[Shaun Thomas]

On 04/04/2013 09:16 AM, Roy Anderson wrote:


We have a very robust Oracle and SQL Server presence at work but we're
looking to farm out some of the load to PostgreSQL to limit costs.


You can do what we did. EnterpriseDB has built-in Oracle compatibility 
through a series of plugins, patches, and wrappers. You can generally 
convert with only a few small changes.


From there, you can take your time converting to PG syntax and go 
through more iterations in your dev and staging environments.


If it's early enough in the lifetime of your platform, you could also 
just convert everything at once. That's not always an option for 
everyone though. :)


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL transition?

[Joshua D. Drake]

On 04/05/2013 08:03 AM, Robert Treat wrote:


On Fri, Apr 5, 2013 at 2:12 AM, Szymon Guz mabew...@gmail.com wrote:

On 4 April 2013 16:16, Roy Anderson roy.ander...@gmail.com wrote:


Hey all,

We have a very robust Oracle and SQL Server presence at work but we're
looking to farm out some of the load to PostgreSQL to limit costs. I'm
curious if there are any DBAs out there who have gone down this route
before. Any tips, tricks, failures, successes, etc.? I would just like to
hear some first-hand commentary on this topic.

Thank you so much!


Yes, we have done this for customers on more than one occasion. The 
single biggest tip I can give you is to remember that PostgreSQL is not 
Oracle. You are porting to PostgreSQL, don't expect it to act like 
Oracle. As long as you keep that in mind, and you aren't running RAC 
things are pretty straightforward. They can also be quite a bit easier 
(and cheaper) to get a lot of external functionality such as replication.


Sincerely,

Joshua D. Drake

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC
@cmdpromptinc - 509-416-6579


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL transition?

[Martín Marqués]

2013/4/5 Robert Treat r...@xzilla.net:

 Yeah, it's worth reading through those links to get an idea of things;
 you'll find much less literature (and tools) for MSSQL, but the
 principals are mostly the same. One thing to decide on is if you are
 going to port applications wholesale, or try to run some kind of
 hybrid oracle/mssql - postgres mix. If it's the latter, you'll
 probably need to write your own tools; at least we've always done that
 as we've never found anything that worked with both Oracle and MSSQL
 into Postgres reliably; but really it shouldn't be too difficult;
 basically just ETL or some home brew replication scripting to glue
 things together.

I gave some training a few years ago for devs that were migrating from
SQLServer to PostgreSQL and they had a bunch of SP, some of easy
resolution, and others that where impossible with PG functions written
in plpgsql. They used CURSORS a lot to simulate what they did with
SQLServer (there was a better solution, but time was crucial, and they
decided for that approach)


--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Oracle to PostgreSQL replication with Goldengate

[Jayadevan M]

Hello all,
GoldenGate added PostgreSQL as a target database for replication. I tried
setting it up, and not finding any tutorial, put together a how to here -
http://jayadevanm.wordpress.com/2012/11/07/goldengate-replication-from-oracle-to-postgresql/
I think near real-time replication might have quite a few cases - such as
trying out the application on PostgreSQL before really cutting over from an
Oracle database, may be running reports off PostgreSQL and so on.
Regards,
Jayadevan

[GENERAL] Oracle to PostgreSQL

[Greenhorn]

Hi,

I have almost 1300 files from Oracle (no access to oracle server).  I
have to create the tables and data as follows.

-- start script

-- file name:  aa_asset_type.sql

CREATE TABLE AS_ASSET_TYPE
(
  MAIN_TYPE CHAR(3) NOT NULL,
  SUB_TYPE  NUMBER(3)   NOT NULL,
  DESCRIPTION   VARCHAR2(25)DEFAULT ' '
   NOT NULL,
  SERVICE_SCHEDULE  VARCHAR2(8) DEFAULT ' '
   NOT NULL,
  PRODUCT_CODE  CHAR(4) DEFAULT ' '
   NOT NULL
);

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON
GENADMIN.AS_ASSET_TYPE TO PUBLIC;

INSERT INTO AS_ASSET_TYPE ( MAIN_TYPE, SUB_TYPE, DESCRIPTION, SERVICE_SCHEDULE,
PRODUCT_CODE ) VALUES (
'000', 0, ' ', ' ', '');
INSERT INTO AS_ASSET_TYPE ( MAIN_TYPE, SUB_TYPE, DESCRIPTION, SERVICE_SCHEDULE,
PRODUCT_CODE ) VALUES (
'001', 0, ' ', ' ', '');
INSERT INTO AS_ASSET_TYPE ( MAIN_TYPE, SUB_TYPE, DESCRIPTION, SERVICE_SCHEDULE,
PRODUCT_CODE ) VALUES (
'100', 0, 'BUILDINGS', ' ', '');

-- file name:  as_master.sql

CREATE TABLE AS_MASTER
(
  ASSET_NO VARCHAR2(16) DEFAULT ' '
   NOT NULL,
  FUND CHAR(1)  NOT NULL,
  FINAL_ACCOUNT_NO NUMBER(6)NOT NULL,
  ITEM_NO  NUMBER(6)NOT NULL,
  SUB_NO   NUMBER(6)NOT NULL,
  TYPE NUMBER(6)DEFAULT 0
   NOT NULL,
  SUB_TYPE NUMBER(6)DEFAULT 0
   NOT NULL,
  LOCATION NUMBER(6)DEFAULT 0
   NOT NULL,
  SUB_LOCATION NUMBER(6)DEFAULT 0
   NOT NULL,
  LEVEL_A  VARCHAR2(4)  DEFAULT ' '
   NOT NULL,
  LEVEL_B  VARCHAR2(4)  DEFAULT ' '
   NOT NULL,
  LEVEL_C  VARCHAR2(4)  DEFAULT ' '
   NOT NULL,
  LEVEL_D  VARCHAR2(4)  DEFAULT ' '
   NOT NULL,
  LEVEL_E  VARCHAR2(4)  DEFAULT ' '
   NOT NULL,
  OPENING_ACQ_VALUENUMBER(15,2) DEFAULT 0
   NOT NULL,
  OPENING_ACQ_QTY  NUMBER(12)   DEFAULT 0
   NOT NULL,
  WITHDRAWAL_VALUE NUMBER(15,2) DEFAULT 0
   NOT NULL,
  WITHDRAWAL_QTY   NUMBER(12)   DEFAULT 0
   NOT NULL,
  PROFIT_LOSS_VALUENUMBER(15,2) DEFAULT 0
   NOT NULL,
  DEPREC_VALUE NUMBER(15,2) DEFAULT 0
   NOT NULL,
  PROFIT_LOSS_YTD  NUMBER(15,2) DEFAULT 0
   NOT NULL,
  DEPREC_YTD   NUMBER(15,2) DEFAULT 0
   NOT NULL,
  DEPREC_LAST_MONTHNUMBER(6)DEFAULT 0
   NOT NULL,
  DEPREC_LAST_YEAR NUMBER(6)DEFAULT 0
   NOT NULL,
  DEPREC_METHODCHAR(1)  DEFAULT ' '
   NOT NULL,
  DEPREC_RATE_2NUMBER(15,6) DEFAULT 0
   NOT NULL,
  DEPREC_ROUND_FACTOR  NUMBER(6)DEFAULT 0
   NOT NULL,
  DEPREC_FREQUENCY CHAR(1)  DEFAULT ' '
   NOT NULL,
  DEPREC_MINIMUM_VAL   NUMBER(12)   DEFAULT 0
   NOT NULL,
  DEPREC_TYPE  NUMBER(6)DEFAULT 0
   NOT NULL,
  DEPREC_FOLIO_DR  VARCHAR2(8)  DEFAULT ' '
   NOT NULL,
  DEPREC_FOLIO_CR  VARCHAR2(8)  DEFAULT ' '
   NOT NULL,
  INSURANCE_VALUE  NUMBER(12)   DEFAULT 0
   NOT NULL,
  INSURANCE_CATEGORY   VARCHAR2(2)  DEFAULT ' '
   NOT NULL,
  INSURANCE_POLICY VARCHAR2(10) DEFAULT ' '
   NOT NULL,
  INSURANCE_CODE   CHAR(1)  DEFAULT ' '
   NOT NULL,
  ASSET_FILE_NOVARCHAR2(10) DEFAULT ' '
   NOT NULL,
  PROPERTY_REF VARCHAR2(20) DEFAULT ' '
   NOT NULL,
  LAST_AUDIT_DATE  DATE NULL,
  LAST_AUDIT_PAGE  NUMBER(6)DEFAULT 0
   NOT NULL,
  DESCRIPTION_1VARCHAR2(40) DEFAULT ' '
   NOT NULL,
  DESCRIPTION_2VARCHAR2(40) DEFAULT ' '
   NOT NULL
);


GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON
GENADMIN.AS_MASTER TO PUBLIC;

ALTER TABLE GENADMIN.AS_MASTER ADD (
  PRIMARY KEY
 (ASSET_NO));

INSERT INTO AS_MASTER ( ASSET_NO, FUND, FINAL_ACCOUNT_NO, ITEM_NO,
SUB_NO, TYPE, SUB_TYPE,
LOCATION, SUB_LOCATION, LEVEL_A, LEVEL_B, LEVEL_C, LEVEL_D, LEVEL_E,
OPENING_ACQ_VALUE,
OPENING_ACQ_QTY, WITHDRAWAL_VALUE, WITHDRAWAL_QTY, PROFIT_LOSS_VALUE,
DEPREC_VALUE, PROFIT_LOSS_YTD,
DEPREC_YTD, DEPREC_LAST_MONTH, DEPREC_LAST_YEAR, DEPREC_METHOD,
DEPREC_RATE_2, DEPREC_ROUND_FACTOR,
DEPREC_FREQUENCY, DEPREC_MINIMUM_VAL, DEPREC_TYPE, DEPREC_FOLIO_DR,
DEPREC_FOLIO_CR,
INSURANCE_VALUE, INSURANCE_CATEGORY, INSURANCE_POLICY, INSURANCE_CODE,
ASSET_FILE_NO, PROPERTY_REF,
LAST_AUDIT_DATE, LAST_AUDIT_PAGE, DESCRIPTION_1, 

Re: [GENERAL] Oracle to PostgreSQL

[John R Pierce]

Greenhorn wrote:

Unfortunately When I execute the above files using \i I am getting errors like

ERROR:  syntax error at or near (
LINE 4:   SUB_TYPE  NUMBER(3)   NOT NULL,

Obviously 'number' is not PostgreSQL data type so is 'varchar2'.  What
is your suggestion to create these tables in PostgreSQL?  Should I
pre-process using Sed/Awk to find/replace the field type first?  Any
recommendation is greatly appreciated :)
  


change number to numeric, and varchar2 to char and you'll likely be good.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL

[Thomas Kellerer]

John R Pierce wrote on 16.03.2009 00:41:

Greenhorn wrote:
Unfortunately When I execute the above files using \i I am getting 
errors like


ERROR:  syntax error at or near (
LINE 4:   SUB_TYPE  NUMBER(3)   NOT NULL,

Obviously 'number' is not PostgreSQL data type so is 'varchar2'.  What
is your suggestion to create these tables in PostgreSQL?  Should I
pre-process using Sed/Awk to find/replace the field type first?  Any
recommendation is greatly appreciated :)
  


change number to numeric, and varchar2 to char and you'll likely be good.




My guess would have been that varchar is the equivalent to varchar2

char does a blank padding and is different in behaviour to varchar (as it is in 
Oracle)


Thomas





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL

[John R Pierce]

Thomas Kellerer wrote:


change number to numeric, and varchar2 to char and you'll likely be 
good.





My guess would have been that varchar is the equivalent to varchar2

char does a blank padding and is different in behaviour to varchar (as 
it is in Oracle)



geez, yeah, what you said!BTDT, and had the t-shirt.i think ill 
have another beer now.





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Blazej]

There is some my publications about SART AML System, where is more
detailed described all this things (and more) that I wrote below.

http://www.analyticsql.org/documentation.html

Regards,
Blazej Oleszkiewicz

2008/9/4 Blazej [EMAIL PROTECTED]:
 Hi Artacus

 2008/9/4 Artacus [EMAIL PROTECTED]:
 Oh, as I was writing a CUBE query today and realized that I forgot to
 mention this. And unlike most gripes, like MERGE INTO or CTE's which are
  really convenience things, this is key piece of functionality that you just
 can't reproduce in Postgres.


 It is not true. First look at this:
 http://www.analyticsql.org/

 In atachment is two snapshots from production system (Analytic SQL
 Server based on PostgreSQL 8.3):

 Picture: non_uniform_dimension.JPG

 Heterogeneous data warehouse dimensions where each class of dimension:
 Chart of Account (PK), Synthetic Account (KS), Analytical Account (KA)
 may have a heterogeneous structure of the hierarchy (~60 000 items in
 hierarchy, max depth 9).

 For example, on picture marked heterogeneity of the hierarchy:
 -- Green: [PK, KS operations involving cash and interbank
 operations, KS Kasa, KS Cash / banknotes and coins / KA Cash in
 hand GBP]
 -- Red: [PK, KS operations involving cash and interbank operations,
 KS Kasa, KS bankers' cheques, KS foreign bankers' cheques, KS
 bankers' cheques, foreign currency, KA bankers' cheques in USD ]

 Hence, we can see that both hierarchical structure:
 -- [PK, KS, KS, KS, KA] for a KA Cash in hand GBP (ID: 339 in column KNT_ID);
 -- [PK, KS, KS, KS, KS, KS, KA] for a KA foreign bankers' cheques in
 U.S. dollars (ID: 363 in column KNT_ID)

 are different from each other in terms of the structure of the hierarchy.

 Picture:olap_raport_non_uniform_dimension.JPG - example of OLAP raport
 based on Chart of Account.

 so it is much more then OLAP ORACLE extensions like CUBE etc.

 Regards,
 Blazej Oleszkiewicz


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Casey Allen Shobe]

On Sep 15, 2008, at 6:58 AM, David Fetter wrote:

Roles,


We have 'em.


We do NOT have secure application roles or anywhere near the level of  
configurability in security aspects as Oracle.  We've got a great  
foundation, but we lack a lot of fine-grained granularity (e.g. an  
Oracle SAR can allow a role to execute a particular function based on  
the result of another function call or query, which has rather a lot  
of possibilities - consider grant connect on database to staff when  
hour_of_day () between 9 and 6; also consider row-level and column- 
level and even field-level access controls).


It's complicated in Oracle, but there's a lot of possibilities there  
that we simply cannot reproduce.  But this could be extended one day. :)


Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Asko Oja]

On Wed, Sep 24, 2008 at 11:13 PM, Casey Allen Shobe [EMAIL PROTECTED]wrote:

 On Sep 15, 2008, at 6:58 AM, David Fetter wrote:

 Roles,


 We have 'em.


 We do NOT have secure application roles or anywhere near the level of
 configurability in security aspects as Oracle.  We've got a great
 foundation, but we lack a lot of fine-grained granularity (e.g. an Oracle
 SAR can allow a role to execute a particular function based on the result of
 another function call or query, which has rather a lot of possibilities


but why would you put part of your business logic into some configuration
tables while you could keep it in your own functions


 - consider grant connect on database to staff when hour_of_day () between 9
 and 6; also consider row-level and column-level and even field-level access
 controls).

 It's complicated in Oracle, but there's a lot of possibilities there that
 we simply cannot reproduce.  But this could be extended one day. :)


I see it as a strong side of PostgreSQL that we have not bloated our code
with all this fancy micromanagement that seems too complex to be useful
anyway considering that quality of Oracle database management tools :)

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

-- 

 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Andrew Sullivan]

On Thu, Sep 25, 2008 at 01:13:29PM +0300, Asko Oja wrote:
 
 but why would you put part of your business logic into some configuration
 tables while you could keep it in your own functions

Because the parameters of the business logic should not be in the
code.  The parameters should be part of the configuration, to be
administered by the administrators (i.e. the DBAs) and not by the
database developers.  In traditional large database shops, that is the
division of responsibility, and the inability to work in that way will
hamper Postgres adoption in that environment.  (Maybe we don't care,
but let's at least be honest that changing the culture of such
database shops is not something we're going to achieve quickly.)

A

-- 
Andrew Sullivan
[EMAIL PROTECTED]
+1 503 667 4564 x104
http://www.commandprompt.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Casey Allen Shobe]

On Sep 15, 2008, at 6:58 AM, David Fetter wrote:

Roles,


We have 'em.


We do NOT have secure application roles or anywhere near the level of  
configurability in security aspects as Oracle.  We've got a great  
foundation, but we lack a lot of fine-grained granularity (e.g. an  
Oracle SAR can allow a role to execute a particular function based on  
the result of another function call or query, which has rather a lot  
of possibilities - consider grant connect on database to staff when  
hour_of_day () between 9 and 6; also consider row-level and column- 
level and even field-level access controls).


It's complicated in Oracle, but there's a lot of possibilities there  
that we simply cannot reproduce.  But this could be extended one day. :)


Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Casey Allen Shobe]

On Sep 4, 2008, at 7:40 PM, Robert Treat wrote:
It is not as simple as Oracles database link syntax. Setting up a  
connection
involves a couple of sql looking commands, and once you setup a  
connection to
a remote database, you can reference a table with something like  
select *
from [EMAIL PROTECTED]  There's no way a function oriented solution  
can

match that imho.


I have long thought that what would be really useful is a standard way  
for third-party modules to extend or override the SQL language support  
within PostgreSQL itself without needing to be integrated in core.


E.g. it should be possible for all of EnterpriseDB's Oracle-compatible  
SQL changes to exist as a separate module, somebody could change the  
behavior of a select to default ordering to imitate Oracle etc.  It  
should be possible for a replication engine to add syntax for options  
specific to it.  Contrib modules like dblink could install SQL-like  
command support.


This would be both invaluable for compatibility efforts and probably  
raise the amount of 3rd party stuff that actually gets used  
(currently, many places I've seen avoid Slony because they fear having  
to use the commandline scripts it comes with, and if you want to  
manipulate Slony from the database itself, oftentimes this means you  
have to use pl/perlu or another untrusted language.


Don't get me wrong, functions are great too. :)  But currently the  
above means that a lot of risk is introduced and you have to put a lot  
of faith in the perl code - an exploit poses a lot of risk.  If Slony  
exposed it's own data to PG via custom SQL extensions, this would be  
more secure by design.


Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Casey Allen Shobe]

On Sep 1, 2008, at 12:42 AM, Henry wrote:
This is /finally/ being addressed, although (very) belatedly.  The  
Pg core
dev team always argued that replication was an add-on and should not  
form

part of the core (ie, similar nonsense excuses the MySQL team used for
add-ons such as triggers, etc).


I believe the developer stance is more the same than you seem to  
imagine.  The upcoming developments allow replication utilities to tie  
in at a deeper and more effective level, and with that new replication  
solutions will come along.  But I do not think there is any goal to  
implement a single replication solution within core and not support  
external solutions.


The point of the PostgreSQL developer stance is that until something  
can be done correctly, even if it's a lot more work, it's sometimes  
better not to do at all.  It was recognized early on that if we tried  
to figure out the replication puzzle ourself, it would invariably be  
complex and never ideally suited for every situation.  It would cost a  
lot of resources that the team really needed to spend elsewhere at the  
time.


MySQL's stance on things like triggers and subselects and so on is  
*not* that at all.  They recognize that a proper implementation would  
be complicated and take a lot of time, so they strongly want to avoid  
it, and make lame excuses a lot.  When they do finally get around to  
implementing something, they have traditionally done it in a broken or  
lazy way - e.g. you cannot have two triggers on the same type of  
action on the same table, instead you must write a wrapper function  
that calls other functions; subselects are always evaluated  
independently meaning they usually equate to horribly slow, there's  
a lot of bugs, etc.


I prefer the way PostgreSQL development has been going, personally. :)

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Asko Oja]

On Thu, Sep 25, 2008 at 3:52 PM, Andrew Sullivan [EMAIL PROTECTED]wrote:

 On Thu, Sep 25, 2008 at 01:13:29PM +0300, Asko Oja wrote:
 
  but why would you put part of your business logic into some configuration
  tables while you could keep it in your own functions

 Because the parameters of the business logic should not be in the
 code.  The parameters should be part of the configuration, to be
 administered by the administrators (i.e. the DBAs) and not by the
 database developers.  In traditional large database shops, that is the
 division of responsibility, and the inability to work in that way will
 hamper Postgres adoption in that environment.  (Maybe we don't care,
 but let's at least be honest that changing the culture of such
 database shops is not something we're going to achieve quickly.)


Well by configuration tables i meant some oracle/postgresql system tables.
We also have parameters of business logic in configuration database that is
replicated into each oltp database that needs them and they are updated by
dba's during normal release process. Althou this part is managed by DBA's
the changes themselves are prepared by developers. So i see no PostgreSQL
ability to work that way.  What i see is lack of useless bells and whistles
in PostgreSQL and i like it.

regards,
Asko

Re: [GENERAL] Oracle and Postgresql

[Casey Allen Shobe]

On Sep 25, 2008, at 3:13 AM, Asko Oja wrote:
but why would you put part of your business logic into some  
configuration tables while you could keep it in your own functions


Because as bad as my Not Invented Here syndrome might be at times, I  
know that I would not be able to alone build as elegant of a system as  
the community could together, which would be more adaptable and  
generic.  Things tend not to make it into PostgreSQL releases until  
they are very solid.


Then instead of worrying about whether or not my function code is  
good, I can rely on the PostgreSQL-provided foundation, using things  
in a standard, supported way.  If there is a bug, it's found, fixed,  
and the whole community benefits.  Many people could benefit from SAR  
support, so why make them all spend time reimplementing thigs?



There is a much, much more important reason for this though, which is  
that I can only extend security functionality by adding additional  
restrictions within my function call.  The SAR stuff in Oracle is  
superior to this because it affects *all use of the database*, not  
just function calls.  I don't put much value in security through  
obscurity - sorry.


To an extent we *can* simulate row-level and column-level security  
through the use of very restricted data tables and more generally- 
available or specific-purpose views, but we cannot make PostgreSQL  
call a custom function to determine from it's output whether or not it  
should allow a particular action.


I see it as a strong side of PostgreSQL that we have not bloated our  
code with all this fancy micromanagement that seems too complex to  
be useful anyway considering that quality of Oracle database  
management tools :)


Lack of bloat is a strong point.  Bloat is more the result of putting  
things in without enough forethought, and then needing to maintain  
compatibility with old stuff even after you add better - the  
maintenance of the old code interfering with the time/quality put into  
the new, etc.  It's also a question of how well they are implemented.   
If 0.1% of the user base will use a feature, it's probably not worth  
adding.  If 10% will use it, but it's implementation requires added  
overhead for the 90% of others who don't - that's bloat.  If 10% will  
use it, and it doesn't cost anything to those who don't, it's worth  
doing.


I would like to clarify that I'm not saying anything like PostgreSQL  
needs / should have SAR support in this thread, although I would  
personally find them handy.  I just wanted to clarify that what  
roles means to an Oracle DBA is a lot different from what it means  
to a PostgreSQL DBA.


Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Casey Allen Shobe]

On Sep 15, 2008, at 1:04 PM, Christophe wrote:
More seriously, this is the issue with code-encryption on an open  
source platform: Where do you keep the key?  From my (admittedly  
brief) research, it appears that Oracle bakes it into the server  
binary, which isn't going to work for PG.


Just because Oracle implements something poorly doesn't mean it's the  
only way.  I don't know what Oracle actually does, but I wouldn't put  
much faith in the safety of code protection if that's the way they do  
it, because an Oracle employee in the right position could easily  
disclose the key one day.


If this functionality were to be implemented, the proper way to do it  
would be to require a key file stored on the server or maybe within  
postgresql.conf.  Users who wish to use this functionality could be  
required to create this by hand, or it could be autogenerated at  
initdb time.


I don't find this functionality useful, but I also don't think that  
it's completely worthless.  There are enterprises with very different  
needs and perspectives.


Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Casey Allen Shobe]

On Sep 15, 2008, at 1:15 PM, Scott Marlowe wrote:

But whether it's oracle or postgresql, this is just security through
obscurity. If you have root access on the server either method would
be trivial to hack.


You just contradicted yourself.  If you have root access on the server  
all bets are off, period.  No database security at all applies any  
longer.  Even just non-privileged read access to the files is  
sufficient.


Database-level security only pertains to accesses within the database,  
and for this reason, it would not be security through obscurity,  
unless you could work around it by querying the catalog tables as a  
non-superuser.


Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Martin Gainty]

depends on Postgres support for Oracle java packages which is now available 
thru PL/Java
http://my.safaribooksonline.com/0672327562/ch19lev1sec1

Martin 
__ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business 
of Sender. This transmission is of a confidential nature and Sender does not 
endorse distribution to any party other than intended recipient. Sender does 
not necessarily endorse content contained within this transmission. 


 From: [EMAIL PROTECTED]
 To: pgsql-general@postgresql.org
 Subject: Re: [GENERAL] Oracle and Postgresql
 Date: Thu, 25 Sep 2008 11:15:24 -0700
 
 On Sep 4, 2008, at 7:40 PM, Robert Treat wrote:
  It is not as simple as Oracles database link syntax. Setting up a  
  connection
  involves a couple of sql looking commands, and once you setup a  
  connection to
  a remote database, you can reference a table with something like  
  select *
  from [EMAIL PROTECTED]  There's no way a function oriented solution  
  can
  match that imho.
 
 I have long thought that what would be really useful is a standard way  
 for third-party modules to extend or override the SQL language support  
 within PostgreSQL itself without needing to be integrated in core.
 
 E.g. it should be possible for all of EnterpriseDB's Oracle-compatible  
 SQL changes to exist as a separate module, somebody could change the  
 behavior of a select to default ordering to imitate Oracle etc.  It  
 should be possible for a replication engine to add syntax for options  
 specific to it.  Contrib modules like dblink could install SQL-like  
 command support.
 
 This would be both invaluable for compatibility efforts and probably  
 raise the amount of 3rd party stuff that actually gets used  
 (currently, many places I've seen avoid Slony because they fear having  
 to use the commandline scripts it comes with, and if you want to  
 manipulate Slony from the database itself, oftentimes this means you  
 have to use pl/perlu or another untrusted language.
 
 Don't get me wrong, functions are great too. :)  But currently the  
 above means that a lot of risk is introduced and you have to put a lot  
 of faith in the perl code - an exploit poses a lot of risk.  If Slony  
 exposed it's own data to PG via custom SQL extensions, this would be  
 more secure by design.
 
 Cheers,
 --
 Casey Allen Shobe
 Database Architect, The Berkeley Electronic Press
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

_
Stay up to date on your PC, the Web, and your mobile phone with Windows Live.
http://clk.atdmt.com/MRT/go/msnnkwxp1020093185mrt/direct/01/

Re: [GENERAL] Oracle and Postgresql

[Casey Allen Shobe]

On Sep 15, 2008, at 2:40 PM, Scott Marlowe wrote:

Like MySQL has built in replication.


You know, I hear this particular example about MySQL's replication  
implementation a lot against any sort of new feature, and it's  
important to recognize the difference here.


Replication is *not* a function of the database, it's a function of  
the network/cluster.  For this reason, it's entirely appropriate to  
not build it into the core binary.  It's entirely a different matter  
when it is core database functionality being discussed.  Modular  
programming exists for a reason.


Similarly, it has been argued that psql -l and things like  
(php)pgadmin should not show databases you cannot log in to in MySQL  
style.  For many users, this may not matter, but I've done shared  
hosting before, and it matters a lot there - not for any good reason  
so much as the fact that every client gets concerned and complains  
about being able to see the existence of others' databases and vice  
versa.  However once logged in, you can get at this information by  
querying the catalog, so that *is* security through obscurity.   
However, if the catalog workaround were also addressed, perhaps with  
row-level access control, this would have the same effect and *not* be  
security through obscurity.


People love throwing the security through obscurity and bloat  
terms around when discussing new features, but these are really more a  
question about how and where the implementation is done than it is  
about the feature itself.


Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Casey Allen Shobe]

On Sep 15, 2008, at 7:19 PM, Tom Lane wrote:
The problem is that the people who ask for this type of feature are  
usually

imagining that they can put their code on customer-controlled machines
and it will be safe from the customer's eyes.


That's a broken expectation.  All that can realistically be expected  
is database/catalog-level constraints.


Well, it isn't, and I don't think Postgres should encourage them to  
think it is.


Adding such a feature would NOT be encouraging them to think this -  
the documentation could be very explicit about this fact.  Maybe  
that's what Oracle is selling, and that's crappy of them, but that  
doesn't mean we should use that as justification to not add a more  
appropriate implementation.


As for the expectation above - could pl/pgsql be made compilable?  It  
would seem easy to translate pl/pgsql code into C and compile a C  
function.  That *could* go onto customer-controlled machines and be  
safe from the customer's eyes.



FWIW, I think most people who want to hide code aren't concerned about  
IP, they're concerned about clients seeing embarrassingly bad/sloppy  
code.  But there *are* some very real and legitimate needs for this,  
though it's a small minority of those who think they do.



Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Bruce Momjian]

Casey Allen Shobe wrote:
 To an extent we *can* simulate row-level and column-level security  
 through the use of very restricted data tables and more generally- 
 available or specific-purpose views, but we cannot make PostgreSQL  
 call a custom function to determine from it's output whether or not it  
 should allow a particular action.

We were just talking today about adding column and row-level security to
Postgres 8.4:

http://archives.postgresql.org/pgsql-hackers/2008-09/msg01654.php

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[David Fetter]

On Thu, Sep 25, 2008 at 01:05:26PM -0700, Casey Allen Shobe wrote:
 On Sep 15, 2008, at 7:19 PM, Tom Lane wrote:
 The problem is that the people who ask for this type of feature are
 usually imagining that they can put their code on
 customer-controlled machines and it will be safe from the
 customer's eyes.

 That's a broken expectation.  All that can realistically be expected
 is database/catalog-level constraints.

It's far from clear that those offer protection of any reasonable kind.

 Well, it isn't, and I don't think Postgres should encourage them to  
 think it is.

 Adding such a feature would NOT be encouraging them to think this - the 
 documentation could be very explicit about this fact.  Maybe that's what 
 Oracle is selling, and that's crappy of them, but that doesn't mean we 
 should use that as justification to not add a more appropriate 
 implementation.

You've got the burden of proof exactly backwards there.  It's on you
or anyone who cares to to explain why it might be a good idea to add
this feature, understanding that every feature has a maintenance
cost and is a potential source of bugs.

 As for the expectation above - could pl/pgsql be made compilable?
 It  would seem easy to translate pl/pgsql code into C and compile a
 C  function.  That *could* go onto customer-controlled machines and
 be safe from the customer's eyes.

No, it would not.  As many others have mentioned, strings does a
pretty good job on such stuff, let alone the impossibility even in
theory of hiding what a program does from someone with access to run
it using arbitrary inputs, even when they have no binary to examine.

 FWIW, I think most people who want to hide code aren't concerned about  
 IP, they're concerned about clients seeing embarrassingly bad/sloppy  
 code.  But there *are* some very real and legitimate needs for this,  
 though it's a small minority of those who think they do.

Please elucidate those needs in detail, then explain why it might be
PostgreSQL's job to meet them.

Cheers,
David.
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Christophe]

On Sep 25, 2008, at 1:05 PM, Casey Allen Shobe wrote:

As for the expectation above - could pl/pgsql be made compilable?


Without getting into the argument as to the level of security  
provided, it strikes me that a reasonable approach would be a non- 
core pluggable language which accepts encrypted strings as functions,  
decrypts them (using a key compiled into the language module), and  
passes them on to PL/pgSQL for execution.  This would keep the  
functionality out of core, allow the developer who distributes the  
code to plop in their own key and distribute the code as a compiled  
module, and minimizes reimplementation.


This would, of course, be easily hacked with someone who can step  
through the language module with a debugger, but I don't see any  
reasonable way of preventing someone with that level of access from  
breaking the code without OS-level support.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Casey Allen Shobe]

On Sep 16, 2008, at 6:39 AM, Jonathan Bond-Caron wrote:

After some research, I found this article that I believe will make a
stronger use case:
http://www.iosn.net/network/news/Managing%20the%20insider%20threat%20through
%20code%20obfuscation


I can tell without even clicking the link that it would be a waste of  
time to read.


The only way to manage internal threats are to have a good security  
policy that limits what each person can do to what they need to do,  
and having backups.


If I work as a developer or DBA and cannot immediately go looking at  
code when there's a problem, it's very detrimental to productivity.   
Gee, I wonder why companies that support these antics grow to insane  
sizes of employees?  Having access to a copy of the code should not be  
a security threat in any way.  If it is, then the underlying  
infrastructure/security policy is broken.  Not having visibility into  
what unrelated code does leads to crappy, bloated code.  For instance,  
I was updating the SQL in a class method the other day.  Technically,  
I don't require any visibility into the calling code, and the above  
page probably advocates that it would be hidden from me.  But I noted  
that the database results were coming back in an ordered fashion, and  
being returned as an unordered hash.  I did some grepping through the  
rest of the codebase, and found that the callers then did their own  
sort in perl to reproduce the lost database sorting.  So I was able to  
easily fix more than required and improve overall efficiency.  With a  
code obfuscation approach, this would be impossible and the current  
code would be more bloated and slow.


My previous employer has some ~100 PostgreSQL servers serving up one  
of the most popular websites in the world.  The only thing standing  
between me and complete destruction of all that data is a wifi  
password (WEP even, because that's more convenient than requiring  
folks to have WPA2 support) because convenience is the first priority  
and any security architects are lazy or nonexistent (in the latter  
case, the responsibility lands on sysadmins, which does not work out  
well in most cases).  In than environment, they give postgres user  
trust access to anyone on the production network, and then make the  
office a part of the production network via a permanent VPN link (it's  
a VPN, so it must be secure, right?) to enable the developers to work  
easier (like many places, they don't have a completely isolated dev  
environment).


I've seen a lot of PostgreSQL environments in the last few years, and  
every single one of them connects to the database as the postgres  
user or another superuser, requires ability to execute DDL, and all  
sorts of other gaping security issues, and I cannot think of the last  
time I heard somebody else in a work setting speak of a GRANT that was  
not a GRANT ALL, even when all that is needed is select access, for  
example.


Companies need to fix their security architecture and keep production  
isolated from development, not go down the obfuscation rathole.  If  
they do that and still have a threat that they think code obfuscation  
would help manage, I'd love to see it.


Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Casey Allen Shobe]

On Sep 24, 2008, at 8:05 AM, David Fetter wrote:

C is not magic obfuscation gear.  Anybody with a debugger can expose
what it's doing.


Yes, but you don't get original code, comments, etc. and it takes a  
lot of effort to refine it back down into something maintainable.   
People looking to protect IP are often not looking to prevent the same  
functionality from being executed elsewhere, but to prevent somebody  
from taking the results of their hard work with minimal effort (think  
of Microsoft and the FreeBSD TCP/IP stack).  If it costs the  
competition a lot of time to decompile code and then rebuild  
maintainable code out of it that probably doesn't take all the same  
things into consideration and will lead to difficulties keeping up  
with new features, that's adequate protection.


Of course, there are idiots out there who think that not making pl/ 
pgsql code visible should protect even against root-level access and  
that compilation equals irreversible encryption, but not everyone  
using these techniques is one of those idiots, and a few do have  
pretty good reasons.  Consider for example chipmakers, who compete  
against each other selling to a very small number of clients.  A board  
maker will buy whatever does the job well at lowest cost, and the cost  
associated with creating these is purely development time.  If you put  
months into making a really fast/efficient chip to do a specific task,  
protecting this is very important, or you go out of business.  This is  
much the opposite of a business that provides a service.


Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Casey Allen Shobe]

On Sep 24, 2008, at 6:12 PM, Scott Ribe wrote:

the sort of person who
thinks re-using someone else's undocumented code is easier than
writing it from scratch is probably not going to be able to learn  
the code

via debugging tools.


There are two distinct extremes here, and I think most people on the  
list are more familiar with the former.


The first is where you build a big system/application, and any code  
you do go out and get from the OSS community or by stealing or  
whatever just does some small function within your overall  
architecture.  Maintainability of any acquired code is important here.


The second is where you release a product that is never updated, and  
designed for a specific purpose.  If somebody else can produce an  
equivalent product without the associated development time, even  
without any understanding, through use of theft, it can bring down the  
original business.  Maintainability does NOT matter here because the  
dodgy competitor only cares to produce something functionally  
equivalent, not to have something maintainable.


Realistically, it's a pretty stupid long-term decision to use acquired  
code and screw over the original business, because then once you've  
killed the competitor or lost the ability to steal more, you no longer  
have the ability to keep up with customer demand, but many businesses  
don't care about the long-term so much as a quick buck, unfortunately.


Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Casey Allen Shobe]

On Sep 25, 2008, at 11:16 AM, Asko Oja wrote:
What i see is lack of useless bells and whistles in PostgreSQL and i  
like it.


Then you aren't paying attention very well.  PostgreSQL comes with an  
extremely rich and useful set of bells and whistles than most people  
never use, in a non-detrimental way thanks to proper implementation  
techniques.  Most people use a fraction of possible SQL techniques,  
let alone most of the awesome functions that come provided in  
pg_catalog.  But the implementation is not bloated, so you don't see  
it as a problem. :)


Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Casey Allen Shobe]

On Sep 25, 2008, at 1:14 PM, David Fetter wrote:

On Thu, Sep 25, 2008 at 01:05:26PM -0700, Casey Allen Shobe wrote:

On Sep 15, 2008, at 7:19 PM, Tom Lane wrote:

The problem is that the people who ask for this type of feature are
usually imagining that they can put their code on
customer-controlled machines and it will be safe from the
customer's eyes.


That's a broken expectation.  All that can realistically be expected
is database/catalog-level constraints.


It's far from clear that those offer protection of any reasonable  
kind.


Define protection.  If there is no effective way to query the  
catalog and see function code at the SQL level, this is a complete and  
effective form of protection - it's just not protection from somebody  
with server-level access.



You've got the burden of proof exactly backwards there.  It's on you
or anyone who cares to to explain why it might be a good idea to add
this feature, understanding that every feature has a maintenance
cost and is a potential source of bugs.


I don't personally want this feature.  But I can see where it's  
valuable in some contexts, and if the understanding is correct, it can  
be used responsibly.  People misuse basic SQL all the time, but that  
doesn't mean the basic SQL should be nonexistent or stupider.


You do have a very valid point in indicating the added maintenance  
cost of any new feature, but protecting stuff at the SQL level is not  
a complicated thing to do, and well, people concerned with this  
feature can be the ones maintaining it - there seems to be a good many  
already and existence of the feature would attract more.  Could this  
be implemented via a contrib/ module?



As for the expectation above - could pl/pgsql be made compilable?
It  would seem easy to translate pl/pgsql code into C and compile a
C  function.  That *could* go onto customer-controlled machines and
be safe from the customer's eyes.


No, it would not.  As many others have mentioned, strings does a
pretty good job on such stuff, let alone the impossibility even in
theory of hiding what a program does from someone with access to run
it using arbitrary inputs, even when they have no binary to examine.


I am not saying that C is an encryption technique.  It does however  
protect code from customer eyes.  People are often not trying to truly  
encrypt a protected algorithm, but removing maintainability, adding  
cost to theft, and hiding code that's badly done.


FWIW, I think most people who want to hide code aren't concerned  
about

IP, they're concerned about clients seeing embarrassingly bad/sloppy
code.  But there *are* some very real and legitimate needs for this,
though it's a small minority of those who think they do.


Please elucidate those needs in detail, then explain why it might be
PostgreSQL's job to meet them.


See my other posts talking about chipmakers.  We should NOT add this  
feature /for/ idiots, but the fact that idiots will inevitably end up  
misusing any feature should not be a justification for not  
implementing it.


Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press
[EMAIL PROTECTED] (email/jabber/aim/msn)
http://www.bepress.com | +1 (510) 665-1200 x163


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Andrew Sullivan]

On Thu, Sep 25, 2008 at 01:25:25PM -0700, Casey Allen Shobe wrote:

 Gee, I wonder why companies that support these antics grow to insane
 sizes of employees?

Meetings.  Lots and lots of meetings.

A

-- 
Andrew Sullivan
[EMAIL PROTECTED]
+1 503 667 4564 x104
http://www.commandprompt.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Greg Smith]

On Thu, 25 Sep 2008, Christophe wrote:

it strikes me that a reasonable approach would be a non-core pluggable 
language which accepts encrypted strings as functions, decrypts them 
(using a key compiled into the language module), and passes them on to 
PL/pgSQL for execution...This would, of course, be easily hacked with 
someone who can step through the language module with a debugger


If we presume that the module doing the encryption/decryption is itself is 
a common open-source implementation, all I have to do is read in the 
de-obfuscator code byte at a time, stopping every time I have a key length 
worth of bytes to see if they unlock something that looks like plaintext. 
You have to move to at least another layer of relatively serious security 
before you need debugger-level skills to crack it.


People routinely tear through protection like this even on closed-source 
systems that benefit some from security by obscurity, and if you can know 
the method used that usually allows an even easier approach.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Casey Allen Shobe]

On Sep 25, 2008, at 1:16 PM, Christophe wrote:
Without getting into the argument as to the level of security  
provided, it strikes me that a reasonable approach would be a non- 
core pluggable language which accepts encrypted strings as  
functions, decrypts them (using a key compiled into the language  
module), and passes them on to PL/pgSQL for execution.


The only way this could work is if the key is set at compile time.   
Using a single key is impossible in an open source product as Greg  
pointed out, and very stupid in any other.  Now I'll ignore the fact  
that you can reverse engineer the key out of compiled code, as you  
already acknowledged that - this is still problematic for another  
reason.


Let's consider the original goal of I want to keep customers, with  
full control of the server machine, from being able to see clearly  
what a function does.  In cases where you just want to keep database  
users from seeing a function code, the implementation should be easy,  
and that's the only form I see any value in adding, really.


You could add encryption properly by storing the key in an external  
file with very restrictive permissions, and that would solve the goal  
of I don't want people to be able to read function code in pg_dump  
output, etc., so it takes things a step farther.  But of course on  
customer-controlled boxes, they can read any file they want, defeating  
the encryption.


So you think ah I'll just compile it in by requiring ./configure -- 
key=whatever to compile the thing.  Well, now you suddenly have to  
build every release of PostgreSQL for every single one of those  
customers - they cannot upgrade or rebuild themselves, without losing  
all the encrypted functions.  Maybe that's something you can accept,  
but I would say that most people who want to hide code from customers  
want nothing to do with setting up the database for the customer.  In  
cases where you fully dictate the PostgreSQL build and upgrade policy  
and mandate it for your clients, this could work, but I'm guessing  
that's a pretty esoteric corner case.


Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Martin Gainty]

i would recommend
dont publish in HTML/JS as with a simple View Page Source any browser client 
can figure out what is doing what
also i would shy from Scripting macro languages as they are not compiled 
modules and anyone with a text editor can easily see your code
Functions and procedure are another story as one would need DB access to 
view..so someone who is doing 
a simple query with Toad or sqlplus suddenly sees this interesting procedure 
hmm..

can the more proprietary routines be placed in a compiled language like Java 
and then use PL/Java packaging to pull in the packages you need?
?
Martin 
__ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business 
of Sender. This transmission is of a confidential nature and Sender does not 
endorse distribution to any party other than intended recipient. Sender does 
not necessarily endorse content contained within this transmission. 


 CC: pgsql-general@postgresql.org
 From: [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Subject: Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and 
 Postgresql) 
 Date: Thu, 25 Sep 2008 16:38:18 -0700
 
 On Sep 25, 2008, at 1:16 PM, Christophe wrote:
  Without getting into the argument as to the level of security  
  provided, it strikes me that a reasonable approach would be a non- 
  core pluggable language which accepts encrypted strings as  
  functions, decrypts them (using a key compiled into the language  
  module), and passes them on to PL/pgSQL for execution.
 
 The only way this could work is if the key is set at compile time.   
 Using a single key is impossible in an open source product as Greg  
 pointed out, and very stupid in any other.  Now I'll ignore the fact  
 that you can reverse engineer the key out of compiled code, as you  
 already acknowledged that - this is still problematic for another  
 reason.
 
 Let's consider the original goal of I want to keep customers, with  
 full control of the server machine, from being able to see clearly  
 what a function does.  In cases where you just want to keep database  
 users from seeing a function code, the implementation should be easy,  
 and that's the only form I see any value in adding, really.
 
 You could add encryption properly by storing the key in an external  
 file with very restrictive permissions, and that would solve the goal  
 of I don't want people to be able to read function code in pg_dump  
 output, etc., so it takes things a step farther.  But of course on  
 customer-controlled boxes, they can read any file they want, defeating  
 the encryption.
 
 So you think ah I'll just compile it in by requiring ./configure -- 
 key=whatever to compile the thing.  Well, now you suddenly have to  
 build every release of PostgreSQL for every single one of those  
 customers - they cannot upgrade or rebuild themselves, without losing  
 all the encrypted functions.  Maybe that's something you can accept,  
 but I would say that most people who want to hide code from customers  
 want nothing to do with setting up the database for the customer.  In  
 cases where you fully dictate the PostgreSQL build and upgrade policy  
 and mandate it for your clients, this could work, but I'm guessing  
 that's a pretty esoteric corner case.
 
 Cheers,
 -- 
 Casey Allen Shobe
 Database Architect, The Berkeley Electronic Press
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

_
Want to do more with Windows Live? Learn “10 hidden secrets” from Jamie.
http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entry?ocid=TXT_TAGLM_WL_domore_092008

Re: [GENERAL] Oracle and Postgresql

[Andrew]

For an alternative view of the security argument, which may be a little 
off topic...


One consideration in regard to arguments for additional security, 
whether column and row level security or the divergent thread on 
obfuscated stored procedures is whether postgresql currently supports 
PCI (international), Basel II (EU - international) and Sarbanes-Oxley 
(US) requirements for restricted access, logging and differentiation of 
roles and responsibilities, or whether the additional security is 
required to provide better matching support.  These are important 
considerations in the corporate, and especially financial institutions, 
though I would suspect that postgresql does not have great penetration 
into such organisations.


In my mind, postgresql as is, in combination with application design 
considerations and OS level security, does support PCI, Basel II and 
Sarbanes-Oxley security requirements.  However, I thought I would bring 
this up as some people may have different interpretations on what it 
means to be compliant to these standards and regulations, and may have a 
convincing argument for their case based on what is needed to support them.


This is assuming that the postgresql development community see any value 
in being seen to be enablers of PCI, Basel II or Sarbanes-Oxley 
requirements.  Many commercial version control systems and database 
systems now throw in Sarbanes-Oxley compliant in their advertising, 
though I have not seen any open source applications do so (which doesn't 
mean that they haven't), and personally I think it is a somewhat 
misrepresentative to imply that the application itself makes you compliant.


If interested, the following are the relevant Wikipedia links, with 
references to the standards and regulations themselves:

PCI: http://en.wikipedia.org/wiki/PCI_DSS
Basel II:   http://en.wikipedia.org/wiki/Basel_II
SOX:  http://en.wikipedia.org/wiki/Sarbanes-oxley

Not that any of these regulations have done much to avert the market 
turmoil of the last few months, despite the bureaucratic overhead that 
they generated...  But that is another story.


Cheers,

Andy



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Glyn Astill]

I'm not sure what the policy is on putting stuff in the docs, but how about 
putting that in the relevant place, as well as a note about the other option; 
using C and SPI.


 Added to TODO under features not wanted:
 
   Incomplete itemObfuscated function source code (not
 wanted)
   
   Obfuscating function source code has minimal
 protective benefits
   because anyone with super-user access can find a way to
 view the code.
   To prevent non-super-users from viewing function source
 code, remove
   SELECT permission on pg_proc. 





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[David Fetter]

On Wed, Sep 24, 2008 at 02:12:19PM +, Glyn Astill wrote:
 I'm not sure what the policy is on putting stuff in the docs, but
 how about putting that in the relevant place, as well as a note
 about the other option; using C and SPI.

C is not magic obfuscation gear.  Anybody with a debugger can expose
what it's doing.  There have been math papers showing that it's
impossible to hide the functionality of a piece of software based only
on the ability to run it, so the entire prospect of obscuring the
software's functionality when people can send arbitrary inputs to it
is one of those known-impossible problems like the halting problem.

Cheers,
David.
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Bruce Momjian]

David Fetter wrote:
 On Wed, Sep 24, 2008 at 02:12:19PM +, Glyn Astill wrote:
  I'm not sure what the policy is on putting stuff in the docs, but
  how about putting that in the relevant place, as well as a note
  about the other option; using C and SPI.
 
 C is not magic obfuscation gear.  Anybody with a debugger can expose
 what it's doing.  There have been math papers showing that it's

I bet 'strings' shows all the SQL queries in a C object file too.

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Andrew Sullivan]

On Wed, Sep 24, 2008 at 08:05:18AM -0700, David Fetter wrote:

 C is not magic obfuscation gear.  Anybody with a debugger can expose
 what it's doing. There have been math papers showing that it's
 impossible to hide the functionality of a piece of software based only
 on the ability to run it, so the entire prospect of obscuring the
 software's functionality when people can send arbitrary inputs to it
 is one of those known-impossible problems like the halting problem.

To be fair, one of the points that others are trying to make is not
secure this function for real but secure this function enough to
make it a little costly.  Sure, someone with a debugger and probably
not much work could figure out what the function is.  If all you're
trying to do is make it expensive for dodgy software shops to re-use
your code, however, this is probably enough: the sort of person who
thinks re-using someone else's undocumented code is easier than
writing it from scratch is probably not going to go to the trouble of
really learning the code via debugging tools.  As a defence against
criminally lazy developers, compliled C code is probably good
enough.  (Of course, clever non-C code is probably also enough, in my
opinion, but obviously others disagree.)

A

-- 
Andrew Sullivan
[EMAIL PROTECTED]
+1 503 667 4564 x104
http://www.commandprompt.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Casey Allen Shobe]

On Aug 31, 2008, at 8:44 PM, David Fetter wrote:

What they want to have is a huge entity they can blame when everything
goes wrong.  They're not interested in the actual response times or
even in the much more important time-to-fix because once they've
blamed Oracle, they know the responsibility is no longer on their
shoulders.


The usual individual developer, open source community, and small  
company attitude is one that prefers to employ intelligent staff and  
give them a lot of responsibility (with varying degrees of success).   
They would rather spend a week refactoring code for performance, or  
experimenting with another language or database, sending an employee  
to training or conferences, contribute patches to open source  
projects, etc.  They will try to make a well thought-out decision up  
front when possible, and often this results in an early choice for  
PostgreSQL, especially because it fits within any budget.  When these  
people end up working in larger companies with different mindsets,  
they sometimes are successful at getting PostgreSQL utilized through  
resources like Command Prompt, EnterpriseDB, Greenplum, and so on, to  
replace the vendor support that Oracle comes with.  They might start  
off with MySQL as a first database, but once learning about  
PostgreSQL, will invest lots of time into porting if they understand  
the advantages (this can be evidenced all the time by people  
communication in the #postgresql IRC channel), and will put a lot of  
effort into doing things the architecturally best way over time rather  
than just slopping together bandaided bits and poor lazy table design.


Others are lazy, go with some popular name of something free they  
hear, and end up as Red Hat/PHP/MySQL shops, with a huge pile of  
random crappy free apps bandaided together - hoping to make some quick  
cash.  If they end up with PostgreSQL it's not a thought-out decision  
(well or otherwise), and they use it very irresponsibly and then  
everyone will blame PostgreSQL for all their problems simply because  
it's not the most common name.  There's a perception here that MySQL  
is better for them because it's more popular, has more random free  
crappy apps available for it, and they don't care much about the added  
reliability of PostgreSQL (often they'll run with fsync=off), as  
they're a rickety shop anyways.  They'll also have a perception that  
Oracle is some magical holy grail simply because it is so out of their  
reach during early development, but as they become profitable enough,  
the idea of buying Oracle becomes very exciting.


The corporate attitude is one that prefers things to be as  
encompassing, bundled, automatic, and self-maintaining as possible.   
They prefer Oracle because they provide a wide array of inbuilt  
replication, backup, and administrative functionality - things like  
raw device management (Oracle has implemented a couple different  
filesystems as well), early integration with Java when it was all the  
rage, tons of complicated shinies for permission control that managers  
don't really comprehend but think they like and need and are using  
correctly.  These are typically Java shops, with no diversity at all -  
you won't find a single perl or bash script lying around - they'll  
load up a slow common Java framework for even the simplest task.  Code  
quality tends to be pretty decent, but there is heavy denial of any  
problems and fixes are slow and obstructed by managerial complexity  
and too much influence from Sales. :P


Another similar example can be found with ZFS or VxFS versus  
traditional simple filesystems.  ZFS has few tuning options and their  
use is discouraged.  It does a lot of things automatically and there  
is a favoring of defaults over workload-specific tuning and  
administrative overhead.  It builds in every filesystem-related thing  
(the filesystem itself, a logical volume manager, clustering tools,  
and even it's own ZFS-specific filesystem cache daemon) into a single  
manager-friendly bundle.  You can't get the same levels of performance  
out of it as you can by carefully tuning other systems, but that  
tuning requires hiring intelligent staff and this seems to be  
amazingly challenging for large corporations, and they'd rather pay  
some middle-level manager a salary worth 5 developers, and have him  
buy and assemble big packaged solutions instead.


PostgreSQL can't really take over the corporate market - Oracle and  
DB2 will always be around too. :)  To do that we'd need to do a lot of  
unwanted things to the code, build in much more unecessary complexity  
that most will never use, reduce flexibility and options in the  
process, spange up incredible amounts of well-placed marketing dollars  
and slowly get more acceptance by proving years of experience at an  
increasing number of corporate PostgreSQL users.


I worked with PostgreSQL at Cingular - and the simple fact is that it  
was not 

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Glyn Astill]

 C is not magic obfuscation gear.  Anybody with a debugger
 can expose
 what it's doing.  There have been math papers showing
 that it's
 impossible to hide the functionality of a piece of software
 based only
 on the ability to run it, so the entire prospect of
 obscuring the
 software's functionality when people can send arbitrary
 inputs to it
 is one of those known-impossible problems like
 the halting problem.

And the first word in the title is obfuscated, not encrypted, secured or 
anything else...




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Scott Marlowe]

On Wed, Sep 24, 2008 at 1:02 PM, Casey Allen Shobe [EMAIL PROTECTED] wrote:

 A knowledgeable PostgreSQL DBA can make significantly more than an Oracle
 DBA as they're a scarcer resource and generally higher quality on average.
  But it may be harder for them to find work - they may end up having to
 move, telecommute, or commute a longer distance simply because there are
 less PostgreSQL shops.  It also means a much higher probability of working
 for a small-medium company versus a corporation.

 An Oracle DBA can be a lot lazier, and lean on the vendor a lot more.  There
 are open Oracle DBA positions everywhere, and it is very easy for them to
 find another job, so learning a lot and focusing on doing a good job are not
 important to them.  In the corporate environment in which most of these jobs
 are, they are responsible for far less in their job role, whereas the
 PostgreSQL DBA tends to end up responsible for a lot more pieces of the
 puzzle.

These two paragraphs really ring true for me.  I've yet to meet an
oracle dba who was the jack of all trades that being a postgresql DBA
requires.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Tom Lane]

Andrew Sullivan [EMAIL PROTECTED] writes:
 On Wed, Sep 24, 2008 at 08:05:18AM -0700, David Fetter wrote:
 C is not magic obfuscation gear. ...

 To be fair, one of the points that others are trying to make is not
 secure this function for real but secure this function enough to
 make it a little costly.

Agreed, but there seems no particular need to have such a feature in
core Postgres.  An add-on PL could accomplish that task just as well;
perhaps more so, if you don't make the add-on available to all and
sundry.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Casey Allen Shobe]

On Sep 4, 2008, at 7:40 PM, Robert Treat wrote:
It is not as simple as Oracles database link syntax. Setting up a  
connection
involves a couple of sql looking commands, and once you setup a  
connection to
a remote database, you can reference a table with something like  
select *
from [EMAIL PROTECTED]  There's no way a function oriented solution  
can

match that imho.


I have long thought that what would be really useful is a standard way  
for third-party modules to extend or override the SQL language support  
within PostgreSQL itself without needing to be integrated in core.


E.g. it should be possible for all of EnterpriseDB's Oracle-compatible  
SQL changes to exist as a separate module, somebody could change the  
behavior of a select to default ordering to imitate Oracle etc.  It  
should be possible for a replication engine to add syntax for options  
specific to it.  Contrib modules like dblink could install SQL-like  
command support.


This would be both invaluable for compatibility efforts and probably  
raise the amount of 3rd party stuff that actually gets used  
(currently, many places I've seen avoid Slony because they fear having  
to use the commandline scripts it comes with, and if you want to  
manipulate Slony from the database itself, oftentimes this means you  
have to use pl/perlu or another untrusted language.


Don't get me wrong, functions are great too. :)  But currently the  
above means that a lot of risk is introduced and you have to put a lot  
of faith in the perl code - an exploit poses a lot of risk.  If Slony  
exposed it's own data to PG via custom SQL extensions, this would be  
more secure by design.


Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Scott Ribe]

 the sort of person who
 thinks re-using someone else's undocumented code is easier than
 writing it from scratch is probably not going to go to the trouble of
 really learning the code via debugging tools.

Fixed that for you:

the sort of person who
thinks re-using someone else's undocumented code is easier than
writing it from scratch is probably not going to be able to learn the code
via debugging tools.


-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Bruce Momjian]

Added to TODO under features not wanted:

Incomplete itemObfuscated function source code (not wanted)

Obfuscating function source code has minimal protective benefits
because anyone with super-user access can find a way to view the code.
To prevent non-super-users from viewing function source code, remove
SELECT permission on pg_proc. 


---

Merlin Moncure wrote:
 On Tue, Sep 16, 2008 at 9:15 AM, Glyn Astill [EMAIL PROTECTED] wrote:
 
  As much as I'm impressed with the we do it properly or not at all 
  attitude, it'd be nice if there was an option to stop the casual user from 
  viewing code.
 
  I'll admit to obfusicating bits and pieces using C, even though the 
  function and everything it acts on are tied down with permissions. I 
  understand in reality it provides no real extra security but somehow users 
  being able to easily view something they don't have access to execute 
  beyond it's name just feels wrong.
 
 This is one of those threads that reappears like magic every six
 months or so.  The last round of discussion went longer than normal
 including a couple of routes to implementation.
 
 One big reason why nothing hasn't been done is that there is a decent
 'low tech' obfuscation tactic already: remove select access from
 pg_proc to the user accounts in question and 'public'.  This will
 essentially disable casual browsing of procedure code from user
 accounts.
 
 Any real solution should focus on:
 *) key management (any serious discussion with encryption starts here)
 *) other things you can do with function source besides encryption
 
 for example, take a look at one idea I had (not at all vetted, but a start):
 http://archives.postgresql.org/pgsql-performance/2007-12/msg00337.php
 
 merlin
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Joris Dobbelsteen]

Merlin Moncure wrote:

On Mon, Sep 15, 2008 at 1:10 PM, Martin Gainty [EMAIL PROTECTED] wrote:
  

accessing:
i *thought* the advantage of creating any SQL procedure/function was the
entity is stored in procedure cache
load time:
Java vs C++ compare here
http://www.idiom.com/~zilla/Computer/javaCbenchmark.html



This is completely off topic as it is, but I can't help it: anyone who
is arguing that Java is faster than C must be oblivious to the fact
that Java internals are *written in C*.  If Java was really faster
than C, it would be self hosting, and we would be writing operating
systems, databases, and various other systems level stuff in Java.
(yes, there are a few well known projects, notably lucene, but that's
the exception rather than the rule).

Anybody making the case that Java is faster than C simply doesn't know
how a computer works.

merlin
  
According to your argument any hardware implementation is limited to 
software performance, as it is compiled by a software compiler? In fact, 
compiling assembly with a compiler written in C will destroy its 
performance to be worse than C? Come on... This is utter bullshit. You 
should rather reflect on your last line...


Its worst yet, there are actually situation where the Java optimizer 
does a better job than many C compiler. The reverse is also true. If you 
are a software guy you SHOULD know that code optimization and algorithm 
design are NP problems and have no obvious optimal solutions. It seems 
easier to optimize Java code than it is optimizing C code.


Better yet, you should actually read the article. It has the explanation 
contained inside it. The domain of application Java has (eventually) 
been developed for is just different.


Regards,

- Joris

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Michelle Konzack]

Am 2008-09-15 10:12:08, schrieb Joshua Drake:
 Are we going to start a VI vs Emacs argument too?

They are out of concurence since I am using mc (Midnight Commander). :-P

Thanks, Greetings and nice Day/Evening
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant


-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
Michelle Konzack   Apt. 917  ICQ #328449886
+49/177/935194750, rue de Soultz MSN LinuxMichi
+33/6/61925193 67100 Strasbourg/France   IRC #Debian (irc.icq.com)


signature.pgp
Description: Digital signature

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Glyn Astill]

 Because it's so full of obvious loopholes.  Yes,
 it might slow down
  someone who didn't have superuser access to the
 database or root access
  to the machine it's on; but that doesn't count
 as secure really.  The
  problem is that the people who ask for this type of
 feature are usually
  imagining that they can put their code on
 customer-controlled machines
  and it will be safe from the customer's eyes. 
 Well, it isn't, and
  I don't think Postgres should encourage them to
 think it is.
 

As much as I'm impressed with the we do it properly or not at all attitude, 
it'd be nice if there was an option to stop the casual user from viewing code.

I'll admit to obfusicating bits and pieces using C, even though the function 
and everything it acts on are tied down with permissions. I understand in 
reality it provides no real extra security but somehow users being able to 
easily view something they don't have access to execute beyond it's name just 
feels wrong.




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Merlin Moncure]

On Tue, Sep 16, 2008 at 9:15 AM, Glyn Astill [EMAIL PROTECTED] wrote:

 As much as I'm impressed with the we do it properly or not at all attitude, 
 it'd be nice if there was an option to stop the casual user from viewing code.

 I'll admit to obfusicating bits and pieces using C, even though the function 
 and everything it acts on are tied down with permissions. I understand in 
 reality it provides no real extra security but somehow users being able to 
 easily view something they don't have access to execute beyond it's name just 
 feels wrong.

This is one of those threads that reappears like magic every six
months or so.  The last round of discussion went longer than normal
including a couple of routes to implementation.

One big reason why nothing hasn't been done is that there is a decent
'low tech' obfuscation tactic already: remove select access from
pg_proc to the user accounts in question and 'public'.  This will
essentially disable casual browsing of procedure code from user
accounts.

Any real solution should focus on:
*) key management (any serious discussion with encryption starts here)
*) other things you can do with function source besides encryption

for example, take a look at one idea I had (not at all vetted, but a start):
http://archives.postgresql.org/pgsql-performance/2007-12/msg00337.php

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Jonathan Bond-Caron]

On Tue Sep 16 08:40 AM, Bill Moran wrote:
 In response to Tom Lane [EMAIL PROTECTED]:
 
 Bill Moran [EMAIL PROTECTED] writes:
 What I'm _asking_ is why would extending SECURITY DEFINER to include 
 preventing unauthorized users from viewing code _not_ be a valid 
 method of securing the code.
 
 Because it's so full of obvious loopholes.  Yes, it might slow down 
 someone who didn't have superuser access to the database or root 
 access to the machine it's on; but that doesn't count as secure 
 really.  The problem is that the people who ask for this type of 
 feature are usually imagining that they can put their code on 
 customer-controlled machines and it will be safe from the customer's 
 eyes.  Well, it isn't, and I don't think Postgres should encourage
 them to think it is.
 
 Shame that.  I can imagine it being a useful feature in certain 
 situations (such as a hosted environment), although I understand the 
 concern.
 
 Code obfuscation is the norm, though.  The world at large still seems 
 to believe that compiling code make it secret, despite the fact that 
 crooks have demonstrated again and again that they're more than 
 willing to read through opcodes, and the fact that there are 
 decompilers available for just about every major compiled format.
 

I agree here. I hope there's a consensus that it does offer some level of
protection. 

After some research, I found this article that I believe will make a
stronger use case: 
http://www.iosn.net/network/news/Managing%20the%20insider%20threat%20through
%20code%20obfuscation

Whether or not it belongs in PG I don't really have an opinion. 




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Gregory Williamson]

Nice trick ... thanks!

 One big reason why nothing hasn't been done is that there is a decent
 'low tech' obfuscation tactic already: remove select access from
 pg_proc to the user accounts in question and 'public'.  This will
 essentially disable casual browsing of procedure code from user
 accounts.

 merlin

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Glyn Astill]

 One big reason why nothing hasn't been done is that
 there is a decent
 'low tech' obfuscation tactic already: remove
 select access from
 pg_proc to the user accounts in question and
 'public'.  This will
 essentially disable casual browsing of procedure code from
 user
 accounts.

Neat :-)




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

[Andrew Sullivan]

On Tue, Sep 16, 2008 at 09:39:03AM -0400, Jonathan Bond-Caron wrote:
 
 I agree here. I hope there's a consensus that it does offer some level of
 protection. 

There is not, in fact, in the security community a consensus that it
offers some level of protection.  There are some security people who
think that obfuscation and secrets are one of the tools (and one of
the weakest) to protect the computing infrastructure.  There are
others who claim, as strongly and with as great authority, that
anything that cannot be secured even if the attacker knows all about
it (excluding the private key itself) cannot be secured at all.
 
 After some research, I found this article that I believe will make a
 stronger use case:
 http://www.iosn.net/network/news/Managing%20the%20insider%20threat%20through
 %20code%20obfuscation

That article is not evidence for your conclusion.  Obfuscated code in
the example cases in the article would not be a good investment.  

In the first example, an insider who is an expert in the system
deleted a key file: it would make no difference if the code were
obfuscated, because an expert operator doesn't need to understand the
innards in order to be able to know which files are critical.  (Your
expert operators _have_ to know which are critical, because they have
to pay more attention to those files.)  

In the second example, of Mallory the disgruntled employee, we don't
have any information about what Mallory knows; so either Mallory's
defeat of the access controls (which is the real problem) will give
Mallory access to a system she understands, or else she'll have to
comprehend a complex system on the fly while evading detection of the
access control failure.  Obfuscation isn't likely to provide
additional defence against a vandal, which is what the
disgruntled-employee scenario represents.

Finally, the bigger example of distributed code that is decompilable
doesn't prove much, either.  If you are analysing the code in order to
attack it for a given end, you can do this just as easily with opaque
bytes (if you couldn't, Windows wouldn't be plagued with the security
problems it is: there wasn't a remarkable rash of new exploits that
appeared after the apparently leaking of the Windows source).

Obfuscation is mostly useful against the casual attacker.  There are
better and less costly measures against such attackers.  In
particular, careful management of access controls is far more
important.  

The important thing to remember is that, if the secrecy of the system
is your main or only defence, you're going to be in trouble because
such systems are brittle.  When they fail, they fail spectacularly.

All of that said, I can in fact think of use cases where casual users
not being able to view the source of a function could be useful.
These cases have more to do with corporate governance and internal
Chinese walls than any real security.  They're the sort of thing
that would make auditors happy, and for that reason they might be
worth implementing.  So far, I don't believe anyone's had an itch of
this sort strong enough to scratch.  

A

-- 
Andrew Sullivan
[EMAIL PROTECTED]
+1 503 667 4564 x104
http://www.commandprompt.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Roberts, Jon]

 
 On Mon, Sep 15, 2008 at 2:04 PM, Christophe [EMAIL PROTECTED] wrote:
 
  On Sep 15, 2008, at 12:56 PM, Scott Marlowe wrote:
 
  I could totally get behind needing permission to see the plpgsql
code.
 
  :)
 
 I wasn't kidding up there.  Setting view permissions on plpgsql (or
 any pl code really) would be understandable.  If you're not a super
 user or the owner, you need permission to see it.
 

How can I make that a feature request?  


Jon

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Joshua Drake]

On Tue, 16 Sep 2008 11:54:18 -0500
Roberts, Jon [EMAIL PROTECTED] wrote:
 
  I wasn't kidding up there.  Setting view permissions on plpgsql (or
  any pl code really) would be understandable.  If you're not a super
  user or the owner, you need permission to see it.
  
 
 How can I make that a feature request?  

You need to send it to -hackers and get support for it.  You might have
better luck if you or someone you agrees with you actually does the
work to get it implemented. 

Sincerely,

Joshua D. Drake

-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[0123 zyxw]

Kevin Hunter wrote:

1. Oracle was first, and has vendor lock-in momentum.
2. Oracle ...speed/performance/concurrency...
3. Oracle has application lock-in as well.  ...
4. Oracle is company-backed, so there is ostensibly someone to blame..
5. ... individuals ... may prefer it *because* it's expensive...
6. Mucho better advertising to the right people
7. ...print-version...


8. Oracle salespeople will help B2B software companies help sell
and bid on larger oracle-based products and projects.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Blazej]

2008/9/9 0123 zyxw [EMAIL PROTECTED]:
 Kevin Hunter wrote:

 1. Oracle was first, and has vendor lock-in momentum.
 2. Oracle ...speed/performance/concurrency...
 3. Oracle has application lock-in as well.  ...
 4. Oracle is company-backed, so there is ostensibly someone to blame..
 5. ... individuals ... may prefer it *because* it's expensive...
 6. Mucho better advertising to the right people
 7. ...print-version...

 8. Oracle salespeople will help B2B software companies help sell
 and bid on larger oracle-based products and projects.

 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general


1. Maybe ...
2. Of course, like others DBMS, but how many people knows how it's
configure? Are you configure ORACLE DBMS? 1000+ strange parameters and
don't tell me that they are well document. The same with Oracle Apps.
3. Are you kidding? Are you work with Oracle AS, ifs, cmsdk etc? I've
never seen so good, fast and scalable software.
4. Software AS IS - no comment.
5. ? Lets look:
with Oracle:
App = Oracle licences + yours work = X (and pay for everything - help,
support etc.),
with PostgreSQL:
App = 0 for licences + work = Y,
if X=Y who has more profits?

6. Agree :-((( Oracle marketing is realy the best.
7. ?
8. As I say in 5. - pay for everything - if you are money everybady
helps you (even I, and if you pay 1.000.000 $ I say Oracle is the
best, like other Oracle experts).

Regards,
Blazej

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general