Limit access to specific database only, and restrict access to system tables

Question

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.

Answer 1

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 views SELECT - ordinary users cannot change data in pg_catalog anyway. For cleanliness, it is possibly better to revoke ALL, which then includes the former two as applicable.

Let's see now what happens:

# CREATE USER lame_bob [WITH PASSWORD 'l'];  -- note that this way the password 
                                             -- might appear in the database logs, 
                                             -- so use psql's \password command instead

# REVOKE EXECUTE ON FUNCTION pg_current_xlog_location() FROM lame_bob;

# \c test lame_bob

> SELECT pg_current_xlog_location();

 pg_current_xlog_location 
──────────────────────────
 0/26517910

Oops.

The trick here is the public pseudorole, which has privileges on certain objects (like EXECUTE on the function in this example). To disable Lame Bob, this has to be also revoked:

> \c test dezso

# REVOKE EXECUTE ON FUNCTION pg_current_xlog_location() FROM public;

# \c test lame_bob

> SELECT pg_current_xlog_location();

ERROR:  permission denied for function pg_current_xlog_location

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:

# CREATE ROLE can_use_system_functions; -- no login rights

# GRANT EXECUTE ON FUNCTION pg_current_xlog_location() TO can_use_system_functions;

# GRANT can_use_system_functions TO able_alice;

And then able_alice (and all other users that eventually gets the membership in can_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.