Postgresql – Role, whose name doesn’t match database name, can’t access the database

permissionspostgresqlpostgresql-9.3

Role app can access database app. But role app2 can't. I don't see any specific privileges for role app, and it's not an owner according to \l:

app2=> \l

      Name       |  Owner   | Encoding |   Collate   |    Ctype    |    Access privileges
-----------------+----------+----------+-------------+-------------+--------------------------
 app             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres            +
                 |          |          |             |             | postgres=CTc/postgres   +
                 |          |          |             |             | app=CTc/postgres        +
                 |          |          |             |             | app2=CTc/postgres
...

app2=> \dp
                                               Access privileges
 Schema |                    Name                     |   Type   | Access privileges | Column access privileges
--------+---------------------------------------------+----------+-------------------+--------------------------
 public | access_users                                | table    |                   |
 public | access_users_id_seq                         | sequence |                   |
...


app2=> select * from users;
ERROR:  permission denied for relation users

Why role app is able to access database? What can I check?

Best Answer

As it sounds, the tables (not necessary the database) is owned by the app user. This is what the documentation tells about this:

If the "Access privileges" column is empty for a given object, it means the object has default privileges (that is, its privileges column is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above.

As nothing is displayed under "Access privileges" in the \dp output, and the app user can read from and write to the table, it is clear that it's the owner. One can prove this, for example, by issuing \dt access_users, which should return something like

        List of relations
 Schema │ Name         │ Type  │ Owner  
────────┼──────────────┼───────┼───────
 public │ access_users │ table │ app

This also means (not surprisingly), that no other user (except superusers and the members of the app role) has access to the tables. If you want app2 to have some rights on them (and future tables), an earlier answer of mine might be interesting.

Related Question