Re: [HACKERS] Mysql.whynot or PG vs MySQL comparison table?
Greg Stark wrote: Before i duplicate work does anyone have a MySQL.whynot or Postgres versus MySQL comparison table? Like f.ex http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009 ? -- Tommy Gildseth -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [patch] fix dblink security hole
Tom Lane wrote: Joe Conway [EMAIL PROTECTED] writes: Tom Lane wrote: No, the test to see if the server actually *asked* for the password is the important part at that end. Oh, I see that now. So yes, as far as I can tell, password_from_string is not used for anything anymore and should be removed. Okay. I just committed the patch without that change, but I'll go back and add it. I'm not quite sure I fully understand the consequence of this change. Does it basically mean that it's not possible to use .pgpass with dblink for authentication? The alternative then would be to hardcode the password in your stored procedures, or store it in a separate table somehow? -- Tommy Gildseth -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add dblink function to check if a named connection exists
Joe Conway wrote:
Tom Lane wrote:
Tommy Gildseth [EMAIL PROTECTED] writes:
One obvious disadvantage of this approach, is that I need to connect
and disconnect in every function. A possible solution to this, would
be having a function f.ex dblink_exists('connection_name') that
returns true/false depending on whether the connection already exists.
Can't you do this already?
SELECT 'myconn' = ANY (dblink_get_connections());
A dedicated function might be a tad faster, but it probably isn't going
to matter compared to the overhead of sending a remote query.
I agree. The above is about as simple as
SELECT dblink_exists('dtest1');
and probably not measurably slower. If you still think a dedicated
function is needed, please send the output of some performance testing
to justify it.
If you really want the notational simplicity, you could use an SQL
function to wrap it:
CREATE OR REPLACE FUNCTION dblink_exists(text)
RETURNS bool AS $$
SELECT $1 = ANY (dblink_get_connections())
$$ LANGUAGE sql;
dblink_get_connections() returns null if there are no connections
though, so the above will fail if you haven't already established a
connection, unless you also check for null, and not just false.
I guess you could rewrite the above function to something like:
CREATE OR REPLACE FUNCTION dblink_exists(text)
RETURNS bool AS $$
SELECT COALESCE($1 = ANY (dblink_get_connections()), false)
$$ LANGUAGE sql;
--
Tommy Gildseth
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add dblink function to check if a named connection exists
Joe Conway wrote: If you really want the notational simplicity, you could use an SQL function to wrap it: CREATE OR REPLACE FUNCTION dblink_exists(text) RETURNS bool AS $$ SELECT $1 = ANY (dblink_get_connections()) $$ LANGUAGE sql; Thanks, that seems like a reasonable way to solve this. -- Tommy Gildseth -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Add dblink function to check if a named connection exists
I have locked down access to all dblink_* functions, so that only
certain privileged users have access to them, and instead provide a set
of SRF functions defined as security definer functions, where I connect
to the remote server, fetch some data, disconnect from remote server,
and return the data.
One obvious disadvantage of this approach, is that I need to connect and
disconnect in every function. A possible solution to this, would be
having a function f.ex dblink_exists('connection_name') that returns
true/false depending on whether the connection already exists. This
way, I could just check if a named connection exists, and establish a
connection if not, and wait until the end of the session to disconnect
all established connections.
I've attached a patch with a suggested implementation of such a function.
--
Tommy Gildseth
Index: dblink.c
===
RCS file: /projects/cvsroot/pgsql/contrib/dblink/dblink.c,v
retrieving revision 1.73
diff -c -c -r1.73 dblink.c
*** dblink.c 4 Apr 2008 17:02:56 - 1.73
--- dblink.c 28 May 2008 08:06:23 -
***
*** 192,208
freeconn = true; \
} \
} while (0)
-
#define DBLINK_GET_NAMED_CONN \
do { \
! char *conname = text_to_cstring(PG_GETARG_TEXT_PP(0)); \
rconn = getConnectionByName(conname); \
if(rconn) \
conn = rconn-conn; \
- else \
- DBLINK_CONN_NOT_AVAIL; \
} while (0)
#define DBLINK_INIT \
do { \
if (!pconn) \
--- 192,214
freeconn = true; \
} \
} while (0)
#define DBLINK_GET_NAMED_CONN \
do { \
! char *conname = NULL; \
! DBLINK_GET_NAMED_CONN_IF_EXISTS; \
! if(!rconn) \
! DBLINK_CONN_NOT_AVAIL; \
! } while (0)
!
! #define DBLINK_GET_NAMED_CONN_IF_EXISTS \
! do { \
! conname = text_to_cstring(PG_GETARG_TEXT_PP(0)); \
rconn = getConnectionByName(conname); \
if(rconn) \
conn = rconn-conn; \
} while (0)
+
#define DBLINK_INIT \
do { \
if (!pconn) \
***
*** 1056,1061
--- 1062,1090
PG_RETURN_INT32(PQisBusy(conn));
}
+
+ /*
+ * Checks if a given named remote connection exists
+ *
+ * Returns 1 if the connection is busy, 0 otherwise
+ * Params:
+ * text connection_name - name of the connection to check
+ *
+ */
+ PG_FUNCTION_INFO_V1(dblink_exists);
+ Datum
+ dblink_exists(PG_FUNCTION_ARGS)
+ {
+ PGconn *conn = NULL;
+ remoteConn *rconn = NULL;
+ char *conname = NULL;
+
+ DBLINK_INIT;
+ DBLINK_GET_NAMED_CONN_IF_EXISTS;
+
+ PG_RETURN_BOOL(conn != NULL);
+ }
+
/*
* Cancels a running request on a connection
*
Index: dblink.h
===
RCS file: /projects/cvsroot/pgsql/contrib/dblink/dblink.h,v
retrieving revision 1.20
diff -c -c -r1.20 dblink.h
*** dblink.h 4 Apr 2008 16:57:21 - 1.20
--- dblink.h 28 May 2008 08:06:23 -
***
*** 49,54
--- 49,55
extern Datum dblink_get_result(PG_FUNCTION_ARGS);
extern Datum dblink_get_connections(PG_FUNCTION_ARGS);
extern Datum dblink_is_busy(PG_FUNCTION_ARGS);
+ extern Datum dblink_exists(PG_FUNCTION_ARGS);
extern Datum dblink_cancel_query(PG_FUNCTION_ARGS);
extern Datum dblink_error_message(PG_FUNCTION_ARGS);
extern Datum dblink_exec(PG_FUNCTION_ARGS);
Index: dblink.sql.in
===
RCS file: /projects/cvsroot/pgsql/contrib/dblink/dblink.sql.in,v
retrieving revision 1.17
diff -c -c -r1.17 dblink.sql.in
*** dblink.sql.in 5 Apr 2008 02:44:42 - 1.17
--- dblink.sql.in 28 May 2008 08:06:23 -
***
*** 178,183
--- 178,188
AS 'MODULE_PATHNAME', 'dblink_is_busy'
LANGUAGE C STRICT;
+ CREATE OR REPLACE FUNCTION dblink_exists(text)
+ RETURNS boolean
+ AS 'MODULE_PATHNAME', 'dblink_exists'
+ LANGUAGE C STRICT;
+
CREATE OR REPLACE FUNCTION dblink_get_result(text)
RETURNS SETOF record
AS 'MODULE_PATHNAME', 'dblink_get_result'
Index: expected/dblink.out
===
RCS file: /projects/cvsroot/pgsql/contrib/dblink/expected/dblink.out,v
retrieving revision 1.23
diff -c -c -r1.23 dblink.out
*** expected/dblink.out 6 Apr 2008 16:54:48 - 1.23
--- expected/dblink.out 28 May 2008 08:06:23 -
***
*** 731,736
--- 731,748
0
(1 row)
+ SELECT dblink_exists('dtest1');
+ dblink_exists
+ ---
+ t
+ (1 row)
+
+ SELECT dblink_exists('doesnotexist');
+ dblink_exists
+ ---
+ f
+ (1 row)
+
SELECT dblink_disconnect('dtest1');
dblink_disconnect
---
Index: sql/dblink.sql
===
RCS file: /projects/cvsroot/pgsql/contrib/dblink/sql/dblink.sql,v
retrieving revision 1.20
diff -c -c -r1.20 dblink.sql
*** sql/dblink.sql 6 Apr 2008 16:54:48 - 1.20
--- sql/dblink.sql 28 May 2008 08:06:23
Re: [HACKERS] DBLink's default user: postgres
Rodrigo Hjort wrote:
Is this the regular behavior on DBLink?
rot= SELECT user, current_database();
current_user | current_database
--+--
sa_rot | rot
(1 registro)
rot= SELECT *
rot- FROM dblink('dbname=escola',
rot( 'SELECT user, current_database()')
rot- AS (usr name, db name);
usr| db
--+
postgres | escola
(1 registro)
This way, I fear DBLink functions should become a vulnerability issue
on my database.
Is there any way to protect or override this setting? Or it should be
done on pg_hba.conf only?
This issue has been thoroughly discussed before. You can read more about
it in f.ex these threads:
http://archives.postgresql.org/pgsql-hackers/2007-06/msg00678.php
http://archives.postgresql.org/pgsql-patches/2007-07/msg0.php
--
Tommy Gildseth
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] XML export function signatures
Peter Eisentraut wrote: tableforest = false gives you something like tablename ... /tablename tableforest = true gives you something like tablename ... /tablename tablename ... /tablename ... How do you define the table name when fetching data using a join, union etc. where the data doesn't necessarily originate from a single table? Could it be an idea to allow the table name to be specified as a parameter to the function? Another neat feature would be if you could specify a set of columns which should be represented as attributes instead of nodes. -- Tommy ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
