Role Membership

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?

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