Connecting to postgres

sudo -u postgres psql

I delete the user, but it does not work:

 postgres=# drop user test_user; ERROR: role "test_user" cannot be dropped because some objects depend on it DETAIL: privileges for database postgres

I give a list of all tables:

 postgres=# \dt List of relations Schema | Name | Type | Owner --------+-----------------+-------+---------- public | groupped_points | table | postgres public | point1 | table | postgres public | points | table | postgres public | polygon1 | table | postgres public | polygons | table | postgres public | spatial_ref_sys | table | postgres public | temp_points | table | postgres public | test | table | postgres (8 rows)

As you can see, no table is associated with test_user . I give a list of users:

 postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} test_user |

What could be the problem? What can test_user still be associated with ?

    2 answers 2

    because some objects depend on it

    Objects in the database - this is very, very far from just the plates. And also the bases themselves, the schema in them, rights, sequence, functions, triggers, view, tablespace, other roles and users and everything else.

    When a user is deleted , the list of dependencies in the pg_shdepend system directory is pg_shdepend . Translating to the SQL language, the following query is executed:

     select * from pg_shdepend where refclassid = 'pg_authid'::regclass and refobjid = ( select oid from pg_roles where rolname = 'someuser' );

    Actually, in a detail error message, a list is written along the way (possibly truncated, there is a limit of 100 elements in checkSharedDependencies ) of those things with which deletion conflicts. In your case, the hint was quite clearly indicated.

    DETAIL: privileges for database postgres

    That is, deleting a user is not given privileges granted in the postgres database.

    For my user, for example, are displayed:

     ERROR: role "melkij" cannot be dropped because some objects depend on it DETAIL: owner of database melkij owner of database dbname 99 objects in database melkij 6 objects in database dbname

    That is, the user is the owner of a pair of bases and has some objects there in large numbers.

    For each type of object will need their own teams to change the owner or delete. There are also two special commands:

    • DROP OWNED - which removes the corresponding objects. More careful with this, deletion after all.
    • REASSIGN OWNED - which transfers ownership of objects to some other role.

    It is important to note that both commands work at the level of only one DB. Deleting a user requires the absence of objects in all databases - that is, it is necessary to go through all databases.

    Sometimes it is easier to dump the pg_dumpall -s cluster pg_dumpall -s and look for user references in it.

      The solution is simple and trivial. It was necessary to take all rights from the user:

       REVOKE ALL ON DATABASE postgres FROM test_user; REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM test_user;

      After that we do:

       drop user test_user;

      We look at users:

       postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

      User removed.

      Remark Writing rights can be similar:

       GRANT ALL ON DATABASE postgres TO test_user; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO test_user;