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/


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.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/


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Hugo <htakada@gmail.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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)