PostgreSQL User Group Management

Groups are entirely optional in postgresql. They are only used to simplify granting and revoking privileges for the db admin, and users do not need to be a member of any group.

Creating Groups

Unlike creating users, you can only create groups using SQL. The command is:

CREATE GROUP name [ [ WITH ] 
     SYSID gid
     | USER  username [, ...]]

If we wanted to create a group with alice as an initial member, we can use:

 template1=# CREATE GROUP sales WITH USER alice;
CREATE GROUP

Adding or removing users from groups

You can add or remove users from groups after they have been created using the ALTER GROUP command:

 ALTER GROUP groupname [ADD|DROP] USER username [, ...  ]

Imagine we wanted to add bob to the sales group and remove alice:

template1=# ALTER GROUP sales ADD USER bob;
ALTER GROUP
template1=# ALTER GROUP sales DROP USER alice;
ALTER GROUP

PostgreSQL User Groups Management

Groups are entirely optional in postgresql. They are only used to simplify granting and revoking privileges for the db admin, and users do not need to be a member of any group.

Creating Groups

Unlike creating users, you can only create groups using SQL. The command is:

CREATE GROUP name [ [ WITH ] 
     SYSID gid
     | USER  username [, ...]]

If we wanted to create a group with alice as an initial member, we can use:

 template1=# CREATE GROUP sales WITH USER alice;
CREATE GROUP

Adding or removing users from groups

You can add or remove users from groups after they have been created using the ALTER GROUP command:

 ALTER GROUP groupname [ADD|DROP] USER username [, ...  ]

Imagine we wanted to add bob to the sales group and remove alice:

template1=# ALTER GROUP sales ADD USER bob;
ALTER GROUP
template1=# ALTER GROUP sales DROP USER alice;
ALTER GROUP

Viewing groups

We can see group membership by viewing the pg_group system table. In this example I’ve added alice back into the sales group.

template1=# select * from pg_group ;
 groname | grosysid | grolist
---------+----------+---------
 sales   |      100 | {102,101}
(1 row)

The grolist column shows a list of user ids that are in the group. If you want to see the usernames in a particular group you can use:

template1=# select usename from pg_user, (select grolist from pg_group where groname = 'sales') as groups where usesysid = ANY(grolist) ;
 usename
---------
 alice
 bob
(2 rows)

Renaming Groups

You can also rename groups using:

ALTER GROUP groupname RENAME TO newname

To rename sales to presales we would use:

template1=# ALTER GROUP sales RENAME TO presales;
ALTER GROUP

Removing Groups

Removing groups can be done using DROP GROUP

template1=# DROP GROUP presales;
      DROP GROUP