pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedNew role can connect to all dbs with no grants
3+ messages / 2 participants
[nested] [flat]
* New role can connect to all dbs with no grants
@ 2012-03-20 21:02 Gordon Shannon <gordo169@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Gordon Shannon @ 2012-03-20 21:02 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: New role can connect to all dbs with no grants
@ 2012-03-20 22:14 Mark Lane <dartflight@gmail.com>
parent: Gordon Shannon <gordo169@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Mark Lane @ 2012-03-20 22:14 UTC (permalink / raw)
To: Gordon Shannon <gordo169@gmail.com>; +Cc: pgsql-admin
Gordon
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
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-tp5581221p558...
> 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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: New role can connect to all dbs with no grants
@ 2012-03-23 14:52 Gordon Shannon <gordo169@gmail.com>
parent: Gordon Shannon <gordo169@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Gordon Shannon @ 2012-03-23 14:52 UTC (permalink / raw)
To: pgsql-admin
That worked, thanks for the clarification.
Gordon
--
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-03-23 14:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-20 21:02 New role can connect to all dbs with no grants Gordon Shannon <gordo169@gmail.com>
2012-03-20 22:14 ` Mark Lane <dartflight@gmail.com>
2012-03-23 14:52 ` Gordon Shannon <gordo169@gmail.com>
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