Created user can access all databases in PostgreSQL without any grants

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:

1. apt-get install postgresql
2. sudo -u postgres createuser -DRSP mike1
   (Specifying the password for the new user)
3. sudo -u postgres createdb data1
4. 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.

At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:

REVOKE connect ON DATABASE database_name FROM PUBLIC;

Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:

GRANT connect ON DATABASE database_name TO rolename;

Edit:
In a multi-tenant scenario, more than just the connect privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki: Shared Database Hosting and Managing rights in PostgreSQL.

PUBLIC has access to the database by default, but it can’t access the data. You can REVOKE the PUBLIC:

REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;

If you want this setting for all future databases, revoke CONNECT on the template1 database (default template database for creating a new database):

REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;

Besides revoking connection privileges from PUBLIC by default, and granting them as specifically desired, the other level at which you can control access is through the pg_hba.conf file.

You can find where the file is stored with:

SHOW hba_file;

If you choose to use this mechanism, there are embedded comments which may be enough to get you started. The docs are here:

http://www.postgresql.org/docs/current/interactive/auth-pg-hba-conf.html

I came across this thread looking for a way to prevent users from even listing the other database names. The REVOKE CONNECT does not prevent this.

As per the answers to this SO question there isn’t a (recommendable) way to achieve it.