PostgreSQL

Hiển thị danh sách các ROLE đã tạo

Hiển thị danh sách các POSTGRESQL ROLE đã tạo
Được viết bởi Minh Hoàng

Series chia sẻ về PostgreSQL – Cơ sở dữ liệu mã nguồn mở tiên tiến nhất thế giới.

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+

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

Hiển thị danh sách các ROLE đã tạo (1)

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ínhrole đó 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+

Hiển thị danh sách các ROLE đã tạo (2)

2. Lấy từ danh mục hệ thống pg_roles

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, rolsuperrolcanlogin từ pg_roles như sau:

SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;

Hiển thị danh sách các ROLE đã tạo (3)

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_%';

Hiển thị danh sách các ROLE đã tạo (4)

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;

Hiển thị danh sách các ROLE đã tạo (5)

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;

Hiển thị danh sách các ROLE đã tạo (6)

※Để 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';

Cảm ơn bạn đã theo dõi. Đừng ngần ngại hãy cùng thảo luận với chúng tôi!

Giới thiệu

Minh Hoàng

Xin chào, tôi là Hoàng Ngọc Minh, hiện đang làm BrSE, tại công ty Toyota, Nhật Bản. Những gì tôi viết trên blog này là những trải nghiệm thực tế tôi đã đúc rút ra được trong cuộc sống, quá trình học tập và làm việc. Các bài viết được biên tập một cách chi tiết, linh hoạt để giúp bạn đọc có thể tiếp cận một cách dễ dàng nhất. Hi vọng nó sẽ có ích hoặc mang lại một góc nhìn khác cho bạn[...]

Translate »