Gordon On Tue, Mar 20, 2012 at 5:02 PM, Gordon Shannon <gordo...@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 REVOKE ALL ON DATABASE foo FROM public; > 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >
