postgres removing user from role membership

Question

How do I drop a role member? My role name tester is member of role postgres (which I do not want). How do I remove this membership?

 Role name | Attributes | Member of 
-----------+------------------------------------------------+------------
 tester | | {postgres}
 postgres | Superuser, Create role, Create DB, Replication | {}

Answer 1

With REVOKE e.g.:

REVOKE postgres FROM tester;

Answer 2

Check the existence of a member role in another role.

SELECT r.rolname as username, r1.rolname as "role", roleid, member
FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
JOIN pg_roles r1 ON (m.roleid=r1.oid)
WHERE r.rolname = 'tester' AND r1.rolname = 'postgres';

Solution

REVOKE postgres FROM tester;
-- Or also
REVOKE "postgres" FROM "tester";

Notice that 'postgres' and 'tester' does not work.

---

WARNING:

Hack in the internals is extremely bad advice

But in case the code helps you to do something else that does not have a native implementation.

DELETE FROM pg_auth_members WHERE
member = (SELECT member
 FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m
 ON (m.member = r.oid)
 JOIN pg_roles r1 ON (m.roleid=r1.oid)
 WHERE r.rolname = 'tester' AND r1.rolname = 'postgres') AND
roleid = (SELECT roleid
 FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m
 ON (m.member = r.oid)
 JOIN pg_roles r1 ON (m.roleid=r1.oid)
 WHERE r.rolname = 'tester' AND r1.rolname = 'postgres');

GL

Source

• Colin 't Hart: Why write code to hack in the internals when a simple revoke will do? This is extremely bad advice.
• Braian Coronel: REVOKE DOES NOT WORK !!! And if I want to do it at a low level, what is your problem? Also, if Postgres allows editing for a reason, that decision precedes your opinions. And if only to get the members I have to access that way as in this answer [dba.stackexchange.com/a/136869/154867], that remains for the edit.
• Colin 't Hart: Did you remember to commit after revoke?
• Braian Coronel: I found the problem, I was using 'postgres' instead of postgres or "postgres" which do work. Sorry. I will proceed to edit the answer.
• Braian Coronel: Updated answer with suggested.