我需要幫助在 PostgreSQL 中設置角色和密碼

我正在使用 El Capitan 執行 9.4.0 版的 Mac Mini Server 上使用 postgres.app。我沒有在 pg-hba.conf 中進行任何自定義。我沒有 .pgpass 文件。我相信 .pgpass 用於儲存密碼，您在訪問數據庫實例時不必輸入密碼。我真的需要更詳細地了解 pg-hba.conf 中使用的 Unix 術語。

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

對於我的 Ruby on Rails 項目，角色具有不同類型訪問權限的密碼就足夠了。據我所知，我想要的一切都會被認為是本地的。

我去年在 Stack Overflow 中送出了以下兩個問題，並嘗試實現它們但無濟於事。我最終要麼將自己鎖定在 PostgesSQL 之外，要麼沒有密碼工作。當時我在我的 Mac Mini 伺服器上使用內置的 PostgreSQL 實例。

https://stackoverflow.com/questions/22882280/postgresql-9-2-4-desire-to-change-the-localhost-server-password-in-mavericks

https://stackoverflow.com/questions/25167284/rails-4-desire-to-set-postgresql-password-access-database-in-rails

我想為我目前的角色添加一個加密密碼。我還想添加兩個具有加密密碼的角色，以便在我的 Mac Mini 伺服器上託管的 Ruby on Rails 應用程序中使用，一個具有隻讀訪問權限，另一個具有讀/寫訪問權限。

我假設我會在 PostgreSQL 終端或 pgAdmin 中執行以下命令。

CREATE USER readonly WITH ENCRYPTED PASSWORD 'readonlypasswordencryptedtext';
CREATE USER readwrite WITH ENCRYPTED PASSWORD 'readwritepasswordencryptedtext';
GRANT SELECT PRIVILEGES ON ALL TABLES IN public TO readonly, readwrite;
GRANT INSERT, UPDATE, DELETE PRIVILEGES ON TABLE table1, table2, table3, etc. TO readwrite;
ALTER ROLE myuserid WITH ENCRYPTED PASSWORD 'myuseridpasswordencryptedtext';

我需要有關如何更改 pg-hba.conf 以及更改順序的幫助，這樣我就不會鎖定 myuserid 以及是否需要考慮其他事項。

我有多個帶有表的數據庫。我認為我的 GRANT 聲明沒有考慮到這一點。我是否需要創建一個架構來包含我的所有數據庫以供只讀使用者和要為讀寫使用者更新的數據庫？

基本角色和使用者管理

預設情況下，PostgreSQL 儲存加密密碼：

create user u_one with password 'password';

select * 
from pg_authid
where rolname = 'u_one';

-[ RECORD 1 ]--+------------------------------------
rolname        | u_one
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcatupdate   | f
rolcanlogin    | t
rolreplication | f
rolconnlimit   | -1
rolpassword    | md54e83b97ec0bf59917d9d2689a961e390
rolvaliduntil  | 

進行一些測試：

create table tt (
   a serial primary key,
   v varchar
);

insert into tt (v)
values ('one'), ('two');

目標是創建一個具有隻讀訪問權限的使用者和一個具有所有權限的使用者。最好創建定義了權限的角色，然後將使用者附加到每個角色：

create role readonly;
create role readwrite;

grant select on all tables in schema public to readonly;
grant insert, update, delete on table tt to readwrite;

在我的範例中，我希望 u_one 具有隻讀訪問權限。該使用者還沒有：

select * 
from tt;
ERROR:  permission denied for relation tt

我要將 u_one 附加到只讀：

grant readonly to u_one;

現在 u_one 可以從表 tt 中選擇。

現在我將創建一個可讀寫的 u_two：

create user u_two with password 'password';
grant readwrite to u_two;

您可能還打算允許 u_two 選擇權限：

grant readonly to u_two;

當您嘗試使用 u_two 插入 tt 時：

insert into tt (v) values ('three');
ERROR:  permission denied for sequence tt_a_seq

您需要為要修改此序列的使用者添加插入、更新和/或刪除權限：

grant usage on tt_a_seq to readwrite;

請注意我是如何僅將其添加到角色而不是使用者的：u_two 現在能夠插入新記錄。

使用此角色/使用者策略將通過減少重複權限並允許您快速創建和設置新使用者，為您提供相當大的靈活性。

HBA 配置文件：

預設情況下，所有使用者都可以從 localhost 登錄，只要是這樣，您就不必更改 pg_hba.conf。如果您希望使用者能夠從另一個 IP 地址（遠端電腦）訪問數據庫，則需要進行更改。

如果您要授予遠端訪問權限：

DATABASE: host --
USER: the username
ADDRESS: the ip address the user is connecting from 
(I doubt this will work with roles since roles do not have passwords,     
though I've never tried either)
(if you want the user to be able to connect from any IP, then use
0.0.0.0/0)
METHOD: md5

關於創建角色和使用者語法的 PostgreSQL 文件。 http://www.postgresql.org/docs/current/static/sql-grant.html

授予序列特權： https ://stackoverflow.com/questions/9325017/error-permission-denied-for-sequence-cities-id-seq-using-postgres

hba 上的 PostgreSQL 文件： http ://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html

引用自：https://dba.stackexchange.com/questions/124084