Thread: GRANT privileges strange behavior

Hi,

I have the following situation:

postgres=# create database foo with encoding = 'UTF8';
postgres=# \c foo
foo=# CREATE SCHEMA sc;
foo=# ALTER DATABASE foo SET search_path=sc, pg_catalog;
foo=# CREATE ROLE usr LOGIN PASSWORD 'usr' NOINHERIT CREATEDB VALID UNTIL
'infinity';
foo=# grant all privileges on all sequences in schema sc to usr;
foo=# CREATE SEQUENCE foo_seq INCREMENT BY 1;
foo=# select relname, relacl from pg_class where relkind = 'S';
 relname | relacl
---------+--------
 foo_seq |
(1 row)

i.e. even thought I request to have all privileges for all sequences in the
schema, I don't for any new ones.

This is not a problem for a fresh PostgreSQL database where I can set the
grant at the end of the schema deployment. But if I want to add any new
sequence to the database I need to explicitly add the grant to each new
sequence.

Is this expected behavior?
If so is there a work around to my problem so I can get all privileges to my
user for any new sequence in the schema?

Thanks,
- Ivo

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/GRANT-privileges-strange-behavior-tp4726831p4726831.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Tue, 2011-08-23 at 06:43 -0700, igivanoff wrote:
> Hi,
>
> I have the following situation:
>
> postgres=# create database foo with encoding = 'UTF8';
> postgres=# \c foo
> foo=# CREATE SCHEMA sc;
> foo=# ALTER DATABASE foo SET search_path=sc, pg_catalog;
> foo=# CREATE ROLE usr LOGIN PASSWORD 'usr' NOINHERIT CREATEDB VALID UNTIL
> 'infinity';
> foo=# grant all privileges on all sequences in schema sc to usr;
> foo=# CREATE SEQUENCE foo_seq INCREMENT BY 1;
> foo=# select relname, relacl from pg_class where relkind = 'S';
>  relname | relacl
> ---------+--------
>  foo_seq |
> (1 row)
>
> i.e. even thought I request to have all privileges for all sequences in the
> schema, I don't for any new ones.
>
> This is not a problem for a fresh PostgreSQL database where I can set the
> grant at the end of the schema deployment. But if I want to add any new
> sequence to the database I need to explicitly add the grant to each new
> sequence.
>
> Is this expected behavior?

Sure, GRANT only works on existing objects.

> If so is there a work around to my problem so I can get all privileges to my
> user for any new sequence in the schema?
>

ALTER DEFAULT PRIVILEGES
(http://www.postgresql.org/docs/9.0/interactive/sql-alterdefaultprivileges.html).


--
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com