PostgreSQL - Securing DB and hide structure

Question

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

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
– Lamis IntheSky WithDiamonds, Commented Jul 11, 2013 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. wiki.postgresql.org/wiki/… — JustBeingHelpful
– JustBeingHelpful, Commented Apr 28, 2022 at 19:52
cvedetails.com/vulnerability-list/vendor_id-336/product_id-575/… — JustBeingHelpful
– JustBeingHelpful, Commented Apr 28, 2022 at 19:56

Richard Huxton · Accepted Answer · 2018-12-18 14:33:34Z

7

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.

edited Dec 18, 2018 at 14:33

answered Jul 11, 2013 at 17:04

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lamis IntheSky WithDiamonds Over a year ago

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?

Richard Huxton Over a year ago

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.

Lamis IntheSky WithDiamonds Over a year ago

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?

Richard Huxton Over a year ago

You'll need at least CREATE on the schema where you want to create the function. Plus whatever access the function needs to.

leshik · Accepted Answer · 2021-05-05 09:14:00Z

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.

answered May 5, 2021 at 9:14

leshik

335 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - Securing DB and hide structure

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related