1

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?

Improve this question

2 Answers 2

Reset to default
4

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.

Improve this answer
5
  • In other words, there is no way to avoid the pain in the neck? :/ Commented Nov 8, 2020 at 18:12
  • No, there is no better way. Commented 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. Commented 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. Commented 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) Commented 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.

Improve this answer
2
  • I do connect as postgres but still get the above scenario. Commented Nov 8, 2020 at 16:00
  • Hmm do you have a superuser account you can connect as and try? Commented 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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.