Home | Mirror | Search |
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
啟用tcp/ip連接,去掉下面註釋,修改為你需要的IP地址,預設為localhost
listen_addresses = 'localhost'
如果有多個網絡適配器可以指定 'ip' 或 '*' 任何介面上的IP地址都可能listen.
$ sudo vim /etc/postgresql/9.1/main/postgresql.conf listen_addresses = '*'
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
local 本地使用unix/socket 方式連接, host 使用tcp/ip socket 方式連接
資料庫名.
用戶名.
允許連接的IP地址,可以使用子網掩碼.
認真加密方式.
下面我們做一個簡單測試,首先配置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=>