Skip to content

Commit a776eae

Browse files
jconwayjconway
committed
Restrict non-superusers to password authenticated connections
to prevent possible escalation of privilege. Provide new SECURITY DEFINER functions with old behavior, but initially REVOKE ALL from public for these functions. Per list discussion and design proposed by Tom Lane.
1 parent 7ca6422 commit a776eae

File tree

3 files changed

+194
-0
lines changed

3 files changed

+194
-0
lines changed

‎contrib/dblink/dblink.c

Lines changed: 144 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@
3232

3333
#include "fmgr.h"
3434
#include "funcapi.h"
35+
#include "miscadmin.h"
3536
#include "access/tupdesc.h"
3637
#include "access/heapam.h"
3738
#include "catalog/catname.h"
@@ -71,6 +72,7 @@ static dblink_results *get_res_ptr(int32 res_id_index);
7172
static void append_res_ptr(dblink_results * results);
7273
static void remove_res_ptr(dblink_results * results);
7374
static char *generate_relation_name(Oid relid);
75+
static char *connstr_strip_password(const char *connstr);
7476

7577
/* Global */
7678
List *res_id = NIL;
@@ -105,6 +107,22 @@ dblink_connect(PG_FUNCTION_ARGS)
105107
PQfinish(persistent_conn);
106108

107109
oldcontext = MemoryContextSwitchTo(TopMemoryContext);
110+
111+
/* for non-superusers, check that server requires a password */
112+
if (!superuser())
113+
{
114+
/* this attempt must fail */
115+
persistent_conn = PQconnectdb(connstr_strip_password(connstr));
116+
117+
if (PQstatus(persistent_conn) == CONNECTION_OK)
118+
{
119+
PQfinish(persistent_conn);
120+
persistent_conn = NULL;
121+
elog(ERROR, "Non-superuser cannot connect if the server does not request a password.");
122+
}
123+
else
124+
PQfinish(persistent_conn);
125+
}
108126
persistent_conn = PQconnectdb(connstr);
109127
MemoryContextSwitchTo(oldcontext);
110128

@@ -2032,3 +2050,129 @@ generate_relation_name(Oid relid)
20322050

20332051
return result;
20342052
}
2053+
2054+
/*
2055+
* Modified version of conninfo_parse() from fe-connect.c
2056+
* Used to remove any password from the connection string
2057+
* in order to test whether the server auth method will
2058+
* require it.
2059+
*/
2060+
static char *
2061+
connstr_strip_password(const char *connstr)
2062+
{
2063+
char *pname;
2064+
char *pval;
2065+
char *buf;
2066+
char *cp;
2067+
char *cp2;
2068+
StringInfoData result;
2069+
2070+
/* initialize return value */
2071+
initStringInfo(&result);
2072+
2073+
/* Need a modifiable copy of the input string */
2074+
buf = pstrdup(connstr);
2075+
cp = buf;
2076+
2077+
while (*cp)
2078+
{
2079+
/* Skip blanks before the parameter name */
2080+
if (isspace((unsigned char) *cp))
2081+
{
2082+
cp++;
2083+
continue;
2084+
}
2085+
2086+
/* Get the parameter name */
2087+
pname = cp;
2088+
while (*cp)
2089+
{
2090+
if (*cp == '=')
2091+
break;
2092+
if (isspace((unsigned char) *cp))
2093+
{
2094+
*cp++ = '\0';
2095+
while (*cp)
2096+
{
2097+
if (!isspace((unsigned char) *cp))
2098+
break;
2099+
cp++;
2100+
}
2101+
break;
2102+
}
2103+
cp++;
2104+
}
2105+
2106+
/* Check that there is a following '=' */
2107+
if (*cp != '=')
2108+
elog(ERROR, "missing \"=\" after \"%s\" in connection string", pname);
2109+
*cp++ = '\0';
2110+
2111+
/* Skip blanks after the '=' */
2112+
while (*cp)
2113+
{
2114+
if (!isspace((unsigned char) *cp))
2115+
break;
2116+
cp++;
2117+
}
2118+
2119+
/* Get the parameter value */
2120+
pval = cp;
2121+
2122+
if (*cp != '\'')
2123+
{
2124+
cp2 = pval;
2125+
while (*cp)
2126+
{
2127+
if (isspace((unsigned char) *cp))
2128+
{
2129+
*cp++ = '\0';
2130+
break;
2131+
}
2132+
if (*cp == '\\')
2133+
{
2134+
cp++;
2135+
if (*cp != '\0')
2136+
*cp2++ = *cp++;
2137+
}
2138+
else
2139+
*cp2++ = *cp++;
2140+
}
2141+
*cp2 = '\0';
2142+
}
2143+
else
2144+
{
2145+
cp2 = pval;
2146+
cp++;
2147+
for (;;)
2148+
{
2149+
if (*cp == '\0')
2150+
elog(ERROR, "unterminated quoted string in connection string");
2151+
if (*cp == '\\')
2152+
{
2153+
cp++;
2154+
if (*cp != '\0')
2155+
*cp2++ = *cp++;
2156+
continue;
2157+
}
2158+
if (*cp == '\'')
2159+
{
2160+
*cp2 = '\0';
2161+
cp++;
2162+
break;
2163+
}
2164+
*cp2++ = *cp++;
2165+
}
2166+
}
2167+
2168+
/*
2169+
* Now we have the name and the value. If it is not a password,
2170+
* append to the return connstr.
2171+
*/
2172+
if (strcmp("password", pname) != 0)
2173+
/* append the value */
2174+
appendStringInfo(&result, " %s='%s'", pname, pval);
2175+
}
2176+
2177+
return result.data;
2178+
}

