PostgreSQL Newbies - confused by role, privileges, and permission issues

Author

confused by role, privileges, and permission issues

Charles Bai

2005-11-22, 3:24 am

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()
RETURNS 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 knows 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.

Sponsored Links