On 05/19/2017 01:06 PM, Micky Hulse wrote:
Hello,
I hope this is the right list for me to ask questions about psql.
Please let me know if I am in the wrong place. :)
I am far from an advanced user of PostgreSQL, so please bear with me ...
I am working with an inherited database/codebase. I am trying to call
this function via psql:
# SELECT * FROM functionName('xxxxxxx', 'xxxxxxx', 'xxxxxxx');
What I get back is this:
ERROR: type "xxx_xxx_xxxxx" does not exist
LINE 1: DECLARE results xxx_xxx_xxxxx;
^
QUERY: DECLARE results xxx_xxx_xxxxx;
.....
.....
When listing the functions, I see that functionName() does exist in
the database.
The type also exists (I think):
# select exists (select 1 from pg_type where typname = 'xxx_xxx_xxxxx');
exists
--------
t
(1 row)
Note that the role that owns the 'type' is not the same user that is
calling the "functionName()" from the psql prompt. When I try to
switch roles, using:
sudo -i -u username
You should not need to do above.
psql -U otherusername -d database
Just do the above.
Are either username or otherusername a superuser?
In psql \du will show you.
… I get:
psql: FATAL: Peer authentication failed for user "otherusername"
This is coming from:
https://www.postgresql.org/docs/9.6/static/auth-methods.html#AUTH-PEER
which is set in pg_hba.conf.
What version of Postgres, OS and how was it installed?
I am asking because that will help find where pg_hba.conf is. If you
have found it, can you share it here?
Do I need to create a Linux user to login as "otherusername" so I can
test calling the functionName() with xxx_xxx_xxxxx type?
No that is not necessary. Postgres usernames do not have to be the same
as the OS usernames. Peer authentication is just a method to map OS
usernames to Postgres usernames if you want to.
Lastly, the type was declared in the SQL dump like this:
CREATE TYPE xxx_xxx_xxxxx AS (
....
);
ALTER TYPE xxx_xxx_xxxxx OWNER TO otherusername;
I know that's a lot of info ... More than anything, I'm just wondering
if someone can give me tips on where to focus my attention in terms of
trouble shooting?
Thanks so much!
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
