hi
2016-10-04 9:18 GMT+02:00 Gilles Darold <gilles.dar...@dalibo.com>:
> Le 03/10/2016 à 23:23, Gilles Darold a écrit :
> > Le 03/10/2016 à 23:03, Robert Haas a écrit :
> >> On Mon, Oct 3, 2016 at 3:54 PM, Gilles Darold <gil...@darold.net>
> wrote:
> >>> 4) An other problem is that like this this patch will allow anyone to
> upload into a
> >>> column the content of any system file that can be read by postgres
> system user
> >>> and then allow non system user to read its content.
> >> I thought this was a client-side feature, so that it would let a
> >> client upload any file that the client can read, but not things that
> >> can only be read by the postgres system user.
> >>
> > Yes that's right, sorry for the noise, forget this fourth report.
> >
>
> After some more though there is still a security issue here. For a
> PostgreSQL user who also have login acces to the server, it is possible
> to read any file that the postgres system user can read, especially a
> .pgpass or a recovery.conf containing password.
>
here is new update - some mentioned issues are fixed + regress tests and
docs
regards
Pavel
>
>
> --
> Gilles Darold
> Consultant PostgreSQL
> http://dalibo.com - http://dalibo.org
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 4806e77..9fb9cd9 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2714,6 +2714,24 @@ testdb=> <userinput>\setenv LESS -imx4F</userinput>
</varlistentry>
<varlistentry>
+ <term><literal>\setfileref <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> ]</literal></term>
+
+ <listitem>
+ <para>
+ Sets the internal variable name as a reference to the file path
+ set as value. To unset a variable, use the <command>\unset</command> command.
+
+ File references are shown as file path prefixed with the ^ character
+ when using the <command>\set</command> command alone.
+
+ Valid variable names can contain characters, digits, and underscores.
+ See the section Variables below for details. Variable names are
+ case-sensitive.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>\sf[+] <replaceable class="parameter">function_description</> </literal></term>
<listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index a9a2fdb..daa40b2 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1363,6 +1363,41 @@ exec_command(const char *cmd,
free(envval);
}
+ /* \setfileref - set variable by reference on file */
+ else if (strcmp(cmd, "setfileref") == 0)
+ {
+ char *name = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+
+ char *ref = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+
+ success = false;
+
+ if (!name && !ref)
+ {
+ /* list all variables */
+ PrintSetFileRefVariables(pset.vars);
+ success = true;
+ }
+ else
+ {
+ if (!name || !ref)
+ {
+ psql_error("\\%s: missing required argument\n", cmd);
+ success = false;
+ }
+ else
+ {
+ if (!SetFileRef(pset.vars, name, ref))
+ {
+ psql_error("\\%s: error while setting variable\n", cmd);
+ success = false;
+ }
+ }
+ }
+ }
+
/* \sf -- show a function's source code */
else if (strcmp(cmd, "sf") == 0 || strcmp(cmd, "sf+") == 0)
{
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index a7789df..64fa5a2 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -12,6 +12,8 @@
#include <limits.h>
#include <math.h>
#include <signal.h>
+#include <sys/stat.h>
+
#ifndef WIN32
#include <unistd.h> /* for write() */
#else
@@ -25,6 +27,7 @@
#include "settings.h"
#include "command.h"
#include "copy.h"
+#include "catalog/pg_type.h"
#include "crosstabview.h"
#include "fe_utils/mbprint.h"
@@ -33,7 +36,6 @@ static bool ExecQueryUsingCursor(const char *query, double *elapsed_msec);
static bool command_no_begin(const char *query);
static bool is_select_command(const char *query);
-
/*
* openQueryOutputFile --- attempt to open a query output file
*
@@ -109,6 +111,143 @@ setQFout(const char *fname)
return true;
}
+void
+psql_reset_query_params(void)
+{
+ int i;
+
+ for (i = 0; i < pset.nparams; i++)
+ if (pset.params[i] != NULL)
+ {
+ PQfreemem(pset.params[i]);
+ pset.params[i] = NULL;
+ }
+
+ pset.nparams = 0;
+}
+
+/*
+ * Load a content of the file_ref related file to query params buffer.
+ * When escaping is requested, then the text content is expected.
+ * Without escaping the bytea content is expected and related bytea
+ * escaping is processed.
+ */
+static char *
+get_file_ref_content(const char *value, bool escape, bool as_ident)
+{
+ PQExpBufferData buffer;
+ FILE *fd = NULL;
+ char *fname;
+ char *escaped_value;
+ char line[1024];
+ size_t size;
+ struct stat fst;
+
+ fname = pstrdup(value);
+
+ expand_tilde(&fname);
+ if (!fname)
+ {
+ psql_error("missing valid path to file\n");
+ return NULL;
+ }
+
+ canonicalize_path(fname);
+
+ fd = fopen(fname, PG_BINARY_R);
+ if (!fd)
+ {
+ psql_error("%s: %s\n", fname, strerror(errno));
+ PQfreemem(fname);
+ return NULL;
+ }
+
+ /* ensure, max size of file content < 1GB */
+ if (fstat(fileno(fd), &fst) == -1)
+ {
+ psql_error("%s: %s\n", fname, strerror(errno));
+ PQfreemem(fname);
+ return NULL;
+ }
+
+ if (fst.st_size > ((int64) 1024) * 1024 * 1024)
+ {
+ psql_error("file %s is too big (bigger than 1GB)\n", fname);
+ PQfreemem(fname);
+ return NULL;
+ }
+
+ /* can append another parameter */
+ if (pset.nparams >= MAX_BINARY_PARAMS)
+ {
+ psql_error("too much binary parameters");
+ PQfreemem(fname);
+ return NULL;
+ }
+
+ if (!pset.db)
+ {
+ psql_error("cannot escape without active connection\n");
+ PQfreemem(fname);
+ return NULL;
+ }
+
+ initPQExpBuffer(&buffer);
+
+ while ((size = fread(line, 1, sizeof(line), fd)) > 0)
+ appendBinaryPQExpBuffer(&buffer, line, size);
+
+ if (ferror(fd))
+ {
+ psql_error("%s: %s\n", fname, strerror(errno));
+ PQfreemem(fname);
+ termPQExpBuffer(&buffer);
+ return NULL;
+ }
+
+ if (escape)
+ {
+ if (as_ident)
+ {
+ /* Identifiers should not be passed as query parameters */
+ psql_error("A file reference cannot be used as a identifier");
+ PQfreemem(fname);
+ termPQExpBuffer(&buffer);
+ return NULL;
+ }
+ else
+ {
+ /* escaping is not necessary for text parameters */
+ escaped_value = pstrdup(buffer.data);
+ pset.paramTypes[pset.nparams] = TEXTOID;
+ }
+ }
+ else
+ {
+ escaped_value = (char *)
+ PQescapeByteaConn(pset.db,
+ (const unsigned char *) buffer.data, buffer.len, &size);
+ pset.paramTypes[pset.nparams] = BYTEAOID;
+ }
+
+ /* fname, buffer is not necessary longer */
+ PQfreemem(fname);
+ termPQExpBuffer(&buffer);
+
+ if (escaped_value == NULL)
+ {
+ const char *error = PQerrorMessage(pset.db);
+
+ psql_error("%s", error);
+ return NULL;
+ }
+
+ pset.params[pset.nparams] = escaped_value;
+
+ snprintf(line, sizeof(line) - 1, "$%d", ++pset.nparams);
+
+ return pstrdup(line);
+}
/*
* Variable-fetching callback for flex lexer
@@ -125,11 +264,15 @@ psql_get_variable(const char *varname, bool escape, bool as_ident)
{
char *result;
const char *value;
+ bool is_file_ref;
- value = GetVariable(pset.vars, varname);
+ value = (char *) GetVariableOrFileRef(pset.vars, varname, &is_file_ref);
if (!value)
return NULL;
+ if (is_file_ref)
+ return get_file_ref_content(value, escape, as_ident);
+
if (escape)
{
char *escaped_value;
@@ -1287,7 +1430,16 @@ SendQuery(const char *query)
if (pset.timing)
INSTR_TIME_SET_CURRENT(before);
- results = PQexec(pset.db, query);
+ if (pset.nparams > 0)
+ results = PQexecParams(pset.db, query,
+ pset.nparams,
+ pset.paramTypes,
+ (const char * const *) pset.params,
+ NULL,
+ NULL,
+ 0);
+ else
+ results = PQexec(pset.db, query);
/* these operations are included in the timing result: */
ResetCancelConn();
@@ -1432,7 +1584,6 @@ sendquery_cleanup:
return OK;
}
-
/*
* ExecQueryUsingCursor: run a SELECT-like query using a cursor
*
diff --git a/src/bin/psql/common.h b/src/bin/psql/common.h
index bdcb58f..4f46b9c 100644
--- a/src/bin/psql/common.h
+++ b/src/bin/psql/common.h
@@ -38,6 +38,8 @@ extern int PSQLexecWatch(const char *query, const printQueryOpt *opt);
extern bool SendQuery(const char *query);
+void psql_reset_query_params(void);
+
extern bool is_superuser(void);
extern bool standard_strings(void);
extern const char *session_username(void);
diff --git a/src/bin/psql/mainloop.c b/src/bin/psql/mainloop.c
index 37dfa4d..23fd6d3 100644
--- a/src/bin/psql/mainloop.c
+++ b/src/bin/psql/mainloop.c
@@ -23,7 +23,6 @@ const PsqlScanCallbacks psqlscan_callbacks = {
psql_error
};
-
/*
* Main processing loop for reading lines of input
* and sending them to the backend.
@@ -403,6 +402,9 @@ MainLoop(FILE *source)
psql_scan_finish(scan_state);
free(line);
+ /* reset binary parameters */
+ psql_reset_query_params();
+
if (slashCmdStatus == PSQL_CMD_TERMINATE)
{
successResult = EXIT_SUCCESS;
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 8cfe9d2..2874704 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -77,6 +77,8 @@ enum trivalue
TRI_YES
};
+#define MAX_BINARY_PARAMS 32
+
typedef struct _psqlSettings
{
PGconn *db; /* connection to backend */
@@ -135,6 +137,9 @@ typedef struct _psqlSettings
const char *prompt3;
PGVerbosity verbosity; /* current error verbosity level */
PGContextVisibility show_context; /* current context display level */
+ int nparams;
+ Oid paramTypes[MAX_BINARY_PARAMS];
+ char *params[MAX_BINARY_PARAMS];
} PsqlSettings;
extern PsqlSettings pset;
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 7ce05fb..05715a6 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -107,6 +107,7 @@ main(int argc, char *argv[])
char password[100];
char *password_prompt = NULL;
bool new_pass;
+ int i;
set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("psql"));
@@ -140,6 +141,10 @@ main(int argc, char *argv[])
pset.cur_cmd_source = stdin;
pset.cur_cmd_interactive = false;
+ pset.nparams = 0;
+ for (i = 0; i < MAX_BINARY_PARAMS; i++)
+ pset.params[i] = NULL;
+
/* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */
pset.popt.topt.format = PRINT_ALIGNED;
pset.popt.topt.border = 1;
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 50a45eb..ff4bf73 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1294,7 +1294,7 @@ psql_completion(const char *text, int start, int end)
"\\f", "\\g", "\\gexec", "\\gset", "\\h", "\\help", "\\H", "\\i", "\\ir", "\\l",
"\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
"\\o", "\\p", "\\password", "\\prompt", "\\pset", "\\q", "\\qecho", "\\r",
- "\\s", "\\set", "\\setenv", "\\sf", "\\sv", "\\t", "\\T",
+ "\\s", "\\set", "\\setenv", "\\setfileref", "\\sf", "\\sv", "\\t", "\\T",
"\\timing", "\\unset", "\\x", "\\w", "\\watch", "\\z", "\\!", NULL
};
@@ -3128,6 +3128,12 @@ psql_completion(const char *text, int start, int end)
matches = completion_matches(text, complete_from_files);
}
+ else if (Matches2("\\setfileref", MatchAny))
+ {
+ completion_charp = "\\";
+ matches = completion_matches(text, complete_from_files);
+ }
+
/*
* Finally, we look through the list of "things", such as TABLE, INDEX and
* check if that was the previous word. If so, execute the query to get a
diff --git a/src/bin/psql/variables.c b/src/bin/psql/variables.c
index f43f418..74b3f7b 100644
--- a/src/bin/psql/variables.c
+++ b/src/bin/psql/variables.c
@@ -71,6 +71,31 @@ GetVariable(VariableSpace space, const char *name)
if (strcmp(current->name, name) == 0)
{
/* this is correct answer when value is NULL, too */
+
+ return current->value;
+ }
+ }
+
+ return NULL;
+}
+
+const char *
+GetVariableOrFileRef(VariableSpace space, const char *name, bool *is_file_ref)
+{
+ struct _variable *current;
+
+ if (!space)
+ return NULL;
+
+ for (current = space->next; current; current = current->next)
+ {
+ if (strcmp(current->name, name) == 0)
+ {
+ /* this is correct answer when value is NULL, too */
+
+ if (is_file_ref)
+ *is_file_ref = current->is_file_ref;
+
return current->value;
}
}
@@ -178,7 +203,7 @@ PrintVariables(VariableSpace space)
for (ptr = space->next; ptr; ptr = ptr->next)
{
if (ptr->value)
- printf("%s = '%s'\n", ptr->name, ptr->value);
+ printf("%s = %s'%s'\n", ptr->name, ptr->is_file_ref ? "^" : "", ptr->value);
if (cancel_pressed)
break;
}
@@ -218,6 +243,64 @@ SetVariable(VariableSpace space, const char *name, const char *value)
/* not present, make new entry */
current = pg_malloc(sizeof *current);
current->name = pg_strdup(name);
+ current->is_file_ref = false;
+ current->value = pg_strdup(value);
+ current->assign_hook = NULL;
+ current->next = NULL;
+ previous->next = current;
+ return true;
+}
+
+void
+PrintSetFileRefVariables(VariableSpace space)
+{
+ struct _variable *ptr;
+
+ if (!space)
+ return;
+
+ for (ptr = space->next; ptr; ptr = ptr->next)
+ {
+ if (ptr->is_file_ref && ptr->value)
+ printf("%s = '%s'\n", ptr->name, ptr->value);
+ if (cancel_pressed)
+ break;
+ }
+}
+
+bool
+SetFileRef(VariableSpace space, const char *name, const char *value)
+{
+ struct _variable *current,
+ *previous;
+
+ if (!space)
+ return false;
+
+ if (!valid_variable_name(name))
+ return false;
+
+ if (!value)
+ return DeleteVariable(space, name);
+
+ for (previous = space, current = space->next;
+ current;
+ previous = current, current = current->next)
+ {
+ if (strcmp(current->name, name) == 0)
+ {
+ /* found entry, so update */
+ if (current->value)
+ free(current->value);
+ current->value = pg_strdup(value);
+ return true;
+ }
+ }
+
+ /* not present, make new entry */
+ current = pg_malloc(sizeof *current);
+ current->is_file_ref = true;
+ current->name = pg_strdup(name);
current->value = pg_strdup(value);
current->assign_hook = NULL;
current->next = NULL;
diff --git a/src/bin/psql/variables.h b/src/bin/psql/variables.h
index d7a05a1..bad4e37 100644
--- a/src/bin/psql/variables.h
+++ b/src/bin/psql/variables.h
@@ -26,6 +26,7 @@ struct _variable
{
char *name;
char *value;
+ bool is_file_ref;
VariableAssignHook assign_hook;
struct _variable *next;
};
@@ -34,6 +35,7 @@ typedef struct _variable *VariableSpace;
VariableSpace CreateVariableSpace(void);
const char *GetVariable(VariableSpace space, const char *name);
+const char *GetVariableOrFileRef(VariableSpace space, const char *name, bool *is_file_ref);
bool ParseVariableBool(const char *value, const char *name);
int ParseVariableNum(const char *val,
@@ -47,8 +49,11 @@ int GetVariableNum(VariableSpace space,
bool allowtrail);
void PrintVariables(VariableSpace space);
+void PrintSetFileRefVariables(VariableSpace space);
+
bool SetVariable(VariableSpace space, const char *name, const char *value);
+bool SetFileRef(VariableSpace space, const char *name, const char *value);
bool SetVariableAssignHook(VariableSpace space, const char *name, VariableAssignHook hook);
bool SetVariableBool(VariableSpace space, const char *name);
bool DeleteVariable(VariableSpace space, const char *name);
diff --git a/src/test/regress/input/misc.source b/src/test/regress/input/misc.source
index dd2d1b2..1ca29fb 100644
--- a/src/test/regress/input/misc.source
+++ b/src/test/regress/input/misc.source
@@ -263,6 +263,29 @@ select i, length(t), octet_length(t), oldstyle_length(i,t) from oldstyle_test;
drop table oldstyle_test;
--
+-- psql refvariables
+--
+
+CREATE TABLE test_setref(a text, b bytea);
+
+-- use two different ways for import data - result should be same
+\lo_import @abs_builddir@/data/hash.data
+\set lo_oid :LASTOID
+INSERT INTO test_setref (b) VALUES(lo_get(:lo_oid));
+\lo_unlink :lo_oid
+SELECT md5(b) FROM test_setref;
+TRUNCATE test_setref;
+
+\setfileref testdata @abs_builddir@/data/hash.data
+INSERT INTO test_setref VALUES(convert_from(:testdata, current_setting('server_encoding')), :testdata);
+SELECT md5(a), md5(b) FROM test_setref;
+TRUNCATE test_setref;
+INSERT INTO test_setref(a) VALUES(:'testdata');
+SELECT md5(a) FROM test_setref;
+
+DROP TABLE test_setref;
+
+--
-- functional joins
--
diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source
index 574ef0d..a26b461 100644
--- a/src/test/regress/output/misc.source
+++ b/src/test/regress/output/misc.source
@@ -700,6 +700,39 @@ select i, length(t), octet_length(t), oldstyle_length(i,t) from oldstyle_test;
drop table oldstyle_test;
--
+-- psql refvariables
+--
+CREATE TABLE test_setref(a text, b bytea);
+-- use two different ways for import data - result should be same
+\lo_import @abs_builddir@/data/hash.data
+\set lo_oid :LASTOID
+INSERT INTO test_setref (b) VALUES(lo_get(:lo_oid));
+\lo_unlink :lo_oid
+SELECT md5(b) FROM test_setref;
+ md5
+----------------------------------
+ e446fe6ea5a347e69670633412c7f8cb
+(1 row)
+
+TRUNCATE test_setref;
+\setfileref testdata @abs_builddir@/data/hash.data
+INSERT INTO test_setref VALUES(convert_from(:testdata, current_setting('server_encoding')), :testdata);
+SELECT md5(a), md5(b) FROM test_setref;
+ md5 | md5
+----------------------------------+----------------------------------
+ e446fe6ea5a347e69670633412c7f8cb | e446fe6ea5a347e69670633412c7f8cb
+(1 row)
+
+TRUNCATE test_setref;
+INSERT INTO test_setref(a) VALUES(:'testdata');
+SELECT md5(a) FROM test_setref;
+ md5
+----------------------------------
+ e446fe6ea5a347e69670633412c7f8cb
+(1 row)
+
+DROP TABLE test_setref;
+--
-- functional joins
--
--
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
