Why new user in PostgreSQL can connect to all data

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.

Answer 1

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;

See also:

• PostgreSQL: View database connect permissions
• http://wiki.postgresql.org/wiki/Shared_Database_Hosting

Answer 2

You probably also need to modify the pg_hba.conf file. By default, a local installation doesn't do authorization checks.

Answer 3

You must use GRANT and/or REVOKE to define the privileges for the user or role. You can also use the following link functions to see if a user has a specific privilege on a table, database, etc...