Thread: double check the role has what's kind of the privilege? And the same for the objects. Thanks.

Hi All friends. Thanks for you guys reply my questions. You helps me a lot.
THank you billions.  Another help. THanks.

After I create table and roles. I want to double check I grant the proper
privileges to the users. I can
use \dp and \z command to check the objects privilege.  I can user pg_authid
to check the system privilege grantee to the users. Am I right??????? the
below is what I get from my test database.



Q : what's the  differences between \dp and \z.

Thanks.
Regards.

Grace



rrp=# \dp
                     Access privileges for database "rrp"
 Schema | Name |   Type   |                 Access privileges
--------+------+----------+---------------------------------------------------
 rrp    | c    | sequence |
 rrp    | p    | sequence |
 rrp    | se1  | sequence |
 rrp    | t1   | table    |
{grace=arwdxt/grace,user1=ar/grace,user2=r/grace}
 rrp    | t2   | table    | {grace=arwdxt/grace,user1=r/grace}

rrp=# \z
                     Access privileges for database "rrp"
 Schema | Name |   Type   |                 Access privileges
--------+------+----------+---------------------------------------------------
 rrp    | c    | sequence |
 rrp    | p    | sequence |
 rrp    | se1  | sequence |
 rrp    | t1   | table    |
{grace=arwdxt/grace,user1=ar/grace,user2=r/grace}
 rrp    | t2   | table    | {grace=arwdxt/grace,user1=r/grace}


(5 rows)rrp=# select
rolname,rolsuper,rolinherit,rolcreaterole,rolcreatedb,rolcanlogin from
pg_authid;
 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin
---------+----------+------------+---------------+-------------+-------------
 gpadmin | t        | t          | t             | t           | t
 grace   | f        | t          | f             | t           | t
 user1   | f        | t          | f             | f           | t
 user2   | f        | t          | f             | f           | t
 user4   | f        | t          | f             | f           | t
(5 rows)





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/double-check-the-role-has-what-s-kind-of-the-privilege-And-the-same-for-the-objects-Thanks-tp5605564p5605564.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Thu, Mar 29, 2012 at 7:49 PM, leaf_yxj <leaf_yxj@163.com> wrote:
> Hi All friends. Thanks for you guys reply my questions. You helps me a lot.
> THank you billions.  Another help. THanks.
>
> After I create table and roles. I want to double check I grant the proper
> privileges to the users. I can
> use \dp and \z command to check the objects privilege.  I can user pg_authid
> to check the system privilege grantee to the users. Am I right??????? the
> below is what I get from my test database.

Cna you be specific about what you are trying to check.  Are you
trying to check whether a user has access to a given role?  If so
check out pg_has_role().

Best Wishes,
Chris Travers

leaf_yxj wrote:
> My bosses ask me  to list all the users and all the privilege which
the superuser granted  to the
> users.
> Then they can double check that I did right thing or not?

Unlike Oracle, PostgreSQL does not have a concept of "grantor",
so it is not possible to find out which privileges were granted
by a superuser.

It is possible to find out all privileges for a certain user,
but it's probably a bit complicated.

What exactly should be checked?

Yours,
Laurenz Albe

"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
> leaf_yxj wrote:
>> My bosses ask me  to list all the users and all the privilege which
> the superuser granted  to the
>> users.
>> Then they can double check that I did right thing or not?

> Unlike Oracle, PostgreSQL does not have a concept of "grantor",
> so it is not possible to find out which privileges were granted
> by a superuser.

Um, we *do* have a concept of grantors, and that information is
recorded.  However, in Postgres any grant or revoke executed by
a superuser is treated as having been done by the object's owner,
so what gets recorded as the grantor in such a case is the owner.
So, right answer, wrong reasoning.

            regards, tom lane

Tom Lane wrote:
>>> My bosses ask me  to list all the users and all the privilege which
>> the superuser granted  to the
>>> users.
>>> Then they can double check that I did right thing or not?
>
>> Unlike Oracle, PostgreSQL does not have a concept of "grantor",
>> so it is not possible to find out which privileges were granted
>> by a superuser.
>
> Um, we *do* have a concept of grantors, and that information is
> recorded.  However, in Postgres any grant or revoke executed by
> a superuser is treated as having been done by the object's owner,
> so what gets recorded as the grantor in such a case is the owner.
> So, right answer, wrong reasoning.

Oops, you're right of course. Sorry for causing confusion.

Yours,
Laurenz Albe

I think they don't care about the grantee. they only care about the users (
for example the application team user, develop team user) and the privileges
they have. Thanks. Guys.  Grace

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/double-check-the-role-has-what-s-kind-of-the-privilege-And-the-same-for-the-objects-Thanks-tp5605564p5606831.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

leaf_yxj wrote:
> My bosses ask me to list
>
> 1)all the users and the roles associated with the users.

This will list all roles in the cluster, whether they can login
(are users) or not, and the array of all roles they are directly
or indirectly members of:

WITH RECURSIVE is_member_of(member, roleid) AS
   (SELECT oid, oid
    FROM pg_roles
    UNION
    SELECT m.member, r.roleid
    FROM is_member_of m JOIN
         pg_auth_members r ON (m.roleid = r.member))
SELECT u.rolname, u.rolcanlogin, array_agg(r.rolname) AS belongs_to
FROM is_member_of m JOIN
     pg_roles u ON (m.member = u.oid) JOIN
     pg_roles r ON (m.roleid = r.oid)
GROUP BY u.rolname, u.rolcanlogin;

> 2) all the users  and the privileges associated with that users.

That is pretty difficult. You have to construct queries for each type
of object. The following query will show the desired information for all
privileges on tables, views and sequences (but not privileges granted on
columns of tables):

SELECT rolname, tablename, privilege_type, is_grantable
FROM (
   SELECT r.rolname, r.oid, n.nspname || '.' || t.relname AS tablename,
          (aclexplode(t.relacl)).grantee,
(aclexplode(t.relacl)).privilege_type,
          (aclexplode(t.relacl)).is_grantable
   FROM pg_shdepend shd JOIN
        pg_roles r ON (shd.refobjid = r.oid) JOIN
        pg_class t ON (shd.objid = t.oid) JOIN
        pg_namespace n ON (t.relnamespace = n.oid)
   WHERE shd.classid = 'pg_class'::regclass
     AND shd.deptype='a'
     AND objsubid = 0) AS q
WHERE oid = grantee;

You'd have to construct similar queries for all other objects for
which privileges can be granted: table columns, databases,
foreign data wrappers, foreign servers, functions, languages,
large objects, schemata.

I leave this as exercise for the reader.

Yours,
Laurenz Albe