5

I am deploying a database in postgreSQL and I created a user that just will be able to execute certain functions.

I revoked all privileges from the user i just created and granted connect privileges executing:

REVOKE ALL PRIVILEGES ON DATABASE <database> FROM my_user;
REVOKE ALL PRIVILEGES ON SCHEMA public TO my_user;
GRANT CONNECT ON DATABASE <database> TO my_user;

But when i connect to the database with this user, i am able to read all table structures and all function source codes. Is there a way to hide it from this user?

I take the chance to make another question: I want to just execute functions (which may include select, insert or update on database tables) with this user, but I don't want to grant privileges on select, update or delete on tables. I am using "SECURITY DEFINER" and then I grant execution, but I think it may be a little insecure. Am I right? is there any other way to do it?

Thanks in Advance. Lamis

Maksym Labutin
  • 561
  • 1
  • 8
  • 17
Lamis IntheSky WithDiamonds
  • 53
  • 1
  • 4
  • I found the following to hide functions source codes: REVOKE SELECT ON TABLE pg_proc FROM user; REVOKE SELECT ON TABLE pg_proc FROM public; – Lamis IntheSky WithDiamonds Jul 11 '13 at 17:20
  • Good idea to read any security vulnerabilities in PostgreSQL. Any user/role created will get access to the public schema in PostgreSQL by default. https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path – JustBeingHelpful Apr 28 '22 at 19:52
  • https://www.cvedetails.com/vulnerability-list/vendor_id-336/product_id-575/Postgresql-Postgresql.html – JustBeingHelpful Apr 28 '22 at 19:56

2 Answers2

6

There's no way to hide the system catalogues from a user in PostgreSQL. If a user can't access the catalogues then they can't locate any other database objects.

If you really can't afford to let them see the structure of the db, you'll need to prevent them connecting. Build some sort of middle layer with a simple API that calls the db.

SECURITY DEFINER is the standard way to provide limited access at a higher privilege level. You have to be careful with any function arguments that can end up in a dynamic query though. That's the same "bobby tables" issue as with any dynamic sql building though.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
  • Thanks for your response, Richard. If i dont use dynamic queries on that function. is it secure to use "SECURITY DEFINER" and grant execute privileges to a "execute only" user? – Lamis IntheSky WithDiamonds Jul 11 '13 at 17:07
  • The only danger is if a malicious user could make it do something you haven't anticipated. The simpler the function is, the more confident you can be. There's nothing insecure of itself. – Richard Huxton Jul 11 '13 at 17:09
  • Thanks again, Richard. one last question and i accept your answer. according to official documentation, "SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it". I think it would be potentially dangerous to execute the "CREATE FUNCTION" script with the "postgres" user. I want to create a "deployer" user. Which GRANTS should this user have? – Lamis IntheSky WithDiamonds Jul 11 '13 at 17:16
  • You'll need at least CREATE on the schema where you want to create the function. Plus whatever access the function needs to. – Richard Huxton Jul 11 '13 at 17:24
2

How about

REVOKE SELECT ON pg_namespace FROM my_user;
REVOKE SELECT ON pg_catalog.pg_database FROM my_user;

You won't be able to see anything, but you'll be able to make queries if you know the namespace and table name.

leshik
  • 33
  • 5