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?


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.

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.