Thread: Revoke access on pg_user

Revoke access on pg_user

From
Oliver Fromme
Date:
Hi,

By default, all users can find out what other users exist,
by looking at pg_user (which is a view on pg_shadow).  For
privacy reasons I would like to disable that.

So I tried this:  REVOKE ALL ON pg_user FROM PUBLIC;
which was acknowledged by the word "REVOKE", and there was
no error message.  But it doesn't have any effect.  All
users can still perform SELECT on pg_user and see everyone.

What am I doing wrong?

Regards
   Oliver

PS:  I'm using 7.3.4 on FreeBSD 4.8, if that matters.

PPS:  I apologize if this post appears twice (I first
mailed it with the wrong subscription address.)

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"C++ is the only current language making COBOL look good."
        -- Bertrand Meyer

Re: Revoke access on pg_user

From
Tom Lane
Date:
Oliver Fromme <olli@lurza.secnetix.de> writes:
> So I tried this:  REVOKE ALL ON pg_user FROM PUBLIC;
> which was acknowledged by the word "REVOKE", and there was
> no error message.  But it doesn't have any effect.  All
> users can still perform SELECT on pg_user and see everyone.

... it works for me ...

regression=# REVOKE ALL ON pg_user FROM PUBLIC;
REVOKE
regression=# create user joe;
CREATE USER
regression=# \c - joe
You are now connected as new user joe.
regression=> select * from pg_user;
ERROR:  pg_user: permission denied

Keep in mind though that you'll need to do this in each database.
(If you do it in template1, databases subsequently cloned from
template1 will have the desired pg_user permissions already.)  Also,
I doubt that pg_dump will create any record of your change to pg_user.

            regards, tom lane