PostgreSQL Database Settings remote login account and password

1. Local login PostgreSQL, build library authorization, set password

Server Local login PostgreSQL database (default is no password required)

[Email protected] ~]$ Psql

Psql.bin (9.5.9)

Type ' help ' for help.

Create a role and set a password for the role:

postgres=# Create user TESTWJW with password ' [email protected]&^%996 ';

CREATE ROLE

To modify the database user and password:

postgres=# alter user TESTWJW with password ' 558996 ';

ALTER ROLE

Specifies that the character set creates the database testdb1 and authorizes the TESTWJW

postgres=# CREATE database testdb1 with encoding= ' UTF8 ' owner=testwjw;

CREATE DATABASE

Authorized:

postgres=# Grant all privileges on the database testdb1 to TESTWJW;

GRANT

2. Modify the port and the listening host in the postgresql.conf file:

Postsql default installation is to listen to the native 127.0.0.1 default port is 5432, is not able to remotely log on, so to modify the listening host address, while modifying the default port is: 36985

The configuration file for the PostgreSQL database is: postgresql.conf, which is located in the data directory specified by PostgreSQL initialization:

[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)

Modify the Listening host to *, Port: 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)

Modify configuration file pg_hba.conf, allow remote IP access to local database, and set server local login PostgreSQL database require password to login

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

Host All 60.223.153.25/32 Trust

Host all 127.0.0.1/32 password

#host Replication Postgres 127.0.0.1/32 Trust

3. Restart the PostgreSQL service in effect:

[Email protected] ~]$ pg_ctl-d/data/postgresql/data-l/data/postgresql/log/postgres.log Restart

Waiting for server-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 is identified, non-owned process info

Would is not being shown, you would has to be the root to see it all.)

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

4. Login database:

[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 '

(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

This article is from the "10931853" blog, please be sure to keep this source http://wujianwei.blog.51cto.com/10931853/1970390

PostgreSQL Database Settings remote login account and password