title: PostgreSQL: List databases user has privilege to connect
body: <p><strong>How do I list only the databases the user has <code>CONNECT</code> privilege on PostgreSQL?</strong> </p>
  
  <p>For instance, <code>user1</code> has access to database <code>db1</code> and <code>user2</code> has access to <code>db2</code>. </p>
  
  <p>When each of these users login/connect to their databases and they need to retrieve the databases list they can connect with. </p>
  
  <p>Here is what i've done so far:</p>
  
  <p>Created two databases <code>db1</code> and <code>db2</code> on my new prostgreSQL server. </p>
  
  <pre><code>CREATE DATABASE db1
      WITH 
      OWNER = admin
      ENCODING = 'WIN1252'
      CONNECTION LIMIT = -1;
  
  CREATE DATABASE db2
      WITH 
      OWNER = admin
      ENCODING = 'WIN1252'
      CONNECTION LIMIT = -1;
  </code></pre>
  
  <p>I've also created two new users (other than admin) <code>user1</code> and <code>user2</code></p>
  
  <pre><code>CREATE USER user1 WITH LOGIN ENCRYPTED PASSWORD 'user1';
  CREATE USER user2 WITH LOGIN ENCRYPTED PASSWORD 'user2';
  </code></pre>
  
  <p>Then I ran the following queries to make <code>user1</code> as owner of <code>db1</code> and <code>user2</code> as the onwer of <code>db2</code></p>
  
  <pre><code>BEGIN;
  ALTER DATABASE db1 owner to user1;
  REVOKE CONNECT ON DATABASE db1 FROM public; 
  GRANT CONNECT ON DATABASE db1 TO user1;
  COMMIT
  
  BEGIN;
  ALTER DATABASE db2 owner to user2;
  REVOKE CONNECT ON DATABASE db2 FROM public; 
  GRANT CONNECT ON DATABASE db2 TO user2;
  COMMIT
  </code></pre>
  
  <p>This way <code>user1</code> cannot connect to <code>db2</code> and <code>user2</code> cannot connect to <code>db1</code>.</p>
  
  <p>Running this query </p>
  
  <pre><code>SELECT datname , (aclexplode(datacl)).grantor,
         (aclexplode(datacl)).grantee,
         (aclexplode(datacl)).privilege_type,
         (aclexplode(datacl)).is_grantable FROM pg_database
  </code></pre>
  
  <p>gives this:</p>
  
  <pre><code> datname | grantor | grantee | privilege_type | is_grantable
  ---------+---------+---------+----------------+--------------
   db1     |   16386 |       0 | TEMPORARY      | f
   db1     |   16386 |   16386 | CREATE         | f
   db1     |   16386 |   16386 | TEMPORARY      | f
   db1     |   16386 |   16386 | CONNECT        | f
   db2     |   16387 |       0 | TEMPORARY      | f
   db2     |   16387 |   16387 | CREATE         | f
   db2     |   16387 |   16387 | TEMPORARY      | f
   db2     |   16387 |   16387 | CONNECT        | f
  (8 rows)
  </code></pre>
  
  <p><em>omitted template1 and template0 from results</em></p>
  
  <p><strong>How do I interpret if <code>user1</code> has access to CONNECT <code>db2</code> or not?</strong></p>
  
  <p><strong>Is there any other way to achieve this?</strong> </p>