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:
http://pgfoundry.org/projects/orafce/
Matt
On Fri, Mar 4, 2011 at 1:20 PM, John R Pierce <pierce@hogranch.com> wrote:
Show quoted text
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:
Show quoted text
It's a "contrib" module:
http://pgfoundry.org/projects/orafce/
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
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.
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:
Show quoted text
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.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--
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:
Show quoted text
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: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.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--
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:
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 mightneed
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.
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:
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?
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:
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 mightneed
to add explicit type casts.
Is the function in your search_path?
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:
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.
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)
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
Yes, my bad for thinking of this as an Oracle schema as opposed to a
different database.