Why new user in PostgreSQL can connect to all databases?
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.
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 toPUBLIC
by default on tables, columns, schemas or tablespaces. For other types, the default privileges granted toPUBLIC
are as follows:CONNECT
andCREATE TEMP TABLE
for databases;EXECUTE
privilege for functions; andUSAGE
privilege for languages. The object owner can, of course,REVOKE
both default and expressly granted privileges. (For maximum security, issue theREVOKE
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
You probably also need to modify the pg_hba.conf
file. By default, a local installation doesn't do authorization checks.
精彩评论