PostgreSQL list users – Helpful Guide

In this PostgreSQL tutorial, we are going to learn about PostgreSQL List Users. Here we will learn how to list users available in PostgreSQL, and we will also cover the following list of topics.

• Postgresql list users
• Postgresql list users and permissions
• Postgresql list users in group
• Postgresql list users and passwords
• Postgresql list users connected
• Postgresql list users privileges
• Postgresql list user defined functions
• Postgresql list user mappings
• Postgresql list user defined types
• Postgresql list user functions

Postgresql list users

In this section, we will study the usage of the PostgreSQL list user command to show all users in a PostgreSQL database server. So, there are two methods with which we can list all user accounts (or roles) in the current PostgreSQL database server. One is \du command and another one is \du+. Let’s implement it with the help of the psql tool.

Now, we will check it with the help of the \du+ command. The \du+ command adds an extra column known as description. This command is generally used when we want to display more information. Let’s see its implementation.

We can also list users using a SQL statement. The statement shown below 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;

Let’s see the output for list users using this SQL statement.

Read: How to backup PostgreSQL database

Postgresql list users and permission

There are few scripts in Postgres databases that help us manage our users. The simplest way to list users is to run the command which we have already discussed i.e. \du and it might not give us detailed information.

But, with the help of the following script, we can see which databases the users have permission to connect to. Let’s check it by implementing the statement.

select pgu.usename as user_name,
       (select string_agg(pgd.datname, ',' order by pgd.datname) 
        from pg_database pgd 
        where has_database_privilege(pgu.usename, pgd.datname, 'CONNECT')) as database_name
from pg_user pgu
order by pgu.usename;

Let’s check the output for the same.

With the help of the below script, we can also list by database basis.

select pgd.datname as database_name,
       (select string_agg(pgu.usename, ',' order by pgu.usename) 
        from pg_user pgu 
        where has_database_privilege(pgu.usename, pgd.datname, 'CONNECT')) as user_name
from pg_database pgd
order by pgd.datname;

Let’s check the output for the same.

In the above script, we can also query other permissions by typing one of the following permission types instead of connecting. i.e. SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, TEMPORARY or TEMP, EXECUTE, and USAGE.

We can check the details of the user permissions in the database with the help of the below script.

SELECT grantee
      ,table_catalog
      ,table_schema
      ,table_name
      ,string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges
FROM information_schema.role_table_grants
WHERE grantee != 'postgres'
GROUP BY grantee, table_catalog, table_schema, table_name;

Let’s check the implementation for the same.

And if we want, we can revoke all the privileges from a user with the command given below.

REVOKE ALL PRIVILEGES ON DATABASE database_name FROM user_name;

Let’s check the output for the same.

Read: PostgreSQL Export Table to CSV

PostgreSQL list users connected

Let’s check how to check who is connected to our PostgreSQL database. The code for connecting users is given below.

select pid as process_id, 
       usename as username, 
       datname as database_name, 
       client_addr as client_address, 
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity;

Let’s check the output for the above query.

Columns

• process_id: This is the process ID of this backend
• username: This is the name of the user logged into this backend
• database_name: This is the name of the database this backend is connected to
• client_address: This is for the IP address of the client connected to this backend
• application_name: This is the name of the application that is connected to this backend
• backend_start: This is the time when this process was started. For client backends, it’s the time the client when connected to the server.
• state: Current overall state of this backend. Possible values are: active, idle, idle in transaction, idle in transaction (aborted), fastpath function call, disabled.
• state_change: It is the time when the state was last changed.

Rows

• One row: Represents one active connection
• Scope of rows: All active connections

Read: PostgreSQL drop all tables

Postgresql list users privileges

This will tell us how to make a query to the Postgres data dictionary to find out all the privileges that a particular user has. Let’s see the code for this and then its implementation. The below code is to check table permissions.

select * from information_schema.role_table_grants 
where grantee='postgres';

Let’s check the output for the same.

And to check the ownership, we will use the below code.

select * from pg_tables 
where tableowner = 'postgres';

Let’s check the output for the same.

For checking schema permissions, we will use the following code.

select  
  r.usename as grantor, e.usename as grantee, nspname, privilege_type, is_grantable
from pg_namespace
join lateral (
  SELECT
    *
  from
    aclexplode(nspacl) as x
) a on true
join pg_user e on a.grantee = e.usesysid
join pg_user r on a.grantor = r.usesysid 
 where e.usename = 'postgres'
;

Let’s check the output for the same.

