Обсуждение: check user in group

Hello,

how can I check if a user is within a group? I use current_user() to get the logged-in user, but I have a group
"service"and I need a check if the user is member of the group 

Thanks

Phil

Hello

you can try

create or replace function is_member_of(text, text) returns boolean as $$
  select exists(select rolname from pg_catalog.pg_auth_members m JOIN
pg_catalog.pg_roles b ON m.roleid = b.oid where m.member = (select oid
from pg_roles where rolname = $1) and rolname = $2)
$$ language sql ;

postgres=# select is_member_of('pavel','admin');
 is_member_of
--------------
 t
(1 row)

Regards

Pavel Stehule

2012/12/25 Philipp Kraus <philipp.kraus@flashpixx.de>:
> Hello,
>
> how can I check if a user is within a group? I use current_user() to get the logged-in user, but I have a group
"service"and I need a check if the user is member of the group 
>
> Thanks
>
> Phil
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On 12/25/2012 01:51 AM, Philipp Kraus wrote:
> Hello,
>
> how can I check if a user is within a group? I use current_user() to get the logged-in user, but I have a group
"service"and I need a check if the user is member of the group 
>

In recent versions of Postgres user and group have been folded into
roles. The terms still exist, to roughly mean user=role with login,
group=role without login. There are built in functions to work with
roles and privileges. See:

http://www.postgresql.org/docs/9.2/interactive/functions-info.html

Table 9-51. Access Privilege Inquiry Functions
...
pg_has_role(user, role, privilege)    boolean    does user have privilege for role
pg_has_role(role, privilege)    boolean    does current user have privilege
for role

There are more available.

> Thanks
>
> Phil
>


--
Adrian Klaver
adrian.klaver@gmail.com