Как сгенерировать заявление об отзыве на postgresql 9.3.x

Команда, я работаю над Amazon RDS Postgres (9.3.1/9.3.2/9.3.3)

pgtest=> select version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.3 20120306 (Red Hat 4.6.3-2), 64-bit
(1 row)

Я динамически генерирую оператор отзыва, используя приведенный ниже сценарий. это работает на красном смещении амазонки. но не в Постгресе. это дает ошибку. Может ли кто-нибудь исправить меня? Я могу получить вывод запроса для подзапроса.

примечание: в красном смещении на данный момент триггер не поддерживается. поэтому я исключил «триггер» из списка отзывов. Но я пытаюсь выполнить сейчас в AMAZON RDS Postgres

select  
    'revoke ' || substring(
                case when charindex('r',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',select ' else '' end 
              ||case when charindex('w',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',update ' else '' end 
              ||case when charindex('a',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',insert ' else '' end 
              ||case when charindex('d',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',delete ' else '' end 
              ||case when charindex('R',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',rule ' else '' end 
              ||case when charindex('x',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',references ' else '' end            
              ||case when charindex('X',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',execute ' else '' end 
              ||case when charindex('U',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',usage ' else '' end 
              ||case when charindex('C',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',create ' else '' end 
              ||case when charindex('T',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',temporary ' else '' end 
           , 2,10000)
    || ' on '||namespace||'.'||item ||' from "'||pu.usename||'";' as grantsql
    from 
    (SELECT 
     use.usename as subject, 
     nsp.nspname as namespace, 
     c.relname as item, 
     c.relkind as type, 
     use2.usename as owner, 
     c.relacl 
     FROM 
     pg_user use 
     cross join pg_class c 
     left join pg_namespace nsp on (c.relnamespace = nsp.oid) 
     left join pg_user use2 on (c.relowner = use2.usesysid)
     WHERE 
     c.relowner = use.usesysid  
     and  nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
     ORDER BY   subject,   namespace,   item 
    ) as AA join pg_user pu on array_to_string(relacl, '|') like '%'||pu.usename||'%' 
    where relacl is not null
     and pu.usename='test_user55';

Я получаю ошибку ниже:

dbsa_db=> select
dbsa_db->       'revoke ' || substring(
dbsa_db(>                   case when charindex('r',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',select ' else '' end
dbsa_db(>                 ||case when charindex('w',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',update ' else '' end
dbsa_db(>                 ||case when charindex('a',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',insert ' else '' end
dbsa_db(>                 ||case when charindex('d',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',delete ' else '' end
dbsa_db(>                 ||case when charindex('R',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',rule ' else '' end
dbsa_db(>                 ||case when charindex('x',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',references ' else '' end
dbsa_db(>                 ||case when charindex('X',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',execute ' else '' end
dbsa_db(>                 ||case when charindex('U',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',usage ' else '' end
dbsa_db(>                 ||case when charindex('C',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',create ' else '' end
dbsa_db(>                 ||case when charindex('T',split_part(split_part(array_to_string(relacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',temporary ' else '' end
dbsa_db(>              , 2,10000)
dbsa_db->       || ' on '||namespace||'.'||item ||' from "'||pu.usename||'";' as grantsql
dbsa_db->       from
dbsa_db->       (SELECT
dbsa_db(>        use.usename as subject,
dbsa_db(>        nsp.nspname as namespace,
dbsa_db(>        c.relname as item,
dbsa_db(>        c.relkind as type,
dbsa_db(>        use2.usename as owner,
dbsa_db(>        c.relacl
dbsa_db(>        FROM
dbsa_db(>        pg_user use
dbsa_db(>        cross join pg_class c
dbsa_db(>        left join pg_namespace nsp on (c.relnamespace = nsp.oid)
dbsa_db(>        left join pg_user use2 on (c.relowner = use2.usesysid)
dbsa_db(>        WHERE
dbsa_db(>        c.relowner = use.usesysid
dbsa_db(>        and  nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
dbsa_db(>        ORDER BY   subject,   namespace,   item
dbsa_db(>       ) as AA join pg_user pu on array_to_string(relacl, '|') like '%'||pu.usename||'%'
dbsa_db->       where relacl is not null
dbsa_db->        and pu.usename='test_user55';
ERROR:  function charindex(unknown, text) does not exist
LINE 3:              case when charindex('r',split_part(split_part(a...
                               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
dbsa_db=>
dbsa_db=>
dbsa_db=> SELECT
dbsa_db->        use.usename as subject,
dbsa_db->        nsp.nspname as namespace,
dbsa_db->        c.relname as item,
dbsa_db->        c.relkind as type,
dbsa_db->        use2.usename as owner,
dbsa_db->        c.relacl
dbsa_db->        FROM
dbsa_db->        pg_user use
dbsa_db->        cross join pg_class c
dbsa_db->        left join pg_namespace nsp on (c.relnamespace = nsp.oid)
dbsa_db->        left join pg_user use2 on (c.relowner = use2.usesysid)
dbsa_db->        WHERE
dbsa_db->        c.relowner = use.usesysid
dbsa_db->        and  nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
dbsa_db->        ORDER BY   subject,   namespace,   item;
     subject     | namespace |    item    | type |      owner      |                                                         relacl
-----------------+-----------+------------+------+-----------------+-------------------------------------------------------------------------------------------------------------------------
 test_user55     | schema1   | table_mmar | r    | test_user55     |
 dbsa            | schema1   | table1     | r    | dbsa            | {dbsa=arwdDxt/dbsa,service_user1=a*r*w*d*D*x*t*/dbsa,service_user2=a*r*w*d*D*x*t*/dbsa}
 dbsa            | schema1   | view1      | v    | dbsa            | {dbsa=arwdDxt/dbsa,service_user1=a*r*w*d*D*x*t*/dbsa,service_user2=a*r*w*d*D*x*t*/dbsa,test_user55=r/service_user1}
(3 rows)


dbsa_db=>

(ii) query2 : ниже запрос я использую для извлечения любых грантов, предоставленных на уровне схемы. это отлично работает в красном смещении. Но не в постгре 9.3.3

select 
    'revoke ' || 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||' from "'||pu.usename||'";' as syntax 
    from pg_namespace pn,pg_user pu
     where pu.usename='test_user55' and array_to_string(nspacl,',') like '%'||pu.usename||'%'
     and nspowner >= 1 ;





dbsa_db=> select substring(
dbsa_db(>         case when charindex('U',split_part(split_part(array_to_string(nspacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',usage ' else '' end , 2,10000),
dbsa_db->        substring(
dbsa_db(>         case when charindex('C',split_part(split_part(array_to_string(nspacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',create ' else '' end , 2,10000)
dbsa_db->         ,nspname
dbsa_db->         from pg_namespace pn, pg_user pu
dbsa_db->         where pu.usename='test_user55' and array_to_string(nspacl,',') like '%'||pu.usename||'%'
dbsa_db->       and nspowner > 1 ;
ERROR:  function charindex(unknown, text) does not exist
LINE 2:         case when charindex('U',split_part(split_part(array_...
                          ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
dbsa_db=>

(iii) запрос3 ниже запроса, который я использую, чтобы проверить, предоставлен ли какой-либо грант на уровне базы данных. это отлично работает с красным смещением, но не с rds postgres.

dbsa_db=> grant all on database dbsa_db to test_user55;
GRANT
dbsa_db=>

select pu.usename, pd.datname, substring(
                     case when charindex('C',split_part(split_part(array_to_string(datacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',create ' else '' end, 2,10000),
                   substring(
                     case when charindex('T',split_part(split_part(array_to_string(datacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',temp ' else '' end, 2,10000)
from pg_database pd,pg_user pu
where pu.usename='test_user55' and array_to_string(datacl,',') like '%'||pu.usename||'%'
and datdba > 1;



dbsa_db=> select pu.usename, pd.datname, substring(
dbsa_db(>                                case when charindex('C',split_part(split_part(array_to_string(datacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',create ' else '' end, 2,10000),
dbsa_db->                              substring(
dbsa_db(>                                case when charindex('T',split_part(split_part(array_to_string(datacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',temp ' else '' end, 2,10000)
dbsa_db-> from pg_database pd,pg_user pu
dbsa_db-> where pu.usename='test_user55' and array_to_string(datacl,',') like '%'||pu.usename||'%'
dbsa_db-> and datdba > 1;
ERROR:  function charindex(unknown, text) does not exist
LINE 2:             case when charindex('C',split_part(split_part(ar...
                              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
dbsa_db=>

Пожалуйста, помогите мне. Спасибо.


amazon-redshift postgresql-9.3
person user3258784    schedule 04.04.2014    source источник
comment
Redshift и Postgres 9.3 противоречат друг другу. Также вопрос кажется длинным. Попробуйте свести его к четкому вопросу или вместо этого рассмотрите codereview.stackexchange.com.   -  person Erwin Brandstetter    schedule 10.04.2014

Ответы (1)


arrow_upward
0
arrow_downward

PostgreSQL не имеет функции charindex, Amazon Redshift добавил ее как своего рода синоним к strpos. Для Amazon RDS, использующего PostgreSQL, вместо этого можно использовать функции position или strpos.

Пример: Amazon Redshift вы можете сделать так:

select charindex('fish', 'dogfish');

Для Amazon RDS с использованием PostgreSQL вы можете выполнить одно из следующих двух действий:

select strpos('dogfish', 'fish');
select position('fish' in 'dogfish');

Обратите внимание, что подстрока и строка в strpos меняются местами!!

Все результаты будут 4.

Кроме того, вот документация по строковым функциям PostgreSQL: http://www.postgresql.org/docs/8.3/static/functions-string.html

person Mark Spiezio    schedule 11.04.2014