Re: Curious case of the unstoppable user

Le 29/03/2011 20:44, Thom Brown a écrit :
> Hi all,
>
> I've just set up a test user, revoked all access from them to a
> database, then tried to connect to that database and it let me in.
> When I try it all from scratch, it works correctly.
>
> Here's the set running correctly:
>
> postgres=# CREATE DATABASE testdb;
> CREATE DATABASE
> postgres=# CREATE ROLE testrole;
> CREATE ROLE
> postgres=# REVOKE ALL ON DATABASE testdb FROM testrole CASCADE;
> REVOKE
> postgres=# \c testdb testrole
> FATAL: role "testrole" is not permitted to log in
> Previous connection kept
>

This is because you created a role without the login attribute. IOW, it
has nothing to do with your REVOKE statement. Proof:

postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# CREATE ROLE testrole;
CREATE ROLE
postgres=# \c testdb testrole
FATAL: role "testrole" is not permitted to log in
Previous connection kept

> But now if I try something similar with an existing user and existing
> database, it doesn't work:
>
> postgres=# REVOKE ALL ON DATABASE stuff FROM meow CASCADE;
> REVOKE
> postgres=# \c stuff meow
> You are now connected to database "stuff" as user "meow".
>
> So, I'm overlooking something. Could someone tell me what it is? I
> bet it's something obvious. I'm using 9.1dev if it's relevant.
>

Yeah. You probably created meow as a user, with is a role with the login
attribute. The \dg+ metacommand tells us exactly that:

> stuff=> \dg+
> List of roles
> Role name | Attributes | Member
> of | Description
> -----------+------------------------------------------------+-----------+-------------
> meow | | {} |
> testrole | Cannot login | {} |
> thom | Superuser, Create role, Create DB, Replication | {} |

So:

postgres=# CREATE USER meow;
CREATE ROLE
postgres=# \c testdb meow
You are now connected to database "testdb" as user "meow".

Now, you not only need to revoke connect permission to meow. You need to
do it to public too:

testdb=> \c testdb postgres
You are now connected to database "testdb" as user "postgres".
testdb=# REVOKE ALL ON DATABASE testdb FROM meow CASCADE;
REVOKE
testdb=# \c testdb meow
You are now connected to database "testdb" as user "meow".

Same result as you. Now, revoke connect permission to public:

testdb=> \c testdb postgres
You are now connected to database "testdb" as user "postgres".
testdb=# REVOKE ALL ON DATABASE testdb FROM PUBLIC CASCADE;
REVOKE
testdb=# \c testdb meow
FATAL: permission denied for database "testdb"
DETAIL: User does not have CONNECT privilege.
Previous connection kept

Cheers.

--
Guillaume
http://www.postgresql.fr
http://dalibo.com