I created a login role "webuser" and was able to connect to server with this role name and password.
My table "user" is defined in database "mylinks" under "public" schema.
CREATE TABLE "user"
(
userid int4 NOT NULL DEFAULT nextval('user_userid_seq'::regclass),
email varchar(30) NOT NULL, -- login id
pwd varchar(30) NOT NULL, -- password
nickname varchar(15) NOT NULL, -- owner of a links page
CONSTRAINT user_key PRIMARY KEY (userid)
)
WITHOUT OIDS;
ALTER TABLE "user" OWNER TO postgres;
GRANT ALL ON TABLE "user" TO postgres;
COMMENT ON COLUMN "user".email IS 'login id';
COMMENT ON COLUMN "user".pwd IS 'password';
COMMENT ON COLUMN "user".nickname IS 'owner of a links page';
I defined the following function:
CREATE OR REPLACE FUNCTION user_list()
RETURN S SETOF "user" AS
$BODY$
select * from mylinks.public.user;
$BODY$
LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION user_list() OWNER TO postgres;
I am able to call up the store procedure user_list() in a web app; But I got
ERROR: 42501: permission denied on user_list().
I checked user_list() properties in pgAdmin window.
In privileges tab, there is a "public" role defined. I can not find where it is defined.
I tried add the following privilege in a SQL window.
GRANT EXECUTE ON FUNCTION user_list() TO postgres;
GRANT EXECUTE ON FUNCTION user_list() TO webuser;
Now, I am getting this error:
ERROR: 42501: permission denied for relation user
Anyone kn ows what is wrong here?
Additional quesitons:
1. What is the role of "public"? What is it used for?
2. When I define a function or other objects to be used by a login role, who should own it? superuser or login role? I tried to switch owner from "postgres" to "webuser", but that made no difference.
3. In pgAdmin, why did it show privileges as "x" for login role "postgres" and "webuser", even though I had already granted them "Exectue" permission? Is this a known bug?
Thanks,
Charles (with Postgresql 8.1/pgAdmin III)
Yahoo! FareChase - Search multiple travel sites in one click.
