From: | "Andreas Neumann" <a(dot)neumann(at)carto(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Constraints involving a system table |
Date: | 2007-10-24 14:14:34 |
Message-ID: | 44474.193.246.86.39.1193235274.squirrel@webmail.customers.iirc.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I would like to create a constraint that involves the system table.
CREATE TABLE metadata_tables
(
schemaname text REFERENCES pg_namespace(nspname) ON DELETE CASCADE
CONSTRAINT valid_schema CHECK (schemaname != 'public' AND schemaname !=
'information_schema' AND schemaname NOT LIKE 'pg_%'),
tablename text,
source text
) WITH (OIDS=FALSE)
;
Basically, I only want to allow that the user can use schemanames present
in the current database as input in the column schemaname.
When I try to create this table, I get an error that it is a system table.
ERROR: permission denied: "pg_namespace" is a system catalog
However, when looking at the system table definition, it has "GRANT SELECT
ON TABLE pg_namespace TO public;" - so everybody should be able to query a
system table.
Why is this not working? What other privileges besides "SELECT" would one
need to define a constraint?
Thanks for any hint,
Andreas
--
Andreas Neumann
Böschacherstrasse 6, CH-8624 Grüt/Gossau, Switzerland
Email: a(dot)neumann(at)carto(dot)net, Web:
* http://www.carto.net/ (Carto and SVG resources)
* http://www.carto.net/neumann/ (personal page)
* http://www.svgopen.org/ (SVG Open Conference)
* http://www.geofoto.ch/ (Georeferenced Photos of Switzerland)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-10-24 14:20:29 | Re: 8.3b1 in production? |
Previous Message | rihad | 2007-10-24 13:59:20 | 8.3b1 in production? |