Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required. 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.

share|improve this question

migrated from Dec 24 '11 at 23:40

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

Hate to say it, that only way I've done this is to pg_dump the database, edit the dump file, and import it. –  Paul Tomblin Dec 24 '11 at 19:42

2 Answers 2

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 future 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 (regardless of previous owner):

            || quote_ident(n.nspname) || '.' 
            || quote_ident(p.proname) || '(' 
            || pg_catalog.pg_get_function_identity_arguments(p.oid)
            || ') OWNER TO foo;'
          , E'\n') AS _sql
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:


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

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.

share|improve this answer
up vote -4 down vote accepted

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);
share|improve this answer
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
This is what I did and it worked. It did exactly as I wanted. Your attitude in protecting the users from themselves is unappreciated, as is your downvote for what was, in fact, the answer. –  Jeremy Holovacs Dec 27 '11 at 16:51
How do you know it didn't have some unexpected side-effect? Have you since tested your backups? Perhaps the next time you use pg_upgrade you will find it doesn't work? Unless you can add some references to your answer (eg to the postgres mailing lists) showing that this approach has no hidden problems, the downvote stays. –  Jack Douglas Dec 27 '11 at 17:19
Please note my downvote and these comments are my opinion as a user, and shouldn't be mistaken for any sort of moderation. I'm happy to discuss this further in chat if you would like to do so... –  Jack Douglas Dec 27 '11 at 17:26
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 posting your answer, you agree to the privacy policy and terms of service.

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