Início / dba / Perguntas / 171932
Accepted
Alex
Asked: 2017-04-26 05:14:54 +0800 CST

PostgreSQL 9.3.13, Como atualizo as visualizações materializadas com diferentes usuários?

  • 772

[Acho que a causa raiz desse problema é que eu não entendo permissões e privilégios...]

Então, para montar o cenário, a configuração que tenho é um DB, chame de MyDb .

Eu tenho dois usuários, spu1 e u1 , spu1 é um superusuário e u1 um usuário 'regular'. O proprietário do MyDb é spu1 . Acho que também devo dizer que o u1 criou bancos de dados e criou privilégios de função herdados de uma função de grupo.

Eu tenho um esquema sch1 , que é um esquema definido pelo usuário.

Dentro desse esquema eu tenho uma tabela, chamo tbl1 , e uma visão materializada, chamo mvw1 .

O dono do tbl1 é o spu1 , o dono do mvw1 é u1 .


O problema:

Na configuração atual, conforme descrito acima, não consigo atualizar mvw1 como u1 ou spu1 . Eu simplesmente recebo o erro divertido abaixo (que pesquisei extensivamente, mas não encontrei nada que resolva bastante minha configuração ..).

ERROR:  permission denied for relation tbl1
********** Error **********

ERROR: permission denied for relation tbl1
SQL state: 42501

eu descobri que

  1. Alterar o proprietário de mvw1 para spu1 , me permite atualizar como spu1 .
  2. Executar o abaixo me permite atualizar mvw1 como u1 .

Estou tentando descobrir quais são as permissões ausentes (idealmente o mínimo necessário) que preciso conceder ao usuário regular, u1 , para que eu possa atualizar essa visualização quando estiver conectado como eles.

A primeira opção, embora seja bom saber, não resolve o meu problema. A segunda opção parece que estou de fato concedendo permissões de superusuário a um não superusuário, ou melhor, concedendo privilégios maiores do que preciso.

Se alguém puder me explicar o que exatamente está acontecendo aqui (ou apontar quais informações eu perdi na descrição do meu problema necessário para resolvê-lo), e deixe-me saber se minha segunda opção é de fato o caminho a seguir ou de uma alternativa melhor?

Muito obrigado!

postgresql permissions

3 respostas

  1. Best Answer

    Você pode fazer isso com uma função que é executada no contexto de segurança de seu proprietário.

    Função que atualiza a view (crie-a com o usuário que possui a MV/tabela):

    CREATE OR REPLACE FUNCTION refresh_mvw1()
RETURNS void
SECURITY DEFINER
AS $$
BEGIN
REFRESH MATERIALIZED VIEW mvw1 with data;
RETURN;
END;
$$ LANGUAGE plpgsql;

    Conceda execute na função para qualquer usuário que você deseja que possa atualizar a visualização:

    -- Users have 'execute' permissions by default on functions!
revoke all on function refresh_mvw1() from public;
grant execute on function refresh_mvw1() to u1;

    Para atualizar:

    select refresh_mvw1();

    Dos documentos do Postgres :

    SECURITY DEFINER especifica que a função deve ser executada com os privilégios do usuário que a criou.

    Versão que suporta um parâmetro:

    CREATE OR REPLACE FUNCTION refresh_mv_xxx(table_name text)
RETURNS void
SECURITY DEFINER
AS $$
DECLARE sql text; 
BEGIN
sql := 'REFRESH MATERIALIZED VIEW ' || table_name || ' with data';
EXECUTE sql;
RETURN;
END;
$$ LANGUAGE plpgsql;

    ... mas não tenho certeza se o SQL dinâmico ainda será executado como definidor.

    • 25

  2. A segunda tentativa de função de @Philᵀᴹ é vulnerável à injeção de SQL usando:

    SELECT refresh_mv_xxx('example_mview_name with data; TRUNCATE TABLE example_table_name CASCADE; REFRESH MATERIALIZED VIEW example_mview_name')

    where example_mview_namee example_table_namereferem-se a qualquer uma de suas visualizações e tabelas materializadas, respectivamente.

    O que é perigoso de várias maneiras.

    Eu sugeriria usar apenas sua primeira tentativa ou da seguinte maneira:

    CREATE OR REPLACE FUNCTION refresh_mv_dynamic(table_name text)
    RETURNS void SECURITY DEFINER
AS $$
DECLARE
    sql text;
BEGIN
    sql := format('REFRESH MATERIALIZED VIEW %I with data', table_name);
    EXECUTE sql;
    RETURN;
END;
$$
    LANGUAGE plpgsql;

    format()com especificador de formato%I

    Itrata o valor do argumento como um identificador SQL, colocando-o entre aspas, se necessário. É um erro o valor ser nulo (equivalente a quote_ident).

    • 7

  3. Outra opção é criar uma função de grupo (não superusuário) à qual spu1 e u1 pertencem/herdarem (por exemplo, "refreshers") e atribuir essa função de grupo como o proprietário da visão materializada.

    • 5

relate perguntas