Re: Curious case of the unstoppable user

Le 29/03/2011 23:12, Thom Brown a écrit :
> On 29 March 2011 21:59, Guillaume Lelarge <guillaume(at)lelarge(dot)info> wrote:
>> 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
>
> I altered the role with NOLOGIN, then tried to connect as that user
> again, and it doesn't let the user in, so you're correct.
>
> Thanks for the explanation. I take it the access priviledges field
> shown in \l+ reveals this? It must be the line that begins with =. I
> need to familiarise myself with it more.
>

it doesn't tell you about the LOGIN attribute, but it tells you about
the priviledges, and among them, the CONNECT one. When the line begins
with =, it's the priviledges for public. If you have a user name before
the equal sign, then it's the priviledges for this user. For example:

testdb=# grant connect on database testdb to testrole;
GRANT
testdb=# \l+
Name | Access privileges
--------------+-------------------------
b1 |
testdb | guillaume=CTc/guillaume+
testrole=c/guillaume

On b1, anyone with the LOGIN attribute can connect. On testdb, only
guillaume and testrole can connect, but only guillaume can create objects.

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