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

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.

2
  • In other words, there is no way to avoid the pain in the neck? :/ – MadPhysicist Nov 8 '20 at 18:12
  • No, there is no better way. – Laurenz Albe Nov 9 '20 at 11:14
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. – MadPhysicist Nov 8 '20 at 16:00
  • Hmm do you have a superuser account you can connect as and try? – J.D. Nov 8 '20 at 16:52

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.