Listing all users and roles in PostgreSQL
PostgreSQL: Show Users in a Database
In PostgreSQL, users (also called roles) have specific privileges and permissions. Displaying a list of all users is often necessary for database administration, to review access control, and manage permissions across the system. This guide covers how to list all users in PostgreSQL, detailing the syntax and useful examples.
Syntax:
To show all users in a PostgreSQL database, you can use the following queries:
1. Using \du Command in psql
The \du command is a quick way to display all users and their roles directly from the psql command-line interface.
\du
2. Querying the pg_roles System Catalog
Another method is to query the pg_roles system catalog, which stores information about all roles (users and groups) in PostgreSQL.
SELECT rolname FROM pg_roles;
Example 1: Displaying Users with the \du Command
Code:
-- Lists all users and roles in the PostgreSQL database with associated attributes
\du
Explanation:
- This command, when executed in the psql interface, shows all users along with attributes like whether they have superuser status, login privileges, and more. The \du output will also indicate if a user is a superuser, inherits privileges, can create databases, and other permissions.
Example 2: Displaying Users with a Query on pg_roles
Code:
-- Retrieves a list of all usernames (roles) from the pg_roles system catalog
SELECT rolname FROM pg_roles;
Explanation:
- The pg_roles system catalog contains role-related information. By querying the rolname column, you get a simple list of all user names in the database.
Example 3: Showing Additional Details for Users
Code:
-- Retrieves user details, including superuser and login privileges
SELECT
rolname, -- Username
rolsuper, -- Superuser status (true/false)
rolcreaterole, -- Role creation privileges (true/false)
rolcreatedb, -- Database creation privileges (true/false)
rolcanlogin -- Login capability (true/false)
FROM pg_roles;
Explanation:
- This query provides a detailed view of each user's permissions, such as whether they are a superuser, can create roles, create databases, or log in. This detailed output is useful for auditing and managing user permissions in PostgreSQL.
Important Notes:
1. Difference Between Roles and Users:
- In PostgreSQL, a user is essentially a role with login privileges. All roles are stored in pg_roles, whether they have login capabilities or not. To see only roles that can log in, you can add a WHERE rolcanlogin = true filter.
- To modify user permissions, you can use the ALTER ROLE command. For example:
Code:
ALTER ROLE username WITH CREATEDB;
This command grants the user username permission to create databases.
3. Superuser Privileges:
- Users with superuser privileges have full control over the PostgreSQL instance, so it's crucial to monitor and limit superuser access as much as possible.
All PostgreSQL Questions, Answers, and Code Snippets Collection.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/PostgreSQL/snippets/postgresql-show-users.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics