| From: | Jukka Inkeri <jukka(dot)inkeri(at)awot(dot)fi> | 
|---|---|
| To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> | 
| Subject: | Permission denied for relation pg_database , one role has problem | 
| Date: | 2012-08-25 09:06:17 | 
| Message-ID: | 50389589.10108@awot.fi | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
And many others errors when xsome user connect to database x, schema 
xxx. PG 8.4.
Problems started about same time as search_path has SET.
search_path has reset, but did'nt help.
ALTER DATABASE x SET search_path=some, public;
-- xxx wasn't in that list
ALTER DATABASE x RESET search_path;  - has done
Only one group has this priviledge problem, xgroup.
pg_log after tried to connect:
2012-08-25 11:39:36 EEST ERROR:  permission denied for relation pg_database
2012-08-25 11:39:36 EEST STATEMENT:  SET DateStyle=ISO;
	SELECT oid, pg_encoding_to_char(encoding) AS encoding, datlastsysoid
	  FROM pg_database WHERE oid = 16395
role xuser has done:
CREATE ROLE xuser LOGIN
   ENCRYPTED PASSWORD 'some...'
   NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
ALTER ROLE xuser SET search_path=xxx;
GRANT xgroup TO xuser;
And xgroup has done:
CREATE ROLE xgroup
   NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
COMMENT ON ROLE xgroup IS 'X Group';
Schema xxx has done:
CREATE SCHEMA xxx
   AUTHORIZATION postgres;
GRANT ALL ON SCHEMA xxx TO postgres;
GRANT USAGE ON SCHEMA xxx TO xgroup;
Schema xxx include some views to public.tables and some own tables.
Views:
CREATE OR REPLACE VIEW
    SELECT * FROM product;
ALTER TABLE xxx.product
   OWNER TO postgres;
GRANT ALL ON TABLE xxx.product TO postgres;
GRANT SELECT ON TABLE xxx.product TO xgroup;
Own tables:
CREATE TABLE xxx.mytable
(
   stamp timestamp without time zone NOT NULL
)
WITH (
   OIDS=FALSE
);
ALTER TABLE xxx.mytable
   OWNER TO postgres;
GRANT ALL ON TABLE xxx.mytable TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE xxx.mytable TO xgroup;
Something interesting happened when search_path has set ?
Is this some kind of catalog priviledge problem ?
-jukka-
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Craig Ringer | 2012-08-25 09:15:47 | Re: Windows SIngle Sign On - LINUX Server | 
| Previous Message | Craig Ringer | 2012-08-25 08:41:31 | Re: FETCH in subqueries or CTEs |