標籤:
部署完的新機器報錯
App 12595 stderr: PG::ConnectionBad (FATAL: Peer authentication failed for user "dbuser"App 12595 stderr: ):
上網查了一下,很多文章上都說要修改/etc/postgresql/9.4/main/pg_hba.conf 這個檔案配置,然而並沒有什麼用
回憶一下整個資料庫的搭建過程
(1)建立資料庫使用者dbuser,並指定為超級使用者
sudo -u postgres createuser --superuser dbuser
(2)建立資料庫uppers_staging,並指定其所有者為dbuser
sudo -u postgres createdb -O dbuser uppers_staging
(3)給dbuser設定登入密碼,之前沒設定的時候報這個錯psql: fe_sendauth: no password supplied,設定之後就不報錯了
# sudo -u postgres psqlpsql (9.4.4)Type "help" for help.postgres=# \password dbuserEnter new password: Enter it again: postgres=# \q
(4)登入查看下資料庫
# 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)
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 | {}
接下來要將資料庫uppers_staging 的所有許可權都賦給dbuser,否則dbuser只有登入控制台的許可權,沒有操作資料庫的許可權
GRANT ALL PRIVILEGES ON DATABASE uppers_staging to dbuser;
這時候再看一下資料庫的許可權就變了
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)
其實報錯的原因就是沒有授權
常見的操作命令如下
\h:查看SQL命令的解釋,比如\h select。\?:查看psql命令列表。\l:列出所有資料庫。\c [database_name]:串連其他資料庫。\d:列出當前資料庫的所有表格。\d [table_name]:列出某一張表格的結構。\du:列出所有使用者。\e:開啟文字編輯器。\conninfo:列出當前資料庫和串連的資訊。
著作權聲明:本文為博主原創文章,未經博主允許不得轉載。
PostgreSQL新手上路PG::ConnectionBad (FATAL: Peer authentication failed