On Tue, Mar 20, 2012 at 5:02 PM, Gordon Shannon <gordo169@gmail.com> wrote:
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?
There is a public role that can connect to foo and all roles are part of it, you will need to