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@aklaver.com