Re: grant all privileges to all tables in a database

I'm a refugee from MySQL due to license restrictions.
With MySQL, i was used to do "GRANT ALL PRIVILEGES ON dbname.* TO
username" to allow a certain user to do anything within a given
database. This is useful when using applications that run on a SQL
backend, e.g. a blog or a logging server or something like that - one
just creates a dedicated database and lets the application rule supreme.

On PostgreSQL, i lost about half a day trying to figure it out. I'm
posting this message to help others in my situation. I googled for an
answer, but everything that i've found was unhelpful. Hopefully this
mailing list is indexed by Google.

So, you have a database named dbname and a user named username. You want
to give the user all privileges on that particular database.
On MySQL, it's enough to do this:

GRANT ALL PRIVILEGES ON dbname.* TO username [IDENTIFIED BY 'password'];

On PostgreSQL, you have to give it privileges not only to the database,
but to all components within (tables, sequences and whatnot). The
following three commands will grant those privileges, first to the
database, then to the tables, then to the sequences.

echo "GRANT ALL ON DATABASE dbname TO username;" | psql -d dbname

psql -At -d dbname -c "SELECT 'GRANT ALL ON '||tablename||' TO
username;' FROM pg_tables WHERE schemaname='public';" | psql -d dbname

psql -At -d dbname -c "SELECT 'GRANT ALL ON '||c.relname||' TO
username;' FROM pg_class c JOIN pg_namespace n ON (n.oid=c.relnamespace)
WHERE c.relkind='S' AND n.nspname='public';" | psql -d dbname

It seems to work fine on pgsql version 8.
Of course, after creating new tables and stuff, you may have to re-run
the last two commands. That is not necessary on MySQL.

Thanks to AndrewSN who helped me on IRC.

--
Florin Andrei

http://florin.myip.org/

On Apr 10, 2005, at 3:10 PM, Florin Andrei wrote:

> On PostgreSQL, i lost about half a day trying to figure it out. I'm
> posting this message to help others in my situation. I googled for an
> answer, but everything that i've found was unhelpful. Hopefully this
> mailing list is indexed by Google.

The lists are indexed by google, but sometimes it is better to search
the archives directly (http://archives.postgresql.org/). Or ask the
question on the list when you did not find the answer after searching
on your own. This question comes up quite frequently, so I'm sure there
are responses in the list archives.

You can find some SQL functions to GRANT ALL PRIVILEGES on every table
plus a lot of other useful things here:

http://pgedit.com/node/view/20

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Florin Andrei wrote:

> On MySQL, it's enough to do this:
>
> GRANT ALL PRIVILEGES ON dbname.* TO username [IDENTIFIED BY 'password'];
>
> On PostgreSQL, you have to give it privileges not only to the database,
> but to all components within (tables, sequences and whatnot). The
> following three commands will grant those privileges, first to the
> database, then to the tables, then to the sequences.

In this case, why not let 'username' create the database and all its objects so
that it will have all privileges on them afterwards without any specific GRANT
required?

--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

On Mon, 2005-04-11 at 03:28 +0200, Daniel Verite wrote:
> Florin Andrei wrote:
>
> > On MySQL, it's enough to do this:
> >
> > GRANT ALL PRIVILEGES ON dbname.* TO username [IDENTIFIED BY 'password'];
> >
> > On PostgreSQL, you have to give it privileges not only to the database,
> > but to all components within (tables, sequences and whatnot). The
> > following three commands will grant those privileges, first to the
> > database, then to the tables, then to the sequences.
>
> In this case, why not let 'username' create the database and all its objects so
> that it will have all privileges on them afterwards without any specific GRANT
> required?

Those are not system accounts, just DB accounts.

--
Florin Andrei

http://florin.myip.org/

is it possible to have nested groups in postgres like in Adaptive
server anywhare , I couldn't find anything about it in the help

thanks

Hugo

Hugo <htakada(at)gmail(dot)com> writes:
> is it possible to have nested groups in postgres

Not at the moment. There are plans to have 'em for 8.1, though I can't
promise for sure that it will get done in time.

regards, tom lane

On Thursday 14 April 2005 00:33, Florin Andrei wrote:
> On Mon, 2005-04-11 at 03:28 +0200, Daniel Verite wrote:
> > Florin Andrei wrote:
> > > On MySQL, it's enough to do this:
> > >
> > > GRANT ALL PRIVILEGES ON dbname.* TO username [IDENTIFIED BY
> > > 'password'];
> > >
> > > On PostgreSQL, you have to give it privileges not only to the database,
> > > but to all components within (tables, sequences and whatnot). The
> > > following three commands will grant those privileges, first to the
> > > database, then to the tables, then to the sequences.
> >
> > In this case, why not let 'username' create the database and all its
> > objects so that it will have all privileges on them afterwards without
> > any specific GRANT required?
>
> Those are not system accounts, just DB accounts.
>

And? CREATE DATABASE myblog WITH owner blogsoftware;

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL