[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

New role can connect to all dbs with no grants



Hi.  I'm confused about how to restrict databases from roles.  When I create
a new database, I thought I would have to grant connect to a role in order
for that role to connect to it and see its objects.

But...

[admin@toolbox:acct] 14:50:23> create database foo owner postgres;
CREATE DATABASE
[admin@toolbox:acct] 14:50:34> \c foo
psql (9.0.4, server 9.1.1)
WARNING: psql version 9.0, server version 9.1.
         Some psql features might not work.
You are now connected to database "foo".
[admin@toolbox:foo] 14:51:15> create table stuff(id int);
CREATE TABLE
[admin@toolbox:foo] 14:51:31> create role bob login password 'secret';
CREATE ROLE
[admin@toolbox:foo] 14:51:57> \c foo bob
Password for user bob: 
psql (9.0.4, server 9.1.1)
WARNING: psql version 9.0, server version 9.1.
         Some psql features might not work.
You are now connected to database "foo" as user "bob".
[I did not grant access to bob, why can he connect? He can also see the
table metadata...]
[bob@toolbox:foo] 14:52:14> \dt
       List of relations
 Schema | Name  | Type  | Owner 
--------+-------+-------+-------
 public | stuff | table | admin
(1 row)

[bob@toolbox:foo] 14:52:16> \d stuff
     Table "public.stuff"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 

[bob@toolbox:foo] 14:53:38> select * from stuff;
ERROR:  permission denied for relation stuff


I expected that I'd have to do a GRANT CONNECT for this to occur.
Also, I tried REVOKE CONNECT ON DATABASE foo FROM bob, and he can still
connect as before.

What am I missing?

Thanks!
Gordon (9.1.1/Linux)


--
View this message in context: http://postgresql.1045698.n5.nabble.com/New-role-can-connect-to-all-dbs-with-no-grants-tp5581221p5581221.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


[Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

Powered by Linux