Re: [GENERAL] grant all privileges to all tables in a database
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 [EMAIL PROTECTED])
Re: [GENERAL] grant all privileges to all tables in a database
Hugo <[EMAIL PROTECTED]> 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
Re: [GENERAL] grant all privileges to all tables in a database
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] grant all privileges to all tables in a database
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] grant all privileges to all tables in a database
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
Re: [GENERAL] grant all privileges to all tables in a database
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
[GENERAL] 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/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