‎contrib/dblink/dblink.sql.in

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,11 +14,23 @@
1414
--AS 'MODULE_PATHNAME','dblink_last_oid'
1515
--LANGUAGE 'C' WITH (isstrict);
1616

17+
-- dblink_connect now restricts non-superusers to password
18+
-- authenticated connections
1719
CREATE OR REPLACE FUNCTION dblink_connect (text)
1820
RETURNS text
1921
AS 'MODULE_PATHNAME','dblink_connect'
2022
LANGUAGE 'C' WITH (isstrict);
2123

24+
-- dblink_connect_u allows non-superusers to use
25+
-- non-password authenticated connections, but initially
26+
-- privileges are revoked from public
27+
CREATE OR REPLACE FUNCTION dblink_connect_u (text)
28+
RETURNS text
29+
AS 'MODULE_PATHNAME','dblink_connect'
30+
LANGUAGE C STRICT SECURITY DEFINER;
31+
32+
REVOKE ALL ON FUNCTION dblink_connect_u (text) FROM public;
33+
2234
CREATE OR REPLACE FUNCTION dblink_disconnect ()
2335
RETURNS text
2436
AS 'MODULE_PATHNAME','dblink_disconnect'

‎contrib/dblink/doc/connection

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,12 @@ Outputs
1818

1919
Returns status = "OK"
2020

21+
Notes
22+
23+
Only superusers may use dblink_connect to create non-password
24+
authenticated connections. If non-superusers need this capability,
25+
use dblink_connect_u instead.
26+
2127
Example usage
2228

2329
test=# select dblink_connect('dbname=template1');
@@ -26,6 +32,38 @@ test=# select dblink_connect('dbname=template1');
2632
OK
2733
(1 row)
2834

35+
==================================================================
36+
Name
37+
38+
dblink_connect_u -- Opens a persistent connection to a remote database
39+
40+
Synopsis
41+
42+
dblink_connect_u(text connstr)
43+
44+
Inputs
45+
46+
connstr
47+
48+
standard libpq format connection string,
49+
e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
50+
51+
Outputs
52+
53+
Returns status = "OK"
54+
55+
Notes
56+
57+
With dblink_connect_u, a non-superuser may connect to any database server
58+
using any authentication method. If the authentication method specified
59+
for a particular user does not require a password, impersonation and
60+
therefore escalation of privileges may occur. For this reason,
61+
dblink_connect_u is initially installed with all privileges revoked from
62+
public. Privilege to these functions should be granted with care.
63+
64+
Example usage
65+
66+
2967
==================================================================
3068
Name
3169

0 commit comments

Comments
 (0)