netkiller.github.com/postgresql/pgsql.conf.html at master · hpmdoc/netkiller.github.com

History

156 lines (139 loc) · 10.8 KB

Raw

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>1.4. PostgreSQL 配置</title><link rel="stylesheet" type="text/css" href="/docbook.css" /><meta name="generator" content="DocBook XSL Stylesheets V1.78.1" /><link rel="home" href="index.html" title="Netkiller PostgreSQL 手札" /><link rel="up" href="install.html" title="第 1 章 PostgreSQL 安装" /><link rel="prev" href="pgsql.yum.new.html" title="1.3. PostgreSQL YUM 源安装" /><link rel="next" href="dbauser.html" title="1.5. 创建dba用户" /></head><body><a xmlns="" href="http://netkiller.github.io/">Home</a> |

<a xmlns="" href="http://netkiller.github.io/">简体中文</a> |

<a xmlns="" href="http://netkiller.sourceforge.net/">繁体中文</a> |

<a xmlns="" href="/journal/index.html">杂文</a> |

<a xmlns="" href="/search.html">Search</a> |

<a xmlns="" href="http://netkiller-github-com.iteye.com/">ITEYE 博客</a> |

<a xmlns="" href="http://my.oschina.net/neochen/">OSChina 博客</a> |

<a xmlns="" href="https://www.facebook.com/bg7nyt">Facebook</a> |

<a xmlns="" href="http://cn.linkedin.com/in/netkiller/">Linkedin</a> |

<a xmlns="" href="mailto:[email protected]">Email</a><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">1.4. PostgreSQL 配置</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="pgsql.yum.new.html">上一页</a> </td><th width="60%" align="center">第 1 章 PostgreSQL 安装</th><td width="20%" align="right"> <a accesskey="n" href="dbauser.html">下一页</a></td></tr></table><hr /><table width="100%" border="0"><tr><td align="left"><a href="http://netkiller.sourceforge.net/">Home</a> |

<a href="http://netkiller.github.com/">Mirror</a> |

<a href="/search.html">Search</a></td><td align="right"></td></tr></table></div><table xmlns=""><tr><td><iframe src="http://ghbtns.com/github-btn.html?user=netkiller&repo=netkiller.github.com&type=watch&count=true&size=large" height="30" width="170" frameborder="0" scrolling="0" style="width:170px; height: 30px;" allowTransparency="true"></iframe></td><td><iframe src="http://ghbtns.com/github-btn.html?user=netkiller&repo=netkiller.github.com&type=fork&count=true&size=large" height="30" width="170" frameborder="0" scrolling="0" style="width:170px; height: 30px;" allowTransparency="true"></iframe></td><td><iframe src="http://ghbtns.com/github-btn.html?user=netkiller&type=follow&count=true&size=large" height="30" width="240" frameborder="0" scrolling="0" style="width:240px; height: 30px;" allowTransparency="true"></iframe></td></tr></table><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="pgsql.conf"></a>1.4. PostgreSQL 配置</h2></div></div></div>

<p>su 到 postgres 用户</p>

$ su - postgres

Password:

$ pwd

/var/lib/postgresql

</pre>

<p>备份配置文件，防止修改过程中损毁</p>

cp /etc/postgresql/9.1/main/postgresql.conf /etc/postgresql/9.1/main/postgresql.conf.original

cp /etc/postgresql/9.1/main/pg_hba.conf /etc/postgresql/9.1/main/pg_hba.conf.original

</pre>

<div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="idp55971936"></a>1.4.1. postgresql.conf</h3></div></div></div>

<p>启用tcp/ip连接，去掉下面注释,修改为你需要的IP地址，默认为localhost</p>

listen_addresses = 'localhost'

</pre>

<p>如果有多个网络适配器可以指定 'ip' 或 '*' 任何接口上的IP地址都可能listen.</p>

$ sudo vim /etc/postgresql/9.1/main/postgresql.conf

listen_addresses = '*'

</pre>

</div>

<p>pg_hba.conf配置文件的权限需要注意以下，-rw-r----- 1 postgres postgres 4649 Dec 5 18:00 pg_hba.conf</p>

$ ll /etc/postgresql/9.1/main/

total 52

drwxr-xr-x 2 postgres postgres 4096 Dec 6 09:40 ./

drwxr-xr-x 3 postgres postgres 4096 Dec 5 18:00 ../

-rw-r--r-- 1 postgres postgres 316 Dec 5 18:00 environment

-rw-r--r-- 1 postgres postgres 143 Dec 5 18:00 pg_ctl.conf

-rw-r----- 1 postgres postgres 4649 Dec 5 18:00 pg_hba.conf

-rw-r----- 1 postgres postgres 1636 Dec 5 18:00 pg_ident.conf

-rw-r--r-- 1 postgres postgres 19259 Dec 5 18:00 postgresql.conf

-rw-r--r-- 1 postgres postgres 378 Dec 5 18:00 start.conf

</pre>

<p>pg_hba.conf配置文件负责访问权限控制</p>

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only

local all all peer

# IPv4 local connections:

