Thread: Help with privilages please

Dear All<br /><br /> We are running pg v 7.4.1 and importantly the database has been converted from earlier versions
ofpg (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 />
andI get all the records!!!<br /><br /> If I create a **new** table though and then do the above, the permissions work
Iget a polite message telling me "no go".  This sounds to me like a problem with earlier compatibility.  Is there a way
Ican overcome this.  A simple dump/restore does not solve the problem.<br /><br /> Many thanks<br /> Hilary<br /><p>
HilaryForbes<br /> DMR Limited (UK registration 01134804) <br /> A DMR Information and Technology Group company (<a
eudora="autourl"href="http://www.dmr.co.uk/"><font color="#0000FF"><u>www.dmr.co.uk</u></font></a>) <br /> Direct tel
01689889950 Fax 01689 860330 <br /> DMR is a UK registered trade mark of DMR Limited<br />
**********************************************************

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.

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.

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

Tom<br /><br /> Thank you - I think that the underlying problem is that I was trying out<br /><br /> REVOKE ALL FROM
TABLEsuppliers FOR public;<br /><br /> then connect as hilary and I can still see the table rows.<br /><br /> I appear
tohave 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:0820/07/2006 -0400, Tom Lane wrote:<br /><br /><blockquote cite="cite" class="cite" type="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
loginas 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 />
                        regards,tom lane<br /><br /> ---------------------------(end of
broadcast)---------------------------<br/> TIP 6: explain analyze is your friend</blockquote><p> Hilary Forbes<br />
DMRLimited (UK registration 01134804) <br /> A DMR Information and Technology Group company (<a eudora="autourl"
href="http://www.dmr.co.uk/"><fontcolor="#0000FF"><u>www.dmr.co.uk</u></font></a>) <br /> Direct tel 01689 889950 Fax
01689860330 <br /> DMR is a UK registered trade mark of DMR Limited<br />
**********************************************************

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
>  **********************************************************