Thread: Curious case of the unstoppable user
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 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. Thanks -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 29/03/2011 19:44, Thom Brown wrote: > 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 > > 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. Does the "public" role still have privileges on the database? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 03/29/2011 11:44 AM, Thom Brown wrote: > 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 > > 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. > > Thanks > Not sure, but is user "meow" either the owner of the database "stuff" or member of a group that permits access to "stuff"? Cheers, Steve
On 29 March 2011 21:06, Raymond O'Donnell <rod@iol.ie> wrote:
> On 29/03/2011 19:44, Thom Brown wrote:
>>
>> 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
>>
>> 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.
>
>
> Does the "public" role still have privileges on the database?
The access privileges shown on both databases are identical: "=Tc/thom
thom=CTc/thom", and both owned by user "thom".
Both users meow and testrole show blank membership: "{}"
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Tue, Mar 29, 2011 at 07:44:51PM +0100, Thom Brown wrote:
> 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.
perhaps meow is superuser?
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
On 29 March 2011 21:28, hubert depesz lubaczewski <depesz@depesz.com> wrote:
> On Tue, Mar 29, 2011 at 07:44:51PM +0100, Thom Brown wrote:
>> 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.
>
> perhaps meow is superuser?
stuff=> \dg+
List of roles
Role name | Attributes | Member
of | Description
-----------+------------------------------------------------+-----------+-------------
meow | | {} |
testrole | Cannot login | {} |
thom | Superuser, Create role, Create DB, Replication | {} |
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 03/29/2011 01:32 PM, Thom Brown wrote:
> On 29 March 2011 21:28, hubert depesz lubaczewski<depesz@depesz.com> wrote:
>> On Tue, Mar 29, 2011 at 07:44:51PM +0100, Thom Brown wrote:
>>> 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.
>>
>> perhaps meow is superuser?
>
> stuff=> \dg+
> List of roles
> Role name | Attributes | Member
> of | Description
> -----------+------------------------------------------------+-----------+-------------
> meow | | {} |
> testrole | Cannot login | {} |
> thom | Superuser, Create role, Create DB, Replication | {} |
>
My guess is you have pg_hba.conf set up to use trust for the connection.
In your original example testrole failed because it is not a login role
not for permissions reasons. When \c to stuff as meow can you do \d?
--
Adrian Klaver
adrian.klaver@gmail.com
On 29 March 2011 21:51, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> On 03/29/2011 01:32 PM, Thom Brown wrote:
>>
>> On 29 March 2011 21:28, hubert depesz lubaczewski<depesz@depesz.com>
>> wrote:
>>>
>>> On Tue, Mar 29, 2011 at 07:44:51PM +0100, Thom Brown wrote:
>>>>
>>>> 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.
>>>
>>> perhaps meow is superuser?
>>
>> stuff=> \dg+
>> List of roles
>> Role name | Attributes | Member
>> of | Description
>>
>> -----------+------------------------------------------------+-----------+-------------
>> meow | | {} |
>> testrole | Cannot login | {} |
>> thom | Superuser, Create role, Create DB, Replication | {} |
>>
>
> My guess is you have pg_hba.conf set up to use trust for the connection. In
> your original example testrole failed because it is not a login role not for
> permissions reasons. When \c to stuff as meow can you do \d?
I can do \d, but it doesn't show anything since there's nothing in
there. But it does let me create a table, then see it using \d...
stuff=> \c stuff meow
You are now connected to database "stuff" as user "meow".
stuff=> \d
No relations found.
stuff=> create table test (id serial);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
serial column "test.id"
CREATE TABLE
stuff=> \d
List of relations
Schema | Name | Type | Owner
--------+-------------+----------+-------
public | test | table | meow
public | test_id_seq | sequence | meow
(2 rows)
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
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
On 29 March 2011 21:59, Guillaume Lelarge <guillaume@lelarge.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.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Le 29/03/2011 23:12, Thom Brown a écrit :
> On 29 March 2011 21:59, Guillaume Lelarge <guillaume@lelarge.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
On Mar 30, 2011, at 12:14 AM, Thom Brown wrote: > 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 You have created role and trying to login as role. Create user and then try. USER=ROLE+Login Privilege. Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.kumar@enterprisedb.com Blog:http://vibhork.blogspot.com