Re: dblink connection security

Tom Lane wrote:
>
> Here's a straw-man proposal that we could perhaps do for 8.3:

What about using the attached for 8.3, as well as earlier?

It simply does not allow the local database user to become someone else
on the libpq remote connection unless they are a superuser. As Tom
noted, a simple SECURITY DEFINER function created as a superuser could
allow backward compatible behavior.

CREATE OR REPLACE FUNCTION dblink_connect_u(connstr TEXT)
RETURNS TEXT AS $$
DECLARE passed TEXT;
BEGIN
         SELECT  dblink_connect(connstr) INTO passed;
         RETURN passed;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

contrib_regression=# \c - foo
You are now connected to database "contrib_regression" as user "foo".

contrib_regression=> select dblink_connect('dbname=contrib_regression');
ERROR:  switching user not allowed
DETAIL:  failed to connect local user "foo" as remote user "postgres"
HINT:  only superuser may switch user name

contrib_regression=> select dblink_connect_u('dbname=contrib_regression');
  dblink_connect_u
------------------
  OK
(1 row)

Comments?

Thanks,

Joe
Index: dblink.c
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/dblink/dblink.c,v
retrieving revision 1.63
diff -c -r1.63 dblink.c
*** dblink.c    6 Apr 2007 04:21:41 -0000    1.63
--- dblink.c    7 Jul 2007 04:39:49 -0000
***************
*** 37,42 ****
--- 37,43 ----
  #include "libpq-fe.h"
  #include "fmgr.h"
  #include "funcapi.h"
+ #include "miscadmin.h"
  #include "access/heapam.h"
  #include "access/tupdesc.h"
  #include "catalog/namespace.h"
***************
*** 230,235 ****
--- 231,249 ----
          rconn = (remoteConn *) palloc(sizeof(remoteConn));
      conn = PQconnectdb(connstr);

+     if (!superuser())
+     {
+         char       *luser = PQuser(conn);
+         char       *cuser = GetUserNameFromId(GetUserId());
+
+         if (strcmp(luser, cuser) != 0)
+             ereport(ERROR,
+                     (errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
+                      errmsg("switching user not allowed"),
+                      errdetail("failed to connect local user \"%s\" as remote user \"%s\"", cuser, luser),
+                      errhint("only superuser may switch user name")));
+     }
+
      MemoryContextSwitchTo(oldcontext);

      if (PQstatus(conn) == CONNECTION_BAD)