On 9/11/16, Tom Lane <t...@sss.pgh.pa.us> wrote: > Vitaly Burovoy <vitaly.buro...@gmail.com> writes: >> On 9/11/16, Kevin Grittner <kgri...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers