0

How do you revoke all privileges for a user in Postgres and then drop the user?

The docs say it should be as simple as:

REVOKE ALL PRIVILEGES ON DATABASE database FROM user;
DROP USER user;

However, if I open my psql prompt, I get the bizarre output:

mydb=> REVOKE ALL PRIVILEGES ON DATABASE mydb FROM myuser;
REVOKE
mydb=> DROP USER myuser;
ERROR:  role "myuser" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema myschema

Uh, what? Ok, so I just need to explicitly revoke permissions on "myschema"? But then I get:

mydb=> REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschema FROM myuser;
REVOKE
mydb=> DROP USER myuser;
ERROR:  role "myuser" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema myschema

It's saying it's successfully revoking all database and schema access, but yet schema access remains, preventing me from dropping the user.

Even more bizarrely, if I try to confirm the user has that schema access with:

SELECT *
FROM pg_namespace
WHERE has_schema_privilege('myuser', 'myschema', 'USAGE');

it returns nothing except "pg_catalog", "information_schema", and "public".

What am I doing wrong here?

1
  • Your query against pg_namespace doesn't do what you think it does. Perhaps you should simply try revoking the schema privilege, which is what the error message suggests.
    – mustaccio
    Jan 30 at 20:00

1 Answer 1

1

The error message is not talking about privileges on tables in the schema, but about privileges on the schema itself.

REVOKE ALL ON SCHEMA myschema FROM myuser;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.