host all all 127.0.0.1/32 md5

# IPv6 local connections:

host all all ::1/128 md5

</pre>

local 本地使用unix/socket 方式连接, host 使用tcp/ip socket 方式连接

</p></dd><dt><span class="glossterm">DATABASE</span></dt><dd class="glossdef"><p>

数据库名.

</p></dd><dt><span class="glossterm">USER</span></dt><dd class="glossdef"><p>

用户名.

</p></dd><dt><span class="glossterm">ADDRESS</span></dt><dd class="glossdef"><p>

允许连接的IP地址，可以使用子网掩码.

</p></dd><dt><span class="glossterm">METHOD</span></dt><dd class="glossdef"><p>

认真加密方式.

</p></dd></dl></div>

<p>下面我们做一个简单测试，首先配置pg_hba。conf文件</p>

$ sudo vi /etc/postgresql/9.1/main/pg_hba.conf

host * dba 0.0.0.0/0 md5

host test test 0.0.0.0/0 md5

</pre>

<p>运行创建数据,用户的SQL语句</p>

CREATE ROLE test LOGIN PASSWORD 'test' NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;

CREATE DATABASE test WITH OWNER = test ENCODING = 'UTF8' TABLESPACE = pg_default;

</pre>

<p>进入psql</p>

$ psql

psql (9.1.6)

Type "help" for help.

postgres=# CREATE ROLE test LOGIN PASSWORD 'test' NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;

CREATE ROLE

postgres=# CREATE DATABASE test WITH OWNER = test ENCODING = 'UTF8' TABLESPACE = pg_default;

CREATE DATABASE

postgres=# \q

</pre>

<p>使用psql登录</p>

$ psql -hlocalhost -Utest test

Password for user test:

psql (9.1.6)

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

Type "help" for help.

test=> \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(4 rows)

test=>

</pre>

</div>

</div><div xmlns="" id="disqus_thread"></div><script xmlns="" type="text/javascript">

/* * * CONFIGURATION VARIABLES: EDIT BEFORE PASTING INTO YOUR WEBPAGE * * */

//if(document.domain == 'netkiller.github.com'){

var disqus_shortname = 'netkiller'; // required: replace example with your forum shortname

//}else{

//var disqus_shortname = 'neochan';

//}

/* * * DON'T EDIT BELOW THIS LINE * * */

(function() {

var dsq = document.createElement('script'); dsq.type = 'text/javascript'; dsq.async = true;

dsq.src = 'http://' + disqus_shortname + '.disqus.com/embed.js';

(document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);

})();

</script><noscript xmlns="">Please enable JavaScript to view the <a href="http://disqus.com/?ref_noscript">comments powered by Disqus.</a></noscript><a xmlns="" href="http://disqus.com" class="dsq-brlink">comments powered by <span class="logo-disqus">Disqus</span></a><br xmlns="" /><div xmlns="" id="clustrmaps-widget"></div><script xmlns="" type="text/javascript">var _clustrmaps = {'url' : 'http://netkiller.github.io', 'user' : 1107643, 'server' : '2', 'id' : 'clustrmaps-widget', 'version' : 1, 'date' : '2013-08-14', 'lang' : 'en', 'corners' : 'square' };(function (){ var s = document.createElement('script'); s.type = 'text/javascript'; s.async = true; s.src = 'http://www2.clustrmaps.com/counter/map.js'; var x = document.getElementsByTagName('script')[0]; x.parentNode.insertBefore(s, x);})();</script><noscript xmlns=""><a href="http://www2.clustrmaps.com/user/87410e6bb"><img src="http://www2.clustrmaps.com/stats/maps-no_clusters/netkiller.github.io-thumb.jpg" alt="Locations of visitors to this page" /></a></noscript><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="pgsql.yum.new.html">上一页</a> </td><td width="20%" align="center"><a accesskey="u" href="install.html">上一级</a></td><td width="40%" align="right"> <a accesskey="n" href="dbauser.html">下一页</a></td></tr><tr><td width="40%" align="left" valign="top">1.3. PostgreSQL YUM 源安装 </td><td width="20%" align="center"><a accesskey="h" href="index.html">起始页</a></td><td width="40%" align="right" valign="top"> 1.5. 创建dba用户</td></tr></table></div><script xmlns="">

(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){

(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),

m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)

})(window,document,'script','//www.google-analytics.com/analytics.js','ga');

ga('create', 'UA-11694057-1', 'auto');

ga('send', 'pageview');

</script><script xmlns="" type="text/javascript">

var _bdhmProtocol = (("https:" == document.location.protocol) ? " https://" : " http://");

document.write(unescape("%3Cscript src='" + _bdhmProtocol + "hm.baidu.com/h.js%3F997cd4a7320a82d72cb74d179118f697' type='text/javascript'%3E%3C/script%3E"));

</script><script xmlns="" type="text/javascript" src="/js/q.js"></script></body></html>

Navigation Menu

Uh oh!

Uh oh!

FilesExpand file tree

pgsql.conf.html

Latest commit

History

pgsql.conf.html

File metadata and controls