Ubuntu 12.04.1 LTS + PostgreSQL 9.1
Mr. Neo Chen (netkiller), 陈景峰(BG7NYT)
中国广东省深圳市龙华新区民之街道溪山美地
518109
+86 13113668890
+86 755 29812080<netkiller@msn.com>
版权 2011, 2012 http://netkiller.github.com
$Date: 2012-12-06 11:53:11 +0800 (Thu, 06 Dec 2012) $
目录
1. Ubuntu 12.04.1 LTS2. PostgreSQL 配置2.1. postgresql.conf2.2. pg_hba.conf3. 创建dba用户
1. Ubuntu 12.04.1 LTS
安装环境
PostgreSQL 9.1
$ sudo apt-get install postgresql
$ sudo apt-get install postgresql
更改postgres管理员用户密码
$ sudo passwd postgres Enter new UNIX password: Retype new UNIX password: passwd: password updated successfully
然后切换到postgres用户环境
$ su - postgres Password: Added user postgres.
进入psql客户款, PostgreSQL的psql命令相当于sqlplus,MySQL命令
$ psql psql (9.1.6) Type "help" for help. postgres=#
退出\q
postgres=# \q
2. PostgreSQL 配置
su 到 postgres 用户
$ su - postgres Password: $ pwd /var/lib/postgresql $
备份配置文件，防止修改过程中损毁
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
2.1. postgresql.conf
启用tcp/ip连接，去掉下面注释,修改为你需要的IP地址，默认为localhost
listen_addresses = 'localhost'
如果有多个网络适配器可以指定 'ip' 或 '*' 任何接口上的IP地址都可能listen.
$ sudo vim /etc/postgresql/9.1/main/postgresql.conf listen_addresses = '*'
2.2. pg_hba.conf
pg_hba.conf配置文件的权限需要注意以下，-rw-r----- 1 postgres postgres 4649 Dec 5 18:00 pg_hba.conf
$ 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
pg_hba.conf配置文件负责访问权限控制
# 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
TYPE
local 本地使用unix/socket 方式连接, host 使用tcp/ip socket 方式连接
DATABASE
数据库名.
USER
用户名.
ADDRESS
允许连接的IP地址，可以使用子网掩码.
METHOD
认真加密方式.
下面我们做一个简单测试，首先配置pg_hba。conf文件
$ 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
运行创建数据,用户 的SQL语句
CREATE ROLE test LOGIN PASSWORD 'test' NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; CREATE DATABASE test WITH OWNER = test ENCODING = 'UTF8' TABLESPACE = pg_default;
进入psql
$ 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
使用psql登录
$ 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
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | test | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
test=>
3. 创建dba用户
创建一个远程维护数据库dba用户，具有创建数据库与创建用户的权限
CREATE USER dba PASSWORD 'dba' CREATEDB CREATEUSER; CREATE ROLE
进入psql
$ psql psql (9.1.6) Type "help" for help. postgres=# CREATE USER dba PASSWORD 'dba' CREATEDB CREATEUSER; CREATE ROLE postgres=# \q
使用psql登录
$ psql -hlocalhost -Udba postgres Password for user dba: psql (9.1.6) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. postgres=#