Skip site navigation (1) Skip section navigation (2)

Re: New role can connect to all dbs with no grants

From: Mark Lane <dartflight(at)gmail(dot)com>
To: Gordon Shannon <gordo169(at)gmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: New role can connect to all dbs with no grants
Date: 2012-03-20 22:14:35
Message-ID: CADb5pWmMSa-yqhyKRXfJORQnAbSLRAg+KRD4K=OJpUGKwtHjmw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
Gordon

On Tue, Mar 20, 2012 at 5:02 PM, Gordon Shannon <gordo169(at)gmail(dot)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(at)toolbox:acct] 14:50:23> create database foo owner postgres;
> CREATE DATABASE
> [admin(at)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(at)toolbox:foo] 14:51:15> create table stuff(id int);
> CREATE TABLE
> [admin(at)toolbox:foo] 14:51:31> create role bob login password 'secret';
> CREATE ROLE
> [admin(at)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(at)toolbox:foo] 14:52:14> \dt
>       List of relations
>  Schema | Name  | Type  | Owner
> --------+-------+-------+-------
>  public | stuff | table | admin
> (1 row)
>
> [bob(at)toolbox:foo] 14:52:16> \d stuff
>     Table "public.stuff"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  id     | integer |
>
> [bob(at)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

In response to

pgsql-admin by date

Next:From: Imre OolbergDate: 2012-03-20 23:42:53
Subject: deciding between amd and intel processor
Previous:From: Gordon ShannonDate: 2012-03-20 21:02:31
Subject: New role can connect to all dbs with no grants

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group