postgresql数据库设置远程登陆账户和密码
|
1.本地登陆postgresql,建库授权，设置密码 服务器本地登陆postgresql数据库（默认是不需要密码的） postgres@localhost ~]$ psql psql.bin (9.5.9) Type "help" for help. 创建角色，并且给角色设置密码: postgres=# create user testwjw with password 'Zykj@5&^%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数据目录下： [postgres@localhost ~]$ ll /data/postgresql/data/postgresql.conf -rw------- 1 postgres postgres 21305 Oct 3 11:18 /data/postgresql/data/postgresql.conf [postgres@localhost ~]$ 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 [postgres@localhost ~]$ 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数据库要求输入密码才可以登陆 [postgres@localhost ~]$ 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 允许60.223.153.25ip访问服务器postgresql数据库 psql -Utestwjw -dpostgres -p36985 -h 127.0.0.1 这样访问数据库127.0.0.1数据库必须输入密码才可以 3.重启postgresql服务生效： [postgres@localhost ~]$ 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 [postgres@localhost ~]$ 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.登陆数据库: [postgres@localhost ~]$ 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"? [postgres@localhost ~]$ psql -Utestwjw -dpostgres -p36985 -h 127.0.0.1 Password for user testwjw: psql.bin: fe_sendauth: no password supplied [postgres@localhost ~]$ psql -Utestwjw -dpostgres -p36985 -h 127.0.0.1 Password for user testwjw: psql.bin (9.5.9) Type "help" for help. postgres=> q [postgres@localhost ~]$ 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=> [postgres@localhost ~]$ psql -Utestwjw -dtestdb1 -p36985 psql.bin (9.5.9) Type "help" for help. testdb1=> q [postgres@localhost ~]$ psql -Utestwjw -dtestdb2 -p36985 psql.bin (9.5.9) Type "help" for help. testdb2=> q （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |