Czy istnieje sposób na ustawienie właściciela wszystkich obiektów w bazie danych PostgreSQL jednocześnie?

/programming/1348126/modify-owner-on-all-tables-simultently-in-postgresql opisuje kilka fajnych sposobów na zmianę tabeli i innych obiektów na konkretnego użytkownika i działa płynnie, jednak wszystkie sugestie wydają się ignorować utworzone przeze mnie funkcje.

Czy istnieje dość łatwy sposób zresetowania właściciela WSZYSTKICH obiektów w bazie danych, w tym funkcji? Robienie tego ręcznie jest wysoce niepożądane.

Powinieneś zawsze manipulować katalogami systemowymi tylko bezpośrednio, jeśli dokładnie wiesz, co robisz. Może mieć nieoczekiwane skutki uboczne. Lub możesz uszkodzić bazę danych (lub cały klaster bazy danych) nie do naprawienia.

Odpowiedź Jeremy'ego , choć w zasadzie robi to samo, nie jest wskazana dla ogółu społeczeństwa. Bezwarunkowo zmienia wszystkie funkcje w schemacie. Czy na pewno nie ma to wpływu na funkcje systemu lub funkcje zainstalowane przez dodatkowy moduł?
Nie ma również sensu zmieniać właściciela funkcji, które już należą do wyznaczonego właściciela.

Najpierw sprawdź, czy REASSIGN OWNEDmoże Ci pomóc:

zmienić własność obiektów bazy danych należących do roli bazy danych

Musisz jawnie wymienić wszystkie role, które mają zostać odrzucone. Ale również przypisuje funkcje .

Aby przypisać wszystkie funkcje (i żadnych innych obiektów) w danym schemacie do nowego właściciela (opcjonalnie niezależnie od poprzedniego właściciela):

SELECT string_agg('ALTER FUNCTION ' || oid::regprocedure || ' OWNER TO foo;', E'\n') AS ddl
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'public';
-- AND p.relowner <> (SELECT oid FROM pg_roles WHERE rolname = 'foo')
-- AND p.proname ~~ 'f_%'

Generuje to kanoniczne polecenia SQLALTER FUNCTION ... do zmiany wszystkich funkcji (w określonym schemacie). Możesz sprawdzić polecenia przed ich wykonaniem - pojedynczo lub wszystkie na raz:

ALTER FUNCTION public.bar(text, text) OWNER TO foo;
ALTER FUNCTION public.foo(x integer) OWNER TO foo;
...

Zawarłem kilka skomentowanych WHEREklauzul, których możesz użyć do filtrowania wyników.

Rzutowanie na regproceduretworzy prawidłową nazwę funkcji z parametrami, w razie potrzeby podwójnie cudzysłów, schemat - kwalifikowany w razie potrzeby dla prądu search_path.

Funkcja agregująca string_agg () wymaga PostgreSQL 9.0 lub nowszego. W starszej wersji zamień na array_agg()i array_to_string().

Ty mógł postawić wszystko na DOrachunku lub funkcji jak wykazano w tym związanej z odpowiedzią:

• Usunąć wszystkie funkcje w przestrzeni nazw? (Wykonać wygenerowane polecenia DDL?)

W Postgresie 9.5 lub nowszym możesz uprościć zapytanie, używając nowych typów identyfikatorów obiektów regnamespaceiregrole :

SELECT string_agg('ALTER FUNCTION '|| oid::regprocedure || ' OWNER TO foo;', E'\n') AS ddl
FROM   pg_catalog.pg_proc
WHERE  pronamespace = 'public'::regnamespace;
-- AND relowner <> 'foo'::regrole
-- AND proname ~~ 'f_%'

Używam tej funkcji do zmiany właściciela tabel, funkcji, typów itp. Możesz zmienić zapytanie kursorów, aby dostosować je do swoich potrzeb.

