PostgreSQL 建立資料庫管理使用者 create database admin user

PostgreSQL建立管理資料庫管理權限的使用者的方式如下。

安裝好PostgreSQL資料庫時預設只有superuser使用者，但通常不直接使用superuser來操作資料庫，而是新建一個有管理權限的使用者來管理資料庫已減少superuser被盜用的安全性問題。

範例環境：

• macOS Big Sur
• PostgreSQL 14

事前要求

參考「Mac Homebrew安裝PostgreSQL」安裝PostgreSQL資料庫。

建立admin

下面建立一個新的資料庫管理員admin，密碼12345。

以superuser登入指定資料庫(e.g. postgres)的psql並執行以下script。

CREATE USER admin WITH PASSWORD '12345';

admin為要建立使用者名稱；
12345為使用者的密碼。

postgres=> CREATE USER admin WITH PASSWORD '12345';
CREATE ROLE

然後執行以下script給予admin對postgres資料庫及table的存取權限。

GRANT ALL PRIVILEGES ON DATABASE postgres TO admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO admin;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public to admin;

postgres=# GRANT ALL PRIVILEGES ON DATABASE postgres TO admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO admin;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public to admin;
GRANT
GRANT
GRANT
GRANT

在psql輸入\du檢視資料庫現有的角色（使用者）即可看到新建的admin。

postgres=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 admin     |                                                            | {}
 matt      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

登出psql並以新建的admin重新登入psql。輸入psql -U admin -d postgres，然後會跳提示輸入admin的密碼。-U相當於--username用來指定登入的使用者名稱。-d相當於--dbname用來指定登入的資料庫。

$ psql -U admin -d postgres
Password for user admin:12345
psql (14.1)
Type "help" for help.

postgres=>

輸入\conninfo可以看到目前連線的使用者為admin。

postgres=> \conninfo
You are connected to database "postgres" as user "admin" on host "localhost" (address "::1") at port "5432".

• Creating user, database and adding access on PostgreSQL
• CREATE USER
• GRANT