How can we get access to all the users and their respective role based acces to the database from Az-CodePudding

I want to see all the current users and their roles of a database in Azure, I need a SQL query to get that list of users and their corresponding roles.

CodePudding user response：

You can run this query to get the list of users and roles in your PostgreSQL database:

SELECT usename, usesuper, usecreatedb 
FROM pg_catalog.pg_user
ORDER BY role_name desc;

CodePudding user response：

I advise you this request to see all the users of your database:

postgres-# \du

if you want more information from your users you can use

postgres-# \du

i advise you this request if you have more difficulties

SELECT usename AS role_name, CASE WHEN usesuper AND usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text) WHEN usesuper THEN CAST('superuser' AS pg_catalog.text) WHEN usecreatedb THEN CAST('create database' AS pg_catalog.text) ELSE CAST('' AS pg_catalog.text) END role_attributes FROM pg_catalog.pg_user ORDER BY role_name desc;

I advise you to show this doc: https://ubiq.co/database-blog/how-to-list-all-users-in-postgresql/#:~:text=Using psql command,-Log into PostgreSQL&text=Enter password to log into PostgreSQL.&text=Enter \du command to list all users in PostrgeSQL.&text=You will see the list,user, enter \du+ command.