Port Oracle encryption function to Postgres

Question

I'm trying to port an Oracle function to Postgres. The purpose of this function is encrypt data using AES-256 algorithm. Since I'm no expert in either Oracle or cryptography I would highly appreciate all possible help. The goal is to get the same encrypted result both in Oracle and Postgres using the same data and key.

The Oracle function is

function encrypt(data varchar2, key raw) return raw as
begin
  return dbms_crypto.encrypt(
      utl_i18n.string_to_raw(data, 'AL32UTF8'),
      dbms_crypto.ENCRYPT_AES256
      + dbms_crypto.CHAIN_CBC
      + dbms_crypto.PAD_PKCS5,
      key
    );
end;

Please confirm exactly which distribution and version of Postgres you are working with. There are a number of possible responses here, depending on your answer. — pmdba
– pmdba, Commented Mar 31, 2020 at 12:59
Do you need this function to return exactly the same thing as the Oracle function (e.g. so that you can validate an encrypted password)? — user330315
– user330315, Commented Mar 31, 2020 at 13:09
The ported function should work in free version of PostgreSQL, preferable version 9.3 or later — tok
– tok, Commented Apr 1, 2020 at 13:16

OracleDba · Accepted Answer · 2021-06-02 16:44:30Z

I know this is an old thread but I had same issue. Here is identical encryption/decryption from oracle/postgres using AES128
I have not (as yet) enjoyed success with 256 encryption but I haven't given up either.

    ORACLE  
    -----------------
      select RAWTOHEX ( DBMS_CRYPTO.encrypt (
                                          src   => UTL_I18N.STRING_TO_RAW ('TEST', 'AL32UTF8'),
                                          typ   => 6+256+4096/* DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5   */,
                                          key   => UTL_RAW.cast_to_raw (RPAD ('1234', (128/8))))) val
      from dual;
      --4710C33D1568176FD6862FC58930B274
      
      select  UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.decrypt (
                                          src   => hextoraw('4710C33D1568176FD6862FC58930B274'),
                                          typ   => 6+256+4096/* DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5   */,
                                          key   => UTL_RAW.cast_to_raw (RPAD ('1234', (128/8)))), 'AL32UTF8') val
      from dual;
      --TEST
        
    POSTGRES  
    -----------------  
      select  upper(encode(encrypt('TEST', rpad('1234',128/8)::bytea, 'aes'),'HEX')) VAL;
      -- 4710C33D1568176FD6862FC58930B274 
       
       select convert_from(decrypt('\x4710C33D1568176FD6862FC58930B274',rpad('1234',128/8)::bytea,'aes'),'SQL_ASCII') VAL;
       --TEST

Laurenz Albe · Accepted Answer · 2020-03-31 12:23:09Z

0

You could have a look at the encrypt function of the pgcrypto extension. It may not be a 100% replacement, but perhaps it is close enough.

answered Mar 31, 2020 at 12:23

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

1 Comment

tok Over a year ago

Thanks. I'm aware of pgcrypto extension but I was not able to create a function similar as above with it, probably because of lack of knowledge of cryptography.

pmdba · Accepted Answer · 2020-03-31 13:16:51Z

-1

I'm not an expert in Postgres, but it appears that the Postgres DBMS_CRYPTO package is only included in the closed source Postgres EDB, not PostgreSQL, and that it only supports RAW, BLOB, and CLOB data types, not VARCHAR2. Also, it supports a different set of ciphers than Oracle, none being stronger than AES128. See here for details: https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/database-compatibility-for-oracle-developers-built-in-package-guide/12/Database_Compatibility_for_Oracle_Developers_Built-in_Package_Guide.1.16.html

In short, it doesn't look like you can do exactly what you're trying to (use DBMS_CRYPTO, that is). If you're using PostgreSQL, you'll need to look at some other alternative like pg_crypto. If you're considering EDB, then you'll need to switch data types and sacrifice some level of security to make the switch.

edited Mar 31, 2020 at 13:16

answered Mar 31, 2020 at 12:16

pmdba

7,0932 gold badges10 silver badges17 bronze badges

3 Comments

Laurenz Albe Over a year ago

There is no DBMS_CRYPTO package in PostgreSQL, and indeed there are no packages at all.. You are talking about a closed-source fork of PostgreSQL.

pmdba Over a year ago

The OP did not specify PostgreSQL, but rather Postgres; Postgres EDB Advanced Server appears to be alive and well, and does support DBMS_CRYPTO. It @tok would please confirm which version they are using, it would help clarify the appropriate answer for this post.

tok Over a year ago

I'm sorry I was too inaccurate. I meant free version of Postgres, the PostgreSQL, and version number 9.3 or later.

Collectives™ on Stack Overflow

Port Oracle encryption function to Postgres

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related