Обсуждение: User rights
Hello. We are currently running PostgreSQL 7.0.3 for our Intranet database. We will switch to 7.1.2 in a while. However, I just experienced strange behaviours with our current installation. 1) password authentification We use this method for our local network, with this line in pg_hba.conf: host all <network-address> 255.255.255.0 password passwd The "passwd" file is generated from our shadow database. The problem is, if a user tries to authenticate and is not in the pg_shadow system table but *is* in the passwd file, the access will always fail. If the user appears in the pg_shadow table, no problems. Is this a normal behaviour ? 2) User re-creation By mistake, I deleted a user (me in fact) from the pg_shadow table. I was the owner of several tables. As a superuser, I re-created the user, with the same information, sysid included. But now, I am unable to manage or even do some SELECT on the table I was the owner. I checked the relowner field in the pg_class table, and it has the value of my sysid....The only particular thing about these tables I am unable to access is that they have some access rules, setup using GRANT statements. My question is : why the owner of a table have all the rights on it, or did I missed something ? Nicolas.
Nicolas Kowalski <Nicolas.Kowalski@imag.fr> writes:
> The problem is, if a user tries to authenticate and is not in the
> pg_shadow system table but *is* in the passwd file, the access will
> always fail. If the user appears in the pg_shadow table, no problems.
If you're not in pg_shadow you are not a postgres user. Bogus
entries in password files won't help you.
> By mistake, I deleted a user (me in fact) from the pg_shadow table. I
> was the owner of several tables. As a superuser, I re-created the user,
> with the same information, sysid included. But now, I am unable to
> manage or even do some SELECT on the table I was the owner. I checked
> the relowner field in the pg_class table, and it has the value of my
> sysid...
Hmm, that should work, if you're sure you got the right sysid. Also
check to make sure there is only one row in pg_shadow with that sysid.
regards, tom lane
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
On Wed, 13 Jun 2001, Tom Lane wrote:
TL> Nicolas Kowalski <Nicolas.Kowalski@imag.fr> writes:
TL> > The problem is, if a user tries to authenticate and is not in the
TL> > pg_shadow system table but *is* in the passwd file, the access will
TL> > always fail. If the user appears in the pg_shadow table, no problems.
TL>
TL> If you're not in pg_shadow you are not a postgres user. Bogus
TL> entries in password files won't help you.
Normal behaviour. Ok, I surrender.
TL>
TL> > By mistake, I deleted a user (me in fact) from the pg_shadow table. I
TL> > was the owner of several tables. As a superuser, I re-created the user,
TL> > with the same information, sysid included. But now, I am unable to
TL> > manage or even do some SELECT on the table I was the owner. I checked
TL> > the relowner field in the pg_class table, and it has the value of my
TL> > sysid...
TL>
TL> Hmm, that should work, if you're sure you got the right sysid. Also
TL> check to make sure there is only one row in pg_shadow with that sysid.
As superuser :
datavm=# SELECT usename,usesysid from pg_shadow where
usename='kowalski';
usename | usesysid
----------+----------
kowalski | 5519
(1 row)
datavm=# SELECT usename,usesysid from pg_shadow where usesysid='5519';
usename | usesysid
----------+----------
kowalski | 5519
(1 row)
datavm=# SELECT relowner from pg_class where relname='sys_machine';
relowner
----------
5519
(1 row)
datavm=# SELECT relowner from pg_class where relname='sys_equipment';
relowner
----------
5519
(1 row)
datavm=# \dz
...
sys_machine | {"=","admin=r"}
...
sys_equipment |
...
As user kowalski :
datavm=> SELECT * from sys_machine ;
ERROR: sys_machine: Permission denied.
datavm=> SELECT * from sys_equipment ;
id | description | delivery | ownership | location | invoice_id
| comme
nt
-----+------------------+------------+-----------+----------+------------+------
---
9 | IMPRIMANTE | 1996-11-12 | P | VERIMAG |
|
1 | LECTEUR DE BANDE | 1997-07-16 | P | VERIMAG |
|
204 | MAC PORTABLE | 2000-06-27 | P | VERIMAG |
...and so on.
What is wrong ?
Nicolas.
On Wed, 13 Jun 2001, Tom Lane wrote: TL> but 7.0 (and probably some releases before it) gets this wrong. The fix TL> is to explicitly GRANT ALL to yourself after the first GRANT to someone TL> else. Or update to 7.1, which gets it right. After the specified GRANT all goes fine of course. However I definitely have to upgrade. Thanks. Nicolas.
A little bit off-topic but is there in the near future LDAP support available? gr, Willem "Nicolas Kowalski" <Nicolas.Kowalski@imag.fr> wrote in message news:Pine.LNX.4.33.0106131508290.32370-100000@girose.imag.fr... > > Hello. > > We are currently running PostgreSQL 7.0.3 for our Intranet database. We > will switch to 7.1.2 in a while. > > However, I just experienced strange behaviours with our current > installation. > > 1) password authentification > > We use this method for our local network, with this line in pg_hba.conf: > > host all <network-address> 255.255.255.0 password passwd > > The "passwd" file is generated from our shadow database. > The problem is, if a user tries to authenticate and is not in the > pg_shadow system table but *is* in the passwd file, the access will > always fail. If the user appears in the pg_shadow table, no problems. > > Is this a normal behaviour ? > > > 2) User re-creation > > By mistake, I deleted a user (me in fact) from the pg_shadow table. I > was the owner of several tables. As a superuser, I re-created the user, > with the same information, sysid included. But now, I am unable to > manage or even do some SELECT on the table I was the owner. I checked > the relowner field in the pg_class table, and it has the value of my > sysid....The only particular thing about these tables I am unable to > access is that they have some access rules, setup using GRANT > statements. > > My question is : why the owner of a table have all the rights on it, or > did I missed something ? > > Nicolas. > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl