PostgresDB 01006: Privilege Not Revoked

What is PostgresDB 01006: Privilege Not Revoked

When a user encounters the message <Cell R15C1 '01006: Privilege Not Revoked'> from PostgresDB, the recommended action involves investigating why the privilege revocation failed, as this message indicates an attempt to revoke one or more privileges from a user, role, or group that was unsuccessful. Here are the steps a user should take:

Identify the Privilege and Role/User: First, understand which privilege was attempted to be revoked and from which user or role. Use the query:SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='your_table_name';Check Current Privileges: Verify the current privileges of the user or role to understand if the privilege was indeed not revoked or if there was another issue. You can check privileges by running:\dp your_table_nameor for a more detailed view:SELECT * FROM information_schema.table_privileges WHERE grantee='your_role_or_user';Review Dependency Objects: Sometimes, privileges cannot be revoked because they are required by other database objects or roles that depend on them. Check for any dependencies that might prevent the privilege from being revoked:SELECT dependent_obj.*FROM pg_dependJOIN pg_class dependent_obj ON pg_depend.objid = dependent_obj.oidJOIN pg_authid ON pg_depend.refobjid = pg_authid.oidWHERE pg_authid.rolname = 'role_name';Attempt Revocation Again: If the privilege is still present and no dependencies are blocking its revocation, try to revoke the privilege again using the correct syntax, ensuring to specify the right database, table, and privilege:REVOKE SELECT ON your_table_name FROM your_role_or_user;Check for System Errors or Logs: If the privilege still appears not to be revoked, check the PostgreSQL logs for any system errors or warnings that might indicate why the revocation failed. This can provide clues to underlying issues:tail -f /var/log/postgresql/postgresql-xx-main.logConsult Documentation or Community: If you're unable to resolve the issue, consult the PostgreSQL documentation or community forums for additional insights. Sometimes, specific versions of PostgreSQL might have nuances that are addressed in documentation or have been encountered by others in the community.Review Access Control Lists (ACLs): As a last resort, directly examine and edit the Access Control Lists (ACLs) for the database object in question. This is an advanced action and should be done with caution:SELECT relacl FROM pg_class WHERE relname = 'your_table_name';This will show the ACLs for the table, and you may manually adjust them if necessary, although this is not recommended without a thorough understanding of PostgreSQL ACLs.

Remember, direct manipulation of system catalogs or ACLs should be done with extreme caution and is generally not recommended unless you have a deep understanding of PostgreSQL internals.