Postgresql - просматривать привилегии схемы

Есть ли запрос, который я могу выполнить, чтобы показать назначенные в настоящее время привилегии на конкретной схеме?

то есть. привилегии, которые были назначены так:

GRANT USAGE ON SCHEMA dbo TO MyUser

Я пробовал

SELECT *
FROM information_schema.usage_privileges;

но это возвращает только гранты во встроенную роль PUBLIC. Вместо этого я хочу видеть, какие пользователи получили привилегии в различных схемах.

Примечание. Я использую Amazon Redshift вместо чистого PostgreSQL, хотя я соглашусь на чистый ответ PostgreSQL, если это невозможно в Amazon Redshift. (Хотя я подозреваю, что это так)

Ответ 1

Привилегии сохраняются в поле nspacl в pg_namespace. Поскольку это поле массива, вам нужно сделать небольшое причудливое кодирование для его анализа. Этот запрос даст вам заявления о грантах, используемые для пользователей и групп:

select 
'grant ' || substring(
          case when charindex('U',split_part(split_part(array_to_string(nspacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',usage ' else '' end 
          ||case when charindex('C',split_part(split_part(array_to_string(nspacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',create ' else '' end 
       , 2,10000)
|| ' on schema '||nspname||' to "'||pu.usename||'";' 
from pg_namespace pn,pg_user pu
 where  array_to_string(nspacl,',') like '%'||pu.usename||'%' --and pu.usename='<username>' 
and nspowner > 1 
union
select 
'grant ' || substring(
          case when charindex('U',split_part(split_part(array_to_string(nspacl, '|'),pg.groname,2 ) ,'/',1)) > 0 then ',usage ' else '' end 
          ||case when charindex('C',split_part(split_part(array_to_string(nspacl, '|'),pg.groname,2 ) ,'/',1)) > 0 then ',create ' else '' end 
       , 2,10000)
|| ' on schema '||nspname||' to group "'||pg.groname||'";' 
from pg_namespace pn,pg_group pg
 where array_to_string(nspacl,',') like '%'||pg.groname||'%' --and pg.groname='<username>' 
 and nspowner > 1

Ответ 2

в консоли используйте psql:

\dn+

покажет вам

     Name      |  Owner   |   Access privileges   |      Description

Ответ 3

Перечислите все схемы с их привилегиями для текущего пользователя:

WITH "names"("name") AS (
  SELECT n.nspname AS "name"
    FROM pg_catalog.pg_namespace n
      WHERE n.nspname !~ '^pg_'
        AND n.nspname <> 'information_schema'
) SELECT "name",
  pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS "create",
  pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS "usage"
    FROM "names";

Ответ будет, например:

  name   | create | usage 
---------+--------+-------
 public  | t      | t
 test    | t      | t
 awesome | f      | f
(3 rows)

В этом примере текущий пользователь не является владельцем awesome схемы.

Как вы могли догадаться, похожий запрос для конкретной схемы:

SELECT
  pg_catalog.has_schema_privilege(
    current_user, 'awesome', 'CREATE') AS "create",
  pg_catalog.has_schema_privilege(
    current_user, 'awesome', 'USAGE') AS "usage";

и ответ:

 create | usage 
--------+-------
 f      | f

Как вы знаете, для текущей схемы можно использовать pg_catalog.current_schema().

Из всех возможных привилегий

-- SELECT
-- INSERT
-- UPDATE
-- DELETE
-- TRUNCATE
-- REFERENCES
-- TRIGGER
-- CREATE
-- CONNECT
-- TEMP
-- EXECUTE
-- USAGE

единственное CREATE и USAGE разрешено для схем.

Как и current_schema() current_user можно заменить определенной ролью.

БОНУС с current столбцом

WITH "names"("name") AS (
  SELECT n.nspname AS "name"
    FROM pg_catalog.pg_namespace n
      WHERE n.nspname !~ '^pg_'
        AND n.nspname <> 'information_schema'
) SELECT "name",
  pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS "create",
  pg_catalog.has_schema_privilege(current_user, "name", 'USAGE')  AS "usage",
  "name" = pg_catalog.current_schema() AS "current"
    FROM "names";

--   name   | create | usage | current
-- ---------+--------+-------+---------
--  public  | t      | t     | t
--  test    | t      | t     | f
--  awesome | f      | f     | f
-- (3 rows)

С | Функции системной информации | GRANT (привилегии)

Ответ 4

Попробуйте это (работает для роли PUBLIC):

SELECT nspname,
       coalesce(nullif(role.name,''), 'PUBLIC') AS name,
       substring(
          CASE WHEN position('U' in split_part(split_part((','||array_to_string(nspacl,',')), ','||role.name||'=',2 ) ,'/',1)) > 0 THEN ', USAGE' ELSE '' END 
          || CASE WHEN position('C' in split_part(split_part((','||array_to_string(nspacl,',')), ','||role.name||'=',2 ) ,'/',1)) > 0 THEN ', CREATE' ELSE '' END 
       , 3,10000) AS privileges
FROM pg_namespace pn, (SELECT pg_roles.rolname AS name
   FROM pg_roles UNION ALL SELECT '' AS name) AS role
 WHERE (','||array_to_string(nspacl,',')) LIKE '%,'||role.name||'=%'
 AND nspowner > 1;

Ответ 5

Комбинированная версия (группы, пользователи, PUBLIC), которая работает для AWS Redshift:

    SELECT *
FROM (SELECT CASE
               WHEN charindex ('U',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,'|'),pu.usename,2),'/',1)) > 0 THEN ' USAGE'
               ELSE ''
             END ||case WHEN charindex('C',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,'|'),pu.usename,2),'/',1)) > 0 THEN ' CREATE' ELSE '' END AS rights,
             nspname AS schema,
             '' AS role,
             pu.usename AS user
      FROM pg_namespace pn,
           pg_user pu
      WHERE ARRAY_TO_STRING(nspacl,',') LIKE '%' ||pu.usename|| '%'
      --and pu.usename='<username>' 
      AND   nspowner > 1

  UNION

      SELECT CASE
               WHEN charindex ('U',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,'|'),pg.groname,2),'/',1)) > 0 THEN ' USAGE '
               ELSE ''
             END ||case WHEN charindex('C',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,'|'),pg.groname,2),'/',1)) > 0 THEN ' CREATE' ELSE '' END as rights,
             nspname AS schema,
             pg.groname AS role,
             '' AS user
      FROM pg_namespace pn,
           pg_group pg
      WHERE ARRAY_TO_STRING(nspacl,',') LIKE '%' ||pg.groname|| '%'
      --and pg.groname='<username>' 
      AND   nspowner > 1

  UNION

      SELECT CASE
               WHEN POSITION('U' IN SPLIT_PART(SPLIT_PART((',' ||array_to_string (nspacl,',')),',' ||roles.name|| '=',2),'/',1)) > 0 THEN ' USAGE'
               ELSE ''
             END 
      || CASE
               WHEN POSITION('C' IN SPLIT_PART(SPLIT_PART((',' ||array_to_string (nspacl,',')),',' ||roles.name|| '=',2),'/',1)) > 0 THEN ' CREATE'
               ELSE ''
             END AS rights,
             nspname AS schema,
             COALESCE(NULLIF(roles.name,''),'PUBLIC') AS role,
             '' AS user
      FROM pg_namespace pn,
           (SELECT pg_group.groname AS name
            FROM pg_group
            UNION ALL
            SELECT '' AS name) AS roles
      WHERE (',' ||array_to_string (nspacl,',')) LIKE '%,' ||roles.name|| '=%'
      AND   nspowner > 1) privs

