HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Does PostgreSQL allow multiple grantees in GRANT commands?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlgranteescommandsgrantallowmultipledoes

Problem

Looking at the PostgreSQL docs it seems that the GRANT command only grants to a single user or role. In fact it doesn't seem to be PostgreSQL alone.

Isn't possible to do something like GRANT SELECT UPDATE ON atable TO mary, bob, sue?

It looks like a major omission to me, or is there some reason, historical or practical for it?

Solution

Yes it does, but that will turn into a maintenance headache down the road.

Read http://en.wikipedia.org/wiki/Role-based_access_control for justification for assigning rights to roles over individual users.

Simply create a role and assign users to it, and then grant privileges to that role.

create role some_group;

grant some_group to mary, bob, sue;

grant select, update on a_table to some_group;

Code Snippets

create role some_group;

grant some_group to mary, bob, sue;

grant select, update on a_table to some_group;

Context

StackExchange Database Administrators Q#73490, answer score: 8

Revisions (0)

No revisions yet.