CREATE OR REPLACE FUNCTION fn_setowner(varchar(50), boolean) RETURNS void AS
$BODY$
DECLARE
p_owner ALIAS FOR $1;
p_debug ALIAS FOR $2;
v_i integer := 0;
v_sql text;

--  CURSORS
-- SCHEMA
pesquemas CURSOR FOR
    SELECT quote_ident(schema_name) as nombre_esquema from information_schema.schemata WHERE schema_name NOT LIKE 'pg_%'
    and schema_name NOT IN ('information_schema') ORDER BY 1 ASC;

-- TABLE
ptablas CURSOR FOR
    SELECT quote_ident(table_schema) || '.' || quote_ident(table_name) as nombre_tabla, * FROM information_schema.tables
    WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
    AND table_type <> 'FOREIGN TABLE' ORDER BY 1 ASC;

-- FUNCTION
pfunciones CURSOR FOR
    SELECT quote_ident(b.nspname) || '.' || quote_ident(a.proname) || '(' || pg_catalog.oidvectortypes(a.proargtypes) || ')' as nombre_function 
    FROM pg_proc a  INNER JOIN pg_namespace b on a.pronamespace = b.oid 
    WHERE b.nspname NOT IN ('pg_catalog', 'information_schema') AND proisagg = 'f'
    AND a.proname not like 'fsym_%' AND a.proname not like 'dblink%' ORDER BY 1 ASC;

-- SEQUENCE
psecuencias CURSOR FOR
    SELECT quote_ident(sequence_schema) || '.' || quote_ident(sequence_name) as nombre_secuencia FROM information_schema.sequences
    WHERE sequence_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY 1 ASC;

-- TYPE
ptipos CURSOR FOR
    SELECT quote_ident(n.nspname) || '.' || quote_ident(t.typname) as nombre_tipo
    FROM pg_type t
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace 
    WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) 
    AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
    AND n.nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY 1 ASC;


