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