Cambiare l’owner di tutte le funzioni in un database postgresql

Talvolta può presentarsi la necessità di modificare l’owner degli oggetti presenti in uno schema del database postresql.
Di seguito un metodo per cambiare l’owner superuser postgres delle funzioni presenti in uno schema di un database postgresql utilizzando la console psql e il salvataggio  su file esterno .

Per elencare le funzioni presenti nello schema public si può utilizzare la query

SELECT p.proname, n.nspname,
       pg_catalog.pg_get_function_identity_arguments(p.oid) args
 FROM pg_catalog.pg_namespace n
 JOIN pg_catalog.pg_proc p
 ON p.pronamespace = n.oid
 WHERE n.nspname = 'public'

Posizionarsi in una directory in cui lo user postgres ha il permesso di scrittura oppure creare una directory ad hoc.

$ mkdir /home/postgres 
$ sudo su 
$ chown postgres:postgres
$ cd /home/postgres
$ su postgres

Avviare la console di postgresql e selezionare il database con lo funzioni il cui owner va modificato

$ psql
selezionare il database con le tabella da cambiare 
postgres=# \c eth2010

Per generare i comandi per la modifica dell’owner delle tabelle si può usare la query:

eth2010=# select 'alter function '||n.nspname||'.'||p.proname||'('||pg_get_function_identity_arguments(p.oid)||') owner to NUOVO_USER ;' from pg_proc p join pg_namespace n ON p.pronamespace = n.oid where n.nspname = 'public';

Il risultato della query sara del tipo

?column? 
 
 
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
 alter function public.chkpass_in(cstring) owner to NUOVO_USER;
 alter function public.chkpass_out(chkpass) owner to NUOVO_USER;
 alter function public._getsingleproductid(integer) owner to NUOVO_USER;
 alter function public._getmenu() owner to NUOVO_USER;
 alter function public.__getsetoflinks(integer, integer) owner to NUOVO_USER;
 alter function public._bblock_global(character varying) owner to NUOVO_USER;
 alter function public._catalog() owner to NUOVO_USER;
 alter function public._deletemenu(integer) owner to NUOVO_USER;
...
...
...
(217 rows)

salviamo la query nel file esterno alterfunctions_own ridirigendo l’output da terminale con

eth2010=# \o alterfunctions_own

rieseguire la query sostituendo NUOVO_USER con lo user desiderato.
Il risultato verrà salvato sul file esterno alterfunctions_own che conterrà a questo punto  un elenco di comandi alter function 

eth2010=# select 'alter function '||n.nspname||'.'||p.proname||'('||pg_get_function_identity_arguments(p.oid)||') owner to NUOVO_USER ;' from pg_proc p join pg_namespace n ON p.pronamespace = n.oid where n.nspname = 'public';

Per eseguire la lista di comandi alter function importare il file con

eth2010=# \i alterfunctions_own

psql:alterfx1_own.sql:3: ERROR: syntax error at or near "?"
LINE 1: ?column? ...
 ^
psql:alterfx1_own.sql:637: ERROR: syntax error at or near "317"
LINE 1: (217 rows)

Il file generato contiene in testa e in coda caratteri che non rappresentano comandi validi e che generano alcuni sintax error tuttavia  in genere non vale la pena di pulire il file.

Modificare l’owner delle funzioni di un database con una query plpgsql

La query cambia l’owner di tutte le funzioni del database in cui viene eseguita. Consente di precisare lo schema su cui agire in genere public  definito da:

  • in_schema = primo parametro
  • new_owner = secondo parametro
-- FUNCTION: public.__chown_functions(character varying, character varying)

-- DROP FUNCTION public.__chown_functions(character varying, character varying);

CREATE OR REPLACE FUNCTION public.__chown_functions(
	in_schema character varying,
	new_owner character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    -- ROWS 0
AS $BODY$

DECLARE
  object_types VARCHAR[];
  object_classes VARCHAR[];
  object_type record;
 
  r record;
BEGIN


  FOR r IN 
    SELECT  p.proname, n.nspname,
       pg_catalog.pg_get_function_identity_arguments(p.oid) args
    FROM    pg_catalog.pg_namespace n
    JOIN    pg_catalog.pg_proc p
    ON      p.pronamespace = n.oid
    WHERE   n.nspname = in_schema
  LOOP
    raise notice 'Changing ownership of function %.%(%) to %', 
                 r.nspname, r.proname, r.args, new_owner;
    EXECUTE 
       'alter function ' || r.nspname || '."' || r.proname ||
       '"(' || r.args || ') owner to ' || new_owner;
  END LOOP;


END;

$BODY$;

ALTER FUNCTION public.__chown_functions(character varying, character varying)
    OWNER TO postgres;

 

Risorse:

Plpgsql 

Postgresql 

Postgresql cancella record duplicati

Creare eliminare database e tabelle

Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...