2

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 | {}
6

With REVOKE e.g.:

REVOKE postgres FROM tester;
-3

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

5
  • 3
    Why write code to hack in the internals when a simple revoke will do? This is extremely bad advice. – Colin 't Hart Nov 7 '20 at 7:51
  • 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. – Braian Coronel Nov 7 '20 at 16:41
  • Did you remember to commit after revoke? – Colin 't Hart Nov 7 '20 at 16:42
  • 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 Nov 7 '20 at 16:48
  • Updated answer with suggested. – Braian Coronel Nov 7 '20 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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