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.

ygoe
  • 243
  • 1
  • 4
  • 10
  • 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 –  Sep 30 '19 at 20:34
  • Related: https://dba.stackexchange.com/questions/35316/ –  Sep 30 '19 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 Oct 01 '19 at 02: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 Oct 01 '19 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 Oct 01 '19 at 21:28
  • @LaurenzAlbe You obviously don't now much about privacy. If I see the table wordpress_posts then I know there's a WordPress installation. And there's more suspicious software than that. One customer simply is not allowed to see anything about what another customer does. They should not even know which other customers exist. You can't get a directory file listing of all the other webhosting customers on your server, either, can you? – ygoe Dec 02 '19 at 19:28
  • What you are propagating is known as "security by obscurity", and it has a bad reputation among security experts. If you are concerned about security, you probably wouldn't keep your valuable data in Wordpress, would you? – Laurenz Albe Dec 03 '19 at 06:49
  • I'm not using WordPress, correct. But that's not the point. I'm not asking about security. I'm asking about privacy. And that is a valid concept. (Maybe not equally everywhere in the world.) – ygoe Dec 03 '19 at 17:36

0 Answers0