postgresql数据库设置远程登陆账户和密码

前端之家收集整理的这篇文章主要介绍了postgresql数据库设置远程登陆账户和密码前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。


@H_404_2@

1.本地登陆postgresql,建库授权,设置密码@H_404_2@

服务器本地登陆postgresql数据库(默认是不需要密码的)@H_404_2@

postgres@localhost ~]$ psql@H_404_2@

psql.bin (9.5.9)@H_404_2@

Type "help" for help.@H_404_2@

创建角色,并且给角色设置密码:@H_404_2@

postgres=# create user testwjw with password 'Zykj@5&^%996';@H_404_2@

CREATE ROLE@H_404_2@

修改数据库用户和密码:@H_404_2@

postgres=# alter user testwjw with password '558996';@H_404_2@

ALTER ROLE@H_404_2@


@H_404_2@

指定字符集创建数据库testdb1,并且授权给testwjw@H_404_2@

postgres=# create database testdb1 with encoding='utf8' owner=testwjw;@H_404_2@

CREATE DATABASE@H_404_2@

授权:@H_404_2@

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

GRANT@H_404_2@

2.修改postgresql.conf文件中的端口和监听主机:@H_404_2@

postsql默认安装后是监听本机127.0.0.1 默认端口是5432,是不能够远程登陆的,所以要修改监听主机地址,同时修改默认的端口为:36985@H_404_2@

postgresql数据库配置文件是:postgresql.conf,所在位置是:postgresql初始化时所指定的data数据目录下:@H_404_2@

[postgres@localhost ~]$ ll /data/postgresql/data/postgresql.conf@H_404_2@

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


@H_404_2@

[postgres@localhost ~]$ egrep "listen_addresses|5432"/data/postgresql/data/postgresql.conf@H_404_2@

listen_addresses = 'localhost'# what IP address(es) to listen on;@H_404_2@

port = 5432# (change requires restart)@H_404_2@

修改监听主机为*,端口为:36985@H_404_2@

[postgres@localhost ~]$ egrep "listen_addresses|36985" /data/postgresql/data/postgresql.conf@H_404_2@

listen_addresses = '*'# what IP address(es) to listen on;@H_404_2@

port = 36985# (change requires restart)@H_404_2@

修改配置文件pg_hba.conf ,允许远程ip访问本地数据库,以及设置服务器本地登陆postgresql数据库要求输入密码才可以登陆@H_404_2@

[postgres@localhost ~]$ egrep "60.223.153.25|127.0.0.1" /data/postgresql/data/pg_hba.conf@H_404_2@

host all all 60.223.153.25/32 trust@H_404_2@

host all all 127.0.0.1/32 password@H_404_2@

#host replication postgres 127.0.0.1/32 trust@H_404_2@

允许60.223.153.25ip访问服务器postgresql数据库@H_404_2@

psql -Utestwjw -dpostgres -p36985 -h 127.0.0.1 这样访问数据库127.0.0.1数据库必须输入密码才可以@H_404_2@

3.重启postgresql服务生效:@H_404_2@

[postgres@localhost ~]$ pg_ctl -D /data/postgresql/data -l /data/postgresql/log/postgres.log restart@H_404_2@

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

LOG: aborting any active transactions@H_404_2@

LOG: autovacuum launcher shutting down@H_404_2@

LOG: shutting down@H_404_2@

LOG: database system is shut down@H_404_2@

done@H_404_2@

server stopped@H_404_2@

server starting@H_404_2@

[postgres@localhost ~]$ netstat -lntup|grep postgres@H_404_2@

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

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

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

4.登陆数据库:@H_404_2@

[postgres@localhost ~]$ psql@H_404_2@

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

Is the server running locally and accepting@H_404_2@

connections on Unix domain socket "/tmp/.s.PGsql.5432"?@H_404_2@

[postgres@localhost ~]$ psql -Utestwjw -dpostgres -p36985 -h 127.0.0.1@H_404_2@

Password for user testwjw:@H_404_2@

psql.bin: fe_sendauth: no password supplied@H_404_2@

[postgres@localhost ~]$ psql -Utestwjw -dpostgres -p36985 -h 127.0.0.1@H_404_2@

Password for user testwjw:@H_404_2@

psql.bin (9.5.9)@H_404_2@

Type "help" for help.@H_404_2@


@H_404_2@

postgres=> \q@H_404_2@

[postgres@localhost ~]$ psql -Utestwjw -dtestdb1 -p36985 -h 127.0.0.1@H_404_2@

Password for user testwjw:@H_404_2@

psql.bin (9.5.9)@H_404_2@

Type "help" for help.@H_404_2@


@H_404_2@

testdb1=> select * from t;@H_404_2@

id | nan | note@H_404_2@

----+-----+-------@H_404_2@

1 | t | TRUE@H_404_2@

2 | f | FALSE@H_404_2@

3 | t | tRue@H_404_2@

4 | f | fAlse@H_404_2@

11 | | null@H_404_2@

11 | | NULL@H_404_2@

7 | t | 't'@H_404_2@

8 | f | 'f'@H_404_2@

9 | t | 'yes'@H_404_2@

10 | f | '0'@H_404_2@

(10 rows)@H_404_2@

testdb1=>@H_404_2@

[postgres@localhost ~]$ psql -Utestwjw -dtestdb1 -p36985@H_404_2@

psql.bin (9.5.9)@H_404_2@

Type "help" for help.@H_404_2@


@H_404_2@

testdb1=> \q@H_404_2@

[postgres@localhost ~]$ psql -Utestwjw -dtestdb2 -p36985@H_404_2@

psql.bin (9.5.9)@H_404_2@

Type "help" for help.@H_404_2@


@H_404_2@

testdb2=> \q@H_404_2@

猜你在找的Postgre SQL相关文章