confused by role, privileges, and permission issues

From: Charles Bai <charlesbaiusa(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: confused by role, privileges, and permission issues
Date: 2005-11-22 03:13:00
Message-ID: 20051122031300.7771.qmail@web31906.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Browse pgsql-novice by date

  From Date Subject
Next Message Info 2005-11-22 12:52:25 Problem Upgrading from 8.0 to 8.1 (WinXP) ...not still solved!!
Previous Message Bill Dika 2005-11-22 01:15:34 Re: Application using PostgreSQL as a back end (experienced programmers please)