pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUser rights
8+ messages / 5 participants
[nested] [flat]
* User rights
@ 2001-06-13 13:33 Nicolas Kowalski <Nicolas.Kowalski@imag.fr>
0 siblings, 2 replies; 8+ messages in thread
From: Nicolas Kowalski @ 2001-06-13 13:33 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: User rights
@ 2001-06-13 14:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nicolas Kowalski <Nicolas.Kowalski@imag.fr>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2001-06-13 14:24 UTC (permalink / raw)
To: Nicolas Kowalski <Nicolas.Kowalski@imag.fr>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: User rights
@ 2001-06-13 15:56 Nicolas Kowalski <Nicolas.Kowalski@imag.fr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Nicolas Kowalski @ 2001-06-13 15:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: User rights
@ 2001-06-13 16:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nicolas Kowalski <Nicolas.Kowalski@imag.fr>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2001-06-13 16:12 UTC (permalink / raw)
To: Nicolas Kowalski <Nicolas.Kowalski@imag.fr>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: User rights
@ 2001-06-13 16:26 Nicolas Kowalski <Nicolas.Kowalski@imag.fr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Nicolas Kowalski @ 2001-06-13 16:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: User rights
@ 2001-06-17 12:51 W. van den Akker <wvdakker@soneramail.nl>
parent: Nicolas Kowalski <Nicolas.Kowalski@imag.fr>
1 sibling, 0 replies; 8+ messages in thread
From: W. van den Akker @ 2001-06-17 12:51 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* User rights
@ 2007-05-09 19:43 ebmb <ebmb@hotmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: ebmb @ 2007-05-09 19:43 UTC (permalink / raw)
To: pgsql-admin
Hello all,
I'm a MySQL user and trying to migrate some applications to
PostgreSQL 8.1.9. My first question is about Postgre administration...
I would like to know if is possible to revoke users access to
commands like "\l, \du, \dn". In MySQL, limited users cannot see other
users (like root, for example) and other databases (like mysql, for
example). Is it possible in Postgre?
Thanks in advance for any help!
Cheers,
EBMB.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: User rights
@ 2007-05-12 22:05 Jim C. Nasby <decibel@decibel.org>
parent: ebmb <ebmb@hotmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jim C. Nasby @ 2007-05-12 22:05 UTC (permalink / raw)
To: ebmb <ebmb@hotmail.com>; +Cc: pgsql-admin
On Wed, May 09, 2007 at 12:43:55PM -0700, ebmb wrote:
> Hello all,
> I'm a MySQL user and trying to migrate some applications to
> PostgreSQL 8.1.9. My first question is about Postgre administration...
>
> I would like to know if is possible to revoke users access to
> commands like "\l, \du, \dn". In MySQL, limited users cannot see other
> users (like root, for example) and other databases (like mysql, for
> example). Is it possible in Postgre?
Take a look at the setting db_user_namespace; it might help. You *might*
be able to get away with revoking select on pg_database, though you'd
have to do it in every database. Of course you'll want to fully
understand pg_hba.conf as well.
When it comes to schemas, I think you're pretty much out of luck.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-05-12 22:05 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-13 13:33 User rights Nicolas Kowalski <Nicolas.Kowalski@imag.fr>
2001-06-13 14:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-13 15:56 ` Nicolas Kowalski <Nicolas.Kowalski@imag.fr>
2001-06-13 16:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-13 16:26 ` Nicolas Kowalski <Nicolas.Kowalski@imag.fr>
2001-06-17 12:51 ` W. van den Akker <wvdakker@soneramail.nl>
2007-05-09 19:43 User rights ebmb <ebmb@hotmail.com>
2007-05-12 22:05 ` Jim C. Nasby <decibel@decibel.org>
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