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:
table4. And there could be possibility that user
foo123 is able to write queries for
table2, but not for
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:
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
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:
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
Why is that so that I can see all database users listed anywhere else, except pg_user. And table
pg_userdoesn't have any information about privileges using virtual tables.
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.