From: Gordon Shannon <gordo169@gmail.com>
To: pgsql-admin@postgresql.org
Subject: New role can connect to all dbs with no grants
Date: Tue, 20 Mar 2012 14:02:31 -0700 (PDT)
Message-ID: <1332277351350-5581221.post@n5.nabble.com> (raw)
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-tp5581221p558...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gordo169@gmail.com
Subject: Re: New role can connect to all dbs with no grants
In-Reply-To: <1332277351350-5581221.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox