Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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.  

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group