current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 0 down vote favorite

I have a user test, which can view data from the system tables such as pg_class. The idea is to isolate him to have access to a specific database only and not have any access to system resources. The question is, how can one achieve this?

share|improve this question
    
This is a frequent question; have you searched for existing questions first? "multi-tenant" is a useful keyword. – Craig Ringer Oct 16 '15 at 12:38
    
Sure I did, I come from Oracle env so it is somehow different with postgre, trying to figure it out – Petar Spasov Oct 16 '15 at 12:57
    
By system resources, do you mean the pg_catalog schema? Or information_schema, too? – dezso Oct 16 '15 at 13:03
    
I don`t want him having access to tables like pg_stat_replication, pg_stat_activity or using any of the functions like pg_current_xlog_location(); – Petar Spasov Oct 16 '15 at 13:32
up vote 0 down vote

Here is the documentation: http://www.postgresql.org/docs/9.4/static/sql-grant.html

I would recommend something like this -- start with no permissions, no access, and just grant the permissions you need afterward (grant select was included as an example). Note that if you are working in a non-public schema, you will probably have to grant usage on that schema before you can do anything in it (GRANT USAGE ON SCHEMA schemaname TO test).

Please let me know if this doesn't work for you!

CREATE ROLE test WITH
  LOGIN
  CONNECTION LIMIT 1
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOINHERIT
  NOREPLICATION
  ENCRYPTED PASSWORD 'yourpassword';
GRANT SELECT ON pg_class TO test;
share|improve this answer
    
Creating a user has by default all the options you have outlined: Create user test with password test I cannot create DB or create another user or role, however I have access to let`s say pg_class(by default, even the path is not set to public it is set to test schema), which I actually would like to prevent in general. In oracle (where i come from) there is the principle of least privilege in other words you create a user and it does not have any privilege or role granted to him. – Petar Spasov Oct 20 '15 at 16:46
    
You're right. I've come to realize that what you're asking is somewhat different than what I thought you were asking. It looks like revoking access to system tables is possible through explicitly revoking those privileges, but isn't recommended. Hopefully someone with more experience can answer your question better, I may not be the most qualified to do so – jerphiker Oct 20 '15 at 19:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.