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