Postgres Pro
Downloads
Home   >   mailing lists

Thread: FW: GRANT question

FW: GRANT question

From
Együd Csaba (Freemail)
Date:

-----Original Message-----
From: Együd Csaba (Freemail) [mailto:csegyud@freemail.hu]
Sent: Saturday, November 26, 2005 9:36 PM
To: 'Michael Fuhr'
Subject: RE: [GENERAL] GRANT question

OK, here you are.
---------------------------
moson.gdb=> SELECT oid::regprocedure FROM pg_proc WHERE proname ILIKE
'%eden%';
                        oid
----------------------------------------------------
 public.triggerfunc_eden_anamnesis_after_insert()
 public.triggerfunc_eden_anamnesis_before_update()
 public.triggerfunc_eden_cal_att_after_update()
 public.triggerfunc_eden_common_before_update()
 public.triggerfunc_eden_countries_before_delete()
 public.triggerfunc_eden_countries_before_update()
 public.triggerfunc_eden_insurers_before_delete()
 public.triggerfunc_eden_insurers_before_update()
 public.triggerfunc_eden_partners_before_delete()
 public.triggerfunc_eden_partners_before_update()
 public.triggerfunc_eden_patientes_after_insert()
 public.triggerfunc_eden_patientes_before_delete()
 public.triggerfunc_eden_patientes_before_insert()
 public.triggerfunc_eden_patientes_before_update()
 public.triggerfunc_eden_positions_before_delete()
 public.triggerfunc_eden_positions_before_update()
 public.triggerfunc_eden_treatments_before_update()
 public.triggerfunc_eden_user_before_delete()
 public.triggerfunc_eden_user_before_update()
 public.triggerfunc_eden_worksheets_before_insert()
 public.triggerfunc_eden_worksheets_before_update()
 public.triggerfunc_eden_xrays_before_insert()
 public.triggerfunc_eden_xrays_before_update()
 public.edenproc_revoke_grants(integer)
 public.edenproc_admin(integer)
 public.edenproc_admin(text)
 public.edenproc_admin_only(integer)
 public.edenproc_admin_only(text)
 public.edenproc_permission_matrix()
 public.edenproc_revoke_grants(text)
 public.edenproc_set_grants(integer)
 public.edenproc_set_grants(text)
 public.edenproc_set_all_grants()
 public.edenproc_superuser(integer)
 public.edenproc_superuser(text)
 public.edenproc_usename(integer)
 public.edenproc_user_enabled(integer)
 public.edenproc_user_enabled(text)
 public.edenproc_usesysid(text)
(39 rows)

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Michael Fuhr
Sent: Saturday, November 26, 2005 9:23 PM
To: Együd Csaba
Cc: 'Tom Lane'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] GRANT question

On Sat, Nov 26, 2005 at 09:10:40PM +0100, Egyd Csaba wrote:
> moson.gdb=> \df eden*
>                    List of functions
>  Schema | Name | Result data type | Argument data types
> --------+------+------------------+---------------------
> (0 rows)

Oops, I forgot that \df uses pg_function_is_visible(), so it won't show
functions that aren't in your search path.  Try this:

SELECT oid::regprocedure FROM pg_proc WHERE proname ILIKE '%eden%';

or

SELECT n.nspname, p.proname, oidvectortypes(p.proargtypes) FROM pg_proc AS p
LEFT OUTER JOIN pg_namespace AS n ON n.oid = p.pronamespace WHERE p.proname
ILIKE '%eden%';

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25.


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25.

Re: FW: GRANT question

From
Michael Fuhr
Date:
On Sat, Nov 26, 2005 at 09:37:00PM +0100, Egyd Csaba (Freemail) wrote:
> moson.gdb=> SELECT oid::regprocedure FROM pg_proc WHERE proname ILIKE
> '%eden%';
[...]
>  public.edenproc_usesysid(text)

