0

I faced a task: find out minimal user permissions that I should grant to run a query:

select grantee, routine_name from information_schema.routine_privileges where routine_name in ('pg_read_file', 'pg_read_binary_file') group by grantee, routine_name;

I tried to grant

GRANT SELECT ON information_schema.routine_privileges TO <test_user>;

and I failed to get any result

If I run the query under the postgres user - I get all grantees corresponding routine_name

But what should I do to get those records under the test_user account ?

P.S. A remark: as I understood the information_schema.routine_privileges is a view:

so I granted a SELECT on tables: pg_proc, pg_namespace and pg_authid to my test_user

but still was unsuccesfull

P.P.S. It seems to me I have to troubleshoot: find out an object the user has insufficient access rights.. But how to find theese objects?

0

2 Answers 2

0

You can only see routines in information_schema.routine_privileges if you are a member of the grantor or grantee roles or if EXECUTE was granted to PUBLIC.

0
-2

@Laurenz Albe

Ah... I believe I understood. Thank You.

here is an example of a result of the select query I ran thje query under postgres account But there is another account (tst_usr_3) in my test PostgreSQL instance. And when I run the select query under the tst_usr_3 account - I get nothing. If I GRANT EXECUTE ON pg_read_binary_file to tst_usr_3 - and then I run the select query - I get result just upon tst_usr_3. But I cannot see any data upon the tst_usr2. So IF I understand right - I must grant role tst_usr_3 to tst_usr2 - then I will be able to see routines of tst_usr2. Correct?

pg_read_binary_file_query_with_sa_example

In bottom line the point of my question: how to get all roles being grunted to execute pg_read_file / pg_read_binary_file? Paying attention that I'm allowed to use an account without administrator role within the PostgreSQL instance. What permissions should I grant? I'm affraid it's forbiden to grant a user role to another user role..

But what if I use the SECURITY DEFINER clause to create a function that runs with the privileges of the sysadmin? Would It help?

3
  • If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. Please construct your questions based on the details in minimal reproducible example. Adding table DDL statements, sample DML statements, and providing desired output via a markup table as shown in the example makes it easier for us to provide great answers.
    – Hannah Vernon
    May 4, 2023 at 13:15
  • I do not have a new question. I tried to add details that as I thought would clarify the problem I faced. But thank You for lowering my reputation. Wish good health to You.
    – Gregor76
    May 5, 2023 at 8:57
  • If you want to clarify your question, you can do that by editing the question. Clarifications do not belong in an "answer", which are for answers to the question only. When you delete your answer, the points you lost will be returned.
    – Paul White
    May 5, 2023 at 10:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.