October 5th, 2012, 07:13 AM
About the security definer and access security
Dear Dev Shed Team and members,
I am devloping applications based on PostgreSQL and want to improve their securty as I have done in MSSQL.
What I want to achieve is that user access to database just through functions, not having access to tables.
I am migrating to PostgreSQL 9 and this thing is very simplified, but I have some thing that are not clear for me. Perhaps you can help me.
Letīs take a simple example in which a database has one table and two functions. Every user I create I execute:
CREATE ROLE userprueba3 WITH LOGIN PASSWORD 'pass123';
-- Prevent all authenticated users from having access to any object unless we give him permission
REVOKE ALL PRIVILEGES ON DATABASE pruebapermisos FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;
-- Allow access just to functions.
GRANT CONNECT ON DATABASE pruebapermisos TO userprueba3;
GRANT EXECUTE ON FUNCTION cliente_obtener() TO userprueba3;
Then my questions are:
1 - If we create another database we have to revoke all privileges to the limited user or create the objects and REVOKE ALL to public? Because if we do not do that the users will have access to the new database.
2 -Is there a way not to allow the limited user to see the definition of the tables and functions?
3 - As we can see in the official documentation it recommends to SET search_path = admin, pg_temp; so the pg_temp schema is searched in the last position in order to user not be able to mask objects. In our case, we have to set it as: SET search_path = public, pg_temp?
Thank you very much in advanced,