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 :)
You want to make your functions SECURITY DEFINER
and have them owned by a user that does have the requisite rights.
Be very careful when coding SECURITY DEFINER
functions. Don't make them owned by a superuser and read the manual carefully. Create a role that has only the rights required and no more; give it ownership of the SECURITY DEFINER
functions. Where appropriate create multiple roles for different access levels.
See CREATE FUNCTION
.
Best Answer
Since you're running 9.3, you could potentially use an event trigger to catch the tag
DROP TABLE
and prevent that for certain users.Denish Patel has a example in his blog post here Preventing Human Errors in Postgres. Search for the string "Function to prevent single table drop". That should give you enough of a start to modify for your specific needs.