Thread: Removing {"="} privledges
We have a Cobalt RaQ4r that uses Postgres as the back-end of it's
management GUI. I created a Postgres user and gave the user privledges
(SELECT only) to the Cobalt database so I could read information into
a web app of our own. When I did this, it added the {"="} privledge to
that database privledges for all tables and I started having problems
with the GUI. I had to add the 'admin' (pre-defined Cobalt user) with
all privledges to get the problems in the GUI to go away. This is what
it made the privledges look like:
Access permissions for database "cobalt"
Relation | Access permissions
----------+--------------------
bw | {"=","admin=arwR"}
cpu | {"="}
defcon | {"="}
memory | {"="}
network | {"="}
quota | {"="}
service | {"="}
users | {"=","admin=arwR"}
vsite | {"=","admin=arwR"}
With these privledges, some parts of the Cobalt GUI are not operating
properly. I looked at another Cobalt RaQ we have where the problem
does not exist and no changes were made, it looks like this:
Access permissions for database "cobalt"
Relation | Access permissions
----------+--------------------
bw | {"=","admin=arwR"}
cpu |
defcon |
memory |
network |
quota |
service |
users | {"=","admin=arwR"}
vsite | {"=","admin=arwR"}
I want to get the privledges back on the first server to what is on
the second server. If I look in my phpPgAdmin, it shows a public user
with no privledges on all tables in the database, I am assuming this
is what is making the {"="} privledge show up. I tried 'revoke all on
defcon from public;', but the privledge remains for that table. Right
now, the privledges are like below, but I'd rather get them back to
the way it is shown in the last listing above, any help? As you can
tell, I'm pretty new at Postgres.
Access permissions for database "cobalt"
Relation | Access permissions
------------+--------------------------------
bw | {"=","admin=arwR"}
cpu | {"=","admin=arwR"}
defcon | {"=","admin=arwR"}
grpquota | {"=","admin=arwR"}
memory | {"=","admin=arwR"}
network | {"=","admin=arwR"}
quota | {"=","admin=arwR"}
service | {"=","admin=arwR"}
users | {"=","admin=arwR"}
usrquota | {"=","admin=arwR"}
vsite | {"=","admin=arwR"}
--
Thanks:)
Robert
"Robert Fitzpatrick" <robert@webtent.com> writes:
> We have a Cobalt RaQ4r that uses Postgres as the back-end of it's
> management GUI. I created a Postgres user and gave the user privledges
> (SELECT only) to the Cobalt database so I could read information into
> a web app of our own. When I did this, it added the {"="} privledge to
> that database privledges for all tables and I started having problems
> with the GUI.
The GUI is broken if it can't cope with that form of privilege display.
However, if you are not in a position to fix the GUI, you could try
setting pg_class.relacl to NULL:
update pg_class set relacl = NULL where relacl = '{"="}';
(I think that will work, but you'd be well advised to try it inside
a transaction block, and look to make sure the results seem sane
before committing...)
regards, tom lane
> The GUI is broken if it can't cope with that form of
> privilege display. However, if you are not in a position to
> fix the GUI, you could try setting pg_class.relacl to NULL:
>
> update pg_class set relacl = NULL where relacl = '{"="}';
>
> (I think that will work, but you'd be well advised to try it
> inside a transaction block, and look to make sure the results
> seem sane before committing...)
>
> regards, tom lane
Hello Tom:
Thanks for the response. Most of the GUI is fine, the Active Monitor is
the only problem, not monitoring. I gave the Cobalt pre-defined user
'admin' all rights to all the tables in the Cobalt database like
mentioned in my last messages and all works. I am just trying to get the
table privileges back to the default way Cobalt had them by looking at
the other RaQ machine.
When I look at the pg_class table, the only record that shows {"="} in
the relacl field is pg_shadow. What does this mean? However, the same
goes for the other RaQ that does not have the same problem, so I don't
think this is the fix I'm looking for. The basic problem is if I remove
'admin' privileges from one of the tables, it leaves this {"="} under
Access permissions. But doing a '\z' on the database on the good RaQ,
this does not show up.
--
Robert Fitzpatrick
On Sat, Oct 12, 2002 at 03:28:03PM -0400, Robert Fitzpatrick wrote:
> When I look at the pg_class table, the only record that shows {"="} in
> the relacl field is pg_shadow. What does this mean? However, the same
> goes for the other RaQ that does not have the same problem, so I don't
> think this is the fix I'm looking for. The basic problem is if I remove
> 'admin' privileges from one of the tables, it leaves this {"="} under
> Access permissions. But doing a '\z' on the database on the good RaQ,
> this does not show up.
I think the {"="} mean that nobody has permissions (except super users). If
there is nothing at all if means there are no restrictions (IIRC, though
this may vary between releases). If you want to allow anything for anybody
other than admin you can say "GRANT ALL ON <table> TO PUBLIC" though you may
have to check how that relates to other permissions you grant.
I'm not sure how to explain this. Maybe check the online docs.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.
Martijn van Oosterhout <kleptog@svana.org> writes:
> I think the {"="} mean that nobody has permissions (except super
> users).
It means that PUBLIC has no permissions.
> If there is nothing at all if means there are no restrictions
No, that means that the relacl field is NULL, which implies default
permissions, which are typically "all access for owner, no access
for anyone else".
I think this is better explained in the 7.3 GRANT manual page than
it was before. Robert is probably looking at 6.something docs,
which were pretty poor at explaining GRANT :-(
regards, tom lane
I have written a test that demonstrates a behavior that surprises me.
I store a foreign key with a deferable cascade-delete. While in a
transaction, I delete and then re-insert the referenced key. Since the key
value is back in the table, I would expect the delete to NOT cascade, but
apparently it does.
Is there another way to acheive this behavior?
-- here's the test
DROP TABLE a;
DROP TABLE b;
CREATE TABLE a (
pk INTEGER PRIMARY KEY,
this TEXT
);
CREATE TABLE b (
fk INTEGER REFERENCES a(pk) ON delete cascade DEFERRABLE INITIALLY
DEFERRED,
that TEXT
);
INSERT INTO a (pk, this) VALUES (1,'foo');
INSERT INTO a (pk, this) VALUES (2,'bar');
INSERT INTO b (fk,that) VALUES (1,'foofoo');
INSERT INTO b (fk,that) VALUES (2,'barbar');
SELECT * FROM a;
SELECT * FROM b;
BEGIN;
DELETE FROM a WHERE pk = 1;
INSERT INTO a (pk,this) VALUES (1,'foo-replacement');
SELECT * FROM a;
SELECT * FROM b;
COMMIT;
SELECT * FROM a;
SELECT * FROM b;
-- run with "psql -e -f defer_ri_test.sql defertest"
DROP TABLE a;
psql:defer_ri_test.sql:3: NOTICE: DROP TABLE implicitly drops referential
integrity trigger from table "b"
DROP
DROP TABLE b;
DROP
CREATE TABLE a (
pk INTEGER PRIMARY KEY,
this TEXT
);
psql:defer_ri_test.sql:9: NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index 'a_pkey' for table 'a'
CREATE
CREATE TABLE b (
fk INTEGER REFERENCES a(pk) ON delete cascade DEFERRABLE INITIALLY
DEFERRED,
that TEXT
);
psql:defer_ri_test.sql:14: NOTICE: CREATE TABLE will create implicit
trigger(s) for FOREIGN KEY check(s)
CREATE
INSERT INTO a (pk, this) VALUES (1,'foo');
INSERT 212767 1
INSERT INTO a (pk, this) VALUES (2,'bar');
INSERT 212768 1
INSERT INTO b (fk,that) VALUES (1,'foofoo');
INSERT 212769 1
INSERT INTO b (fk,that) VALUES (2,'barbar');
INSERT 212770 1
SELECT * FROM a;
pk | this
----+------
1 | foo
2 | bar
(2 rows)
SELECT * FROM b;
fk | that
----+--------
1 | foofoo
2 | barbar
(2 rows)
BEGIN;
BEGIN
DELETE FROM a WHERE pk = 1;
DELETE 1
INSERT INTO a (pk,this) VALUES (1,'foo-replacement');
INSERT 212771 1
SELECT * FROM a;
pk | this
----+-----------------
2 | bar
1 | foo-replacement
(2 rows)
SELECT * FROM b;
fk | that
----+--------
1 | foofoo
2 | barbar
(2 rows)
COMMIT;
COMMIT
SELECT * FROM a;
pk | this
----+-----------------
2 | bar
1 | foo-replacement
(2 rows)
SELECT * FROM b;
fk | that
----+--------
2 | barbar
(1 row)
On Mon, 14 Oct 2002, Michael Adler wrote:
>
> I have written a test that demonstrates a behavior that surprises me.
>
> I store a foreign key with a deferable cascade-delete. While in a
> transaction, I delete and then re-insert the referenced key. Since the key
> value is back in the table, I would expect the delete to NOT cascade, but
> apparently it does.
We've not been able to determine what the "correct" behavior for this is,
the spec is not entirely clear.
The question is what are matching rows and does re-inserting a row that
matches that key value invalidate the mark for deletion, because the SQL92
says basically (11.8):
5) If a <delete rule> is specified and a row of the referenced
table that has not previously been marked for deletion is marked
for deletion, then
Case:
a) If <match type> is not specified or if FULL is specified,
then
Case:
i) If the <delete rule> specifies CASCADE, then all matching
rows are marked for deletion.
It talks about matching rows and the time those are chosen, but we've
never managed a real consensus on what the spec intends. We've had
a couple of arguments about it in the past on -hackers.
> Is there another way to acheive this behavior?
Apart from making a new trigger, I don't think so currently. It's
not too hard to add a check to a copy of the trigger (new versions of no
action already do), but then you're not really using the foreign key
stuff, per se.