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.
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();
.
The question is, how can one achieve this?
For example:
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.
Best Answer
You can achieve this goal by revoking certain privileges. You might have the reasons to do so - the only scenario where I'd start thinking about it is multi-tenancy. Still, in this case, it is better not to allow the tenants to directly touch the database.
So, after this detour, what do you have to revoke? For functions it's the
EXECUTE
privilege, for tables and viewsSELECT
- ordinary users cannot change data inpg_catalog
anyway. For cleanliness, it is possibly better to revokeALL
, which then includes the former two as applicable.Let's see now what happens:
Oops.
The trick here is the
public
pseudorole, which has privileges on certain objects (likeEXECUTE
on the function in this example). To disable Lame Bob, this has to be also revoked:In turn, this might disable other users, too, who should keep the ability of using this function. If necessary, you can grant them the
EXECUTE
privilege one by one - but there is a better way:And then
able_alice
(and all other users that eventually gets the membership incan_use_system_functions
) will be able to do what they need to do. Allowing new users to these objects is just a matter of granting them this single umbrella role.