ORDER BY schema,rights

Ответ 6

Это то, что psql использует внутренне:)

SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
  pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
  pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;

Ответ 7

Я знаю, что этот пост старый, но я сделал еще один запрос, основанный на разных ответах, чтобы получить короткий и простой в использовании впоследствии:

select
    nspname as schema_name
    , r.rolname as role_name
    , pg_catalog.has_schema_privilege(r.rolname, nspname, 'CREATE') as create_grant
    , pg_catalog.has_schema_privilege(r.rolname, nspname, 'USAGE') as usage_grant
from pg_namespace pn,pg_catalog.pg_roles r
where array_to_string(nspacl,',') like '%'||r.rolname||'%' 
    and nspowner > 1

Я продолжаю думать, что однажды я сделаю запрос, чтобы иметь все права только в одном представлении... Однажды. ;)

Ответ 8

Для текущего вопроса можете попробовать это:

SELECT r.rolname AS role_name,
       n.nspname AS schema_name,
       p.perm AS privilege
FROM pg_catalog.pg_namespace AS n
    CROSS JOIN pg_catalog.pg_roles AS r
    CROSS JOIN (VALUES ('USAGE'), ('CREATE')) AS p(perm)
WHERE has_schema_privilege(r.oid, n.oid, p.perm)
--      AND n.nspname <> 'information_schema'
--      AND n.nspname !~~ 'pg\_%'
--      AND NOT r.rolsuper

Может быть довольно низкая производительность в базе данных с большим количеством объектов и пользователей, с которыми я сталкивался. Таким образом, у меня есть возможный обходной путь с использованием aclexplode() умолчанию aclexplode() как это:

SELECT  oid_to_rolname(a.grantee) AS role_name,
        n.nspname AS schema_name,
        a.privilege_type AS privilege_type
FROM pg_catalog.pg_namespace AS n,
        aclexplode(nspacl) a
WHERE n.nspacl IS NOT NULL 
        AND oid_to_rolname(a.grantee) IS NOT NULL 
--      AND n.nspname <> 'information_schema'
--      AND n.nspname !~~ 'pg\_%'

Но, будьте осторожны, последний не включает привилегии, которые пользователи получили от роли PUBLIC. Где oid_to_rolname() - простая пользовательская функция SELECT rolname FROM pg_roles WHERE oid = $1.

И, как @Jaisus, моя задача требовала иметь все привилегии, которые есть у всех пользователей. Поэтому у меня есть похожие запросы на привилегии schema для table, views, columns, sequences, functions, database и даже привилегий по default.

Также есть полезное расширение pg_permission где я получаю логику для предоставленных запросов и просто обновляю ее для своих целей.