Обсуждение: Unprivileged access to pgsql functions?
Good afternoon.
I've been looking at the Oracle Functionality package. It's very interesting. However, the one place I'm stuck is that while user Postgres can access the functions, no other user seems to have access. I'm sure this is something simple I'm missing, but so far Google hasn't shown me the answer.
I've already tried explicitly granting execute permissions on the function, and the search path, as I understand it, is already supposed to be looking in pg_catalog.
Any pointers?
*** Non-privileged user
offload=> select nvl(null,1);
ERROR: function nvl(unknown, integer) does not exist
LINE 1: select nvl(null,1);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
offload=> \q
-bash-3.00$
*** Postgres user
-bash-3.00$ psql
psql (9.0.3)
Type "help" for help.
postgres=# select nvl(null,1);
nvl
-----
1
(1 row)
postgres=# \q
TIA,
Matt
On 03/04/11 1:11 PM, Matt Warner wrote: > Good afternoon. > > I've been looking at the Oracle Functionality package. ... what is this? doesn't sound like anything in PostgreSQL I'm familiar with. Is this part of EntepriseDB's Postgres+ package or something? You should probably contact them via their support channels.
It's a "contrib" module:
Matt
On Fri, Mar 4, 2011 at 1:20 PM, John R Pierce <pierce@hogranch.com> wrote:
On 03/04/11 1:11 PM, Matt Warner wrote:Good afternoon.
I've been looking at the Oracle Functionality package. ...
what is this? doesn't sound like anything in PostgreSQL I'm familiar with. Is this part of EntepriseDB's Postgres+ package or something? You should probably contact them via their support channels.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
To be clear, this is open source Postgres I'm using, not the enterprise product.
Matt
On Fri, Mar 4, 2011 at 1:29 PM, Matt Warner <matt@warnertechnology.com> wrote:
It's a "contrib" module:MattOn Fri, Mar 4, 2011 at 1:20 PM, John R Pierce <pierce@hogranch.com> wrote:On 03/04/11 1:11 PM, Matt Warner wrote:Good afternoon.
I've been looking at the Oracle Functionality package. ...
what is this? doesn't sound like anything in PostgreSQL I'm familiar with. Is this part of EntepriseDB's Postgres+ package or something? You should probably contact them via their support channels.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mar 5, 2011, at 2:50 AM, John R Pierce wrote: > On 03/04/11 1:11 PM, Matt Warner wrote: >> Good afternoon. >> >> I've been looking at the Oracle Functionality package. ... > > what is this? doesn't sound like anything in PostgreSQL I'm familiar with. Is this part of EntepriseDB's Postgres+ packageor something? You should probably contact them via their support channels. GRANT all on function nvl to public would help. Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.kumar@enterprisedb.com Blog:http://vibhork.blogspot.com
No luck:
*** as postgres
postgres=# GRANT all on function nvl(anyelement,anyelement) to public;
GRANT
postgres=#
*** as unprivileged user
offload=> select nvl(0,1);
ERROR: function nvl(integer, integer) does not exist
LINE 1: select nvl(0,1);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
On Fri, Mar 4, 2011 at 1:34 PM, Vibhor Kumar <vibhor.kumar@enterprisedb.com> wrote:
GRANT all on function nvl to public would help.
On Mar 5, 2011, at 2:50 AM, John R Pierce wrote:
> On 03/04/11 1:11 PM, Matt Warner wrote:
>> Good afternoon.
>>
>> I've been looking at the Oracle Functionality package. ...
>
> what is this? doesn't sound like anything in PostgreSQL I'm familiar with. Is this part of EntepriseDB's Postgres+ package or something? You should probably contact them via their support channels.
Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Here's how the script is defining the function, if that helps:
CREATE FUNCTION nvl(anyelement, anyelement)
RETURNS anyelement
AS '$libdir/orafunc','ora_nvl'
LANGUAGE C IMMUTABLE;
On Fri, Mar 4, 2011 at 1:41 PM, Matt Warner <matt@warnertechnology.com> wrote:
No luck:*** as postgrespostgres=# GRANT all on function nvl(anyelement,anyelement) to public;GRANTpostgres=#*** as unprivileged useroffload=> select nvl(0,1);ERROR: function nvl(integer, integer) does not existLINE 1: select nvl(0,1);^HINT: No function matches the given name and argument types. You might need to add explicit type casts.On Fri, Mar 4, 2011 at 1:34 PM, Vibhor Kumar <vibhor.kumar@enterprisedb.com> wrote:GRANT all on function nvl to public would help.
On Mar 5, 2011, at 2:50 AM, John R Pierce wrote:
> On 03/04/11 1:11 PM, Matt Warner wrote:
>> Good afternoon.
>>
>> I've been looking at the Oracle Functionality package. ...
>
> what is this? doesn't sound like anything in PostgreSQL I'm familiar with. Is this part of EntepriseDB's Postgres+ package or something? You should probably contact them via their support channels.
Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Matt Warner wrote: > No luck: > > *** as postgres > postgres=# GRANT all on function nvl(anyelement,anyelement) to public; > GRANT > postgres=# > > *** as unprivileged user > offload=> select nvl(0,1); > ERROR: function nvl(integer, integer) does not exist > LINE 1: select nvl(0,1); > ^ > HINT: No function matches the given name and argument types. You might need > to add explicit type casts. This looks like you defined the function in template1 *after* you created the user's DB. Or never defined it there at all. Try defining the function in the user's DB itself or, if able, recreate the user's DB after defining it in template1. HTH. Bosco.
On 03/04/11 1:41 PM, Matt Warner wrote: > No luck: > > *** as postgres > postgres=# GRANT all on function nvl(anyelement,anyelement) to public; > GRANT > postgres=# > > *** as unprivileged user > offload=> select nvl(0,1); > ERROR: function nvl(integer, integer) does not exist > LINE 1: select nvl(0,1); > ^ > HINT: No function matches the given name and argument types. You > might need to add explicit type casts. > what schema is this function defined in? is that schema in the search path?
On Mar 5, 2011, at 3:11 AM, Matt Warner wrote: > postgres=# > > *** as unprivileged user > offload=> select nvl(0,1); > ERROR: function nvl(integer, integer) does not exist > LINE 1: select nvl(0,1); > ^ > HINT: No function matches the given name and argument types. You might need to add explicit type casts. Error Message is only saying nvl(integer,integer) does not exists. Please check offload database is having function nvl and then, grant all on that function. Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.kumar@enterprisedb.com Blog:http://vibhork.blogspot.com
On Fri, Mar 04, 2011 at 01:41:34PM -0800, Matt Warner wrote: > No luck: > > *** as postgres > postgres=# GRANT all on function nvl(anyelement,anyelement) to public; > GRANT > postgres=# > > *** as unprivileged user > offload=> select nvl(0,1); > ERROR: function nvl(integer, integer) does not exist > LINE 1: select nvl(0,1); > ^ > HINT: No function matches the given name and argument types. You might need > to add explicit type casts. Is the function in your search_path? A -- Andrew Sullivan ajs@crankycanuck.ca
On Fri, Mar 4, 2011 at 1:48 PM, Bosco Rama <postgres@boscorama.com> wrote:
Matt Warner wrote:This looks like you defined the function in template1 *after* you created the
> No luck:
>
> *** as postgres
> postgres=# GRANT all on function nvl(anyelement,anyelement) to public;
> GRANT
> postgres=#
>
> *** as unprivileged user
> offload=> select nvl(0,1);
> ERROR: function nvl(integer, integer) does not exist
> LINE 1: select nvl(0,1);
> ^
> HINT: No function matches the given name and argument types. You might need
> to add explicit type casts.
user's DB. Or never defined it there at all.
Try defining the function in the user's DB itself or, if able, recreate the
user's DB after defining it in template1.
HTH.
Bosco.
The function cannot be defined in the user's DB because "language C" is considered a security risk, so only the superuser can do that. Or that's what I get from reading anyway...
Recreating the user DB is problematic because there are already tables in place.
Matt
On Fri, Mar 4, 2011 at 1:49 PM, John R Pierce <pierce@hogranch.com> wrote:
On 03/04/11 1:41 PM, Matt Warner wrote:what schema is this function defined in? is that schema in the search path?No luck:
*** as postgres
postgres=# GRANT all on function nvl(anyelement,anyelement) to public;
GRANT
postgres=#
*** as unprivileged user
offload=> select nvl(0,1);
ERROR: function nvl(integer, integer) does not exist
LINE 1: select nvl(0,1);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
l
The function was loaded as user postgres since it's a C-language module. The definitions for most of the functions in the script have pg_catalog as the prefix (e.g. CREATE FUNCTION pg_catalog.to_date(str text)).
Matt Warner wrote: > > The function cannot be defined in the user's DB because "language C" is > considered a security risk, so only the superuser can do that. Or that's > what I get from reading anyway... psql -U postgres -d user_db will allow the superuser to then define the function in the user's DB. Once there, you can grant access, if needed. Bosco.
On Fri, Mar 4, 2011 at 1:51 PM, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
On Fri, Mar 04, 2011 at 01:41:34PM -0800, Matt Warner wrote:Is the function in your search_path?
> No luck:
>
> *** as postgres
> postgres=# GRANT all on function nvl(anyelement,anyelement) to public;
> GRANT
> postgres=#
>
> *** as unprivileged user
> offload=> select nvl(0,1);
> ERROR: function nvl(integer, integer) does not exist
> LINE 1: select nvl(0,1);
> ^
> HINT: No function matches the given name and argument types. You might need
> to add explicit type casts.
A
--
Andrew Sullivan
ajs@crankycanuck.ca
Not sure. I believe public and pg_catalog are in the path by default. Most of the create function declarations prepend pg_catalog, and I believe I saw somewhere that pg_catalog is the default. But I may be misunderstanding that...
On Fri, Mar 4, 2011 at 1:56 PM, Bosco Rama <postgres@boscorama.com> wrote:
Matt Warner wrote:psql -U postgres -d user_db
>
> The function cannot be defined in the user's DB because "language C" is
> considered a security risk, so only the superuser can do that. Or that's
> what I get from reading anyway...
will allow the superuser to then define the function in the user's DB.
Once there, you can grant access, if needed.
Bosco.
Success! Too much exposure to Oracle had me forgetting that the databases are separate in Postgres. D'oh!
Thank you very much!
Matt
On 03/04/11 1:57 PM, Matt Warner wrote: > Not sure. I believe public and pg_catalog are in the path by default. > Most of the create function declarations prepend pg_catalog, and I > believe I saw somewhere that pg_catalog is the default. But I may be > misunderstanding that... CREATE FUNCTION nvl(anyelement, anyelement) RETURNS anyelement AS '$libdir/orafunc','ora_nvl' LANGUAGE C IMMUTABLE; doesn't specify any schema, so that function is created in the current schema in the current database
On Fri, Mar 4, 2011 at 2:03 PM, John R Pierce <pierce@hogranch.com> wrote:
On 03/04/11 1:57 PM, Matt Warner wrote:Not sure. I believe public and pg_catalog are in the path by default. Most of the create function declarations prepend pg_catalog, and I believe I saw somewhere that pg_catalog is the default. But I may be misunderstanding that...CREATE FUNCTION nvl(anyelement, anyelement)doesn't specify any schema, so that function is created in the current schema in the current database
RETURNS anyelement
AS '$libdir/orafunc','ora_nvl'
LANGUAGE C IMMUTABLE;
Yes, my bad for thinking of this as an Oracle schema as opposed to a different database.