Re: Postgres 8.3 Grant all on database or schema

[Saikumar <saikumar1@xxxxxxxxxxxxxx>]

Hi Guillaume,

 

Thanks for the update.

I found some script to implement this.

 

Regards,

Saikumar

 

From: Guillaume Lelarge <guillaume@xxxxxxxxxxxx>
Date: Wednesday, 7 November 2018 at 5:05 PM
To: Saikumar <saikumar1@xxxxxxxxxxxxxx>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: Postgres 8.3 Grant all on database or schema

 

Hi,

 

Le mer. 7 nov. 2018 à 09:59, Saikumar <saikumar1@xxxxxxxxxxxxxx> 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.

---

Singapore Power and all members of the group are now serving the public as SP Group. Our registered business address is 2 Kallang Sector, Singapore 349277 and our contact details have changed. Visit us at spgroup.com.sg or connect with us at fb.com/SPGroupSG

---

This email and any files transmitted with it are confidential and intended for the named recipient only. The contents herein are privileged to the sender and the use thereof is restricted to the intended purpose. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient please do not disclose, copy, distribute or take any action in reliance on the contents of this e-mail. Thank you.

---