Postgresql – Remove persistent privileges on table

permissionspostgresqlpostgresql-9.2

I'm trying to remove the role my_schema-writers. I can't because it owns objects:

ERROR:  role "my_schema-writers" cannot be dropped because some objects depend on it
DETAIL:  privileges for table your_schema.your_table
1101 objects in database *******
1 object in database *******
1 object in database *******
1 object in database *******
1 object in database *******

Sure enough, it has privileges:

my_database=# \dp your_schema.your_table

                                                  Access privileges
┌─────────────┬───────────────────┬───────┬────────────────────────────────────────────────────────────┬───────────────────┐
│   Schema    │       Name        │ Type  │                  Access privileges                         │ Column privileges │
├─────────────┼───────────────────┼───────┼────────────────────────────────────────────────────────────┼───────────────────┤
│ your_schema │     your_table    │ table │ "your_schema-writers"=arwdDxt/"your_schema-writers"       ↵│                   │
│             │                   │       │ "***-writers"=arwdDxt/"your_schema-writers"               ↵│                   │
│             │                   │       │ "server-superusers"=a*r*w*d*D*x*t*/"your_schema-writers"  ↵│                   │
│             │                   │       │ "server-writers"=arwdDxt/"server-superusers"              ↵│                   │
│             │                   │       │ =r/"server-superusers"                                    ↵│                   │
│             │                   │       │ "my_schema-writers"=rx/"server-superusers"                 │                   │
└─────────────┴───────────────────┴───────┴────────────────────────────────────────────────────────────┴───────────────────┘
(1 row)

That is, role my_schema-writers has privileges on your_schema.your_table.

I can revoke all privileges:

my_database=# REVOKE ALL ON TABLE your_schema.your_table FROM "my_schema-writers";
Time: 1.884 ms

But the privileges persist:

my_database=# \dp your_schema.your_table

                                                  Access privileges
┌─────────────┬───────────────────┬───────┬────────────────────────────────────────────────────────────┬───────────────────┐
│   Schema    │       Name        │ Type  │                  Access privileges                         │ Column privileges │
├─────────────┼───────────────────┼───────┼────────────────────────────────────────────────────────────┼───────────────────┤
│ your_schema │     your_table    │ table │ "your_schema-writers"=arwdDxt/"your_schema-writers"       ↵│                   │
│             │                   │       │ "***-writers"=arwdDxt/"your_schema-writers"               ↵│                   │
│             │                   │       │ "server-superusers"=a*r*w*d*D*x*t*/"your_schema-writers"  ↵│                   │
│             │                   │       │ "server-writers"=arwdDxt/"server-superusers"              ↵│                   │
│             │                   │       │ =r/"server-superusers"                                    ↵│                   │
│             │                   │       │ "my_schema-writers"=rx/"server-superusers"                 │                   │
└─────────────┴───────────────────┴───────┴────────────────────────────────────────────────────────────┴───────────────────┘
(1 row)

And I still can't remove the role:

ERROR:  role "my_schema-writers" cannot be dropped because some objects depend on it
DETAIL:  privileges for table your_schema.your_table
1101 objects in database *******
1 object in database *******
1 object in database *******
1 object in database *******
1 object in database *******

my_schema-writers is not a member any other role and has no member roles:

rpm_2016_02=# \du "my_schema-writers"
               List of roles
┌───────────────────┬──────────────┬───────────┐
│    Role name      │  Attributes  │ Member of │
├───────────────────┼──────────────┼───────────┤
│ my_schema-writers │ Cannot login │ {}        │
└───────────────────┴──────────────┴───────────┘

How do I remove these privileges and the role? I'm able to remove the 1101 objects and the other 4 individual 1 object... is the same table in backup databases.

I also tried DROP OWNED BY "my_schema-writers"; with no error, but still not able to drop the role.

Best Answer

I also tried DROP OWNED BY "my_schema-writers"; ...

DROP OWNED is the right tool. Plus, REASSIGN OWNED first if the role might own anything. But the manual instructs:

DROP OWNED is often used to prepare for the removal of one or more roles. Because DROP OWNED only affects the objects in the current database, it is usually necessary to execute this command in each database that contains objects owned by a role that is to be removed.

Bold emphasis mine. So repeat this in every involved database:

REASSIGN OWNED BY "my_schema-writers" TO postgres;  -- or any target role of your choice
DROP OWNED BY "my_schema-writers";

Of course, you need privileges on source and target roles. Executing this as superuser avoids all "permission denied" errors.

Then try again:

DROP ROLE "my_schema-writers";

Closely related answer with more details:

Why didn't REVOKE ALL work as expected?

You ran:

my_database=# REVOKE ALL ON TABLE your_schema.your_table FROM "my_schema-writers";

You haven't been too forthcoming, but the # in a psql prompt my_database=# would normally indicate a superuser as session user.

The manual:

A user can only revoke privileges that were granted directly by that user. If, for example, user A has granted a privilege with grant option to user B, and user B has in turn granted it to user C, then user A cannot revoke the privilege directly from C. Instead, user A could revoke the grant option from user B and use the CASCADE option so that the privilege is in turn revoked from user C. For another example, if both A and B have granted the same privilege to C, A can revoke their own grant but not B's grant, so C will still effectively have the privilege.

[...]

If a superuser chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object. Since all privileges ultimately come from the object owner (possibly indirectly via chains of grant options), it is possible for a superuser to revoke all privileges, but this might require use of CASCADE as stated above.

Privileges might have persisted this way. If so, repeating with CASCADE should remove them. But I wouldn't bother. REASSIGN OWNED / DROP OWNED is the way to go.

A table of the same name in another DB (like you mentioned in a comment) has no bearing on this for Postgres - as long as you are not logged into the wrong DB by mistake.

Aside:
Postgres 9.2 is too old by now (reached EOL in 2017). Consider updating to a current version at your earliest opportunity. Don't expect it to have any part in this, but I am not sure. It's just too old.

Related Question