Work out whether a role can use a table, view, function or procedure, and which rows.
promptread-only tools
check-role-access(role, object, [privilege], [connection])The text below is exactly what prompts/get returns for the example arguments. A client inserts it as the opening message of a conversation, and the model then calls the tools it names.
Determine whether reporting can use shop.orders, specifically SELECT. Access is a chain of gates, not a grant. Every one has to pass, and the first that fails is the whole answer -- so find that one and name it, rather than reporting the object ACL and stopping. The usual wrong answer comes from checking the table and forgetting the schema. Two questions hide inside the one being asked, and they have different answers: whether the role may REACH the object at all, and WHICH ROWS it then sees. Grants settle the first. Row-level security settles the second and can reduce a complete set of grants to nothing without touching them. Answer both explicitly. 0. Call checkRoleAccess with grantee reporting, the schema and the object. It answers the grant question outright, the way PostgreSQL answers it: has_table_privilege and its relatives fold in inheritance, grants to PUBLIC, ownership and superuser exactly as the server does. Everything below interprets that answer rather than recomputing it -- do not add up ACLs by hand when the server has already been asked. Read four parts of it before anything else. privileges is the verdict per privilege. schema_access.usage is a separate gate and a false there beats a true above it. columns lists a partial grant where the table-level answer was no. row_level_security is a different question entirely and step 4 is where it is read. 1. Call listRoles and establish what reporting IS before asking what it has. Three attributes can end the investigation here: - superuser bypasses every check below, including row-level security. The answer is yes to everything; say so and stop rather than tracing grants that do not matter. - bypass_rls means step 4 does not apply to this role, however the policies read. - inherit explains a no that step 0 already reported. The has_* functions honour rolinherit, so a NOINHERIT member of a granted group comes back false rather than true -- the privilege is real and one SET ROLE away, and application code almost never issues one. Read it together with member_of to tell "never granted" from "granted, unreachable as the application connects", because those look identical in the verdict and need different fixes. Then walk member_of transitively: a grant to any role in the closure counts, subject to inherit. Say which role in the chain actually carries the grant, because that is where an operator has to go to change it. 2. Where checkRoleAccess said no, walk the gates to find WHICH one said it -- the tool gives the verdict, and this is how you turn a verdict into something an operator can act on. Where it said yes, read the same list to say what the yes does not cover. a. CONNECT on the database, and whether the role can log in at all -- a group role with every grant in the cluster still cannot open a session. b. USAGE on the schema, which step 0 returned as schema_access.usage. This is the gate that is missed most often: SELECT on the table is inert without it, and the error names permission on the TABLE, which sends people to the wrong object. Go to listSchemas only for what the tool does not carry -- who else holds USAGE, and whether PUBLIC does -- which is what says whether granting it is a one-role change or a decision about the schema. c. The object's own privileges, from tableDetails or functionDetails. PUBLIC is a grantee like any other and applies to every role, so a grant to PUBLIC answers the question on its own -- and EXECUTE is granted to PUBLIC by default on every new function, which surprises people who never granted anything. d. Ownership. The owner holds every privilege implicitly and need not appear in the grants map at all, so an empty map is not an empty answer. e. Column-level grants, where the columns list carries them. SELECT on three of ten columns is a real yes that fails the moment a query names the fourth, so report it as the partial it is. 3. If the object is a VIEW, the privileges checked are the view's own, and the base tables are read as the VIEW OWNER. So a role can hold SELECT on a view while holding nothing at all on what it selects from, and that is normal rather than a misconfiguration. Check reloptions for security_invoker: on PostgreSQL 15 and later a view can be defined to run as the caller instead, which flips this entirely -- base-table grants and base-table policies both come back into play. Say which of the two you are looking at. 3b. Two questions this prompt cannot close, each with its own tool. defaultPrivileges says what the NEXT object of each type will grant: everything above answers about the objects that exist, and a correct answer today that is wrong for tomorrow's table is the standing cause of "the new table is not readable and every old one is" -- which presents as a broken grant and is a missing default. Note that a default applies only to objects created by the granting role, so read granted_by beside it. And roleDependencies answers the inverse of this whole prompt: what the role OWNS, cluster-wide, which is what blocks DROP ROLE and what an offboarding has to reassign. 4. Now row-level security, and only for a table or a view over one. Step 0 returned the row_level_security block: if enabled is false, the grants above are the complete answer and this step is over. If it is true, everything above answers only whether the role may reach the table. Read applies_to_role before the policies. It is the one thing no grants map can tell you, because it folds together three exemptions -- superuser, BYPASSRLS, and ownership without FORCE -- and a role that is exempt makes every policy below irrelevant. Each policy carries its own applies_to_role for the same reason: a policy that does not name this role, directly or through a role it belongs to, is not part of the answer however it reads. - The OWNER BYPASSES RLS unless forced is also true. This is the single most common wrong test: someone checks as the owner, sees every row, and concludes the policies are permissive. FORCE ROW LEVEL SECURITY is what makes the owner subject to them, and without it the owner's experience says nothing about anyone else's. - Read the policies map. Each policy names a command and a set of roles, and a policy whose roles are PUBLIC applies to everybody. Only policies matching both reporting and the command in question are in play. - RLS ENABLED WITH NO APPLICABLE PERMISSIVE POLICY IS DENY ALL. This is the state nobody can explain: every grant checks out, has_table_privilege would say yes, and the table returns zero rows. If you find it, that is the finding -- say it in those words. - Permissive policies OR together and restrictive ones AND, so adding a restrictive policy can veto everything the permissive ones allow. Report the combination, not a list. - using gates which rows can be read or affected; with_check gates which rows may be written. They are frequently different, and a role that can read a row it cannot write back is a normal and confusing result worth stating outright. 5. For a FUNCTION or PROCEDURE the privilege is EXECUTE, and security_definer changes who the body runs as. Under a security definer function the caller borrows the owner's privileges for everything inside, so the caller's own grants on the tables it touches are irrelevant -- and EXECUTE on it is effectively a grant of whatever the body can do. Say so plainly when you find one: it is the intended mechanism for controlled escalation and also the way access is given by accident. 6. Report the chain, not a verdict. Name the gate that decided, and if the answer is no, name the single grant that would change it. If the answer is yes, say what it does not include -- the columns outside the grant, the rows outside the policy, the write that the read does not imply. 7. One thing checkRoleAccess cannot fold in, so say it when it applies: a false for a NOINHERIT member of a granted group means 'not right now' rather than 'never'. The has_* functions honour rolinherit, so the privilege is real and one SET ROLE away -- and application code almost never issues one, which is usually what makes the answer no in practice rather than in principle. The role block carries inherits and member_of precisely so the two can be told apart.
{
"jsonrpc": "2.0",
"id": 1,
"method": "prompts/get",
"params": {
"name": "check-role-access",
"arguments": {
"role": "reporting",
"object": "shop.orders",
"privilege": "SELECT"
}
}
}