Why can a new user in PostgreSQL connect to all databases?

Question

I installed the PostgreSQL 9 database (migration from Oracle10g) and I am really confused about user / role management. When I create a new user using the SQL command, for example CREATE USER or CREATE ROLE , or using the Navicat tool, the created user can see all the databases! He really can connect them! Although he cannot select any data from the table, he can see objects and sequences of tables and so on. I tried to cancel the connection with the privilege, but no effect. I expected that the new user would not receive any privileges and would not 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 (my attention):

PostgreSQL provides default privileges for some types of PUBLIC objects. By default, tables, columns, schemas, or table spaces do not have PUBLIC privileges. For other types, the default privileges granted by PUBLIC are: CONNECT and CREATE TEMP TABLE for databases ; EXECUTE privilege for functions; and USAGE privilege for languages. The owner of the object can, of course, REVOKE by default and explicitly grant privileges. (For maximum security, enter REVOKE in the same transaction that creates the object, and then there is no window in which another user can use this object.) In addition, these initial default privilege settings can be changed using the ALTER DEFAULT PRIVILEGES command .

To remove all privileges (including CONNECT ) for all unspecified users in the database, use:

 REVOKE ALL PRIVILEGES ON DATABASE <database> FROM public; 

See also:

• PostgreSQL: View Database Connection Permissions
• http://wiki.postgresql.org/wiki/Shared_Database_Hosting

Answer 2

You may also need to modify the pg_hba.conf . By default, the local installation does not perform authorization checks.

Answer 3

You must use GRANT and / or REVOKE to determine privileges for a user or role. You can also use the following functions to find out if a user has certain rights to a table, database, etc.