BEGIN
--  CHECK LOGIN
    IF NOT EXISTS (SELECT 1 FROM pg_user WHERE usename = p_owner) THEN                     
        RAISE EXCEPTION 'Login role not exists --> %', p_owner
            USING HINT = 'Please specify correct login and try again.';
    END IF;

    v_i = 0;
    if (p_debug) THEN
    RAISE NOTICE '--########## CHANGE SCHEMA OWNER ##########--';
    END IF;
    FOR resquema IN pesquemas LOOP
        v_sql = 'ALTER SCHEMA ' || resquema.nombre_esquema || ' OWNER TO ' || quote_ident(p_owner) || ';';
        if (p_debug) THEN RAISE NOTICE '%', v_sql; END IF;
        EXECUTE v_sql;
        v_i = v_i + 1;
    END LOOP;
    if (p_debug) THEN
    RAISE NOTICE '--@@@@@@ SCHEMAS WITH OWNER % TOTAL = % @@@@@@--', p_owner, CAST(v_i AS VARCHAR);
    END IF;

    v_i = 0;
    if (p_debug) THEN
    RAISE NOTICE '--########## CHANGE TABLE OWNER ##########--';
    END IF;
    FOR rtables IN  ptablas LOOP
        v_sql = 'ALTER TABLE ' || rtables.nombre_tabla || ' OWNER TO ' || quote_ident(p_owner) || ';';
        if (p_debug) THEN RAISE NOTICE '%', v_sql; END IF;
        EXECUTE v_sql;
        v_i = v_i + 1;
    END LOOP;
    if (p_debug) THEN
    RAISE NOTICE '--@@@@@@ TABLES WITH OWNER % TOTAL = % @@@@@@--', p_owner, CAST(v_i AS VARCHAR);
    END IF;

    v_i = 0;
    if (p_debug) THEN
    RAISE NOTICE '--########## CHANGE FUNCTION OWNER ##########--';
    END IF;
    FOR rfunction IN  pfunciones LOOP
        v_sql = 'ALTER FUNCTION ' || rfunction.nombre_function || ' OWNER TO ' || quote_ident(p_owner) || ';';
        if (p_debug) THEN RAISE NOTICE '%', v_sql; END IF;
        EXECUTE v_sql;
        v_i = v_i + 1;
    END LOOP;
    if (p_debug) THEN
    RAISE NOTICE '--@@@@@@ FUNCTIONS WITH OWNER % TOTAL = % @@@@@@--', p_owner, CAST(v_i AS VARCHAR);
    END IF;

    v_i = 0;
    if (p_debug) THEN
    RAISE NOTICE '--########## CHANGE SEQUENCE OWNER ########## --';
    END IF;
    FOR rsecuencias IN  psecuencias LOOP
        v_sql = 'ALTER TABLE ' || rsecuencias.nombre_secuencia || ' OWNER TO ' || quote_ident(p_owner) || ';';             
        if (p_debug) THEN RAISE NOTICE '%', v_sql; END IF;
        EXECUTE v_sql;
        v_i = v_i + 1;
    END LOOP;
    if (p_debug) THEN
    RAISE NOTICE '--@@@@@@ SEQUENCES WITH OWNER % TOTAL = % @@@@@@--', p_owner, CAST(v_i AS VARCHAR);
    END IF;

    v_i = 0;
    if (p_debug) THEN
    RAISE NOTICE '--########## CHANGE TYPE OWNER ##########--';
    END IF;
    FOR rtipos IN  ptipos LOOP                
        v_sql = 'ALTER TYPE ' || rtipos.nombre_tipo || ' OWNER TO ' || quote_ident(p_owner) || ';';                
        if (p_debug) THEN RAISE NOTICE '%', v_sql; END IF;
        EXECUTE v_sql;
        v_i = v_i + 1;
    END LOOP;
    if (p_debug) THEN
    RAISE NOTICE '--@@@@@@  TYPES WITH OWNER % TOTAL = % @@@@@@--', p_owner, CAST(v_i AS VARCHAR);
    END IF;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Następnie po prostu wykonuję (jeśli chcesz debugować wyjście, po prostu ustaw drugi parametr na true):

SELECT fn_setowner('demo', false);
DROP FUNCTION fn_setowner(varchar(30), boolean);

To powinno działać dla funkcji:

IFS=$'\n'
for fnc in `psql -qAt -c "SELECT  '\"' || p.proname||'\"' || '(' || pg_catalog.pg_get_function_identity_arguments(p.oid) || ')' FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid WHERE n.nspname = 'public';" YOUR_DB`
do
  psql -c "alter function $fnc owner to NEW_OWNER" YOUR_DB
done

Możesz użyć polecenia REASSIGN OWNED

Wystarczy zalogować się do bazy danych przy użyciu administratora i wykonać poniżej

REASSIGN OWNED BY [old_user] TO [new_user];

Spowoduje to zmianę wszystkich obiektów, tj. Tabel, sekwencji, funkcji itp. Należących do old_role na nową rolę. Nie musisz myśleć o tym, jakie obiekty ma użytkownik, wszystkie zostaną zmienione. To zmienia obiekty tylko wtedy, gdy chcesz zmienić własność samej bazy danychALTER DATABASE name OWNER TO new_owner

Jest to najlepsza metoda, ponieważ nie będzie n liczby tabel, sekwencja raczej będzie polegała na zapętlaniu i skryptach bash

Cóż, nie znalazłem procesu jednoetapowego, ale to zajmuje się wszystkimi obiektami, które widzę w mojej bazie danych:

update pg_class 
SET relowner = (SELECT oid FROM pg_roles WHERE rolname = 'foo')
where relnamespace = (select oid 
                      from pg_namespace 
                      where nspname = 'public' 
                      limit 1);

update pg_proc 
set proowner = (select oid from pg_roles where rolname = 'foo')
where pronamespace = (select oid 
                      from pg_namespace 
                      where nspname = 'public' 
                      limit 1);