Home | 簡體中文 | 繁體中文 | 雜文 | Search | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 作品與服務 | Email

Ubuntu 12.04.1 LTS + PostgreSQL 9.1

Mr. Neo Chen (陳景峯), netkiller, BG7NYT


中國廣東省深圳市龍華新區民治街道溪山美地
518131
+86 13113668890


版權聲明

轉載請與作者聯繫,轉載時請務必標明文章原始出處和作者信息及本聲明。

文檔出處:
http://netkiller.github.io
http://netkiller.sourceforge.net

微信掃瞄二維碼進入 Netkiller 微信訂閲號

QQ群:128659835 請註明“讀者”

2016-03-29: 2013-07-24 18:04:58 +0800 (Wed, 24 Jul 2013)


目錄

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=#
		
comments powered by Disqus