Re: Postgres 8.3 Grant all on database or schema

Hi,

Le mer. 7 nov. 2018 à 09:59, Saikumar <saikumar1(at)spgroup(dot)com(dot)sg> a écrit :

> Dear Sir,
>
>
>
> I am quite new the Postgre DB and working as DBA.
>
>
>
> I wanted to set the grant all permissions on the database to the role.
>
>
>
> Whatever the user assign to this role should inherits the same permissions
> on the database tables. To achieve this scenarios, I have executed below
> steps.
>
>
>
> GRANT ALL ON database RM to RM_ADMIN_ROLE;
>
> create user junkai;
>
> ALTER ROLE junkai WITH LOGIN;
>
> grant RM_ADMIN_ROLE to junkai;
>
>
>
> I login using junkai, and try to access the tables, its giving the error
> as permission denied error.
>
>
>
> If I grant the same access on table level, it’s working. I have hundreds
> of tables in the schema. I can’t grant the permissions on table level.
>
>
>
> I have check in the internet, all suggesting same as above. When I run the
> above commands, it’s not throwing any error all says granted. When I do
> select, It’s not allowing to fetch the data.
>
>
>
> Could you please help why it’s not working as expected?
>
>
>

It's working as expected. When you GRANT ALL on a database, you give this
role CREATE, TEMP and CONNECT privilege to this database (IOW, all
privileges related to a database object). It doesn't give any privilege to
objects inside this database. On a 8.3, you'll have to give privileges
table per table. On a more recent release, you would be able to do it
schema by schema. Usually, people use scripts to do this when they have a
lot of tables.

Regards.

--
Guillaume.