Postgresql – Changing Table Ownership in Postgres from Postgres User

permissionspostgresql

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?

Best Answer

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.

Related Question