Thread: Re-nameing a database?
Hi all, is there an easy way to re-name a database? Best regards, Chris -- Chris Ruprecht Network grunt and bit pusher extraordinaíre _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Can I restrict a user to a single database? It seems like any user can
connect to any database. . .
-Jon
---
Voice: (973) 560-9095 | Internection - Electronic Commerce, Internet
Cell : (973) 801-6071 | Consulting and Grade A Web Hosting Since 1995.
Chris Ruprecht <chrup999@yahoo.com> writes:
> is there an easy way to re-name a database?
Officially, no. Unofficially, since 7.1 it should work to UPDATE the
appropriate row in pg_database. I think. Better try it on a test
database...
regards, tom lane
Jon, the only way I can think of doing this is by revoking the user's rights to the tables of the databases they have no business in. I know this is not exactly the answer you're looking for ... Best regards, Chris At 17:54 -0500 02/10/2002, Jon August wrote: >Can I restrict a user to a single database? It seems like any user can >connect to any database. . . > > -Jon > >--- >Voice: (973) 560-9095 | Internection - Electronic Commerce, Internet >Cell : (973) 801-6071 | Consulting and Grade A Web Hosting Since 1995. > > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html -- Chris Ruprecht Network grunt and bit pusher extraordinaíre _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Thanks, Tom, I will play around with it a bit. Best regards, Chris At 19:07 -0500 02/10/2002, Tom Lane wrote: >Chris Ruprecht <chrup999@yahoo.com> writes: >> is there an easy way to re-name a database? > >Officially, no. Unofficially, since 7.1 it should work to UPDATE the >appropriate row in pg_database. I think. Better try it on a test >database... > > regards, tom lane -- Chris Ruprecht Network grunt and bit pusher extraordinaíre _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
That seems like a lot of work. . . and could become easily outdated. No?
How about in pg_hba.conf? There's no way to say only user xyz can connect
to database abc?
I tried something like this in my pg_hba.conf, but now nobody can connect
to it.
local all password postgres
host all xxx.xxx.xxx.xxx 255.255.255.255 password postgres
local shopdb password mark
host shopdb xxx.xxx.xxx.xxx 255.255.255.255 password mark
I was hoping this config would permit user "postgres" to connect to any
database, but limit user "mark" to shopdb.
Maybe I'm missing something, but isn't this a serious security problem for
postgres installations used by many people - like in an ISP environment?
-Jon
On Sun, 10 Feb 2002, Chris Ruprecht wrote:
> Jon,
>
> the only way I can think of doing this is by revoking the user's
> rights to the tables of the databases they have no business in.
>
> I know this is not exactly the answer you're looking for ...
>
> Best regards,
> Chris
>
> At 17:54 -0500 02/10/2002, Jon August wrote:
> >Can I restrict a user to a single database? It seems like any user can
> >connect to any database. . .
> >
> > -Jon
> >
On Sun, 10 Feb 2002, Jon August wrote: > > That seems like a lot of work. . . and could become easily outdated. No? > > How about in pg_hba.conf? There's no way to say only user xyz can connect > to database abc? > > I tried something like this in my pg_hba.conf, but now nobody can connect > to it. > > local all password postgres > host all xxx.xxx.xxx.xxx 255.255.255.255 password postgres > local shopdb password mark > host shopdb xxx.xxx.xxx.xxx 255.255.255.255 password mark > > I was hoping this config would permit user "postgres" to connect to any > database, but limit user "mark" to shopdb. The final argument goes to an external file in $PGDATA that lists users (and optionally passwords) for the users that are allowed into the db.
Not in postgres, unfortunately. I'd love to be wrong. At 04:26 PM 2/10/02 -0600, Chris Ruprecht wrote: >Hi all, > >is there an easy way to re-name a database? > >Best regards, >Chris > >-- >Chris Ruprecht >Network grunt and bit pusher extraordinaíre > >_________________________________________________________ >Do You Yahoo!? >Get your free @yahoo.com address at http://mail.yahoo.com > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://archives.postgresql.org -- Naomi Walker Chief Information Officer Eldorado Computing, Inc. 602-604-3100 ext 242
On Mon, 2002-02-11 at 15:50, Naomi Walker wrote:
> Not in postgres, unfortunately. I'd love to be wrong.
>
> At 04:26 PM 2/10/02 -0600, Chris Ruprecht wrote:
>
> >Hi all,
> >
> >is there an easy way to re-name a database?
In 7.1.3, you can do it by updating the row in pg_database and then
restarting the postmaster:
$ psql template1
...
template1=# \l
List of databases
Database | Owner | Encoding
-----------+----------+-----------
junk | olly | SQL_ASCII
...
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(8 rows)
template1=# update pg_database set datname = 'rubbish' where datname =
'junk';
UPDATE 1
template1=# \l
List of databases
Database | Owner | Encoding
-----------+----------+-----------
rubbish | olly | SQL_ASCII
...
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(8 rows)
template1=# \c rubbish
FATAL 1: Database "rubbish" does not exist in the system catalog.
Previous connection kept
template1=# \c junk
FATAL 1: Database "junk", OID 44062648, has disappeared from
pg_database
Previous connection kept
template1=# \q
$ sudo /etc/init.d/postgresql restart
Password:
Restarting PostgreSQL database: postmaster
Stopped /usr/lib/postgresql/bin/postmaster (pid 15397).
Starting PostgreSQL postmaster.
postmaster successfully started
$ psql template1
...
template1=# \c rubbish
You are now connected to database rubbish.
rubbish=# \c junk
FATAL 1: Database "junk" does not exist in the system catalog.
Previous connection kept
rubbish=#
An alternative method would be to dump everything with pg_dumpall and
then edit the dump to change the database name.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
Oliver Elphick <olly@lfix.co.uk> writes:
>> is there an easy way to re-name a database?
> In 7.1.3, you can do it by updating the row in pg_database and then
> restarting the postmaster:
I don't believe a postmaster restart is required. It might be a good
idea to vacuum pg_database and then force a checkpoint, however, just
to make sure the updated row is out on disk and not still hanging about
in a shared-memory buffer.
regards, tom lane
What about through pg_hba.conf? We've set it up so to connect to a database, you must be in pg_pwd (through pg_passwd), and know the passwd. I will mention, that pg_pwd should not be clear text. Any plan to encrypt it? At 06:12 PM 2/10/02 -0600, Chris Ruprecht wrote: >Jon, > >the only way I can think of doing this is by revoking the user's rights to >the tables of the databases they have no business in. > >I know this is not exactly the answer you're looking for ... > >Best regards, >Chris > >At 17:54 -0500 02/10/2002, Jon August wrote: >>Can I restrict a user to a single database? It seems like any user can >>connect to any database. . . >> >> -Jon >> >>--- >>Voice: (973) 560-9095 | Internection - Electronic Commerce, Internet >>Cell : (973) 801-6071 | Consulting and Grade A Web Hosting Since 1995. >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 5: Have you checked our extensive FAQ? >> >>http://www.postgresql.org/users-lounge/docs/faq.html > > >-- >Chris Ruprecht >Network grunt and bit pusher extraordinaíre > >_________________________________________________________ >Do You Yahoo!? >Get your free @yahoo.com address at http://mail.yahoo.com > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Naomi Walker Chief Information Officer Eldorado Computing, Inc. 602-604-3100 ext 242