Re: Post Install / Secure PostgreSQL

On Mon, Sep 13, 2010 at 1:57 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> It's definitely a good idea not to use a superuser account when you
> don't have to; just like you don't use Unix root unless you have to.
> You should do your day-to-day database hacking in an ordinary
> unprivileged account.

When I am logged into my Linux DB server as the 'postgres' user, I can
run the shell command 'createuser <user_name>' and that shows me the
following:

# createuser cmennens
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y

Does what I displayed above create a an account that can do
administrative tasks like creating/deleting users, changing their
passwords, etc, but can't hose the system catalogs or do other serious
damage? If what I did doesn't, should I do this using the 'CREATEROLE'
option manually in PostgreSQL?

> There is also an intermediate level, which is an account with the
> CREATEROLE option (if you're on a PG version new enough to have that).
> That kind of account can do administrative things like creating/deleting
> users, changing their passwords, etc, but it can't directly munge system
> catalogs or do other things that can seriously screw up your database.
>
> I'd suggest creating "carlos" as either a plain user or a CREATEROLE
> user depending on whether you think you're likely to be adding/deleting
> plain users regularly.

I also noticed that I created a database called 'ide' in PostgreSQL as
the 'postgres' super user and I am trying to change the owner of the
database to me <cmennens> and when I run the following command, I
don't get an error but the owner doesn't appear to change for some
reason. What am I doing wrong?

ide=# \c ide
psql (8.4.4)
You are now connected to database "ide".

ide=# ALTER DATABASE ide OWNER TO cmennens;
ALTER DATABASE

ide=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | users | table | postgres
(1 row)

Any ideas if I am missing something here?

Thank you very much for all your support so far!