postgresql - granting schema to users I have (2) postgresql databases. I basically used the same pg_dump export file to create the (2) db's on different machines. The DDL is the same, however, I am getting the no relations message on one of them. The search_path's are set correctly.  I believe I'm missing a grant for (2) of my users to see this schema. The settings are correct on Host2. I just don't which grant to use.

Message:

mra_prd_99999=&gt; \dt
No relations found.


HOST1:

mra_prd_99999=&gt; \z
                                                   Access privileges
      Schema       |           Name            | Type  |          Access privileges          | Column access privileges
-------------------+---------------------------+-------+-------------------------------------+--------------------------
 mra_dev_schm_99999 | abc_bounce_category       | table | mra_prd_99999=arwdDxt/mra_prd_99999 |


HOST2:

mra_prd_99999=&gt; \z
                                                        Access privileges
      Schema       |           Name            | Type  |              Access privileges               | Column access privileges
-------------------+---------------------------+-------+----------------------------------------------+--------------------------
 mra_dev_schm_99999 | abc_bounce_category       | table | mra_prd_99999=arwdDxt/mra_prd_99999         +|
                   |                           |       | mra_prd_99999_user1=arwdDxt/mra_prd_99999+|
                   |                           |       | mra_prd_99999_user2=r/mra_prd_99999        |


My rolenames and settings are identical on both (1,2):

 mra_prd_99999          | Create DB                                      | {}
 mra_prd_99999_user1    |                                                | {}
 mra_prd_99999_user2    |                                                | {}


All the users have the search_path set to the same schema:

mra_prd_99999=&gt; show search_path;
    search_path
-------------------
 mra_dev_schm_99999
(1 row)

mra_prd_99999=&gt; \dn
          List of schemas
       Name        |     Owner
-------------------+---------------
 public            | mra
 mra_dev_schm_99999 | mra_prd_99999
(2 rows)


What grants do I need to run in order for HOST1's users to have the same access privileges than HOST2?
You get the message


  No relations found.


because the user does not have the USAGE privilege on the schema (neither granted directly, nor through the public pseudorole).  You can check this by comparing \dn+ mra_dev_schm_99999 on the two databases.

This is what the documentation says about this:


  For schemas, allows access to objects contained in the specified
  schema (assuming that the objects' own privilege requirements are also
  met). Essentially this allows the grantee to "look up" objects within
  the schema. Without this permission, it is still possible to see the
  object names, e.g. by querying the system tables. Also, after revoking
  this permission, existing backends might have statements that have
  previously performed this lookup, so this is not a completely secure
  way to prevent object access.


Furthermore, it appears that the two mra_prd_99999_users don't have the table level privileges on abc_bounce_category - you have to do 

GRANT ALL ON TABLE mra_dev_schm_99999.abc_bounce_category 
      TO mra_prd_99999_user1;
GRANT SELECT ON TABLE mra_dev_schm_99999.abc_bounce_category 
      TO mra_prd_99999_user2;


If you have more tables with the same problem, do

GRANT ALL ON ALL TABLES IN SCHEMA mra_dev_schm_99999 
      TO mra_prd_99999_user1;

-- then for future tables:
ALTER DEFAULT PRIVILEGES FOR ROLE mra_prd_99999 
    IN SCHEMA mra_dev_schm_99999 GRANT ALL ON TABLES 
    TO mra_prd_99999_user1;

-- and similarly to the other user