Introduction to PostgreSQL List Users
In any database, we need to create and have multiple users who can access the database. Each user should have certain access privileges and should be authorized, the user. By default, in PostgreSQL database server has a default user named Postgres. This is the superuser who has all the rights and privileges granted for all the databases and tables. He can itself create new users and assign roles to them, grant and revoke permissions to other users and so on. Often, the superuser or the database administrator needs to know about all the users present in the current database server. In this article, we will learn how we can list out all the users that are present in our current database server. Let us begin by studying where the user-related data is stored and manipulated in the database.
PostgreSQL List Users
Below are the three different List Users:
1. User Data in PostgreSQL
All the user-related data is stored in the table named pg_user, which belongs to the schema named pg_catalog. This table consists of all the information such as username, usesysid, usecreatedb, usesuper, userepl, usebypassrls, passwd, valuntil, and useconfig. Username, usesysid, and passwd are the name, id, and password stored in the md5 hash encryption system of the user. Simultaneously, all other parameters are the boolean parameters that specify whether the user has that privilege, such as usesuper, which specifies whether that user is a superuser or not. If yes, then it contains value as t that stands for true else f that means false. Let us see what all the other fields retrieved mean in practicality.
- Usecreatedb: This field tells us whether that particular user can create a new database and has the privilege to do so.
- Userepl: We can create replications of a single table in many other databases. Only a specific user who has that privilege can do so. Hence, if the user has the privilege to create new replicas, this field is set to true.
- Usebypassrls: This field is set to true only if the user is allowed to bypass the RLS
We will first see how we can retrieve the user-related information from the pg_user table. We will need to build our query in the following way –
Code:
SELECT * FROM pg_catalog.pg_user;
Output:
If you only want to get the list of users containing its names, you can fire the following query.
Code:
SELECT usename FROM pg_catalog.pg_user;
Output:
2. Using MetaCommands
PostgreSQL provides us with a wonderful utility named psql, where we can use metaCommands. MetaCommands are the short ready-made utilities that are available to use, making the work of database administrator very easy. For retrieving the user-related data \, du is the meta-command that can be used. For this, you will need to use the psql command prompt in PostgreSQL. Let us fire this command and see that is the result we are getting:
Code:
\du
Output:
- Here, Role Name is the name of the user. List of roles, i.e. attributes that are the roles which that a particular user has for himself. Superuser, Create role, Create DB, Replication, Bypass RLS are rules assigned to the superuser that means the superuser can create the new roles and users, create new databases, perform replications of the existing objects and bypass the RLS.
- “Member of” field specifies whether the user is a member of any group. In PostgreSQL, the database administrator can create multiple groups and add different users to different groups, which helps him to manage the users properly while grating and revoking permissions. If there are any such groups and that particular user belongs to any particular group, then the name of that group is shown in front of that user in the field Member Of column.
- This metaCommands internally fires a query on the pg_catalog.pg_user table to retrieve the user information. Here, we get the role name as the name of the user, and a list of roles attributes help us to know which privileges are granted to that particular user while a member of the field tells us if the user is a member of any particular group if present. The query which internally gets fire after using \du metacommand is somewhat similar to the below query statement:
Code:
SELECT u.usename AS "Role name",
CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('Create DB, Superuser' AS pg_catalog.text)
WHEN u.usesuper THEN CAST('Superuser' AS pg_catalog.text) WHEN u.usecreatedb THEN CAST('Create DB' AS pg_catalog.text)
ELSE CAST('' AS pg_catalog.text) END AS "Attributes"
FROM pg_catalog.pg_user u ORDER BY 1;
3. Using pg_shadow
One more way to get the list of the users is by using the table pg_shadow that also contains information about the users in the PostgreSQL server. This table is kept for backward compatibility for the versions of PostgreSQL that are previous to 8.1. This table stores the roles that can log in and flag rolcanlogin as 1 in the pg_authid table. Pg_user is the view created on the base table pg_shadow table, which is available publicly. pg_shadow is only accessible by the superuser. pg_user view contains the password field as blank to maintain security. The pg_ shadow table contains username, usesysid, usecreatedb, usesuper, userepl, usebypassrls, passwd, valuntil and useconfig fields for all the users in the database server.
Code:
SELECT * FROM pg_shadow;
Output:
select usesysid as "Id of User", usename as "User Name",
usesuper as "Whether User Is SuperUser", passwd as "Md5 Encrypted Password", valuntil as "Expiry of Password"
from pg_shadow order by usename;
Output:
Conclusion
In the PostgreSQL database server, we can retrieve the information of the user by either retrieving the records from the table pg_user that belongs to pg_catalog schema. One more way is to use the meta-command \du, which internally fires the query on the pg_user table itself. Other than this, there is one more table named pg_shadow, which stores information about the users and can be further used to list all the users in the PostgreSQL database server. Note that the scope of all these methods is the whole database server.
Recommended Articles
This is a guide to PostgreSQL List Users. Here we discuss user data, user metaCommand, and user pg_shadow with examples to implement. You can also go through our other related articles to learn more –
