dblink for 8.4 should work without user-mappings
contrib/dblink in 8.4 supports a server name by CREATE SERVER for connection
string, but it always requires an user-mapping (by CREATE USER MAPPING).
However, I think it should work user-mappings because it works when
the connection string is passed directly.
=# SELECT * FROM dblink('dbname=postgres', 'SELECT current_user') AS t(i name);
(ok)
=# CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
=# CREATE SERVER server1 FOREIGN DATA WRAPPER postgresql OPTIONS (dbname 'postgres');
=# SELECT * FROM dblink('server1', 'SELECT 1') AS t(i integer);
ERROR: user mapping not found for "postgres"
The attached patch adds 'missing_ok' parameter to GetUserMapping() and
made dblink to use it. There should be no additional security issues here
because dblink's original security check works even for server name mode.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
Attachments:
dblink-no-user-mapping.patchapplication/octet-stream; name=dblink-no-user-mapping.patchDownload
Index: contrib/dblink/dblink.c
===================================================================
--- contrib/dblink/dblink.c (HEAD)
+++ contrib/dblink/dblink.c (working copy)
@@ -2408,7 +2408,7 @@
Oid fdwid = foreign_server->fdwid;
Oid userid = GetUserId();
- user_mapping = GetUserMapping(userid, serverid);
+ user_mapping = GetUserMapping(userid, serverid, true);
fdw = GetForeignDataWrapper(fdwid);
/* Check permissions, user must have usage on the server. */
@@ -2432,7 +2432,7 @@
escape_param_str(strVal(def->arg)));
}
- foreach(cell, user_mapping->options)
+ foreach(cell, user_mapping ? user_mapping->options : NIL)
{
DefElem *def = lfirst(cell);
Index: src/backend/foreign/foreign.c
===================================================================
--- src/backend/foreign/foreign.c (HEAD)
+++ src/backend/foreign/foreign.c (working copy)
@@ -212,7 +212,7 @@
* PUBLIC mappings (userid == InvalidOid).
*/
UserMapping *
-GetUserMapping(Oid userid, Oid serverid)
+GetUserMapping(Oid userid, Oid serverid, bool missing_ok)
{
Form_pg_user_mapping umform;
Datum datum;
@@ -235,10 +235,15 @@
}
if (!HeapTupleIsValid(tp))
+ {
+ if (missing_ok)
+ return NULL;
+
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("user mapping not found for \"%s\"",
MappingUserName(userid))));
+ }
umform = (Form_pg_user_mapping) GETSTRUCT(tp);
Index: src/include/foreign/foreign.h
===================================================================
--- src/include/foreign/foreign.h (HEAD)
+++ src/include/foreign/foreign.h (working copy)
@@ -63,7 +63,7 @@
extern ForeignServer *GetForeignServer(Oid serverid);
extern ForeignServer *GetForeignServerByName(const char *name, bool missing_ok);
extern Oid GetForeignServerOidByName(const char *name, bool missing_ok);
-extern UserMapping *GetUserMapping(Oid userid, Oid serverid);
+extern UserMapping *GetUserMapping(Oid userid, Oid serverid, bool missing_ok);
extern ForeignDataWrapper *GetForeignDataWrapper(Oid fdwid);
extern ForeignDataWrapper *GetForeignDataWrapperByName(const char *name,
bool missing_ok);
On Wednesday 24 June 2009 05:42:01 Itagaki Takahiro wrote:
contrib/dblink in 8.4 supports a server name by CREATE SERVER for
connection string, but it always requires an user-mapping (by CREATE USER
MAPPING). However, I think it should work user-mappings because it works
when the connection string is passed directly.
I had looked into this the other day. I *think* that SQL/MED requires a user
mapping to be present. There are cases where either behavior might be useful,
but we should think about this carefully. It's not simply a question of
security, as you appear to imply.
What happened to this patch?
---------------------------------------------------------------------------
Itagaki Takahiro wrote:
contrib/dblink in 8.4 supports a server name by CREATE SERVER for connection
string, but it always requires an user-mapping (by CREATE USER MAPPING).
However, I think it should work user-mappings because it works when
the connection string is passed directly.=# SELECT * FROM dblink('dbname=postgres', 'SELECT current_user') AS t(i name);
(ok)=# CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
=# CREATE SERVER server1 FOREIGN DATA WRAPPER postgresql OPTIONS (dbname 'postgres');
=# SELECT * FROM dblink('server1', 'SELECT 1') AS t(i integer);
ERROR: user mapping not found for "postgres"The attached patch adds 'missing_ok' parameter to GetUserMapping() and
made dblink to use it. There should be no additional security issues here
because dblink's original security check works even for server name mode.Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
[ Attachment, skipping... ]
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +Bruce Momjian <bruce@momjian.us> wrote:
What happened to this patch?
It was rejected because SQL standard always requires an user mapping.
I agree about the decision, too.
---------------------------------------------------------------------------
Itagaki Takahiro wrote:contrib/dblink in 8.4 supports a server name by CREATE SERVER for connection
string, but it always requires an user-mapping (by CREATE USER MAPPING).
However, I think it should work user-mappings because it works when
the connection string is passed directly.The attached patch adds 'missing_ok' parameter to GetUserMapping() and
made dblink to use it. There should be no additional security issues here
because dblink's original security check works even for server name mode.
Regards,
---
Takahiro Itagaki
NTT Open Source Software Center