小 縮小字型大小。 中 重設字型大小。 大 放大字型大小。

標籤: PostgreSQL

在 Laravel 中使用 PostgreSQL JSONB query

Posted on By 日落 在〈在 Laravel 中使用 PostgreSQL JSONB query〉中尚無留言

PostgreSQL 中，要查詢 item 是否在 JSON list (array) 中的語法是：

where user_list::jsonb ? 'user name'

如果使用 DB:raw() 代入 Laravel Eloquent 會出現錯誤訊息：

-- where(DB::raw('user_list::jsonb ? \'user name\''))->get()

SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "$1"

原因是 ? 會被視為 prepare/binding 的記號，上面語法沒有給定 bind 條件，所以發生錯誤。

若不使用 prepare/binding，則要 ? escape 為 ??：

-- where(DB::raw('user_list::jsonb ?? \'user name\''))->get()

  Illuminate\Database\Eloquent\Collection {#6157
    ....
  }
Tags:, ,

SCRAM authentication requires libpq version 10 or above on PostgreSQL

Posted on By 日落 在〈SCRAM authentication requires libpq version 10 or above on PostgreSQL〉中尚無留言

遠端連線到 PostgreSQL 時，遇到錯誤訊息：

SCRAM authentication requires libpq version 10 or above

這個問題主要是新舊版本儲存使用者密碼的方法不同所導致的，只要讓 PostgreSQL 支援 MD5 儲存密碼即可。

開啟 postgresql.conf 修改 password_encryption 參數：

password_encryption = md5

再來修改 pg_hba.conf，讓使用者驗證的時候使用 MD5 方式驗證：

# IPv4 local connections:
host    all      all          127.0.0.1/32       scram-sha-256
host    all      zeroplex     0.0.0.0/0          md5

設定檔修改後記得重新啟動 PostgreSQL。

設定檔改好、啟動也沒問題，就來更新使用者密碼，這樣一來，PostgreSQL 的密碼儲存方式就會改用 MD5：

 ALTER USER zeroplex WITH PASSWORD 'your-password'
Tags:,

在 PostgreSQL 建立 admin 帳號

Posted on By 日落 在〈在 PostgreSQL 建立 admin 帳號〉中有 1 則留言

PostgreSQL 的使用者權限管理和 MySQL 差很多，花了好一陣子才搞懂。

一開始操作都必須使用 postgres 的使用者身份來操作，所以都會需要 sudo：

# 建立預備要給予 admin 權限的帳號
# 如果不想要 sudo 則設定成 Linux 相同帳號名稱
sudo -u postgres createuser zeroplex

再來是要幫上面新增的使用者設定 role，這邊需要進入 PostgreSQL 的 console 中下指令：

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

-- 透過 \du 來列出現有的使用者與角色
postgres=# \du
                                    List of roles
 Role name |                         Attributes                         | Member of  
-----------+------------------------------------------------------------+------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 zeroplex  |                                                            | {}

從上表的內容，可以看出剛剛新增的帳號什麼權限都沒有，但是預設帳號 postgres 擁有 Superuser 的權限。

若要建立的帳號擁有 admin 權限 (在 PostgreSQL 中叫做 superuser)，則須透過 alter user 來授予權限：

postgres=# alter user zeroplex with superuser;
ALTER ROLE

-- 再次列出使用者與其角色 (role)
postgres=# \du
                                    List of roles
 Role name |                         Attributes                         | Member of  
-----------+------------------------------------------------------------+------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 zeroplex  | Superuser                                                  | {}

除了 superuser 以外，還有其他不同權限的角色可以指派，請參考 PostgreSQL alter user 的說明。

接著就可以不用 sudo 直接下 PostgreSQL 管理相關指令：

$ createdb test

注意：使用者如果擁有 superuser 權限以後，下管理相關指令是不需要 sudo 的，因此必須承擔意外造成的風險。個人建議還是切換成 sudo -u postgres 以後在進行操作比較保險。

Tags: