Re: [REVIEW] Tab Completion for CREATE DATABASE ... TEMPLATE ...

On 9/11/16, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com> writes:
>> On 9/11/16, Kevin Grittner <kgrittn(at)gmail(dot)com> wrote:
>>> I was able to find cases during test which were not handled
>>> correctly with either version, so I tweaked the query a little.
>
>> Hmm. Which one? Attempt to "SET ROLE <grouprole>"?
>> Unfortunately, I after reading your letter I realized that I missed a
>> case (it is not working even with your version):
>
> I wasn't aware that this patch was doing anything nontrivial ...
>
> After looking at it I think it's basically uninformed about how to test
> for ownership. An explicit join against pg_roles is almost never the
> right way for SQL queries to do that. Lose the join and write it more
> like this:
>
> +"SELECT pg_catalog.quote_ident(d.datname) "\
> +" FROM pg_catalog.pg_database d "\
> +" WHERE substring(pg_catalog.quote_ident(d.datname),1,%d)='%s' "\
> +" AND (d.datistemplate OR pg_catalog.pg_has_role(d.datdba, 'USAGE'))"
>
> See the information_schema views for precedent.
>
> regards, tom lane

Wow! I have not pay enough attention to a description of "pg_has_role".
Your version works for all my tests. Thank you.

--
Best regards,
Vitaly Burovoy