Thread: [HACKERS] Postgresql gives error that role goes not exists while it exists

2017-10-03 5:49 GMT-03:00 Nick Dro <postgresql@walla.co.il>:
> Can someone assists with the issue posted on StackOverflow?
>
> https://stackoverflow.com/questions/46540537/postgresql-9-3-creation-of-group-role-causes-permission-problems
>
>
> Creation of new Group Role causes postgresql to think that Login roles does
> not exist. I think it's a bug? or at least a wrong error message
>
I'm not sure. I bet a dime that the role was created as "Iris" and you
are trying to assing "iris" (they are different). If you list the
roles, we can confirm that.


--   Euler Taveira                                   Timbira -
http://www.timbira.com.br/  PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 3 October 2017 at 20:47, Euler Taveira <euler@timbira.com.br> wrote:
>
> 2017-10-03 5:49 GMT-03:00 Nick Dro <postgresql@walla.co.il>:
> > Can someone assists with the issue posted on StackOverflow?
> >
> > https://stackoverflow.com/questions/46540537/postgresql-9-3-creation-of-group-role-causes-permission-problems
> >
> >
> > Creation of new Group Role causes postgresql to think that Login roles does
> > not exist. I think it's a bug? or at least a wrong error message
> >
> I'm not sure. I bet a dime that the role was created as "Iris" and you
> are trying to assing "iris" (they are different). If you list the
> roles, we can confirm that.
>

... and the reason we don't emit a HINT here is that the exact same
HINT would apply in any context involving identifiers, so we'd just
flood the logs. It'd be spurious in most cases.

We could only emit a useful HINT if we actually went and looked in the
relevant catalog for a different-cased version. Which is pretty
expensive.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Craig Ringer <craig@2ndquadrant.com> writes:
> We could only emit a useful HINT if we actually went and looked in the
> relevant catalog for a different-cased version. Which is pretty
> expensive.

There is actually a hint somewhat like that for the specific case of
misspelled column names in DML statements:

postgres=# create table foo ("Iris" int);
CREATE TABLE
postgres=# select iris from foo;
ERROR:  column "iris" does not exist
LINE 1: select iris from foo;              ^
HINT:  Perhaps you meant to reference the column "foo.Iris".

but that's a bit different because the set of column names to be
considered is very constrained --- only columns belonging to tables
listed in FROM.  The parser has already sucked in the column name
lists for those tables, so no additional catalog fetches are needed.
        regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Oct 3, 2017 at 8:47 AM, Euler Taveira <euler@timbira.com.br> wrote:
> I'm not sure. I bet a dime that the role was created as "Iris" and you
> are trying to assing "iris" (they are different). If you list the
> roles, we can confirm that.

I don't see how this would explain anything.  "current_role"() is
going to return the role using its actual case, and that's also what
pg_hash_role() expects.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Oct 3, 2017 at 8:47 AM, Euler Taveira <euler@timbira.com.br> wrote:
>> I'm not sure. I bet a dime that the role was created as "Iris" and you
>> are trying to assing "iris" (they are different). If you list the
>> roles, we can confirm that.

> I don't see how this would explain anything.

The query as given has obvious syntax problems:

regression=# select *
from users
where case when (select pg_has_role(select "current_user"(), 'hr_user'::name, 'MEMBER'::text)) then 1=1 else userstatus
<>'Active'end 
;
ERROR:  syntax error at or near "select"
LINE 3: where case when (select pg_has_role(select "current_user"(),...                                           ^

I'm betting that the complainant has tried to obscure what they actually
did, and has obscured away some critical detail --- maybe the case of the
user name, maybe something else.
        regards, tom lane


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers