PostgreSQL Novice Road Pg::connectionbad (fatal:peer authentication failed

Source: Internet
Author: User
Tags postgresql psql postgres createdb ssl connection






New machine error after deployment





App 12595 Stderr:pg::connectionbad (FATAL:  Peer authentication failed for user "Dbuser" app 12595 stderr:):

Surf the internet for a bit, many of the posts are said to modify/etc/postgresql/9.4/main/pg_hba.conf this file configuration, but it is not a good use





Recall the entire database building process



(1) Create database user Dbuser, and specify as Superuser





Sudo-u postgres CreateUser--superuser dbuser

(2) Create a database uppers_staging and specify that its owner is Dbuser







Sudo-u postgres createdb-o dbuser uppers_staging

(3) to Dbuser set the login password, not set before the time reported this error psql:fe_sendauth:no password supplied, set after the error







# sudo -u postgres psql
psql (9.4.4)
Type "help" for help.

postgres=# \password dbuser
Enter new password: 
Enter it again: 
postgres=# \q





(4) Log in to view the database





# psql -U dbuser -d uppers_staging -h 127.0.0.1 
Password for user dbuser: 
psql (9.4.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

uppers_staging=> \l
                                      List of databases
        Name        |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
--------------------+----------+----------+-------------+-------------+-----------------------
 postgres           | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0          | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                    |          |          |             |             | postgres=CTc/postgres
 template1          | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                    |          |          |             |             | postgres=CTc/postgres
 uppers_development | root     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 uppers_staging     | dbuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)

Permissions for Dbuser







uppers_staging=> \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 dbuser    |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 root      | Superuser, Create role, Create DB              | {}

The next step is to assign all permissions for the database uppers_staging to Dbuser, otherwise dbuser only permissions to log on to the console, without permission to manipulate the database







GRANT all privileges on the DATABASE uppers_staging to Dbuser;

Now look at the database and the permissions change.







uppers_staging=> \l
                                      List of databases
        Name        |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
--------------------+----------+----------+-------------+-------------+-----------------------
 postgres           | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0          | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                    |          |          |             |             | postgres=CTc/postgres
 template1          | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                    |          |          |             |             | postgres=CTc/postgres
 uppers_development | root     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 uppers_staging     | dbuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/dbuser           +
                    |          |          |             |             | dbuser=CTc/dbuser
(5 rows)

In fact, the reason for the error is no authorization








The common operations commands are as follows





\ h: View the explanation of SQL commands, such as \ h select.
\ ?: View the list of psql commands.
\ l: List all databases.
\ c [database_name]: Connect to another database.
\ d: List all tables in the current database.
\ d [table_name]: List the structure of a table.
\ du: List all users.
\ e: Open a text editor.
\ conninfo: List information about the current database and connection. 


Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.



PostgreSQL Novice Road Pg::connectionbad (fatal:peer authentication failed


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.