I must be missing something with regards to setting up PostgreSQL. What I'd like to do is create multiple databases and users that are isolated from each other so that a specific user only has access to the databases I specify. However, from what I can determine, any created user has access to all databases without any specific grants being given.
Here is what I do on an Ubuntu Server 12.04:
- apt-get install postgresql
- sudo -u postgres createuser -DRSP mike1 (Specifying the password for the new user)
- sudo -u postgres createdb data1
- psql -h localhost -U mike1 data1 (Specifying the password for the user mike1 to login)
It seems that new user "mike1" has no problem connecting to database "data1" and creating tables etc. And this without running any GRANT command at all (and the owner of "data1" is "postgres" since I didn't specify an owner in step 3). Is this really how it is supposed to work?
What I'd like to do is grant mike1 full access to data1 and then repeat this for more users and databases, making sure that the users only have access to one (or possibly several) databases of my choice.