I am developing an application which will use a postgreSQL database backend. I want to prevent the user from seeing some of the db objects - stored procs implementation in particular etc.
The "obvious" (but perhaps wrong) way is to GRANT CRUD access to the database to a specific user who will have an encrypted uname/pwd?
Is there another way of doing this?.
Note: My target audience are largely non-programmers, so I don't need anything that is "unbreakable" (assuming such a thing existed).
PostgreSQL doesn't really support limiting visibility of procedure source code, user or database lists, etc. The best thing to do is accept that, or implement the procedure in C or PL/Java where it's somewhat harder to examine at the cost of considerably greater complexity of implementation.
In general, you should not have the database/table owner be the day-to-day operational user of the DB. Create a new user and GRANT it only the rights that it needs.
Most of the system catalogs have default SELECT rights granted to public so you really want to limit access you would need to explicitly REVOKE that access then GRANT it back to the database owner and any other users that should have it. You're likely to want to limit access to pg_proc if you want to limit access to procedure sources, for example. Such an approach is limited and fragile (root can always gain PostgreSQL superuser access, and from there do anything), but you've said that's probably OK for your purposes.
Messing with the system catalogs isn't really supported and can cause issues with metadata access in JDBC, psql, etc. See this related answer. If you mess with the catalogs and something breaks, you get to keep the pieces.
BTW, if you modify the catalogs, please try to avoid asking questions about databases with hand-modified catalogs here. At minimum specify extremely clearly that you have messed with the system catalogs and exactly what you have done. If possible, reproduce the issue on an unmodified database first.
REVOKE commands I need to type, I am not familiar with the system catalogue. Ideally, I would also want to prevent anyone from seeing the available users for that database.
pg_proc.prosrc - or, rather, revoke access to pg_proc then grant it to all columns except pg_proc.prosrc. Implementing procs in C is a better option for sure, but is a lot more work.pg_catalog is a schema, not a table. pg_proc is a table in pg_catalog, with the fully qualified name pg_catalog.pg_proc. See postgresql.org/docs/current/static/catalogs.html . I do NOT recommend that you mess with the system catalogs if you aren't familiar with schemas, the privelege model, granting and revoking privs, etc. Just accept that your customers can see your procedure source code. I will not give you a copy-and-paste recipe, you need to understand what you are doing if you mess with the catalogs.