postgres user - ghdrako/doc_snipets GitHub Wiki

\du          -- lista ról (użytkowników i grup)
\du+         -- lista ról z dodatkowymi kolumnami (opis, uprawnienia)


SELECT
  rolname,
  rolsuper,
  rolcreaterole,
  rolcreatedb,
  rolcanlogin,
  rolreplication,
  rolbypassrls
FROM pg_roles
ORDER BY rolname;

-- Zaszyfrowane hasła (tylko superuser)
SELECT
  usename,
  passwd,
  valuntil
FROM pg_shadow;

-- Członkostwo w grupach
SELECT
  m.roleid::regrole   AS grupa,
  m.member::regrole   AS czlonek
FROM pg_auth_members m
ORDER BY 1,2;

Uprawnienia na obiekty - jakie role mają przywileje na schematach, tabelach, itp.

SELECT
  nsp.nspname   AS schema,
  cls.relname  AS tabela,
  pg_get_userbyid(cls.relowner) AS owner,
  array_agg(distinct acl.grantee)   AS grantees
FROM pg_class cls
JOIN pg_namespace nsp ON nsp.oid = cls.relnamespace
LEFT JOIN (
  SELECT
    acl.tableoid,
    (aclexplode(cls.relacl)).grantee::regrole::text AS grantee
  FROM pg_class cls
) acl ON acl.tableoid = cls.oid
WHERE cls.relkind = 'r'       -- tylko tabele
  AND nsp.nspname NOT IN ('pg_catalog','information_schema')
GROUP BY 1,2,3
ORDER BY 1,2;

Zarządzanie rolami

Przykładowe komendy do tworzenia i modyfikacji:

-- tworzenie roli tylko do logowania:
CREATE ROLE nowy_uzytkownik LOGIN PASSWORD 'tajnehaslo';

-- nadanie uprawnienia tworzenia baz
ALTER ROLE nowy_uzytkownik CREATEDB;

-- dodanie do grupy admin
GRANT admin TO nowy_uzytkownik;

-- blokada logowania (np. zamiast usuwania)
ALTER ROLE nowy_uzytkownik NOLOGIN;

-- usunięcie roli
DROP ROLE nowy_uzytkownik;

-- zmiana hasla
ALTER ROLE nazwa_uzytkownika WITH PASSWORD 'noweTajneHaslo';

-- Blokada poprzez NOLOGIN / przywrócenie LOGIN
ALTER ROLE nazwa_uzytkownika NOLOGIN;
ALTER ROLE nazwa_uzytkownika LOGIN;

-- Blokada przez wygaśnięcie hasła (VALID UNTIL)
ALTER ROLE nazwa_uzytkownika VALID UNTIL '2000-01-01';
ALTER ROLE nazwa_uzytkownika VALID UNTIL 'infinity';

Uprawnienia grupy do tabeli

WITH perms AS (
  SELECT
    (aclexplode(c.relacl)).grantee      AS grantee,
    (aclexplode(c.relacl)).privilege_type AS privilege_type,
    (aclexplode(c.relacl)).is_grantable   AS is_grantable
  FROM pg_class c
  JOIN pg_namespace n 
    ON n.oid = c.relnamespace
  WHERE
    n.nspname = 'aci'
    AND c.relname = 'e001'
)
SELECT
  grantee::regrole::text AS role,
  privilege_type,
  is_grantable
FROM perms
WHERE grantee::regrole::text = 'acp_rw_grp';