4

I have a postgresql 10 installed and I want to hide the name of users/roles to other users. I searched through other posts and somebody suggested this:

REVOKE SELECT ON pg_catalog.pg_authid FROM public;
REVOKE SELECT ON pg_catalog.pg_auth_members FROM public;

After doing all of these, user1 can't dig into the info of a user (Error retrieving roles from the database server. ERROR: permission denied for relation pg_auth_members) but still can list them all as you can see on the next image

postgres

Of course user1 is not a superuser.

I already tested this with no effect:

REVOKE SELECT ON pg_catalog.pg_roles FROM user1;
REVOKE SELECT ON pg_catalog.pg_user FROM user1;

Tested this too with no effect:

REVOKE SELECT ON pg_catalog.pg_authid FROM user1;
REVOKE SELECT ON pg_catalog.pg_auth_members FROM user1;

If I launch this next statements users can't login and everything is broken:

REVOKE SELECT ON pg_catalog.pg_roles FROM public;
REVOKE SELECT ON pg_catalog.pg_user FROM public;

Is there a way to achieve this? Is very important to me to hide the name of the users to other users.

Background: I'm creating a hacking ctf (capture the flag) virtual machine. This is one of the challenges... the point is avoid a user can see the "right database user name" to connect. That's something he/she must find inside the database finding hints and other cryptographic stuff.

Edit: a "sad" workaround is to create hundreds of dummy users in order to "try to hide" the real one... but it must be an elegant solution for this. Any help?

  • I might be mistaken, but can't we find this informations in information_schema ? – Arkhena Aug 5 '17 at 9:24
  • How can I retrieve it? what is the select statement? Maybe knowing that I could revoke select permissions for the user to that object, I don't know... – OscarAkaElvis Aug 5 '17 at 10:09
2

pgAdmin is using pg_roles to show that information, so it is enough to run

REVOKE SELECT ON pg_catalog.pg_roles FROM public;

But since the information is available in other views as well, you would need to hide them, for instance the pg_user and pg_shadow views.

It should not stop users from logging in nor breaking anything since those views are not used during authentication, so you should make sure that you didn't change anything else. The logs might show you the reason for why they can't log in.

| improve this answer | |
  • Putting REVOKE SELECT ON pg_catalog.pg_roles FROM public; as you suggested breaks the login on pgAdmin. Extracted from the log: ERROR: permission denied for relation pg_roles. STATEMENT: SELECT oid as id, rolname as name, rolsuper as is_superuser, rolcreaterole as can_create_role, rolcreatedb as can_create_db FROM pg_catalog.pg_roles WHERE rolname = current_user. Removing that line you suggested, users can login again. Tested. – OscarAkaElvis Aug 5 '17 at 12:07
  • 1
    Well, unless you're willing to use a different tool then there is no other option. pgAdmin is quite useless in my opinion, and furthermore, aimed at database administrators, so I guess it assumes full access to everything. That revoke doesn't stop anyone from logging in to the database with any other tool. You could try squirrelsql.org which is open source. – Jimmy Stenke Aug 5 '17 at 12:21
  • hhmmnnn.... interesting. I tested using command line psql and with that statement you put, the \du is not working so the users are stopped to be enumerated. I also revoked to pg_user and now even the select * from pg_catalog.pg_user is not working anymore. The pg_shadow is not working only revoking over pg_roles and pg_user. Anyway, not sure if this could be a solution for me... most of users are using pgAdmin. Thanks. – OscarAkaElvis Aug 5 '17 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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