Thread: Role Membership
I was looking at my users and realized none of my users are members of
a specific group or role. Not sure if there's a difference between the
two (role / group) in PostgreSQL, is there?
easports=# \du
List of roles
Role name | Attributes | Member of
-----------+-----------------------------------+-----------
carlos | Superuser | {}
postgres | Superuser, Create role, Create DB | {}
Now my question is about the section 'Member of' and how this is
commonly utilized by most PG administrators. Is this the same thing as
explained here:
http://www.postgresql.org/docs/8.2/interactive/role-membership.html
If I am not mistaken I can simply create a role called 'accounting'
and add several users to the 'accounting' group rather than juggling
several dozen user grants, correct? If anyone can please tell me if
I'm hot or cold on this issue as well as anything you would think is
helpful for me to know that is not in the documentation that you
learned as a PG administrator.
Thanks so much!
On Monday 20 December 2010 7:27:19 am Carlos Mennens wrote:
> I was looking at my users and realized none of my users are members of
> a specific group or role. Not sure if there's a difference between the
> two (role / group) in PostgreSQL, is there?
>
> easports=# \du
> List of roles
> Role name | Attributes | Member of
> -----------+-----------------------------------+-----------
> carlos | Superuser | {}
> postgres | Superuser, Create role, Create DB | {}
>
> Now my question is about the section 'Member of' and how this is
> commonly utilized by most PG administrators. Is this the same thing as
> explained here:
>
> http://www.postgresql.org/docs/8.2/interactive/role-membership.html
>
> If I am not mistaken I can simply create a role called 'accounting'
> and add several users to the 'accounting' group rather than juggling
> several dozen user grants, correct? If anyone can please tell me if
> I'm hot or cold on this issue as well as anything you would think is
> helpful for me to know that is not in the documentation that you
> learned as a PG administrator.
>
> Thanks so much!
Roles = users/groups. In older versions there where users and groups, that has
been consolidated into the concept of a role. If it makes it easier I use the
concept of roles with login privileges as a users and roles without as groups.
You are on the right track.
--
Adrian Klaver
adrian.klaver@gmail.com
On Mon, Dec 20, 2010 at 10:41 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > Roles = users/groups. In older versions there where users and groups, that has > been consolidated into the concept of a role. If it makes it easier I use the > concept of roles with login privileges as a users and roles without as groups. So I did a \dg & a \du according to '\?' & I can't see the difference between the two commands. One is listed as showing 'users' and the other for 'groups' but the output looks identical to me. Perhaps my database is not that robust yet. So if I wanted to create a group / role for accounting, would I simply just do: CREATE ROLE accounting; My question is I can't find: 1. How to view all previously created roles on my database. '\dg' doesn't show me the new role I created above. 2. How to see which 'users' are all members of 'accounting'? Would that be done simply with '\dg'?
On Mon, Dec 20, 2010 at 9:12 AM, Carlos Mennens
<carlos.mennens@gmail.com> wrote:
> So if I wanted to create a group / role for accounting, would I simply just do:
>
> CREATE ROLE accounting;
>
> My question is I can't find:
>
> 1. How to view all previously created roles on my database. '\dg'
> doesn't show me the new role I created above.
Odd, mine does. Got a complete example of creating a role and not seeing it?
Here's mine:
smarlowe=# create role stans;
CREATE ROLE
smarlowe=# \dg
List of roles
Role name | Attributes | Member of
-----------+--------------+-----------
postgres | Superuser | {}
: Create role
: Create DB
smarlowe | Superuser | {}
: Create role
: Create DB
stans | Cannot login | {}
> 2. How to see which 'users' are all members of 'accounting'? Would
> that be done simply with '\dg'?
Yeah.
On Mon, Dec 20, 2010 at 12:05 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> Odd, mine does. Got a complete example of creating a role and not seeing it?
>
> Here's mine:
> smarlowe=# create role stans;
> CREATE ROLE
> smarlowe=# \dg
> List of roles
> Role name | Attributes | Member of
> -----------+--------------+-----------
> postgres | Superuser | {}
> : Create role
> : Create DB
> smarlowe | Superuser | {}
> : Create role
> : Create DB
> stans | Cannot login | {}
I guess I am still confused by role / group & user accounts. If you
create a role / group called 'finance', it then shows up as a user
when I do \dg? Then how do I make users a member of the 'finance' role
/ group if they're listed just like regular users are?
easports=# CREATE ROLE finance;
CREATE ROLE
easports=# \dg
List of roles
Role name | Attributes | Member of
-----------+-----------------------------------+-----------
cmennens | Superuser | {}
finance | Cannot login | {}
postgres | Superuser, Create role, Create DB | {}
From the above listing, I would expect 'finance' to not be listed with
my users since finance is a role / group, not a single user. I want to
make specific users members of 'finance'. Am I missing something or
just slow today?
>> 2. How to see which 'users' are all members of 'accounting'? Would
>> that be done simply with '\dg'?
>
> Yeah.
On 12/20/2010 08:12 AM, Carlos Mennens wrote: > On Mon, Dec 20, 2010 at 10:41 AM, Adrian Klaver<adrian.klaver@gmail.com> wrote: >> Roles = users/groups. In older versions there where users and groups, that has >> been consolidated into the concept of a role. If it makes it easier I use the >> concept of roles with login privileges as a users and roles without as groups. > > So I did a \dg& a \du according to '\?'& I can't see the difference > between the two commands. One is listed as showing 'users' and the > other for 'groups' but the output looks identical to me. Perhaps my > database is not that robust yet. There is no difference. Let me see if I can explain better. In older versions of Postgres(cannot remember when the switch was made) there where indeed USERS and GROUPS. They where distinct and different commands where used to create and view them. At some point the functions served by the them where all consolidated into the concept of a ROLE. For backwards comparability the terms USER and GROUP are still with us, as well as the creation and view commands. At this point though they point to ROLES. ROLES have the attributes of both USERS and GROUPS and this is where my suggestion above came from. When creating a ROLE with login privileges it is useful to think of that as a USER. A ROLE you create without login privileges can be thought of as a GROUP. These are only mental shortcuts and are not reflected in the \d commands. > > So if I wanted to create a group / role for accounting, would I simply just do: > > CREATE ROLE accounting; Yes > > My question is I can't find: > > 1. How to view all previously created roles on my database. '\dg' > doesn't show me the new role I created above. Did the command above return successfully? > 2. How to see which 'users' are all members of 'accounting'? Would > that be done simply with '\dg'? > Yes it would once you assign users to that ROLE. To really understand you need to go over the SQL commands CREATE ROLE GRANT and go through the below several times: http://www.postgresql.org/docs/9.0/interactive/user-manag.html I ended up setting up a test database and creating roles in it. I then worked through a variety of scenarios to see how the ROLE mechanism worked, especially the INHERIT attribute. -- Adrian Klaver adrian.klaver@gmail.com
On Mon, Dec 20, 2010 at 10:12 AM, Carlos Mennens
<carlos.mennens@gmail.com> wrote:
> On Mon, Dec 20, 2010 at 12:05 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> Odd, mine does. Got a complete example of creating a role and not seeing it?
>>
>> Here's mine:
>> smarlowe=# create role stans;
>> CREATE ROLE
>> smarlowe=# \dg
>> List of roles
>> Role name | Attributes | Member of
>> -----------+--------------+-----------
>> postgres | Superuser | {}
>> : Create role
>> : Create DB
>> smarlowe | Superuser | {}
>> : Create role
>> : Create DB
>> stans | Cannot login | {}
>
> I guess I am still confused by role / group & user accounts. If you
No user, no group, they're al roles. Roles are both / either.
> create a role / group called 'finance', it then shows up as a user
> when I do \dg? Then how do I make users a member of the 'finance' role
Yep, it shows up as a ROLE.
> / group if they're listed just like regular users are?
You grant them that:
grant rolename to username;
Then you only ever have to grant / revoke a role to change
permissions, no need to do a million grants all over the place on each
table. Just grant it once to the role, grant the role to the user,
viola, you're done.
>
> easports=# CREATE ROLE finance;
> CREATE ROLE
> easports=# \dg
> List of roles
> Role name | Attributes | Member of
> -----------+-----------------------------------+-----------
> cmennens | Superuser | {}
> finance | Cannot login | {}
> postgres | Superuser, Create role, Create DB | {}
>
> From the above listing, I would expect 'finance' to not be listed with
> my users since finance is a role / group, not a single user. I want to
> make specific users members of 'finance'. Am I missing something or
> just slow today?
>
>>> 2. How to see which 'users' are all members of 'accounting'? Would
>>> that be done simply with '\dg'?
>>
>> Yeah.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
To understand recursion, one must first understand recursion.
On Mon, Dec 20, 2010 at 1:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> No user, no group, they're al roles. Roles are both / either.
Ah now I understand. Thank you!
> You grant them that:
>
> grant rolename to username;
>
> Then you only ever have to grant / revoke a role to change
> permissions, no need to do a million grants all over the place on each
> table. Just grant it once to the role, grant the role to the user,
> viola, you're done.
OK I now understand:
postgres=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype |
Access privileges
-----------+-----------+----------+-------------+-------------+-----------------------
caldega | cmennens | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
tiburon | cmennens | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
ide | cmennens | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
orlando | jmadeline | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
software | mwilshaw | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
(8 rows)
postgres=# ALTER DATABASE ide OWNER TO it;
ALTER DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype |
Access privileges
-----------+-----------+----------+-------------+-------------+-----------------------
caldega | cmennens | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
tiburon | cmennens | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
ide | it | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
orlando | jmadeline | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
software | mwilshaw | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
Thanks for helping me out!
-Carlos
On Monday 20 December 2010 11:46:29 am Carlos Mennens wrote: > On Mon, Dec 20, 2010 at 1:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > > No user, no group, they're al roles. Roles are both / either. > > Ah now I understand. Thank you! > > > You grant them that: > > > > grant rolename to username; > > > > Then you only ever have to grant / revoke a role to change > > permissions, no need to do a million grants all over the place on each > > table. Just grant it once to the role, grant the role to the user, > > viola, you're done. > > OK I now understand: > Now I don't:) What you show is changing the ownership of an object. I thought you where asking about how to add members to a role and the relationship between 'users' and 'groups'? What Scott showed was a good start, but I would suggest some more experimenting. ROLES are powerful but there is a learning curve, especially when you start using SET ROLE and SET SESSION AUTHORIZATION. > Thanks for helping me out! > > -Carlos -- Adrian Klaver adrian.klaver@gmail.com