13

https://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however all the suggestions seem to ignore the functions I created.

Is there a fairly easy way to reset the owner of ALL objects in the database, including the functions? Doing it by hand is highly undesirable.

migrated from stackoverflow.com Dec 24 '11 at 23:40

This question came from our site for professional and enthusiast programmers.

21

You should only ever manipulate system catalogs directly, if you know exactly what you are doing. It may have unexpected side effects. Or you can corrupt the database (or the whole database cluster) beyond repair.

Jeremy's answer, while basically doing the trick, is not advisable for the general public. It unconditionally changes all functions in a schema. Are you sure there are no system functions affected or functions installed by an additional module?
It would also be pointless to change the owner of functions that already belong to the designated owner.

First, check if REASSIGN OWNED could work for you:

change the ownership of database objects owned by a database role

You have to list all roles to be disowned explicitly. But it also reassigns functions.

To assign all functions (and no other objects) in a given schema to a new owner (optionally regardless of previous owner):

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_%'

This generates the canonical SQL commands ALTER FUNCTION ... to change all functions (in the specified schema). You can inspect the commands before executing them - one by one or all at once:

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

I included some commented WHERE clauses you might want to use to filter the results.

The cast to regprocedure produces a valid function name with parameters, double-quoted where necessary, schema - qualified where necessary for the current search_path.

The aggregate function string_agg() requires PostgreSQL 9.0 or later. In older version substitute with array_agg() and array_to_string().

You could put all of this into a DO statement or a function like demonstrated in this related answer:

In Postgres 9.5 or later, you can simplify the query using new object identifier types regnamespace and regrole:

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_%'
1

I use this function to alter the owner of tables, functions, types, etc. You can change the query of the cursors to adapt it to your needs.

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;

Then I just execute (if you want debugging output simply set the second parameter to true):

SELECT fn_setowner('demo', false);
DROP FUNCTION fn_setowner(varchar(30), boolean);
  • Note that pg_proc.proisagg is replaced in pg 11. The release notes say: Replace system table pg_proc's proisagg and proiswindow with prokind (Peter Eisentraut)` – Erwin Brandstetter May 26 '18 at 18:39
0

This should work for functions:

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
-1

You can use the REASSIGN OWNED command

Just log in to the database with superuser and execute below

REASSIGN OWNED BY [old_user] TO [new_user];

This changes all objects i.e tables, sequence,function etc owned by old_role to the new role. You don't have to think about what kind of objects that the user has, they will all be changed. This changes the objects only if u wanna change ownership of that database itself just use ALTER DATABASE name OWNER TO new_owner

This is the best method as there will n number of tables, sequence rather going for loops and bash scripts

  • 2
    This is mentioned in the answer with the most upvotes since 3 years. Also its limitations. – dezso May 27 '18 at 22:04
-7

Well, I didn't find a one-step process, but this takes care of all the objects I can see in my database:

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);
  • 5
    It's a good question (+1) - -1 for your answer though - I wouldn't want anyone else to think it is ok to directly update system tables like this without being very sure they know what they are doing. – Jack Douglas Dec 26 '11 at 16:56
  • 1
    You are asking for proof that it will not break something, and my counterargument is that if you are downvoting something, you should include an explanation of what it will break and how/ why. If you cannot, then the answer is not wrong, misleading, unuseful, or unhelpful, which are the criteria for a downvote. The relationships in the metadata tables were not difficult to figure out in this case, upon a bit of examination, and as I said, it works swimmingly. The burden of proof should be on the downvoter; I expect you will have difficulties finding what this answer will break. – Jeremy Holovacs Dec 28 '11 at 4:16
  • 1
    I will take the liberty of quoting @Erwin verbatim: "You should only ever manipulate system catalogs directly, if you know exactly what you are doing. It may have unexpected side effects. Or you can corrupt the database (or the whole database cluster) beyond repair". Erwin knows his stuff (and so do I). Check our reputation and past answers on the postgres tag here and on SO. My downvote is an expression of my opinion and I offer no proof because the docs are enough evidence for me (others can decide for themselves). – Jack Douglas Dec 28 '11 at 6:32
  • 1
  • 6
    what's wrong with using Erwin's method? The fact that you have used the method without (apparent) issue gives me no confidence and neither should it: someone might equally say I've used RAID0 for years without issue. – Jack Douglas Aug 20 '14 at 5:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.