Number of virtual tables that specified user is able to write queries in PostgreSQL

Question

I have one question about PostgreSQL that I can't understand. As it seems so far, it needs some of bruteforce in tables. Well, I did that lots of time and I found nothing. So, firstly I will cover my goal.

What I need to do, is to get number of virtual tables that specified user is able to write queries in PostgreSQL. In other words, there could be user foo123 and there could be, let us assume, four tables: table1, table2, table3 and table4. And there could be possibility that user foo123 is able to write queries for table1 and table2, but not for table3 and table4. So, by query doing my task I should get 2 (there is no difference what column name would be - I can think any name for column).

Now I will cover what I tried to do and what I have achieved. For now, I can see that I have to use information from information_schema. But there is one problem. I don't know where I can find users in information_schema and lists of tables, associated with these user by any column. Well, until now I found three tables about granting privileges to do something: information_schema.table_privileges, information_schema.column_privileges, usage_privileges. I think that I have to use information from table information_schema.table_privileges (columns that I should be interested, I think, are grantee and table_name). And it could look like as a path for finding solution, but one small problem. If we would treat grantee as user who is able to do something with virtual table, then there is one thing. For example, database can have lots of users, who are able to use virtual tables in any way. But these users are just in one table: pg_user, i.e. there are no all users listed anywhere that I could take any user to check the of number virtual tables he is able to write queries for. In other words, in table information_schema.table_privileges I see just grantors and users online. For them I can check privilege types, but for no user that is offline.

So, extended version of my question, according to what I have done, could be such questions:

1. Is my path for finding number of virtual tables for user specified correct or not, i.e. am I doing right or wrong trying to analyse information_schema.table_privileges table?

2. Why is that so that I can see all database users listed anywhere else, except pg_user. And table pg_user doesn't have any information about privileges using virtual tables.

3. If my path for finding number of virtual tables is not correct, then in what way I should find that number?

I think that's all I've got. If there are some misunderstanding about my question, I will try to give more information.

Answer 1

I try to answer your question - to be correct, I try to answer the question I think you've asked (see the comments). Until then I'll assume that a virtual table is simply a table and you want to count the tables a user has SELECT privilege on.

First, you have to collect all roles which the given user belongs to. This will include the user itself, all roles that are granted to it, all roles that are granted to the roles granted to your user etc., and finally, but usually most importantly, the PUBLIC role.

You can collect these from the information_schema.applicable_roles view with a query like this:

WITH RECURSIVE privs(grantee, role_name) AS (
    SELECT grantee, role_name 
    FROM information_schema.applicable_roles 
    WHERE grantee = 'my_user'

    UNION ALL

    SELECT ar.grantee, ar.role_name
    FROM 
        information_schema.applicable_roles ar 
        JOIN privs ON ar.grantee = privs.role_name
)
SELECT role_name FROM privs

UNION 

SELECT 'my_user'

UNION 

SELECT 'PUBLIC'

Then you have to consult the information_schema.table_privileges view. In order to see all the rows belonging to all grantees, you have to be logged in as a superuser. With an ordinary user you will see only the roles granted to your user (including indirectly granted roles, ie. if the grants are user -> user1 -> user2, you will see user -> user1 and user1 -> user2 if logged in as user, and only user1 -> user2 if logged in as user1.)

Here you can look for tables with SELECT privilege like

SELECT table_name
FROM information_schema.table_privileges
WHERE 
    privilege_type = 'SELECT'
    AND grantee IN (... the recursive query above goes here ...)
;

Turning this into a count is left as an exercise for the reader :)