Thread: User Management
Coming from a MySQL background, the way PostgreSQL deals with users seems whacked out to say the least. And that's where I'm having my problem. Now in my pg_hba.conf file I have; local all password local chris password passwd so when I go 'select * from pg_shadow' I see the root user I added with 'create user'. With this user I can log into any database and create any database. Which is perfect. However, my problem comes in when I try delegate access to different databases for different users. Say I want the user 'joe' to have access to the database 'chris'. The way I tried to do this is in the file 'passwd' I added; joe:joepass which is in the same directory as my pg_hba.conf. Not only does it seem Postgres isn't reading this file, but how can I have 'joe' own the database when a database is owned my whoever made it? Joe doesn't have the ability to create databases because I only want joe to play with his own database. Needless to say I'm confused. I've scoured the online manuals, and the PostgreSQL book is next to useless for indepth administration. Any help, pointers, or links would be much appreciated, Chris
use the "createuser" command On Wed, 4 Jul 2001, Chris Cameron wrote: > Coming from a MySQL background, the way PostgreSQL deals with users > seems whacked out to say the least. And that's where I'm having my > problem. > > Now in my pg_hba.conf file I have; > local all password > local chris password passwd > > so when I go 'select * from pg_shadow' I see the root user I added with > 'create user'. With this user I can log into any database and create any > database. Which is perfect. However, my problem comes in when I try > delegate access to different databases for different users. Say I want > the user 'joe' to have access to the database 'chris'. The way I tried > to do this is in the file 'passwd' I added; > joe:joepass > > which is in the same directory as my pg_hba.conf. Not only does it seem > Postgres isn't reading this file, but how can I have 'joe' own the > database when a database is owned my whoever made it? Joe doesn't have > the ability to create databases because I only want joe to play with his > own database. > > Needless to say I'm confused. I've scoured the online manuals, and the > PostgreSQL book is next to useless for indepth administration. > > Any help, pointers, or links would be much appreciated, > > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > ----------------------------------------------------------------------------- david@backpack.com BackPack Software, Inc. www.backpack.com +1 651.645.7550 voice "Life is an Adventure. +1 651.645.9798 fax Don't forget your BackPack!" -----------------------------------------------------------------------------
That doesn't solve my problem of whoever creates the database owns the database. I'd like to have user 'joe' have access /only/ to the database 'chris'. As far as I can tell the only way to do this is have 'joe' create the database. I'm looking for the equivalent to 'GRANT ALL ON chris to joe@localhost identified by 'joepass';' in MySQL. Chris -----Original Message----- From: D. Duccini [mailto:duccini@backpack.com] Sent: Wednesday, July 04, 2001 4:48 PM To: Chris Cameron Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] User Management use the "createuser" command
postgres=# \h grant
Command: GRANT
Description: Grants access privilege to a user, a group or all users
Syntax:
GRANT privilege [, ...] ON object [, ...]
TO { PUBLIC | GROUP group | username }
> That doesn't solve my problem of whoever creates the database owns the
> database.
>
> I'd like to have user 'joe' have access /only/ to the database 'chris'.
> As far as I can tell the only way to do this is have 'joe' create the
> database.
>
> I'm looking for the equivalent to 'GRANT ALL ON chris to joe@localhost
> identified by 'joepass';' in MySQL.
>
> Chris
>
> -----Original Message-----
> From: D. Duccini [mailto:duccini@backpack.com]
> Sent: Wednesday, July 04, 2001 4:48 PM
> To: Chris Cameron
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] User Management
>
>
>
> use the "createuser" command
>
-----------------------------------------------------------------------------
david@backpack.com BackPack Software, Inc. www.backpack.com
+1 651.645.7550 voice "Life is an Adventure.
+1 651.645.9798 fax Don't forget your BackPack!"
-----------------------------------------------------------------------------
This has been working well so far, thanks. Um, looking at http://www.postgresql.org/idocs/index.php?privileges.html it seems to be that I'm not actually able to do what I want. Am I correct in believing that to let a user create tables and change tables in a database, they actually have to had created it? The best I can do is grant privileges on premade tables? Thanks, Chris -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of D. Duccini Sent: Wednesday, July 04, 2001 5:01 PM To: Chris Cameron Cc: pgsql-novice@postgresql.org Subject: RE: [NOVICE] User Management postgres=# \h grant Command: GRANT Description: Grants access privilege to a user, a group or all users Syntax: GRANT privilege [, ...] ON object [, ...] TO { PUBLIC | GROUP group | username } > That doesn't solve my problem of whoever creates the database owns the > database. > > I'd like to have user 'joe' have access /only/ to the database 'chris'. > As far as I can tell the only way to do this is have 'joe' create the > database. > > I'm looking for the equivalent to 'GRANT ALL ON chris to joe@localhost > identified by 'joepass';' in MySQL. > > Chris > > -----Original Message----- > From: D. Duccini [mailto:duccini@backpack.com] > Sent: Wednesday, July 04, 2001 4:48 PM > To: Chris Cameron > Cc: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] User Management > > > > use the "createuser" command > ------------------------------------------------------------------------ ----- david@backpack.com BackPack Software, Inc. www.backpack.com +1 651.645.7550 voice "Life is an Adventure. +1 651.645.9798 fax Don't forget your BackPack!" ------------------------------------------------------------------------ ----- ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
hi, i have this very lame problem: i created a column as varchar(15) but now i need to have more than 15 char... how can i change this ? changing it into "text" will also be good ! tnx Giorgio A.
Chris Cameron wrote: > > This has been working well so far, thanks. > > Um, looking at http://www.postgresql.org/idocs/index.php?privileges.html > it seems to be that I'm not actually able to do what I want. Am I > correct in believing that to let a user create tables and change tables > in a database, they actually have to had created it? The best I can do > is grant privileges on premade tables? It's not as straightforward, but you should also be able to grant suitable privileges on the metadata tables. Use \dS to list the metadata tables. Cheers, Andrew. -- _____________________________________________________________________ Andrew McMillan, e-mail: Andrew@catalyst.net.nz Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64(27)246-7091, Fax:+64(4)499-5596, Office: +64(4)499-2267xtn709
On 05 Jul 2001 11:09:29 +0200, Giorgio A. wrote: > hi, i have this very lame problem: i created a column as varchar(15) but now > i need to have more than 15 char... how can i change this ? changing it into > "text" will also be good ! You can't change the type of fields. You could create a new table with proper cols and 'INSERT INTO newtable SELECT * FROM oldtable;DROP TABLE oldtable;ALTER TABLE newtable RENAME TO oldtable;' or something like that. This leaves you in a mess if there are triggers involved (references etc.) I would suggest you 'pg_dump -D dbname > db.sql' modify the table definitions, 'dropdb dbname;createdb dbname;psql dbname -f db.sql' (If you can live with a short downtime and loosing some information like oids, but that should be irrelevant in most cases) cu -- Nabil Sayegh
On 05 Jul 2001 11:09:29 +0200, Giorgio A. wrote: > hi, i have this very lame problem: i created a column as varchar(15) but now > i need to have more than 15 char... how can i change this ? changing it into > "text" will also be good ! You can't change the type of fields. You could create a new table with proper cols and 'INSERT INTO newtable SELECT * FROM oldtable;DROP TABLE oldtable;ALTER TABLE newtable RENAME TO oldtable;' or something like that. This leaves you in a mess if there are triggers involved (references etc.) I would suggest you 'pg_dump -D dbname > db.sql' modify the table definitions, 'dropdb dbname;createdb dbname;psql dbname -f db.sql' (If you can live with a short downtime and loosing some information like oids, but that should be irrelevant in most cases) cu -- Nabil Sayegh
On 05 Jul 2001 11:09:29 +0200, Giorgio A. wrote: > hi, i have this very lame problem: i created a column as varchar(15) but now > i need to have more than 15 char... how can i change this ? changing it into > "text" will also be good ! You can't change the type of fields. You could create a new table with proper cols and 'INSERT INTO newtable SELECT * FROM oldtable;DROP TABLE oldtable;ALTER TABLE newtable RENAME TO oldtable;' or something like that. This leaves you in a mess if there are triggers involved (references etc.) I would suggest you 'pg_dump -D dbname > db.sql' modify the table definitions, 'dropdb dbname;createdb dbname;psql dbname -f db.sql' (If you can live with a short downtime and loosing some information like oids, but that should be irrelevant in most cases) cu -- Nabil Sayegh