pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nicolas Kowalski <Nicolas.Kowalski@imag.fr>
Cc: pgsql-admin@postgresql.org
Subject: Re: User rights
Date: Wed, 13 Jun 2001 12:12:18 -0400
Message-ID: <26812.992448738@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.33.0106131747550.32676-100000@girose.imag.fr>
References: <Pine.LNX.4.33.0106131747550.32676-100000@girose.imag.fr>

Nicolas Kowalski <Nicolas.Kowalski@imag.fr> writes:
> datavm=# \z
> ...
> sys_machine                | {"=","admin=r"}
> ...
> sys_equipment              |

> As user kowalski :

> datavm=> SELECT * from sys_machine ;
> ERROR:  sys_machine: Permission denied.

> datavm=> SELECT * from sys_equipment ;
> [ works ]

> What is wrong ?

sys_machine has an explicitly specified GRANT list: no rights to public,
read-only rights to admin, and (by implication) no rights to anyone
else, including the owner.

sys_equipment has the default (null) access permission list, which
happens to be interpreted as all rights to owner and no rights to anyone
else.

So things are operating as expected, at least given the current state
of the permissions.  I think your problem is unrelated to your
drop/recreate user fiasco.  You have been bit by a bug in older
versions of PG: when you do a GRANT on a table that initially has a
default access permission list, the system forgets to include the "owner
has all rights" part of the default permissions into the now-explicit
permission list.  That is, you probably got sys_machine into its current
state by saying (only) GRANT SELECT ON sys_machine TO admin.  That
should have left you with permissions like

	sys_machine      | {"=","kowalski=arwR","admin=r"}

but 7.0 (and probably some releases before it) gets this wrong.  The fix
is to explicitly GRANT ALL to yourself after the first GRANT to someone
else.  Or update to 7.1, which gets it right.

			regards, tom lane



view thread (8+ messages)  latest in thread

Message-ID: <26812.992448738@sss.pgh.pa.us>
Permalink:  ../26812.992448738@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/26812.992448738@sss.pgh.pa.us

 · 

reply

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: tgl@sss.pgh.pa.us, Nicolas.Kowalski@imag.fr
  Subject: Re: User rights
  In-Reply-To: <26812.992448738@sss.pgh.pa.us>

* 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