11

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.

peterh
  • 11,875
  • 18
  • 85
  • 108
veselej
  • 353
  • 2
  • 4
  • 12
  • 2
    How were you trying to revoke CONNECT privileges? – Mike Sherrill 'Cat Recall' Jul 30 '11 at 16:01
  • If you are using pgAdmin, you can look at the user's login role and rights. In your sever, expand Login Roles, right-click on the Login Role in question and check Roles privileges and Role memberships. Then match those privileges and memberships up against the DBs and DB objects in question. If a DB or object has a role granted, and your user's login has that role, that explains access. If the user has checkboxes clicked on the "Role privileges" tab, that explains other rights. – atrain Jul 31 '11 at 17:52
  • So I started pgAdmin and watched on the role tab on my databases (right click on database, then permissionsI thing). There was a "public" role joined with all my databases. So I remove that. And now it works!!! Thank you a lot because I was a scared a little :-) – veselej Aug 01 '11 at 06:09
  • What's your objective? Are you trying to make the schema secret? are you trying to have a 'deny login from all' default? it's not totally clear what you really intend. – SingleNegationElimination Aug 02 '11 at 17:37

2 Answers2

13

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:

Community
  • 1
  • 1
Lekensteyn
  • 64,486
  • 22
  • 159
  • 192
  • Thanks, great, except, “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” – this sounded like good advice, but doesn't work for databases because (quoting error message) “CREATE DATABASE cannot run inside a transaction block”. So there is no way to close the window for someone to use a new database? – njlarsson Mar 28 '17 at 14:07
1

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

Daniel Lyons
  • 22,421
  • 2
  • 50
  • 77