ApsaraDB RDS:Alterar proprietários de objetos no RDS for PostgreSQL

Contexto

No PostgreSQL, os objetos seguem uma hierarquia: instance > database > schema > table/view/sequence/function. Portanto, altere os proprietários dos objetos nível por nível: primeiro os bancos de dados, depois os schemas e, por fim, as tabelas, visualizações, sequências e funções.

Observações de uso

Ao seguir as instruções na Etapa 3: Gerar comandos SQL em massa para alterar proprietários e verificar as alterações, conecte-se à instância do ApsaraDB RDS for PostgreSQL com um cliente pgAdmin ou a ferramenta de linha de comando do PostgreSQL. Não use o Data Management (DMS) nesta etapa, pois ele suprime a saída NOTICE necessária para as etapas subsequentes.

1. Alterar o proprietário do banco de dados

No console do ApsaraDB RDS, altere o proprietário de um banco de dados.

1. Acesse a página Instances. Na barra de navegação superior, selecione a região onde a instância RDS está localizada. Em seguida, localize a instância RDS e clique em ID da instância.

2. No painel de navegação à esquerda, clique em Database Management.

3. Localize o banco de dados desejado e clique em Change Owner na coluna Actions.

   Na caixa de diálogo Change Owner, selecione a conta de destino na lista suspensa Account List e clique em OK. Caso precise criar uma nova conta, clique em Create New Account.

2. Alterar o proprietário do schema

1. Conecte-se à instância do ApsaraDB RDS for PostgreSQL usando um cliente pgAdmin ou a ferramenta de linha de comando do PostgreSQL.

   psql -U <username_of_the_instance> -h <internal_or_public_endpoint> -p <port_of_the_endpoint>

   Para mais informações, consulte Visualizar os endpoints internos e públicos e os números de porta de uma instância do ApsaraDB RDS for PostgreSQL.

2. Execute o seguinte comando SQL para consultar os schemas de negócio no banco de dados atual:

   SELECT * FROM information_schema.schemata where catalog_name = 'your_business_database_name' and schema_name not in ('information_schema','public','pg_catalog','pg_temp_1', 'pg_toast','pg_toast_temp_1');

3. Execute o seguinte comando SQL para alterar o proprietário do schema especificado para o usuário de destino:

   ALTER schema <your_business_schema_name> OWNER TO <target_owner_name>;

   Nota

   • Use uma conta privilegiada para executar este comando e evitar erros de permissão.

   • Se estiver alterando o proprietário de apenas um schema, execute somente a Etapa 1 e a Etapa 3.

   • Para alterar o proprietário de todos os schemas de negócio em um banco de dados, repita a Etapa 3 para cada schema retornado pela consulta na Etapa 2.

4. Execute o seguinte comando SQL para verificar se o proprietário do schema foi alterado:

   SELECT schema_name, schema_owner FROM information_schema.schemata where schema_name = 'your_business_schema_name';

3. Alterar proprietários de objetos em um schema

1. Conecte-se à instância do ApsaraDB RDS for PostgreSQL usando um cliente pgAdmin ou a ferramenta de linha de comando do PostgreSQL.

   psql -U <username_of_the_instance> -h <internal_or_public_endpoint> -p <port_of_the_endpoint>

   Para mais informações, consulte Visualizar os endpoints internos e públicos e os números de porta de uma instância do ApsaraDB RDS for PostgreSQL.

2. Alterar o proprietário de uma tabela, visualização ou sequência

   Execute o seguinte comando SQL para alterar o proprietário de um objeto específico (tabela, visualização ou sequência):

   ALTER table schema_name.object OWNER TO new_owner;

   Parâmetros:

   • schema_name: nome do schema ao qual o objeto pertence.

   • object: nome da tabela, visualização ou sequência.

   • new_owner: nome de usuário do novo proprietário.

3. Alterar o proprietário de uma função

   Execute o seguinte comando SQL para alterar o proprietário de um objeto específico (função):

   ALTER function schema_name.function OWNER TO new_owner;

   Parâmetros:

   • schema_name: nome do schema ao qual a função pertence.

   • function: nome da função.

   • new_owner: nome de usuário do novo proprietário.

   Nota

   Se você receber o erro abaixo, o nome da função não é único porque existem múltiplas funções com o mesmo nome no banco de dados PostgreSQL atual. Nesse caso, inclua a lista de argumentos da função para identificá-la de forma inequívoca.

   ERROR: function name "function_name" is not unique
   NOTICE: Specify the argument list to SELECT the function unambiguously.

