PostgreSQL user listing
I want to get a list of users for a certain database in psql — for example «template0». Who are the users? Or for «template1» database: — who are the users there? Already tried:
\du+ -- no database is Listed not Users Select * from "pg_users"; -- no database is listed 3 Answers 3
User aren’t actually, «for the database», they’re for cluster and are given different permissions to access databases. To list users \du should do, but you need to be connected. Something like
from the command line prompt should do. (or \du from psql prompt when you are connected to the database).
You must understand that in PostgreSQL users are per database cluster. @Michael already demonstrates how to get a list of those.
So, unless you restrict permissions for a particular databases explicitly with REVOKE and GRANT , all users in the cluster have basic access to any database in the cluster.
To determine, whether a specific user actually has a certain privilege (‘CONNECT’) for a database:
has_database_privilege(user, database, privilege) To determine all specific privileges for a specific database:
SELECT datname, datacl FROM pg_database WHERE datname = 'mydb'; You get NULL for datacl if no specific restrictions apply.
In addition to that you can restrict access per database and per user in the pg_hba.conf file. That’s on a lower level. The user cannot even connect, if pg_hba.conf won’t let him, even if the database itself would allow access.
PostgreSQL List Users
Summary: in this tutorial, you will learn how to use the PostgreSQL list user command to show all users in a PostgreSQL database server.
Listing users using the psql tool
It will prompt you for a password:
Password:Code language: Shell Session (shell)Once you enter the password for the postgres user, you will see the following PostgreSQL command prompt:
postgres=#Code language: Shell Session (shell)Second, use the \du to list all user accounts (or roles) in the current PostgreSQL database server:
postgres=# \du Code language: Shell Session (shell)
If you want to show more information, you can use the \du+ command:
postgres=#\du+Code language: Shell Session (shell)The \du+ command adds an additional column called description .
Listing users using SQL statement
The following statement returns all users in the current database server by querying data from the pg_catalog.pg_user catalog:
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;Code language: SQL (Structured Query Language) (sql)
Summary
- Use \du or \du+ psql command to list all users in the current database server.
- Use the SELECT statement to query the user information from the pg_catalog.pg_user catalog.
PostgreSQL List Users
Everything has its uses in any way possible. Like that, the PostgreSQL database also has users who own it and can manage, edit, update, and perform many functions on it with the help of queries. The PostgreSQL shell has a “psql” command shell utility and pgAdmin GUI utility to list its existing users in just a few clicks and commands. In this article, we will be discussing all those simple commands to list all existing, non-existing users with their information.
Using PostgreSQL Shell:
Let’s make a fresh start by listing the PostgreSQL users in the PostgreSQL shell. So, tap on the search area, write “psql”, and click on the shown “MySQL” shell application to quickly launch it. The shell will appear on your screen, asking for your local hostname. Write your server name add the name of a database existing in your SQL. Use the port number “5432” and any user with its username. We have been using the “aqsayasin” username and adding its password.
To list all the current existing users in your PostgreSQL shell is quite easy. We have to utilize the simple “\du” command. It will list all the current users of your PostgreSQL database on your shell screen, as demonstrated. This “\du” command cannot be used at the PostgreSQL PgAdmin GUI interface. The output shows a table with usernames listed as “Role name” with many of their properties.
We have 2 users currently existing in our database, i.e. aqsayasin and Postgres. Both the users contain the same privileges like superuser, create role, Create Db, Replication. The “Postgres” database also contains the Bypass RLS rights. None of the users belongs to any group as per the “Member of” column is empty in the below table.
We have seen a way to list all the current existing users in our PostgreSQL shell. Now, we will see how a single user, along with its role attributes, can be listed in the PostgreSQL Shell. For this, you need to use the same “\du” three-character command along with the name of a specific user. In this illustration, we have been fetching the record for user “aqsayasin” as shown. It shows its privileges without showing any other useful details.
The same query of “\du” along with the name of a user can be applied for other users, i.e. “Postgres” as shown in the attached black-screen photo.
Let’s search for the user that is not existing in our database right now and see how the “\du” command behaves in the PostgreSQL shell. For instance, we have been searching for the username “john” using the “\du” command in the shell. After executing this command, we have shown the empty “List of roles” table as an output below. This means the listing of the specific user that is not available in our database can never lead you to an error.
Let’s say you want to see the description of all the users you have in your database using the same “\du” command. For this, you need to utilize the “+” sign in concatenated with the “\du” command within the shell query area. You can see we have tried this command and got another column named “Description” within the “List of Roles” table for users. Although the “Description” column is empty, it implies that at the time of creating users, we haven’t added any description for our users. But we can do that as well.
This was all about the most basic information regarding the database users we have in our database. PostgreSQL came up with another command that can list a lot more than this basic information regarding users. That command is “Select” instruction using the “pg_catalog.pg_user” default table. Using the SELECT instruction with “*” specifying the name of a default standard table for users can help you to list all the other information regarding users that you cannot get with the “\du” command. So, we have tried this command in our PostgreSQL shell as shown in the image and got the shown result. It returns many columns along with user information. The “usesysid” column shows the ID for all the existing users. All the values like “t” and “f” stand for true and false for specific user rights beneath the columns “usecreatedb”, “usesuper”, “userepl”, and “usebypassrls” i.e. if the user has those rights or not. The “passwd” column shows the encrypted password for users. Another two columns are empty, i.e. “valuntil” and “useconfig”.
There is another quite easy way to list all the users in the PostgreSQL shell. This method uses a simple list command via the “\l” 2 character command. After using this command, we have seen that it displays the usernames beneath the column “Name”, the Owner database the users belong to, the user encoding type, Collate column, and Ctype column to show some extravagant information. The last column is the “Access privileges” command to show us the path to get the information regarding the specific user rights from our system database.
Using PgAdmin PostgreSQL GUI:
We can also use the PostgreSQL pgAdmin GUI to list the users from our database. But, we will start with the creation of a new user first. Therefore, expand the database tab from the left corner of pgAdmin GUI, and you will see the option “Login/Group Roles”. Right-click on this option, hover over the “Create” option, and tap on the “Login/Group Role…” option.
A screen named “Create – Login/Group Role” will be open on your pgAdmin screen, as shown. Within its “General” tab, you have to add the name of a user you want to create within the “Name” text bar and add comments or descriptions about this user as demonstrated.
Below its “Definition” tab, add the password for this particular user, the expiry date for this user account, and its connection limit. We have been using the default expiration, i.e. No expiry for a particular user and connection limit to “-1” i.e. you can log in as much time as you want.
When it comes to the Privileges tab, we have many rights to assign to this particular user. Tap on the sliding button in front of each option to enable it. We have assigned almost all the rights to test users.
Within the SQL section, you can see the query that has been auto-generated for this particular user creation, i.e. test. Tap on the Save button to finally create this user.
Refresh the “Login/Group Roles” tab from the left corner, and you will get the list of all existing users in your database, including “test”, “aqsyasin”, and “Postgres” as beneath.
To list all the users using the query, you have to use the same “SELECT” instruction using the “pg_catalog.pg_user” keyword on the query area. The list of users has been displayed.
Conclusion:
An immense amount of hard work has been added to this article to demonstrate how to list all the PostgreSQL users in it. We have tried this topic within the PostgreSQL “psql” shell and PostgreSQL GUI “pgAdmin”. We have tried the “\du” command, SELECT instruction with the “pg_catalog.pg_user” database table, and the simple “\l” list command to display the users, their privileges, and official information.
About the author
Aqsa Yasin
I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.
POSTGRESQL — СПИСОК ПОЛЬЗОВАТЕЛЕЙ
В сегодняшней статье мы поговорим о том как можно посмотреть всех пользователей в базе данных PostgreSQL.
В PostgreSQL есть системная таблица по имени pg_user которая хранит в себе всю информацию о пользователях. Вы можете выполнить запрос к этой таблице которая возвращает всех пользователей, которые были созданы в PostgreSQL, а также информацию об этих пользователях.
- usename — Имя пользователя.
- usesysid — Идентификатор пользователя.
- usecreatedb — Может ли пользователь создавать базы данных.
- usesuper — Имеет ли пользователь права суперпользователя.
- userepl — Может ли пользователь инициировать потоковую репликацию, включать и отключать режим резервного копирования.
- usebypassrls — Подчиняется ли пользователь к каким либо политикам защиты на уровне строк.
- passwd — Пароль пользователя в виде звездочек.
- valuntil — Срок действия пароля.
- useconfig — Значения по умолчанию для переменных конфигурации во время выполнения.
Значения полей f значит false, а t значит true.
Всем спасибо, я надеюсь что вам моя статья хоть чем-то помогла.
Последнее обновление: 20 Июня 2023 г.