ALTER DEFAULT PRIVILEGES or "GRANT...on ALL TABLES"

[Douglas Spadotto]

Hello everyone,

I saw that Greenplum does not support either of the commands listed on the subject.

The first, ALTER DEFAULT PRIVILEGES would allow me to set a default privilege for a role in a schema, for future objects created within that schema.

Source: http://www.postgresql.org/docs/9.0/static/sql-alterdefaultprivileges.html

The second, "GRANT ... on ALL TABLES" would allow me to set GRANTs on all existing objects within a schema.

Source: http://www.postgresql.org/docs/9.0/static/sql-grant.html

I can automate these behaviours with a script that periodically checks if a given role has rights to each object within a schema, and grant it individually if not.

But the nicer solution would be to have these 2 PostgreSQL constructs to work on Greenplum. Any plans on supporting them anytime soon?

Or are there some other way to grant rights to all objects in a schema?

Thanks in advance,

Douglas

-----

Frodo: "I wish none of this had happened." 

Gandalf: "So do all who live to see such times, but that is not for them to decide. All we have to decide is what to do with the time that is given to us."

-- Lord of the Rings: The Fellowship of the Ring (2001)

[Heikki Linnakangas]

On 18/04/16 17:08, Douglas Spadotto wrote:
> I saw that Greenplum does not support either of the commands listed on the
> subject.
>
> The first, ALTER DEFAULT PRIVILEGES would allow me to set a default
> privilege for a role in a schema, for future objects created within that
> schema.
> Source:
> http://www.postgresql.org/docs/9.0/static/sql-alterdefaultprivileges.html
>
> The second, "GRANT ... on ALL TABLES" would allow me to set GRANTs on all
> existing objects within a schema.
> Source: http://www.postgresql.org/docs/9.0/static/sql-grant.html
>
> I can automate these behaviours with a script that periodically checks if a
> given role has rights to each object within a schema, and grant it
> individually if not.
>
> But the nicer solution would be to have these 2 PostgreSQL constructs to
> work on Greenplum. Any plans on supporting them anytime soon?

Both of those constructs were added in PostgreSQL 9.0, so we'll get them
as we merge GPDB with PostgreSQL 9.0. We're working on merging with 8.3
at the moment, so it's probably going to take 1-2 more GPDB releases
until we reach that point.

> Or are there some other way to grant rights to all objects in a schema?

Before that syntax was added, people used to write PL/pgSQL functions
with dynamic SQL, or client-side scripts, to do it. Here's one example:

http://www.postgresql.org/message-id/20100120155957.GA5027@tux

- Heikki