4. Gerar comandos SQL em massa para alterar proprietários e verificar as alterações

   1. Para alterar em massa o proprietário de todos os objetos em um schema, execute o seguinte comando para gerar os comandos SQL necessários:

      Importante

      • Limitação do cliente SQL: O script SQL a seguir não gera saída NOTICE no Data Management (DMS). Execute este script usando um cliente como psql ou pgAdmin.

      • Limitação de schema de sistema: Não é possível alterar o proprietário de uma TOAST table no schema pg_toast, pois trata-se de um schema de sistema com proprietário imutável. Isso não afeta o uso normal, já que usuários comuns ainda podem acessar essas tabelas.

      • Tabelas particionadas e externas: A alteração de propriedade de tabelas particionadas e externas segue o mesmo processo das tabelas regulares. O script abaixo lida automaticamente com esses objetos.

      DO $$
      DECLARE
      r record;
      i int;
      v_schema text[] := '{public,schema_name}'; -- Enter the array of schema names to be modified. You can specify multiple schema names. If a schema contains many tables, we recommend that you run the script for each schema individually to avoid impacting your services.
      v_new_owner varchar := 'owner_name';   -- Username of the target owner
      BEGIN
      FOR r IN
      SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.tables WHERE table_schema = ANY (v_schema)
      UNION ALL
      SELECT 'ALTER TABLE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.sequences WHERE sequence_schema = ANY (v_schema)
      UNION ALL
      SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.views WHERE table_schema = ANY (v_schema)
      UNION ALL
      SELECT 'ALTER FUNCTION "' || nsp.nspname || '"."' || p.proname || '"(' || pg_get_function_identity_arguments(p.oid) || ') OWNER TO ' || v_new_owner || ';' AS a FROM pg_proc p JOIN pg_namespace nsp ON p.pronamespace = nsp.oid WHERE nsp.nspname = ANY (v_schema)
      LOOP
      RAISE NOTICE '%', r.a;
      END LOOP;
      END
      $$;

      Após a execução deste bloco de código, o PostgreSQL exibe os comandos SQL como mensagens NOTICE. Revise os comandos gerados quanto à precisão. Se estiverem corretos, copie-os e execute-os para aplicar as alterações de propriedade.

      Exemplo de geração em massa (comando SQL e saída)

      Este exemplo altera o proprietário de todos os objetos, incluindo tabelas, visualizações, sequências e funções, dentro dos schemas public e myschema1 do banco de dados db1 para o usuário user_test.

      db1=> DO $$
      DECLARE
      r record;
      i int;
      v_schema text[] := '{public,myschema1}';
      v_new_owner varchar := 'user_test';
      BEGIN
      FOR r IN
      SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.tables WHERE table_schema = ANY (v_schema)
      UNION ALL
      SELECT 'ALTER TABLE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.sequences WHERE sequence_schema = ANY (v_schema)
      UNION ALL
      SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.views WHERE table_schema = ANY (v_schema)
      UNION ALL
      SELECT 'ALTER FUNCTION "' || nsp.nspname || '"."' || p.proname || '"(' || pg_get_function_identity_arguments(p.oid) || ') OWNER TO ' || v_new_owner || ';' AS a FROM pg_proc p JOIN pg_namespace nsp ON p.pronamespace = nsp.oid WHERE nsp.nspname = ANY (v_schema)
      LOOP
      RAISE NOTICE '%', r.a;
      END LOOP;
      END
      $$;

      O comando produz a seguinte saída:

      NOTICE: ALTER TABLE "public"."t1" OWNER TO user_test;
      NOTICE: ALTER TABLE "public"."user_view_tt1" OWNER TO user_test;
      NOTICE: ALTER TABLE "myschema1"."tt1" OWNER TO user_test;
      NOTICE: ALTER TABLE "myschema1"."user_view_tt1" OWNER TO user_test;
      NOTICE: ALTER TABLE "public"."my_seq2" OWNER TO user_test;
      NOTICE: ALTER TABLE "myschema1"."my_seq2" OWNER TO user_test;
      NOTICE: ALTER TABLE "public"."user_view_tt1" OWNER TO user_test;
      NOTICE: ALTER TABLE "myschema1"."user_view_tt1" OWNER TO user_test;
      NOTICE: ALTER FUNCTION "public"."my_demo_func"(arg1 integer) OWNER TO user_test;
      DO

   2. Verificar as alterações de propriedade

      • Verifique se o proprietário de uma tabela, visualização ou sequência foi alterado.

        SELECT n.nspname AS schema_name, c.relname AS table_name , u.rolname AS owner FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace JOIN pg_roles u ON u.oid = c.relowner WHERE n.nspname = 'schema_name_of_the_object' AND c.relname = 'name_of_the_table_view_or_sequence';

      • Verifique se o proprietário de uma função foi alterado.

        SELECT n.nspname AS schema_name, p.proname AS function_name, u.rolname AS owner FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid JOIN pg_roles u ON p.proowner = u.oid WHERE n.nspname = 'schema_name_of_the_function' AND p.proname = 'name_of_the_function';

Aplicável a

ApsaraDB RDS for PostgreSQL