As long as you have your database organized in such a way that there is an account that is the owner of the tables and other objects, and your application connects using a separate account you can do what you want.
If you have tables and procedures organized in separate owners, you fist have to grant the required tables to the procedure owner (directly granted). Next you can grant the execute to a role or a user.
As soon as you allow connections to an object owner your security is broken since in that case you can always use all the objects of the owner (you are owner so you can use it).
I suggest you create a dedicated schema for this user (if it does not exist already), typically with the same name as the name of the role.
CREATE SCHEMA this_user;
REVOKE ALL ON SCHEMA this_user FROM public;
GRANT USAGE ON SCHEMA this_user TO this_user;
The default search_path in typical Postgres installations is "$user",public. So the new schema is automatically the "current" schema for this_user. If your installation is different, consider setting it explicitly:
ALTER ROLE this_user SET search_path=`"$user",public`;
More about the search_path in this related answer on SO:
Create a VIEW in this schema:
CREATE VIEW this_user.the_table AS
TABLE other_schema.the_table; -- shorthand for "SELECT * FROM"
GRANT SELECT, INSERT ON this_user.the_table TO this_user;
You may need privileges on the SEQUENCE additionally, if a serial column is involved. See:
The same is not necessary for IDENTITY columns in Postgres 10 or later.
Since Postgres 9.3 simple views like the above are automatically updatable. Per documentation:
Simple views are automatically updatable: the system will allow
INSERT, UPDATE and DELETE statements to be used on the view in the
same way as on a regular table. A view is automatically updatable if
it satisfies all of the following conditions:
Then REVOKE all privileges from public, that should stay hidden from the public (including this_user).
More about default privileges and the basics:
Best Answer
Here the script to generate grant select on all the tables and synonyms.
Then you have to create a script to run these grant statements at once or you can use PL/SQL as well. Type the following in the SQL prompt.
And you have got the script file you can run it.
OR
You can run the following PL/SQL block (Run as admin user).
BEGIN FOR s IN (SELECT * FROM user_objects where object_type in('TABLE','SYNONYM')) LOOP EXECUTE IMMEDIATE 'grant select on admin.' || s.object_name || ' to user'; END LOOP; END;