Re: Post Install / Secure PostgreSQL

On 14/09/2010 1:57 AM, Tom Lane wrote:
> 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'd second that.

When I install a new instance of PostgreSQL, I usually set up a "craig"
user to match my Linux login ID. This user has CREATEDB and CREATEROLE
rights, but is not a superuser.

This account will be used automatically by psql unless I override it,
because psql defaults to local unix socket logins with the same
postgresql username as the unix username. pg_hba.conf by default permits
local unix users to use the postgresql user account with the same user
name as their unix account. So I can just run "psql databasename" to
connect to any database that I've granted access rights to "craig" for.

I then usecreate a "craig" database as a test area / playpen. This will
be connected to by default if I run psql without any arguments.

So:

craig$ sudo -u postgres psql
postgres=> CREATE USER craig WITH PASSWORD 'somepassword'
            CREATEDB CREATEROLE;
postgres=> CREATE DATABASE craig WITH OWNER craig;
postgres=> \q

Now I can connect to my new default database with a simple "psql". For
any real work I make new databases, but the "craig" database is handy
for general testing and playing around. I generally revoke public
connect rights on those databases, permitting only specific users to
connect even if they're authenticated and allowed access to other databases.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/