Bài viết này sẽ mô tả cách lấy danh sách các roles đã tạo trong PostgreSQL.
1. Sử dụng lệnh \du hoặc \du+
Đầu tiên là để có được một danh sách các roles bằng cách sử dụng lệnh \du như sau:
\du
Chúng ta đã nhận được danh sách các roles được tạo. Đối với mỗi role, hiển thị thông tin về tên role, danh sách các thuộc tính và role đó thuộc về group roles nào.
Trường hợp muốn hiển thêm cột mô tả (description) thì bạn dùng lệnh \du+
\du+
2. Lấy từ danh mục hệ thống pg_roles
Tiếp theo, lấy danh sách roles từ pg_roles, một trong những danh mục hệ thống (system catalog) của PostgreSQL. pg_roles có các cột sau:
| Tên cột | Kiểu dữ liệu | Mô tả |
| rolname | name | Tên role |
| rolsuper | bool | Có đặc quyền (privilege) superuser hay không |
| rolinherit | bool | Có kế thừa các đặc quyền của các roles mà nó là thành viên hay không |
| rolcreaterole | bool | Có quyền tạo role hay không |
| rolcreatedb | bool | Có quyền tạo database hay không |
| rolcanlogin | bool | Có thể login hay không |
| rolreplication | bool | Có phải là một role để nhân rộng (replication) hay không |
| rolconnlimit | int4 | Số lượng kết nối đồng thời tối đa (-1 : là không giới hạn) |
| rolpassword | text | Mật khẩu, nhưng chỉ hiển thị ra là ******** |
| rolvaliduntil | timestamptz | Ngày hết hạn mật khẩu (NULL : nếu không có ngày hết hạn) |
| rolbypassrls | bool | Có hay không việc bỏ qua tất cả các security policies của từng dòng |
| rolconfig | text[] | Mặc định dành riêng cho role liên quan đến runtime configuration variables |
| oid | oid | Id của role |
Ví dụ thử lấy giá trị của các cột rolname, rolsuper và rolcanlogin từ pg_roles như sau:
SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;
Kết quả chúng ta nhận được là tất cả các roles của hệ thống (roles mặc định) và roles do người dùng tự tạo.
Trường hợp muốn xem thông tin của một role cụ thể thì làm như sau:
SELECT * FROM pg_roles WHERE rolname = 'role_name';
và bạn sẽ nhận được thông tin của tất cả các cột:
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
Trường hợp bạn muốn chỉ hiển thị role do người dùng tự tạo thì sử dụng truy vấn sau:
SELECT rolname, rolsuper, rolcanlogin
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%';
Hoặc:
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
Trường hợp muốn xem thuộc tính, đặc quyền của roles thì làm như sau:
SELECT usename AS role_name,
CASE
WHEN usesuper AND usecreatedb THEN
CAST('superuser, create database' AS pg_catalog.text)
WHEN usesuper THEN
CAST('superuser' AS pg_catalog.text)
WHEN usecreatedb THEN
CAST('create database' AS pg_catalog.text)
ELSE
CAST('' AS pg_catalog.text)
END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;
select (select pg_authid.rolname as role_name from pg_authid where pg_authid.oid = pg_auth_members.roleid), (select pg_authid.rolname as member_name from pg_authid where pg_authid.oid = pg_auth_members.member), (select pg_authid.rolname as grantor_name from pg_authid where pg_authid.oid = pg_auth_members.grantor), admin_option from pg_auth_members;
※Để xem một role cụ thể có những quyền gì, chẳng hạn như SELECT, INSERT, DELETE, UPDATE table, view,… nào thì sử dụng truy vấn sau:
SELECT table_catalog, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'role_name';
