pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with privilages please
6+ messages / 5 participants
[nested] [flat]
* Help with privilages please
@ 2006-07-20 14:01 Hilary Forbes <hforbes@dmr.co.uk>
2006-07-20 20:17 ` Re: Help with privilages please Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-07-20 20:28 ` Re: Help with privilages please Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-20 22:08 ` Re: Help with privilages please Tom Lane <tgl@sss.pgh.pa.us>
2006-07-21 12:05 ` Re: Help with privilages please Hilary Forbes <hforbes@dmr.co.uk>
0 siblings, 4 replies; 6+ messages in thread
From: Hilary Forbes @ 2006-07-20 14:01 UTC (permalink / raw)
To: pgsql-sql
<html>
Dear All<br><br>
We are running pg v 7.4.1 and importantly the database has been converted
from earlier versions of pg (6.5 I seem to recall).<br><br>
I have an existing table suppliers and I have created a new user
'hilary'<br><br>
REVOKE ALL on TABLE suppliers FROM hilary;<br><br>
now login as hilary<br>
SELECT * from suppliers;<br>
and I get all the records!!!<br><br>
If I create a **new** table though and then do the above, the permissions
work I get a polite message telling me "no go". This
sounds to me like a problem with earlier compatibility. Is there a
way I can overcome this. A simple dump/restore does not solve the
problem.<br><br>
Many thanks<br>
Hilary<br>
<x-sigsep><p></x-sigsep>
Hilary Forbes<br>
DMR Limited (UK registration 01134804) <br>
A DMR Information and Technology Group company
(<a href="http://www.dmr.co.uk/"; eudora="autourl"><font color="#0000FF"><u>www.dmr.co.uk</a></u></font>)
<br>
Direct tel 01689 889950 Fax 01689 860330 <br>
DMR is a UK registered trade mark of DMR Limited<br>
**********************************************************</html>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with privilages please
2006-07-20 14:01 Help with privilages please Hilary Forbes <hforbes@dmr.co.uk>
@ 2006-07-20 20:17 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
3 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2006-07-20 20:17 UTC (permalink / raw)
To: Hilary Forbes <hforbes@dmr.co.uk>; +Cc: pgsql-sql
On Thu, 20 Jul 2006, Hilary Forbes wrote:
> Dear All
>
> We are running pg v 7.4.1 and importantly the database has been
> converted from earlier versions of pg (6.5 I seem to recall).
>
> I have an existing table suppliers and I have created a new user 'hilary'
>
> REVOKE ALL on TABLE suppliers FROM hilary;
>
> now login as hilary
> SELECT * from suppliers;
> and I get all the records!!!
This probably means that "public" also has rights on suppliers (and thus,
the user still has access through the public permissions). You can
probably get around this by revoking the public rights and granting rights
explicitly to the users that should have rights.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with privilages please
2006-07-20 14:01 Help with privilages please Hilary Forbes <hforbes@dmr.co.uk>
@ 2006-07-20 20:28 ` Richard Broersma Jr <rabroersma@yahoo.com>
3 siblings, 0 replies; 6+ messages in thread
From: Richard Broersma Jr @ 2006-07-20 20:28 UTC (permalink / raw)
To: Hilary Forbes <hforbes@dmr.co.uk>; pgsql-sql
REVOKE ALL on TABLE suppliers FROM hilary;
now login as hilary
SELECT * from suppliers;
and I get all the records!!!
If I create a **new** table though and then do the above, the permissionswork I get a polite
message telling me "no go". Thissounds to me like a problem with earlier compatibility. Is there
away I can overcome this. A simple dump/restore does not solve theproblem.
You might also have to revoke all from public:
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with privilages please
2006-07-20 14:01 Help with privilages please Hilary Forbes <hforbes@dmr.co.uk>
@ 2006-07-20 22:08 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2006-07-20 22:08 UTC (permalink / raw)
To: Hilary Forbes <hforbes@dmr.co.uk>; +Cc: pgsql-sql
Hilary Forbes <hforbes@dmr.co.uk> writes:
> I have an existing table suppliers and I have created a new user
> 'hilary'<br><br>
> REVOKE ALL on TABLE suppliers FROM hilary;<br><br>
> now login as hilary<br>
> SELECT * from suppliers;<br>
> and I get all the records!!!<br><br>
Most likely there's been a grant of (at least) select privilege to PUBLIC.
You'll need to revoke that if you don't want every user to have that
privilege implicitly.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with privilages please
2006-07-20 14:01 Help with privilages please Hilary Forbes <hforbes@dmr.co.uk>
@ 2006-07-21 12:05 ` Hilary Forbes <hforbes@dmr.co.uk>
2006-07-26 19:29 ` Re: Help with privilages please Andrew Hammond <andrew.george.hammond@gmail.com>
3 siblings, 1 reply; 6+ messages in thread
From: Hilary Forbes @ 2006-07-21 12:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
<html>
Tom<br><br>
Thank you - I think that the underlying problem is that I was trying
out<br><br>
REVOKE ALL FROM TABLE suppliers FOR public;<br><br>
then connect as hilary and I can still see the table rows.<br><br>
I appear to have to revoke each type eg<br>
REVOKE SELECT FROM TABLE suppliers FOR public;<br>
etc and then the code works.<br><br>
Is this a known bug in this version (7.4.1)?<br><br>
Thanks<br>
Hilary<br><br>
<br>
At 18:08 20/07/2006 -0400, Tom Lane wrote:<br><br>
<blockquote type=cite class=cite cite>Hilary Forbes
<hforbes@dmr.co.uk> writes:<br>
> I have an existing table suppliers and I have created a new
user<br>
> 'hilary'<br><br><br>
> REVOKE ALL on TABLE suppliers FROM hilary;<br><br><br>
> now login as hilary<br><br>
> SELECT * from suppliers;<br><br>
> and I get all the records!!!<br><br><br><br>
Most likely there's been a grant of (at least) select privilege to
PUBLIC.<br>
You'll need to revoke that if you don't want every user to have
that<br>
privilege implicitly.<br><br>
<x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>regards,
tom lane<br><br>
---------------------------(end of
broadcast)---------------------------<br>
TIP 6: explain analyze is your friend</blockquote>
<x-sigsep><p></x-sigsep>
Hilary Forbes<br>
DMR Limited (UK registration 01134804) <br>
A DMR Information and Technology Group company
(<a href="http://www.dmr.co.uk/"; eudora="autourl"><font color="#0000FF"><u>www.dmr.co.uk</a></u></font>)
<br>
Direct tel 01689 889950 Fax 01689 860330 <br>
DMR is a UK registered trade mark of DMR Limited<br>
**********************************************************</html>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with privilages please
2006-07-20 14:01 Help with privilages please Hilary Forbes <hforbes@dmr.co.uk>
2006-07-21 12:05 ` Re: Help with privilages please Hilary Forbes <hforbes@dmr.co.uk>
@ 2006-07-26 19:29 ` Andrew Hammond <andrew.george.hammond@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Andrew Hammond @ 2006-07-26 19:29 UTC (permalink / raw)
To: pgsql-sql
7.4.1 is quite old and has a number of serious known bugs. I'd suggest
you either upgrade to 8.1.4 (current) or, if you can't do that, at
least upgrade to 7.4.13 (latest 7.4) immediately.
Hilary Forbes wrote:
> Tom
>
> Thank you - I think that the underlying problem is that I was trying out
>
> REVOKE ALL FROM TABLE suppliers FOR public;
>
> then connect as hilary and I can still see the table rows.
>
> I appear to have to revoke each type eg
> REVOKE SELECT FROM TABLE suppliers FOR public;
> etc and then the code works.
>
> Is this a known bug in this version (7.4.1)?
>
> Thanks
> Hilary
>
>
> At 18:08 20/07/2006 -0400, Tom Lane wrote:
>
> Hilary Forbes <hforbes@dmr.co.uk> writes:
> > I have an existing table suppliers and I have created a new user
> > 'hilary'<br><br>
> > REVOKE ALL on TABLE suppliers FROM hilary;<br><br>
> > now login as hilary<br>
> > SELECT * from suppliers;<br>
> > and I get all the records!!!<br><br>
>
> Most likely there's been a grant of (at least) select privilege to PUBLIC.
> You'll need to revoke that if you don't want every user to have that
> privilege implicitly.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
> Hilary Forbes
> DMR Limited (UK registration 01134804)
> A DMR Information and Technology Group company (_www.dmr.co.uk_)
> Direct tel 01689 889950 Fax 01689 860330
> DMR is a UK registered trade mark of DMR Limited
> **********************************************************
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-07-26 19:29 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-20 14:01 Help with privilages please Hilary Forbes <hforbes@dmr.co.uk>
2006-07-20 20:17 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-07-20 20:28 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-20 22:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-21 12:05 ` Hilary Forbes <hforbes@dmr.co.uk>
2006-07-26 19:29 ` Andrew Hammond <andrew.george.hammond@gmail.com>
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