Also, Learn: Postgres RegEx – Complete tutorial

Postgresql list user defined functions

Let’s study see how to get a list of user-defined functions in the PostgreSQL database. We can get the list function by using the following ways:

• Querying against pg_catalog.
• Using meta-command.

Querying against pg_catalog: We have to go to the database where we want to check the list of functions.

\c mycars

Now, run the below query.

SELECT   quote_ident(n.nspname) as schema , quote_ident(p.proname) as function 
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace 
WHERE  n.nspname not like 'pg%' and n.nspname not like 'information_schema%';

Let’s check the output.

Using meta-command: Now, again we have to go to the database where we want to check the list of functions.

\c mycars

Now, run the below query

\df

Let’s check the output.

Read: PostgreSQL DATE_PART() Function

Postgresql list user mapping

CREATE USER MAPPING explains a mapping of a user to a foreign server. A user mapping generally encapsulates connection data that a foreign-data wrapper uses besides the data encapsulated by a foreign server to access an external data resource.

The owner of a foreign server will make user mappings for that particular server for any user. In addition, a user will create a user mapping for his or her own user name if USAGE privilege on the server has been allowed to the user.

The syntax to create user mapping is given below.

CREATE USER MAPPING [ IF NOT EXISTS ] FOR { user_name | USER | CURRENT_ROLE | CURRENT_USER | PUBLIC }
    SERVER server_name
    [ OPTIONS ( option 'value' [ , ... ] ) ]

Basically, view pg_user_mappings gives access to information about user mappings. This is often primarily a publicly readable view of pg_user_mapping that leaves out the choices field if the user has no rights to use it. Let’s check how to implement it.

select * from pg_user_mappings;

But, there are more ways to list the user mapping. Let’s see the command for that also.

postgres=# \deu
OR
postgres=# \deu+

Read: PostgreSQL DROP TABLE

Postgresql list user defined types

Postgresql permits us to create user-defined types through two statements. One is CREATE TYPE that creates a composite type used in stored procedures because of the data types of returned values. Another is CREATE DOMAIN which creates a user-defined data type with constraints like NOT NULL, CHECK, etc.

CREATE TYPE: This statement will allow us to create a composite type that can be used as the return type of a function.

If we use the psql tool, we can list all user-defined types in the current database using the \dt or \dt+ command. Let’s understand it with its implementation.

CREATE DOMAIN: In PostgreSQL, a domain could be a data type with optional constraints e.g., NOT NULL and CHECK. A domain has a distinctive name within the schema scope.

Domains are functional for centralizing the management of fields with common constraints. Like some tables might have the same column that doesn’t accept NULL and spaces. For getting domain information or to get all domains in a specific schema, you use the following query. To list all domains in the current database, we use the \dD command. Let’s understand its implementation.

SELECT typname 
FROM pg_catalog.pg_type 
  JOIN pg_catalog.pg_namespace 
  	ON pg_namespace.oid = pg_type.typnamespace 
WHERE 
	typtype = 'd' and nspname = '<schema_name>';

The following statement returns domains in the ‘public‘ schema of the current database which will generate the following output.

SELECT typname 
FROM pg_catalog.pg_type 
  JOIN pg_catalog.pg_namespace 
  	ON pg_namespace.oid = pg_type.typnamespace 
WHERE 
	typtype = 'd' and nspname = 'public';

Let’s check the implementation for the same.

Read: PostgreSQL INSERT Multiple Rows

Postgresql list users in group

To check the list of users in the group we will implement with the help of the query given below. This will give us usernames as well as the respective groups.

SELECT usename, groname 
FROM pg_user, pg_group
WHERE pg_user.usesysid = ANY(pg_group.grolist)
AND pg_group.groname in (SELECT DISTINCT pg_group.groname from pg_group);

Let’s check the output for the same.

Also, read some of our latest PostgreSQL articles.

• Postgresql function return table
• PostgreSQL Update
• Postgresql Concat + Examples
• PostgreSQL Date Difference 
• Create a stored procedure in PostgreSQL
• PostgreSQL list databases

In this tutorial, we have learned about PostgreSQL List Users. Here we have also learned how to list users available in PostgreSQL. And we have also covered the following list of topics.

• Postgresql list users
• Postgresql list users and permissions
• Postgresql list users in group
• Postgresql list users and passwords
• Postgresql list users connected
• Postgresql list users privileges
• Postgresql list user defined functions
• Postgresql list user mappings
• Postgresql list user defined types
• Postgresql list user functions