Hello
this patch define new function flag - OBFUSCATE. With this flag
encrypted source code is stored to probin column. Password is stored
in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server
does (where privileged users can access system tables with source code
or can use debugger).
ToDo: Dump
Sample:
postgres=# show obfuscator_password;
obfuscator_password
-----------------------
moje supertajne heslo
(1 row)
postgres=# \x
Expanded display is on.
postgres=# create or replace function fx() returns int as $$begin
return -1; end; $$ language plpgsql;
CREATE FUNCTION
postgres=# \df+ fx
List of functions
-[ RECORD 1 ]-------+-----------------------
Schema | public
Name | fx
Result data type | integer
Argument data types |
Volatility | volatile
Owner | bob
Language | plpgsql
Source code | begin return -1; end;
Description |
postgres=# ALTER FUNCTION fx() obfuscate;
NOTICE: begin return -1; end;
ALTER FUNCTION
postgres=# \df+ fx
List of functions
-[ RECORD 1 ]-------+---------
Schema | public
Name | fx
Result data type | integer
Argument data types |
Volatility | volatile
Owner | bob
Language | plpgsql
Source code | -
Description |
postgres=# select fx();
-[ RECORD 1 ]
fx | -1
postgres=# create or replace function fx() returns int as $$begin
return -1; end; $$ language plpgsql obfuscate;
CREATE FUNCTION
postgres=# select fx();
-[ RECORD 1 ]
fx | -1
postgres=# \df+ fx
List of functions
-[ RECORD 1 ]-------+---------
Schema | public
Name | fx
Result data type | integer
Argument data types |
Volatility | volatile
Owner | bob
Language | plpgsql
Source code | -
Description |
postgres=# select * from pg_proc where proname = 'fx';
-[ RECORD 1
]--+----------------------------------------------------------------------------
proname | fx
pronamespace | 2200
proowner | 16385
prolang | 16421
procost | 100
prorows | 0
proisagg | f
prosecdef | f
proisstrict | f
proretset | f
provolatile | v
pronargs | 0
prorettype | 23
proargtypes |
proallargtypes |
proargmodes |
proargnames |
prosrc | -
probin |
\231\003_\266\361\214}\231\240L/\020\232\036c\234\315P\236\266I\370\324\222
proconfig |
proacl |
[EMAIL PROTECTED] ~]$ psql -U bob postgres
Welcome to psql 8.3RC2, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=> \x
Expanded display is on.
postgres=> show obfuscator_password;
ERROR: must be superuser to examine "obfuscator_password"
postgres=> select fx();
-[ RECORD 1 ]
fx | -1
postgres=> \df+ fx
List of functions
-[ RECORD 1 ]-------+---------
Schema | public
Name | fx
Result data type | integer
Argument data types |
Volatility | volatile
Owner | bob
Language | plpgsql
Source code | -
Description |
postgres=> select * from pg_proc where proname = 'fx';
-[ RECORD 1
]--+----------------------------------------------------------------------------
proname | fx
pronamespace | 2200
proowner | 16385
prolang | 16421
procost | 100
prorows | 0
proisagg | f
prosecdef | f
proisstrict | f
proretset | f
provolatile | v
pronargs | 0
prorettype | 23
proargtypes |
proallargtypes |
proargmodes |
proargnames |
prosrc | -
probin |
\231\003_\266\361\214}\231\240L/\020\232\036c\234\315P\236\266I\370\324\222
proconfig |
proacl |
*** ./src/backend/catalog/pg_proc.c.orig 2008-01-27 21:29:42.000000000 +0100
--- ./src/backend/catalog/pg_proc.c 2008-01-28 11:13:31.000000000 +0100
***************
*** 27,32 ****
--- 27,33 ----
#include "mb/pg_wchar.h"
#include "miscadmin.h"
#include "parser/parse_type.h"
+ #include "parser/parse_func.h"
#include "tcop/pquery.h"
#include "tcop/tcopprot.h"
#include "utils/acl.h"
***************
*** 34,39 ****
--- 35,41 ----
#include "utils/lsyscache.h"
#include "utils/syscache.h"
+ extern char *obfuscator_password;
Datum fmgr_internal_validator(PG_FUNCTION_ARGS);
Datum fmgr_c_validator(PG_FUNCTION_ARGS);
***************
*** 45,51 ****
static bool match_prosrc_to_literal(const char *prosrc, const char *literal,
int cursorpos, int *newcursorpos);
-
/* ----------------------------------------------------------------
* ProcedureCreate
*
--- 47,52 ----
***************
*** 74,80 ****
Datum parameterNames,
Datum proconfig,
float4 procost,
! float4 prorows)
{
Oid retval;
int parameterCount;
--- 75,82 ----
Datum parameterNames,
Datum proconfig,
float4 procost,
! float4 prorows,
! bool obfuscate)
{
Oid retval;
int parameterCount;
***************
*** 248,257 ****
values[Anum_pg_proc_proargnames - 1] = parameterNames;
else
nulls[Anum_pg_proc_proargnames - 1] = 'n';
! values[Anum_pg_proc_prosrc - 1] = DirectFunctionCall1(textin,
CStringGetDatum(prosrc));
! values[Anum_pg_proc_probin - 1] = DirectFunctionCall1(textin,
CStringGetDatum(probin));
if (proconfig != PointerGetDatum(NULL))
values[Anum_pg_proc_proconfig - 1] = proconfig;
else
--- 250,268 ----
values[Anum_pg_proc_proargnames - 1] = parameterNames;
else
nulls[Anum_pg_proc_proargnames - 1] = 'n';
! if (!obfuscate)
! {
! values[Anum_pg_proc_prosrc - 1] = DirectFunctionCall1(textin,
CStringGetDatum(prosrc));
! values[Anum_pg_proc_probin - 1] = DirectFunctionCall1(textin,
CStringGetDatum(probin));
+ }
+ else
+ {
+ values[Anum_pg_proc_prosrc - 1] = DirectFunctionCall1(textin,
+ CStringGetDatum("-"));
+ values[Anum_pg_proc_probin - 1] = Obfuscate(prosrc);
+ }
if (proconfig != PointerGetDatum(NULL))
values[Anum_pg_proc_proconfig - 1] = proconfig;
else
***************
*** 822,824 ****
--- 833,873 ----
*newcursorpos = newcp;
return false;
}
+
+ Datum
+ Obfuscate(const char *prosrc)
+ {
+ Datum encrypted_src;
+
+ FuncDetailCode fdresult;
+ Oid encrypt_argtypes[] = {BYTEAOID, BYTEAOID, TEXTOID};
+ bool retset;
+ Oid *true_oid_array;
+ Oid fnOid = InvalidOid;
+ Oid rettype;
+
+ fdresult = func_get_detail(list_make1(makeString("encrypt")),
+ NIL, 3, encrypt_argtypes,
+ &fnOid, &rettype, &retset,
+ &true_oid_array);
+
+ if (fdresult != FUNCDETAIL_NORMAL || !OidIsValid(fnOid))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_FUNCTION),
+ errmsg("function %s does not exist",
+ func_signature_string(list_make1(makeString("encrypt")), 3, encrypt_argtypes)),
+ errhint("Install pgcrypto first.")));
+
+ encrypted_src = OidFunctionCall3(fnOid,
+ DirectFunctionCall2(binary_decode,
+ DirectFunctionCall1(textin, CStringGetDatum(prosrc)),
+ DirectFunctionCall1(textin, CStringGetDatum("escape"))),
+ DirectFunctionCall2(binary_decode,
+ DirectFunctionCall1(textin, CStringGetDatum(obfuscator_password)),
+ DirectFunctionCall1(textin, CStringGetDatum("escape"))),
+ DirectFunctionCall1(textin, CStringGetDatum("bf")));
+
+ /* probin is bytea datatype */
+
+ return encrypted_src;
+ }
*** ./src/backend/commands/functioncmds.c.orig 2008-01-27 21:15:18.000000000 +0100
--- ./src/backend/commands/functioncmds.c 2008-01-28 13:34:34.000000000 +0100
***************
*** 295,301 ****
DefElem **security_item,
List **set_items,
DefElem **cost_item,
! DefElem **rows_item)
{
if (strcmp(defel->defname, "volatility") == 0)
{
--- 295,302 ----
DefElem **security_item,
List **set_items,
DefElem **cost_item,
! DefElem **rows_item,
! DefElem **obfuscate_item)
{
if (strcmp(defel->defname, "volatility") == 0)
{
***************
*** 336,341 ****
--- 337,349 ----
*rows_item = defel;
}
+ else if (strcmp(defel->defname, "obfuscate") == 0)
+ {
+ if (*obfuscate_item)
+ goto duplicate_error;
+
+ *obfuscate_item = defel;
+ }
else
return false;
***************
*** 412,418 ****
bool *security_definer,
ArrayType **proconfig,
float4 *procost,
! float4 *prorows)
{
ListCell *option;
DefElem *as_item = NULL;
--- 420,427 ----
bool *security_definer,
ArrayType **proconfig,
float4 *procost,
! float4 *prorows,
! bool *obfuscate_p)
{
ListCell *option;
DefElem *as_item = NULL;
***************
*** 423,428 ****
--- 432,438 ----
List *set_items = NIL;
DefElem *cost_item = NULL;
DefElem *rows_item = NULL;
+ DefElem *obfuscate_item = NULL;
foreach(option, options)
{
***************
*** 450,456 ****
&security_item,
&set_items,
&cost_item,
! &rows_item))
{
/* recognized common option */
continue;
--- 460,467 ----
&security_item,
&set_items,
&cost_item,
! &rows_item,
! &obfuscate_item))
{
/* recognized common option */
continue;
***************
*** 506,511 ****
--- 517,524 ----
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("ROWS must be positive")));
}
+ if (obfuscate_item)
+ *obfuscate_p = intVal(obfuscate_item->arg);
}
***************
*** 624,629 ****
--- 637,643 ----
HeapTuple languageTuple;
Form_pg_language languageStruct;
List *as_clause;
+ bool obfuscate;
/* Convert list of names to a name and namespace */
namespaceId = QualifiedNameGetCreationNamespace(stmt->funcname,
***************
*** 642,653 ****
proconfig = NULL;
procost = -1; /* indicates not set */
prorows = -1; /* indicates not set */
/* override attributes from explicit list */
compute_attributes_sql_style(stmt->options,
&as_clause, &language,
&volatility, &isStrict, &security,
! &proconfig, &procost, &prorows);
/* Convert language name to canonical case */
languageName = case_translate_language_name(language);
--- 656,669 ----
proconfig = NULL;
procost = -1; /* indicates not set */
prorows = -1; /* indicates not set */
+ obfuscate = false;
/* override attributes from explicit list */
compute_attributes_sql_style(stmt->options,
&as_clause, &language,
&volatility, &isStrict, &security,
! &proconfig, &procost, &prorows,
! &obfuscate);
/* Convert language name to canonical case */
languageName = case_translate_language_name(language);
***************
*** 801,807 ****
PointerGetDatum(parameterNames),
PointerGetDatum(proconfig),
procost,
! prorows);
}
--- 817,824 ----
PointerGetDatum(parameterNames),
PointerGetDatum(proconfig),
procost,
! prorows,
! obfuscate);
}
***************
*** 1151,1156 ****
--- 1168,1174 ----
List *set_items = NIL;
DefElem *cost_item = NULL;
DefElem *rows_item = NULL;
+ DefElem *obfuscate_item = NULL;
rel = heap_open(ProcedureRelationId, RowExclusiveLock);
***************
*** 1177,1182 ****
--- 1195,1201 ----
errmsg("\"%s\" is an aggregate function",
NameListToString(stmt->func->funcname))));
+
/* Examine requested actions. */
foreach(l, stmt->actions)
{
***************
*** 1188,1194 ****
&security_def_item,
&set_items,
&cost_item,
! &rows_item) == false)
elog(ERROR, "option \"%s\" not recognized", defel->defname);
}
--- 1207,1214 ----
&security_def_item,
&set_items,
&cost_item,
! &rows_item,
! &obfuscate_item) == false)
elog(ERROR, "option \"%s\" not recognized", defel->defname);
}
***************
*** 1252,1257 ****
--- 1272,1307 ----
tup = heap_modifytuple(tup, RelationGetDescr(rel),
repl_val, repl_null, repl_repl);
}
+ if (obfuscate_item)
+ {
+ bool isnull;
+ char *proc_src;
+ Datum prosrc;
+ Datum repl_val[Natts_pg_proc];
+ char repl_null[Natts_pg_proc];
+ char repl_repl[Natts_pg_proc];
+
+ prosrc = SysCacheGetAttr(PROCOID, tup, Anum_pg_proc_prosrc, &isnull);
+
+ if (isnull)
+ elog(ERROR, "null prosrc");
+
+ proc_src = DatumGetCString(DirectFunctionCall1(textout, prosrc));
+ if (strncmp(proc_src, "-", 1) != 0)
+ {
+ memset(repl_repl, ' ', sizeof(repl_repl));
+ memset(repl_null, ' ', sizeof(repl_null));
+
+ repl_repl[Anum_pg_proc_prosrc - 1] = 'r';
+ repl_repl[Anum_pg_proc_probin - 1] = 'r';
+
+ repl_val[Anum_pg_proc_prosrc - 1] = DirectFunctionCall1(textin, CStringGetDatum("-"));
+ repl_val[Anum_pg_proc_probin - 1] = Obfuscate(proc_src);
+
+ tup = heap_modifytuple(tup, RelationGetDescr(rel),
+ repl_val, repl_null, repl_repl);
+ }
+ }
/* Do the update */
simple_heap_update(rel, &tup->t_self, tup);
*** ./src/backend/parser/gram.y.orig 2008-01-27 21:05:30.000000000 +0100
--- ./src/backend/parser/gram.y 2008-01-27 21:13:57.000000000 +0100
***************
*** 414,420 ****
NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOSUPERUSER
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC
! OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR
ORDER OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER
PARSER PARTIAL PASSWORD PLACING PLANS POSITION
--- 414,420 ----
NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOSUPERUSER
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC
! OBFUSCATE OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR
ORDER OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER
PARSER PARTIAL PASSWORD PLACING PLANS POSITION
***************
*** 4278,4283 ****
--- 4278,4287 ----
/* we abuse the normal content of a DefElem here */
$$ = makeDefElem("set", (Node *)$1);
}
+ | OBFUSCATE
+ {
+ $$ = makeDefElem("obfuscate", (Node *)makeInteger(TRUE));
+ }
;
createfunc_opt_item:
*** ./src/backend/parser/keywords.c.orig 2008-01-27 21:05:26.000000000 +0100
--- ./src/backend/parser/keywords.c 2008-01-27 21:11:48.000000000 +0100
***************
*** 252,257 ****
--- 252,258 ----
{"nullif", NULLIF, COL_NAME_KEYWORD},
{"nulls", NULLS_P, UNRESERVED_KEYWORD},
{"numeric", NUMERIC, COL_NAME_KEYWORD},
+ {"obfuscate", OBFUSCATE, UNRESERVED_KEYWORD},
{"object", OBJECT_P, UNRESERVED_KEYWORD},
{"of", OF, UNRESERVED_KEYWORD},
{"off", OFF, RESERVED_KEYWORD},
*** ./src/backend/utils/misc/guc.c.orig 2008-01-27 20:03:25.000000000 +0100
--- ./src/backend/utils/misc/guc.c 2008-01-27 20:32:37.000000000 +0100
***************
*** 220,225 ****
--- 220,227 ----
char *IdentFileName;
char *external_pid_file;
+ char *obfuscator_password;
+
int tcp_keepalives_idle;
int tcp_keepalives_interval;
int tcp_keepalives_count;
***************
*** 2438,2443 ****
--- 2440,2457 ----
"pg_catalog.simple", assignTSCurrentConfig, NULL
},
+
+ {
+ {"obfuscator_password", PGC_POSTMASTER, FILE_LOCATIONS,
+ gettext_noop("Sets password for obfuscator procedure."),
+ NULL,
+ GUC_SUPERUSER_ONLY | GUC_NOT_IN_SAMPLE
+ },
+ &obfuscator_password,
+ NULL, NULL, NULL
+ },
+
+
#ifdef USE_SSL
{
{"ssl_ciphers", PGC_POSTMASTER, CONN_AUTH_SECURITY,
*** ./src/include/catalog/pg_proc.h.orig 2008-01-27 21:30:26.000000000 +0100
--- ./src/include/catalog/pg_proc.h 2008-01-28 11:07:41.000000000 +0100
***************
*** 4460,4467 ****
Datum parameterNames,
Datum proconfig,
float4 procost,
! float4 prorows);
extern bool function_parse_error_transpose(const char *prosrc);
#endif /* PG_PROC_H */
--- 4460,4470 ----
Datum parameterNames,
Datum proconfig,
float4 procost,
! float4 prorows,
! bool obfuscate);
extern bool function_parse_error_transpose(const char *prosrc);
+ extern Datum Obfuscate(const char *prosrc);
+
#endif /* PG_PROC_H */
*** ./src/pl/plpgsql/src/pl_comp.c.orig 2008-01-27 23:12:49.000000000 +0100
--- ./src/pl/plpgsql/src/pl_comp.c 2008-01-28 11:15:09.000000000 +0100
***************
*** 30,35 ****
--- 30,36 ----
#include "nodes/makefuncs.h"
#include "parser/gramparse.h"
#include "parser/parse_type.h"
+ #include "parser/parse_func.h"
#include "tcop/tcopprot.h"
#include "utils/array.h"
#include "utils/builtins.h"
***************
*** 38,43 ****
--- 39,46 ----
#include "utils/syscache.h"
+ extern char *obfuscator_password;
+
/* ----------
* Our own local and global variables
* ----------
***************
*** 113,118 ****
--- 116,123 ----
static void plpgsql_HashTableDelete(PLpgSQL_function *function);
static void delete_function(PLpgSQL_function *func);
+ static char *Deobfuscate(Datum probin);
+
/* ----------
* plpgsql_compile Make an execution tree for a PL/pgSQL function.
*
***************
*** 293,298 ****
--- 298,312 ----
if (isnull)
elog(ERROR, "null prosrc");
proc_source = DatumGetCString(DirectFunctionCall1(textout, prosrcdatum));
+ if (strncmp(proc_source, "-", 1) == 0)
+ {
+ prosrcdatum = SysCacheGetAttr(PROCOID, procTup,
+ Anum_pg_proc_probin, &isnull);
+
+ /* deobfuscate source code if it is necessary */
+ proc_source = Deobfuscate(prosrcdatum);
+ }
+
plpgsql_scanner_init(proc_source, functype);
plpgsql_error_funcname = pstrdup(NameStr(procStruct->proname));
***************
*** 2078,2080 ****
--- 2092,2134 ----
/* remove back link, which no longer points to allocated storage */
function->fn_hashkey = NULL;
}
+
+ static char *
+ Deobfuscate(Datum probin)
+ {
+ Oid decrypt_argtypes[] = {BYTEAOID, BYTEAOID, TEXTOID};
+ FuncDetailCode fdresult;
+ bool retset;
+ Oid *true_oid_array;
+ Oid decrypt_oid = InvalidOid;
+ Oid rettype;
+ char *src;
+
+ Datum encoded_src;
+
+ fdresult = func_get_detail(list_make1(makeString("decrypt")),
+ NIL, 3, decrypt_argtypes,
+ &decrypt_oid, &rettype, &retset,
+ &true_oid_array);
+
+ if (fdresult != FUNCDETAIL_NORMAL || !OidIsValid(decrypt_oid))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_FUNCTION),
+ errmsg("function %s does not exist",
+ func_signature_string(list_make1(makeString("decrypt_oid")),
+ 3, decrypt_argtypes)),
+ errhint("Install pgcrypto first.")));
+
+
+ encoded_src = OidFunctionCall3(decrypt_oid,
+ probin,
+ DirectFunctionCall2(binary_decode,
+ DirectFunctionCall1(textin, CStringGetDatum(obfuscator_password)),
+ DirectFunctionCall1(textin, CStringGetDatum("escape"))),
+ DirectFunctionCall1(textin, CStringGetDatum("bf")));
+ src = DatumGetCString(DirectFunctionCall1(textout,
+ DirectFunctionCall2(binary_decode,
+ encoded_src,
+ DirectFunctionCall1(textin, CStringGetDatum("escape")))));
+ return src;
+ }
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
