0

I'm running a PostgreSQL database server, version 10 on Linux, for multiple users. Today I found out by surprise that any user can see all other users' databases and their tables, columns and other properties, but not the data. I'm wondering why this is the case? Shouldn't a user be restricted to their own databases? Why is this access granted at all and how can I remove it?

The users were created with this command: createuser "user1"

The databases were created with this command: createdb -O "user1" "user1_db1"

I've read something about the CONNECT privilege in the Postgres documentation but I couldn't find out how to list all the privleges that a user currently has. An article suggests revoking all privileges and granting CONNECT only on the user's database. I don't want to do that before I know what other privileges the user possibly has. Also, is this required for each new database created for the user? The documentation doesn't seem to cover this topic at all.

Improve this question
8
  • There is no such thing as "their own database". Users (roles) are global to all database, otherwise you would need to create duplicate users if you want to access more than one database. You only need to revoke the connect privilege from the PUBLIC role once for each database. If you do that in the template database, this will be used for new databases as well
    – user1822
    Commented Sep 30, 2019 at 20:34
  • Related: dba.stackexchange.com/questions/35316
    – user1822
    Commented Sep 30, 2019 at 20:35
  • I find it surprising that so many people think that it might be a security problem to see the metadata of tables that you have no permission on. Maybe Oracle advertises that as a "security feature".
    – Laurenz Albe
    Commented Oct 1, 2019 at 2:51
  • @LaurenzAlbe Well, if two users don't know each other, it is in fact a privacy issue if one user can see what the other user stores in the database. Metadata can actually tell a lot about the data.
    – ygoe
    Commented Oct 1, 2019 at 21:01
  • @ygoe How can metadata tell a lot about the data? If you know there is a table customer with a column credit_card_number, does it get you any closer to getting the data in the table?
    – Laurenz Albe
    Commented Oct 1, 2019 at 21:28

0

Reset to default

Browse other questions tagged or ask your own question.