Thread: DROP USER weirdness in 7.2.1

I have encountered unexpected behavior of DROP USER in 7.2.1.

One would normally expect, that when DROP USER someuser is issued, all 
associated data structures will be readjusted, especially ownership and access 
rights.

This however does not happen.

After droping an user, that had ownership of tables, pg_dump complains :

[...]
pg_dump: WARNING: owner of data type table_one appears to be invalid
pg_dump: WARNING: owner of table "some_seq" appears to be invalid
[...]

The access rights to those tables remain

database=# \z table_oneAccess privileges for database "customer"   Table    |      Access privileges       
-------------+------------------------------table_one | {=,98=arwdRxt,maria=arwdRxt}
(1 row)

There is no way to remove rights of this 'user' 98 using REVOKE etc.

Perhaps full dump/reload will remove the rights, because that user will not be 
found, but restore may fail due to the error conditions.

Any resolution for this?

Regards,
Daniel

Daniel Kalchev writes:

> One would normally expect, that when DROP USER someuser is issued, all
> associated data structures will be readjusted, especially ownership and access
> rights.

Perhaps, but the documentation states otherwise.

> There is no way to remove rights of this 'user' 98 using REVOKE etc.
>
> Perhaps full dump/reload will remove the rights, because that user will not be
> found, but restore may fail due to the error conditions.
>
> Any resolution for this?

Recreate the user with the given ID and drop the objects manually.

-- 
Peter Eisentraut   peter_e@gmx.net

>>>Peter Eisentraut said:> Daniel Kalchev writes:> > > One would normally expect, that when DROP USER someuser is
issued,all> > associated data structures will be readjusted, especially ownership and ac    cess> > rights.> > Perhaps,
butthe documentation states otherwise.> 
 
[...]> > Any resolution for this?> > Recreate the user with the given ID and drop the objects manually.

I was able to modify ownership of all tables using ALTER TABLE. However, the 
associated pg_toastXXXX tables still remain with the wrong ownership.

In my case, I had to recreate the user, because it had to have rights in a 
different database within the same postgres installation... Nevertheless, it 
would be much more convenient, if ALL rights associated with the particular 
users are dropped when the user is dropped and eventually all orphaned objects 
have their owner set to the DBA (postgres).

It is not too difficult to imagine, that in real-world database installation 
users would need to be created and dropped.

Daniel