Re: 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(at)163(dot)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

Hi Chris,

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?

Thanks.

Grace
At 2012-03-30 10:54:50,"Chris Travers-5 [via PostgreSQL]" <ml-node+s1045698n5605567h19(at)n5(dot)nabble(dot)com> wrote:
On Thu, Mar 29, 2012 at 7:49 PM, leaf_yxj <[hidden email]> 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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/double-check-the-role-has-what-s-kind-of-the-privilege-And-the-same-for-the-objects-Thanks-tp5605564p5605567.html
To unsubscribe from double check the role has what's kind of the privilege? And the same for the objects. Thanks., click here.
NAML
Hi

--
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-tp5605564p5605597.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Hi Albe,

My bosses ask me to list

1)all the users and the roles associated with the users.
2) all the users and the privileges associated with that users.

Thanks.

Regards.

Grace

At 2012-03-30 16:07:08,"Albe Laurenz *EXTERN* [via PostgreSQL]" <ml-node+s1045698n5605960h2(at)n5(dot)nabble(dot)com> wrote:
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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/double-check-the-role-has-what-s-kind-of-the-privilege-And-the-same-for-the-objects-Thanks-tp5605564p5605960.html
To unsubscribe from double check the role has what's kind of the privilege? And the same for the objects. Thanks., click here.
NAML

--
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-tp5605564p5606709.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

"Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)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