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 :)
When we configure the memory setting for the Postgres DB what is the
recommended memory allocation for each connection ? Is there any
formula to apply ?
There is no per-connection pre-allocated memory that a DBA would define. A session will allocate dynamically what is needed, or use the shared memory pre-allocated at server start depending on the kind of usage.
See Resource consumption in the doc about what can be defined.
I know 25% of the memory of the Server should be allocated. But how do
we allocate based on the DB connections? How do we know, what is the
maximum number of connections should be allocated?
The maximum number of client connections (max_connections
) is factored into the amount of shared memory that is pre-allocated at server start.
In Managing Kernel Resources, the doc says that its size in bytes is:
(1800 + 270 * max_locks_per_transaction) * max_connections
But memory is not the main factor in deciding the maximum number of connections, it's the raw power and number of cores of the machine, and wether a connection pooler is going to be associated to the server. The point is mostly to avoid too many concurrent active queries provoking the server to halt to a crawl.
Also in a multi-nodes environment can we allocate more connections for
each node (in Postgres-ds.xml max connections) than what is actually
allocated in the DB?
If there are multiple client nodes that point to the same PostgreSQL instance, the combined count of their connections cannot outreach max_connections
.
However when using a connection pooler, connection
as a word becomes ambiguous. You want to know whether it's a connection from the client to the pooler or a connection from the pooler to the database server. Generally there are much more allowed connections to the pool than to the database.
Best Answer
This is not possible now. You have two classes of configuration options
max_connections
statement_timeout
,lock_timeout
,idle_in_transaction_session_timeout
CONNECTION LIMIT
(per user).That's all that the server provides. In order to track what you're talking about the server would have to check
pg_stat_activity
. You can however get the information you're looking for manually..You can run a function periodically that checks the above and calls
pg_cancel_backend(pid int)
, orpg_terminate_backend(pid int)
. See the docs here for more information I don't particularly think that's a good idea. I would work with the default options before I tried to roll my own system based on this.