Getting role comments in PostgreSQL by query

Question

Postgresql allows adding comments to roles and users. For example I've added a comment to role "myuser" by using this SQL command:

COMMENT ON ROLE myuser IS 'User comment...';

My question is: If I want to use a SQL-command to get all users along with their respective comment - how would I do this? What would be the appropriate query for this?

Possible duplicate of How to get role's comment from the PostgreSQL database — Łukasz Kamiński
– Łukasz Kamiński, Commented Mar 2, 2018 at 13:01

Łukasz Kamiński · Accepted Answer · 2018-03-02 13:00:03Z

2

You need to use built-in function shobj_description(object_oid, catalog_name)

SELECT *, shobj_description(oid, 'pg_authid') AS comment
  FROM pg_roles;

answered Mar 2, 2018 at 13:00

Łukasz Kamiński

6,0401 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2018-03-02 13:00:31Z

1

SELECT u.usesysid, u.usename, d.description
FROM pg_catalog.pg_user u
   LEFT JOIN pg_catalog.pg_shdescription d
      ON d.objoid = u.usesysid
         AND classoid = 'pg_catalog.pg_authid'::regclass;

answered Mar 2, 2018 at 13:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Getting role comments in PostgreSQL by query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related