作者
digoal
日期
2017-01-11
标签
PostgreSQL , unix socket , tcp
背景
可能出于某些原因(例如避免数据库的本机用户跨用户通过IP地址访问，而只通过LINUX文件权限来保护数据库的连接权限等)，用户只需要监听unix socket，而不期望监听任何IP（包括127.0.0.1）。
怎么做呢？
配置
配置是很简单的，只需要把listen_addresses设置为空就可以了。
```
vi $PGDATA/postgresql.conf
listen_addresses = ''
port = 5288
unix_socket_directories = '.' ## . 指 $PGDATA
unix_socket_directories = '/tmp' # comma-separated list of directories
# (change requires restart)
unix_socket_group = '' # (change requires restart)
unix_socket_permissions = 0777 # begin with 0 to use octal notation
# (change requires restart)
```
重启数据库
pg_ctl restart -m fast
检查监听端口，仅保留了UNIX SOCKET监听
netstat -anp|grep 5288
unix 2 [ ACC ] STREAM LISTENING 1797020240 19459/postgres ./.s.PGSQL.5288
使用unix socket连接数据库
```
psql -h $PGDATA -p 5288
psql (9.6.1)
Type "help" for help.
postgres=#
```
源码分析
man postgres
手册里面说得很清楚，当listen_addresses为空时，只监听unix socket
```
-h hostname
Specifies the IP host name or address on which postgres is to listen for TCP/IP connections from client applications. The value can also be a comma-separated list of addresses, or * to specify listening
on all available interfaces. An empty value specifies not listening on any IP addresses, in which case only Unix-domain sockets can be used to connect to the server. Defaults to listening only on
localhost. Specifying this option is equivalent to setting the listen_addresses configuration parameter.
-i
Allows remote clients to connect via TCP/IP (Internet domain) connections. Without this option, only local connections are accepted. This option is equivalent to setting listen_addresses to * in
postgresql.conf or via -h.
This option is deprecated since it does not allow access to the full functionality of listen_addresses. It's usually better to set listen_addresses directly.
-k directory
Specifies the directory of the Unix-domain socket on which postgres is to listen for connections from client applications. The value can also be a comma-separated list of directories. An empty value
specifies not listening on any Unix-domain sockets, in which case only TCP/IP sockets can be used to connect to the server. The default value is normally /tmp, but that can be changed at build time.
Specifying this option is equivalent to setting the unix_socket_directories configuration parameter.
```
对应的代码如下
src/backend/postmaster/postmaster.c
```
case 'h':
SetConfigOption("listen_addresses", optarg, PGC_POSTMASTER, PGC_S_ARGV);
break;
case 'i':
SetConfigOption("listen_addresses", "*", PGC_POSTMASTER, PGC_S_ARGV);
break;
case 'k':
SetConfigOption("unix_socket_directories", optarg, PGC_POSTMASTER, PGC_S_ARGV);
break;
```
监听的IP地址列表
```
if (ListenAddresses)
{
char rawstring;
List elemlist;
ListCell *l;
int success = 0;
/* Need a modifiable copy of ListenAddresses */
rawstring = pstrdup(ListenAddresses);
/* Parse string into list of hostnames */
if (!SplitIdentifierString(rawstring, ',', &elemlist))
{
/* syntax error in list */
ereport(FATAL,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("invalid list syntax in parameter \"%s\"",
"listen_addresses")));
}
foreach(l, elemlist)
{
char *curhost = (char *) lfirst(l);
if (strcmp(curhost, "*") == 0)
status = StreamServerPort(AF_UNSPEC, NULL,
(unsigned short) PostPortNumber,
NULL,
ListenSocket, MAXLISTEN);
else
status = StreamServerPort(AF_UNSPEC, curhost,
(unsigned short) PostPortNumber,
NULL,
ListenSocket, MAXLISTEN);
if (status == STATUS_OK)
{
success++;
/* record the first successful host addr in lockfile */
if (!listen_addr_saved)
{
AddToDataDirLockFile(LOCK_FILE_LINE_LISTEN_ADDR, curhost);
listen_addr_saved = true;
}
}
else
ereport(WARNING,
(errmsg("could not create listen socket for \"%s\"",
curhost)));
}
if (!success && elemlist != NIL)
ereport(FATAL,
(errmsg("could not create any TCP/IP sockets")));
list_free(elemlist);
pfree(rawstring);
}
```
监听的unix socket
```
ifdef HAVE_UNIX_SOCKETS
if (Unix_socket_directories)
{
char *rawstring;
List *elemlist;
ListCell *l;
int success = 0;
/* Need a modifiable copy of Unix_socket_directories */
rawstring = pstrdup(Unix_socket_directories);
/* Parse string into list of directories */
if (!SplitDirectoriesString(rawstring, ',', &elemlist))
{
/* syntax error in list */
ereport(FATAL,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("invalid list syntax in parameter \"%s\"",
"unix_socket_directories")));
}
foreach(l, elemlist)
{
char *socketdir = (char *) lfirst(l);
status = StreamServerPort(AF_UNIX, NULL,
(unsigned short) PostPortNumber,
socketdir,
ListenSocket, MAXLISTEN);
if (status == STATUS_OK)
{
success++;
/* record the first successful Unix socket in lockfile */
if (success == 1)
AddToDataDirLockFile(LOCK_FILE_LINE_SOCKET_DIR, socketdir);
}
else
ereport(WARNING,
(errmsg("could not create Unix-domain socket in directory \"%s\"",
socketdir)));
}
if (!success && elemlist != NIL)
ereport(FATAL,
(errmsg("could not create any Unix-domain sockets")));
list_free_deep(elemlist);
pfree(rawstring);
}
endif
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
