Set remote login account and password in postgresql database
1. Log in locally to postgresql, create database authorization, set password
Server Local login postgresql database (default is no password required)
postgres@localhost ~]$ psql
psql.bin (9.5.9)
Type "help" for help.
Create a character and set a password for the character:
postgres=# create user testwjw with password 'Zykj@5&^%996';
CREATE ROLE
Modify database user and password:
postgres=# alter user testwjw with password '558996';
ALTER ROLE
Specify character set to create database testdb1 and license to testwjw
postgres=# create database testdb1 with encoding='utf8' owner=testwjw;
CREATE DATABASE
Authorized:
postgres=# grant all privileges on database testdb1 to testwjw;
GRANT
2. Modify the ports and listening hosts in the postgresql.conf file:
postsql default after installation is to listen to the local machine 127.0.0.1 default port is 5432, is not able to log in remotely, so to modify the listening host address, and modify the default port is:36985
The postgresql database configuration file is:postgresql.conf, located in: data directory specified when postgresql is initialized:
[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)
Change the listening host to * and port to: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)
Modify the configuration file pg_hba.conf to allow remote ip access to the local database, and set the server to log in locally. The postgresql database requires a password to log in.
[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
Allow 60.223.153.25ip to access server postgresql database
psql -Utestwjw -dpostgres -p36985 -h 127.0.0.1 This way to access database 127.0.0.1 database you must enter a password to be able to
3. Restart postgresql service takes effect:
[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. Login database:
[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
