Обсуждение: [GENERAL] type "xxxxxxx" does not exist
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
psql -U otherusername -d database
… I get:
psql: FATAL: Peer authentication failed for user "otherusername"
Do I need to create a Linux user to login as "otherusername" so I can
test calling the functionName() with xxx_xxx_xxxxx type?
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!
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
On 05/19/2017 01:06 PM, Micky Hulse wrote:
> ERROR: type "xxx_xxx_xxxxx" does not exist
> LINE 1: DECLARE results xxx_xxx_xxxxx;
It sounds like the type might be in a different schema. You can say \dn
to see the schemas in your database, and \dT+ will show the types along
with their schema. You could also do \dT+ foo.* to see all the types in
schema foo.
If you find that the type isn't in the public schema, try setting your
schema search path so that the function can locate it, e.g.:
SET search_path TO foo, public;
Good luck!
Paul
Short answer here is that whomever is calling that function needs to ensure that their search_path is setup so that the type can be found somewhere in it. Your desire for obscurity means you are pretty much on the hook for figuring out the right command to do so.
See https://www.postgresql.org/docs/current/static/config-setting.html for help on various ways to go about making the actual change.
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. :)
Right place
When listing the functions, I see that functionName() does exist in
the database.
As the error is coming from within the function it indeed must exist and be visible to you.
The type also exists (I think):
# select exists (select 1 from pg_type where typname = 'xxx_xxx_xxxxx');
exists
--------
t
(1 row)
Existence and visability are two different things. It indeed exists. It is apparently not visible to the user when at the time the function is invoked - and the function doesn't explicitly say where to find it.
Note that the role that owns the 'type' is not the same user that is
calling the "functionName()" from the psql prompt.
Doesn't matter. Types in PostgreSQL are not restricted since they never themselves contain any data. As long as you can find a custom type you can use it.
ALTER TYPE xxx_xxx_xxxxx OWNER TO otherusername;
All objects have owners.
David J.
Wow, so many helpful replies already! Thanks everyone! I'm going to do my best at answering questions … Starting from the first email reply. :) On Fri, May 19, 2017 at 1:20 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> sudo -i -u username > You should not need to do above. >> psql -U otherusername -d database > Just do the above. Perfect, thanks for clarification. > Are either username or otherusername a superuser? > In psql \du will show you. Great question. username is a superuser and otherusername is not. In fact, otherusername has no "local attributes" listed. >> … 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. Ahh, thank you for tip! > What version of Postgres, OS and how was it installed? PostgreSQL 9.3.9 on i686-redhat-linux-gnu, compiled by gcc (GCC) 4.9.2 20150212 (Red Hat 4.9.2-6), 32-bit Installed via yum: $ sudo yum install postgresql-server postgresql-contrib > I am asking because that will help find where pg_hba.conf is. If you have > found it, can you share it here? Totally! pg_hba.conf lives here: /var/lib/pgsql/data/pg_hba.conf The only modifications I made was to change ident to md5 for IPv4 and IPv6 local connections: # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres peer #host replication postgres 127.0.0.1/32 ident #host replication postgres ::1/128 ident >> 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. Great, thank you for the clarification! Thank you for the help Adrian, I really appreciate it!
Hello and thanks for the help!
On Fri, May 19, 2017 at 1:25 PM, Paul Jungwirth
<pj@illuminatedcomputing.com> wrote:
> It sounds like the type might be in a different schema. You can say \dn to
> see the schemas in your database, and \dT+ will show the types along with
> their schema. You could also do \dT+ foo.* to see all the types in schema
> foo.
Ahhhh, interesting!
\dT+ myschema.*
I see the type "xxx_xxx_xxxxx" (which is the one my method is looking for).
There are not types in the public schema:
# \dT+ public.*
List of data types
Schema | Name | Internal name | Size | Elements | Access privileges |
Description
--------+------+---------------+------+----------+-------------------+-------------
(0 rows)
> If you find that the type isn't in the public schema, try setting your
> schema search path so that the function can locate it, e.g.:
> SET search_path TO foo, public;
Cool! Dumb question, but is foo the schema or the type?
Thanks so much for the tips Paul! I really appreciate your help. :)
On Fri, May 19, 2017 at 1:31 PM, David G. Johnston <david.g.johnston@gmail.com> wrote: > Short answer here is that whomever is calling that function needs to ensure > that their search_path is setup so that the type can be found somewhere in > it. Your desire for obscurity means you are pretty much on the hook for > figuring out the right command to do so. This is great information! I was not aware of the search_path. Learning lots of new things here, so thank you David, and all, for the help! I greatly appreciate it! :) > See https://www.postgresql.org/docs/current/static/config-setting.html for > help on various ways to go about making the actual change. Will do, thanks! >> 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. :) > Right place Cool! I much prefer listservs over something like StackOverflow as the conversations tend to be more valuable to me (as has been proven in this thread already). I'm happy to see that this list is active. :) >> When listing the functions, I see that functionName() does exist in >> the database. > As the error is coming from within the function it indeed must exist and be > visible to you. Hehe, that's a good point! :D >> The type also exists (I think): > Existence and visability are two different things. It indeed exists. It is > apparently not visible to the user when at the time the function is invoked > - and the function doesn't explicitly say where to find it. That makes so much sense now that you, and others, have pointed it out. Thank you for kicking me in right direction! >> Note that the role that owns the 'type' is not the same user that is >> calling the "functionName()" from the psql prompt. > Doesn't matter. Types in PostgreSQL are not restricted since they never > themselves contain any data. As long as you can find a custom type you can > use it. >> ALTER TYPE xxx_xxx_xxxxx OWNER TO otherusername; > All objects have owners. Excellent information! Thank you so much for your help David! It's greatly appreciated!!!! Have a nice day!
On Fri, May 19, 2017 at 2:09 PM, Micky Hulse <mickyhulse@gmail.com> wrote: > Cool! Dumb question, but is foo the schema or the type? Doh! I see now that foo is the schema! Thanks again Paul!
On Fri, May 19, 2017 at 2:09 PM, Micky Hulse <mickyhulse@gmail.com> wrote: >> If you find that the type isn't in the public schema, try setting your >> schema search path so that the function can locate it, e.g.: >> SET search_path TO foo, public; Awesome, that worked! SET search_path TO myschema, public; Thanks to everyone for the help!!!!! I really appreciate it. :)
On 05/19/2017 02:25 PM, Micky Hulse wrote: > Awesome, that worked! > > SET search_path TO myschema, public; > > Thanks to everyone for the help!!!!! I really appreciate it. :) Glad you figured it out! Setting the seach_path is often a good thing to put in your ~/.psqlrc so you don't run into the same problem next time. Paul
On 05/19/2017 02:25 PM, Micky Hulse wrote:Awesome, that worked!
SET search_path TO myschema, public;
Thanks to everyone for the help!!!!! I really appreciate it. :)
Glad you figured it out! Setting the seach_path is often a good thing to put in your ~/.psqlrc so you don't run into the same problem next time.
If going for out-of-sight, out-of-mind solutions, and I have superuser access to the database, I'd much rather "ALTER DATABASE db SET search_path TO 'all known schemas';"
The psqlrc file feels to disconnected for me.
David J.
On 05/19/2017 01:57 PM, Micky Hulse wrote: > Wow, so many helpful replies already! Thanks everyone! I'm going to do > my best at answering questions … Starting from the first email reply. > :) > >> What version of Postgres, OS and how was it installed? > > PostgreSQL 9.3.9 on i686-redhat-linux-gnu, compiled by gcc (GCC) 4.9.2 > 20150212 (Red Hat 4.9.2-6), 32-bit FYI, 9.3 is now up to 9.3.17: https://www.postgresql.org/docs/9.3/static/release.html At some point, once you have gotten a handle on using Postgres, you should probably update. Read the Release Notes for each of the minor releases to see what has been fixed. > > Installed via yum: > > $ sudo yum install postgresql-server postgresql-contrib > >> I am asking because that will help find where pg_hba.conf is. If you have >> found it, can you share it here? > > Totally! pg_hba.conf lives here: > > /var/lib/pgsql/data/pg_hba.conf > > The only modifications I made was to change ident to md5 for IPv4 and > IPv6 local connections: > > # TYPE DATABASE USER ADDRESS METHOD > # "local" is for Unix domain socket connections only > local all all peer > # IPv4 local connections: > host all all 127.0.0.1/32 md5 > # IPv6 local connections: > host all all ::1/128 md5 > # Allow replication connections from localhost, by a user with the > # replication privilege. > #local replication postgres peer > #host replication postgres 127.0.0.1/32 ident > #host replication postgres ::1/128 ident So when you did this: " psql -U otherusername -d database … I get: psql: FATAL: Peer authentication failed for user "otherusername" " you where connecting using local, which is the socket connection. If you had done: psql -U otherusername -d database -h localhost it would have asked for a password(md5 auth method). If otherusername does not have the LOGIN attribute you would not been able to log in anyway. For more detailed information see: https://www.postgresql.org/docs/9.3/static/sql-createrole.html > > > Great, thank you for the clarification! > > Thank you for the help Adrian, I really appreciate it! > > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian, On Fri, May 19, 2017 at 3:02 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > FYI, 9.3 is now up to 9.3.17: > At some point, once you have gotten a handle on using Postgres, you should > probably update. Read the Release Notes for each of the minor releases to > see what has been fixed. Ah, good to know! Thank you for tip! :) > you where connecting using local, which is the socket connection. > If you had done: > psql -U otherusername -d database -h localhost > it would have asked for a password(md5 auth method). If otherusername does > not have the LOGIN attribute you would not been able to log in anyway. For > more detailed information see: > https://www.postgresql.org/docs/9.3/static/sql-createrole.html Ahhh, that makes sense! Thank you for clarifying and for the linkage, I really appreciate it! Thanks to everyone for all of the help!