Postgresql – Postgres 10. Hide users/roles from other users

permissionspostgresqlpostgresql-10roleusers

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?

Best Answer

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.

Related Solutions

Sql-server – SQL Server 2005 Easy way to add all users to a database role

Adam Machanic has a blog post on replacing xp_execresultset which in SQL 2000 did what you want: Replacing xp_execresultset in SQL Server 2005

Oracle – list users with access to certain tables

List all users who have been assigned a particular role

-- Change 'DBA' to the required role
select * from dba_role_privs where granted_role = 'DBA'

List all roles given to a user

-- Change 'PHIL@ to the required user
select * from dba_role_privs where grantee = 'PHIL';

List all privileges given to a user

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select 
      null     grantee, 
      username granted_role
    from 
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;

Note: Taken from http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html

List which tables a certain role gives SELECT access to?

-- Change 'DBA' to the required role.
select * from role_tab_privs where role='DBA' and privilege = 'SELECT';

List all tables a user can SELECT from?

--Change 'PHIL' to the required user
select * from dba_tab_privs where GRANTEE ='PHIL' and privilege = 'SELECT';

List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.

-- Change 'TABLENAME' below
select Grantee,'Granted Through Role' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = 'TABLENAME' 
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'TABLENAME' ;

Best Answer

Related Solutions

Sql-server – SQL Server 2005 Easy way to add all users to a database role

Oracle – list users with access to certain tables

Related Question