CODEWITHSUNDEEP
pythonpostgresqlroles
Aravind
Aravind

Reputation: 23

get roles assigned to user in postgresql

I have a postgreSQl database. I have created few users of the database and assigned certain roles to each user. In my python program I need to get the users of the database along with their corresponding roles. How to get this information?

Upvotes: 1

Views: 4169

Answers (1)

mhawke
mhawke

Reputation: 87084

You can perform queries against the system catalogs. Try looking in the pg_user and pg_roles tables. Their schemas are:

test=# \d pg_user
     View "pg_catalog.pg_user"
    Column    |  Type   | Modifiers 
--------------+---------+-----------
 usename      | name    | 
 usesysid     | oid     | 
 usecreatedb  | boolean | 
 usesuper     | boolean | 
 userepl      | boolean | 
 usebypassrls | boolean | 
 passwd       | text    | 
 valuntil     | abstime | 
 useconfig    | text[]  | 

test=# \d pg_roles
              View "pg_catalog.pg_roles"
     Column     |           Type           | Modifiers 
----------------+--------------------------+-----------
 rolname        | name                     | 
 rolsuper       | boolean                  | 
 rolinherit     | boolean                  | 
 rolcreaterole  | boolean                  | 
 rolcreatedb    | boolean                  | 
 rolcanlogin    | boolean                  | 
 rolreplication | boolean                  | 
 rolconnlimit   | integer                  | 
 rolpassword    | text                     | 
 rolvaliduntil  | timestamp with time zone | 
 rolbypassrls   | boolean                  | 
 rolconfig      | text[]                   | 
 oid            | oid                      |

Assuming that you are using psycopg2 to connect to the database with Python you can execute queries against the system catalog tables:

import psycopg2

conn = psycopg2.connect(database="test", user='postgres')
cur = conn.cursor()
cur.execute('select * from pg_catalog.pg_user')
for row in cur.fetchall():
    print(row)

cur.execute('select * from pg_catalog.pg_roles')
for row in cur.fetchall():
    print(row)

Upvotes: 1

Related Questions