0

I have a database with some tables that was created by a certain user. All tables and sequences in that DB were owned by that user. I needed to change the owner of those tables and sequences to be able to run certain operations (Django Migrations).

In doing so, I changed it to postgres by mistake:

REASSIGN OWNED BY delme TO postgres;

Now, when I attempt to correct the oversight, I get the following:

db_name=# REASSIGN OWNED BY postgres TO django_development;       
ERROR:  cannot reassign ownership of objects owned by role postgres because they are required by the database system   

How can I change ownership of that entire db's contents to django_development user?

2 Answers 2

3

The problem is, that the postgres user also owns all system catalogs and you can't reassign ownership for them.

You will need to do this manually for each table in each schema you have.

I would first do it for the schemas:

alter schema public owner to django_development;  
... repeat for all schemas

Then do it for all tables in the schemas using something like the following (not tested):

do
$$
declare
  l_rec record;
  l_sql text;
begin
  for l_rec in select schemaname, tablename
               from pg_tables
               where schemaname in ('public', .... ) --<< adjust for your schemas
                 and tableowner = 'postgres'
  loop 
    l_sql := format('alter table %I.%I owner to django_development', l_rec.schemaname, l_rec.tablename);
    raise notice 'Running %: ', l_sql;
    execute l_sql;
  end loop;
end;
$$

If you have other objects (views, functions, etc) you will need to something equivalent for them as well.

5
  • In other words, there is no way to avoid the pain in the neck? :/ Nov 8, 2020 at 18:12
  • No, there is no better way. Nov 9, 2020 at 11:14
  • the above code works once you end it as suggested by @mustaccio! I am now wondering how to adopt this to sequences? pg_sequence has no identifier for a sequence's owner, so there is missing the handle.
    – vrms
    Dec 7, 2022 at 21:51
  • @vrms every sequence also has an entry in pg_class You can change the above script to get the information from there and applying a WHERE condition on pg_class.relkind to pick the types you want.
    – user1822
    Dec 8, 2022 at 6:29
  • thx @a_horse_with_no_name. I figured out something via pg_sequences, which seems to be a view combining pg_sequence with pg_class (if I understand correctly)
    – vrms
    Dec 8, 2022 at 10:59
0

You have to connect as the postgres user to be able change the objects owner back, or if you have another superuser account that should also work too.

2
  • I do connect as postgres but still get the above scenario. Nov 8, 2020 at 16:00
  • Hmm do you have a superuser account you can connect as and try?
    – J.D.
    Nov 8, 2020 at 16:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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