postgresql資料庫設定遠程登陸賬戶和密碼

標籤：postgresql資料庫設定遠程登陸賬戶和密碼

1.本地登陸postgresql,建庫授權，設定密碼

伺服器本地登陸postgresql資料庫（預設是不要求輸入密碼的）

[email protected] ~]$ psql

psql.bin (9.5.9)

Type "help" for help.

建立角色，並且給角色設定密碼:

postgres=# create user testwjw with password ‘[email protected]&^%996‘;

CREATE ROLE

修改資料庫使用者和密碼：

postgres=# alter user testwjw with password ‘558996‘;

ALTER ROLE

指定字元集建立資料庫testdb1，並且授權給testwjw

postgres=# create database testdb1 with encoding=‘utf8‘ owner=testwjw;

CREATE DATABASE

授權：

postgres=# grant all privileges on database testdb1 to testwjw; 

GRANT

2.修改postgresql.conf檔案中的連接埠和監聽主機:

postsql預設安裝後是監聽本機127.0.0.1 預設連接埠是5432,是不能夠遠程登陸的，所以要修改監聽主機地址，同時修改預設的連接埠為:36985

postgresql資料庫的設定檔是:postgresql.conf,所在位置是：postgresql初始化時所指定的data資料目錄下：

[[email protected] ~]$ ll /data/postgresql/data/postgresql.conf

-rw------- 1 postgres postgres 21305 Oct  3 11:18 /data/postgresql/data/postgresql.conf

[[email protected] ~]$ egrep "listen_addresses|5432"/data/postgresql/data/postgresql.conf 

listen_addresses = ‘localhost‘# what IP address(es) to listen on;

port = 5432# (change requires restart)

修改監聽主機為*，連接埠為:36985

[[email protected] ~]$ egrep "listen_addresses|36985" /data/postgresql/data/postgresql.conf 

listen_addresses = ‘*‘# what IP address(es) to listen on;

port = 36985# (change requires restart)

修改設定檔pg_hba.conf ，允許遠程ip訪問本機資料庫，以及設定伺服器本地登陸postgresql資料庫要求輸入密碼才可以登陸

[[email protected] ~]$ egrep "60.223.153.25|127.0.0.1" /data/postgresql/data/pg_hba.conf 

host    all             all             60.223.153.25/32            trust

host    all             all             127.0.0.1/32            password

#host    replication     postgres        127.0.0.1/32            trust

3.重啟postgresql服務生效：

[[email protected] ~]$ pg_ctl -D /data/postgresql/data -l /data/postgresql/log/postgres.log restart

waiting for server to shut down....LOG:  received fast shutdown request

LOG:  aborting any active transactions

LOG:  autovacuum launcher shutting down

LOG:  shutting down

LOG:  database system is shut down

 done

server stopped

server starting

[[email protected] ~]$ netstat -lntup|grep postgres

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp        0      0 0.0.0.0:36985               0.0.0.0:*                   LISTEN      6472/postgres  

4.登陸資料庫:

[[email protected] ~]$ psql

psql.bin: could not connect to server: No such file or directory

Is the server running locally and accepting

connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

[[email protected] ~]$ psql -Utestwjw -dpostgres -p36985 -h 127.0.0.1

Password for user testwjw: 

psql.bin: fe_sendauth: no password supplied

[[email protected] ~]$ psql -Utestwjw -dpostgres -p36985 -h 127.0.0.1

Password for user testwjw: 

psql.bin (9.5.9)

Type "help" for help.

postgres=> \q

[[email protected] ~]$ psql -Utestwjw -dtestdb1 -p36985 -h 127.0.0.1

Password for user testwjw: 

psql.bin (9.5.9)

Type "help" for help.

testdb1=> select * from t;

 id | nan | note  

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

  1 | t   | TRUE

  2 | f   | FALSE

  3 | t   | tRue

  4 | f   | fAlse

 11 |     | null

 11 |     | NULL

  7 | t   | ‘t‘

  8 | f   | ‘f‘

  9 | t   | ‘yes‘

 10 | f   | ‘0‘

(10 rows)

testdb1=> 

[[email protected] ~]$ psql -Utestwjw -dtestdb1 -p36985 

psql.bin (9.5.9)

Type "help" for help.

testdb1=> \q

[[email protected] ~]$ psql -Utestwjw -dtestdb2 -p36985 

psql.bin (9.5.9)

Type "help" for help.

testdb2=> \q

本文出自 “10931853” 部落格，請務必保留此出處http://wujianwei.blog.51cto.com/10931853/1970390

postgresql資料庫設定遠程登陸賬戶和密碼