Why new user in PostgreSQL can connect to all databases?

Question

I installed PostgreSQL 9 database (migration from Oracle10g) and I am realy confused by user/role management. When I create new user using SQL command like `CREATE USER` or `CREATE ROLE`, or by Navicat tool, created user can see all databases! He realy can connect them! Although he can't select any data from table, he can see table objects and sequences and so on. I was trying revoke connect privilegia but no effect. I was expected the new user has no privilegia and cant see anything. I really don't know why he can.

Accepted Answer

From http://www.postgresql.org/docs/9.2/static/sql-grant.html#SQL-GRANT-DESCRIPTION-OBJECTS (emphasis mine):

PostgreSQL grants default privileges on some types of objects to `PUBLIC`. No privileges are granted to `PUBLIC` by default on tables, columns, schemas or tablespaces. For other types, the default privileges granted to `PUBLIC` are as follows: `CONNECT` and `CREATE TEMP TABLE` for databases; `EXECUTE` privilege for functions; and `USAGE` privilege for languages. The object owner can, of course, `REVOKE` both default and expressly granted privileges. (For maximum security, issue the `REVOKE` in the same transaction that creates the object; then there is no window in which another user can use the object.) Also, these initial default privilege settings can be changed using the ALTER DEFAULT PRIVILEGES command.

In order to remove all privileges (including `CONNECT`) for all unspecified users on a database, use:

```
REVOKE ALL PRIVILEGES ON DATABASE <database> FROM public;
```

Why new user in PostgreSQL can connect to all databases?

Solution

Problem