Hmmm...so the function indeed exists in the public schema.  What
happens if you call the schema-qualified function?  What are the
permissions on the schema itself?

SELECT public.edenproc_usesysid('probauser');
SELECT * FROM pg_namespace WHERE nspname = 'public';

If the first query fails with "permission denied for schema public"
then you probably need to grant at least USAGE on the public schema
to certain users.  That would imply that somebody has changed the
default permissions on public, either in this database or in the
template datbase from which it was created (template1 by default).

--
Michael Fuhr

Re: FW: GRANT question

From
Együd Csaba (Freemail)
Date:
Dear Michael, Tom,
thak you very much! Now it works well.

moson.gdb=> SELECT public.edenproc_usesysid('probauser');
ERROR:  permission denied for schema public
moson.gdb=> select current_schemas(true);
 current_schemas
-----------------
 {pg_catalog}
(1 row)

moson.gdb=# GRANT USAGE ON SCHEMA PUBLIC TO probauser;
GRANT
moson.gdb=# select current_schemas(true);
   current_schemas
---------------------
 {pg_catalog,public}
(1 row)

moson.gdb=# SELECT edenproc_usesysid('probauser');
 edenproc_usesysid
-------------------
               118
(1 row)


Best Regards,
  -- Csaba Együd

-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Saturday, November 26, 2005 10:16 PM
To: Együd Csaba (Freemail)
Cc: pgsql-general@postgresql.org
Subject: Re: FW: [GENERAL] GRANT question

On Sat, Nov 26, 2005 at 09:37:00PM +0100, Egyd Csaba (Freemail) wrote:
> moson.gdb=> SELECT oid::regprocedure FROM pg_proc WHERE proname ILIKE
> '%eden%';
[...]
>  public.edenproc_usesysid(text)

Hmmm...so the function indeed exists in the public schema.  What happens if
you call the schema-qualified function?  What are the permissions on the
schema itself?

SELECT public.edenproc_usesysid('probauser');
SELECT * FROM pg_namespace WHERE nspname = 'public';

If the first query fails with "permission denied for schema public"
then you probably need to grant at least USAGE on the public schema to
certain users.  That would imply that somebody has changed the default
permissions on public, either in this database or in the template datbase
from which it was created (template1 by default).

--
Michael Fuhr

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25.

Re: FW: GRANT question

From
Michael Fuhr
Date:
On Sun, Nov 27, 2005 at 08:02:37AM +0100, Egyd Csaba (Freemail) wrote:
> thak you very much! Now it works well.
[...]
> moson.gdb=# select current_schemas(true);
>    current_schemas
> ---------------------
>  {pg_catalog,public}
> (1 row)
>
> moson.gdb=# SELECT edenproc_usesysid('probauser');
>  edenproc_usesysid
> -------------------
>                118
> (1 row)

Did you try these commands as the ordinary user?  The above is
evidence that it works for a superuser, which it did already
according to your previous posts.

--
Michael Fuhr

Re: FW: GRANT question

From
Együd Csaba (Freemail)
Date:
Dear Michael,
certainly I tried and seems to be ok. Maybe I  fogot to copy it into my
letter?

-- csaba

-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Sunday, November 27, 2005 4:50 PM
To: Együd Csaba (Freemail)
Cc: 'Tom Lane'; pgsql-general@postgresql.org
Subject: Re: FW: [GENERAL] GRANT question

On Sun, Nov 27, 2005 at 08:02:37AM +0100, Egyd Csaba (Freemail) wrote:
> thak you very much! Now it works well.
[...]
> moson.gdb=# select current_schemas(true);
>    current_schemas
> ---------------------
>  {pg_catalog,public}
> (1 row)
>
> moson.gdb=# SELECT edenproc_usesysid('probauser');  edenproc_usesysid
> -------------------
>                118
> (1 row)

Did you try these commands as the ordinary user?  The above is evidence that
it works for a superuser, which it did already according to your previous
posts.

--
Michael Fuhr

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25.