Granting privileges on all tables,sequences , views, procedures

Per 8.0 documentation, in order to ALTER a table (including rename), you
have to be the owner of a table:

"You must own the table to use ALTER TABLE; except for ALTER TABLE OWNER,
which may only be executed by a superuser."
http://www.postgresql.org/docs/8.0/static/sql-altertable.html

If you want to grant ownership of every table in a database to a specific
user, you should be able to:

SELECT exec('alter table '||table_name||' OWNER TO {user}') FROM
information_schema.tables WHERE table_schema='{schema}'

{user} = the user you want to grant to (looks like 'neha' in your case)
{schema} = schema search path for your database (probably 'public' if you
haven't changed it)

HTH,
-Chris.

On Mon, May 16, 2011 at 9:41 PM, Adarsh Sharma <adarsh.sharma@orkash.com>wrote:

2011/5/17 Adarsh Sharma <adarsh.sharma@orkash.com>:

Dear all,

Today I grant a user all privileges to all tables in  a database by

grant all privileges on   svo_data_social to neha ;              grant all
privileges on   svo_phrases to neha ;
.. . .....
.....
...

Now i find it very uncomfort   to grant privileges one by one table.

Is there a simple way to grant privileges on all tables, views, sequences
etc in a database.

Also , after granting all privileges , rename command is not granted.

Is it impossible to grant alter & drop privileges to user which is granted
to only the user who created the object.

What is ALL privileges comprised of ?

Thanks

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I think that you can grant membership in that role, so member role has
ability to run ALTER, DROP etc. on tables, which even doesn't own.

You can also change owner of object using ALTER TABLE for example
ALTER TABLE sometable OWNER TO new_owner, but probably previous owner
need to be granted as new_owner's member.

To grant privileges on all tables, I think you can write sql function,
which obtains list of all tables, schemas, views in database and fire
GRANT ALL for each of them.

Regards,
Grzegorz Szpetkowski

Ah - my apologies, I realized you will need to add a function for exec
(which may also require plpgsql language added to your database).

On Tue, May 17, 2011 at 12:00 AM, Christopher Opena
<counterveil@gmail.com>wrote: