Post Install / Secure PostgreSQL

On Fri, Sep 10, 2010 at 8:12 AM, Carlos Mennens
<carlos.mennens@gmail.com> wrote:

Do you guys know if this exist for PostgreSQL and or do you have any
suggestions for a fresh installation of PostgreSQL on Linux?

I don't believe there is a script like this. However, I would say
that out of the box, PostgreSQL is so secure that some people cannot
figure out how to log in. :)

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Carlos Mennens <carlos.mennens@gmail.com> writes:

I am brand new to PostgreSQL and coming from MySQL. My question is
does anyone know after I install PostgreSQL on my Linux server, is
there a script that secures the database like MySQL offers in most
Linux distributions? I think the script for MySQL is
"/usr/bin/mysql_secure_installation". I checked and there doesn't
appear to be on located there specifically for PostgreSQL. This script
is nice because it lets people who are new to databases set root
password, disable anonymous accounts, remove anonymous accounts,
remove test databases, disable remote root logins to databases.

The reason mysql has such a script is that their installations are
insecure by default. Postgres installations aren't, unless you
tell initdb to use "trust" mode, which isn't usual in prepackaged
distributions.

regards, tom lane

On Fri, Sep 10, 2010 at 11:33 AM, Richard Broersma
<richard.broersma@gmail.com> wrote:

I don't believe there is a script like this.  However, I would say
that out of the box, PostgreSQL is so secure that some people cannot
figure out how to log in. :)

I agree and I am just now learning this. I can't seem to find out how
to login to the database. I am using 'psql -U root' however during my
installation there may have been a default password used which I am
not aware of. I need to read the docs and see how to login to the
database.

On Fri, Sep 10, 2010 at 11:53:12AM -0400, Carlos Mennens wrote:

On Fri, Sep 10, 2010 at 11:33 AM, Richard Broersma
<richard.broersma@gmail.com> wrote:

I don't believe there is a script like this. �However, I would say
that out of the box, PostgreSQL is so secure that some people
cannot figure out how to log in. :)

I agree and I am just now learning this. I can't seem to find out
how to login to the database. I am using 'psql -U root' however
during my installation there may have been a default password used
which I am not aware of. I need to read the docs and see how to
login to the database.

This is where MySQL's crazily-insecure-by-default assumptions are
messing you up.

The root user has nothing to do with PostgreSQL, except in the sense
that root installs software. Thereafter, the postgres (or pgsql on
some of the BSDs) user is the database superuser. Once it's
installed, try:

su - postgres
psql -l

Happy PostgreSQLing :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

   su - postgres
   psql -l

If you didn't set a password for the postgres user it's actually:

sudo su - postgres

I can't seem to find out how
to login to the database. I am using 'psql -U root' however during my
installation there may have been a default password used which I am
not aware of. I need to read the docs and see how to login to the
database.

I usually do like this on a new box

sudo su -
su - postgres
createuser bnl
exit
exit
createdb bnl
psql

That is, I create a user in the db with same name as my os user (linux
here)
then I log out from pg superuser account, and go back to my
os user, and create a database with that os user name.
That is the default db that psql tries to log in to...

--
björn lundin

Carlos Mennens wrote on 10.09.2010 17:53:

On Fri, Sep 10, 2010 at 11:33 AM, Richard Broersma
<richard.broersma@gmail.com> wrote:

I don't believe there is a script like this. However, I would say
that out of the box, PostgreSQL is so secure that some people cannot
figure out how to log in. :)

I agree and I am just now learning this. I can't seem to find out how
to login to the database. I am using 'psql -U root' however during my
installation there may have been a default password used which I am
not aware of. I need to read the docs and see how to login to the
database.

Normally the superuser is called "postgres".

I don't think there is a account named "root" after a default installation.

Regards
Thomas

On 09/11/2010 01:39 AM, Arjen Nienhuis wrote:

su - postgres
psql -l

If you didn't set a password for the postgres user it's actually:

sudo su - postgres

Better written, and less prone to being broken by odd shell setups, as:

sudo -u postgres psql

--
Craig Ringer

On Fri, Sep 10, 2010 at 01:23:39PM -0700, bjjjrn lundin wrote:

I usually do like this on a new box

sudo su -
su - postgres
createuser bnl
exit
exit

It would be somewhat easier to use sudo's "-u" switch, the following
should do the same as the above:

sudo -u postgres createuser "$USER"

--
Sam http://samason.me.uk/

Thanks for all the assistance and clarification with my new install of
PostgreSQL. I am able to switch users to 'postgres' and verify the
default home directory for 'postgres' shell user:

[root@db1 ~]# su - postgres

[postgres@db1 ~]$ pwd
/var/lib/postgres

I am also now able from the documentation to understand how I can
"create" a database and "drop" a database but thats about all I can
figure out for now.

In MySQL, it was recommended that you create a power user account
rather than manage the database with the 'root' account. Is this also
the same thing for PostgreSQL? I know you guys told me that there is
no 'root' account but there is a 'postgres' account which appears to
be the equivalent of MySQL's 'root' database user. My question is do I
need to or is it recommended I create a 'carlos' account and grant
privileges to that user rather than manage the database with the
'postgres' super user account?

test=# SELECT * FROM "pg_user";
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd |
valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+----------+-----------
postgres | 10 | t | t | t | ******** |
|
cmennens | 16393 | f | f | f | ******** |
|
(2 rows)

Carlos Mennens <carlos.mennens@gmail.com> writes:

In MySQL, it was recommended that you create a power user account
rather than manage the database with the 'root' account. Is this also
the same thing for PostgreSQL? I know you guys told me that there is
no 'root' account but there is a 'postgres' account which appears to
be the equivalent of MySQL's 'root' database user. My question is do I
need to or is it recommended I create a 'carlos' account and grant
privileges to that user rather than manage the database with the
'postgres' super user account?

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.

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.

regards, tom lane

On Mon, Sep 13, 2010 at 1:57 PM, Tom Lane <tgl@sss.pgh.pa.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!

On Mon, Sep 13, 2010 at 12:24 PM, Carlos Mennens
<carlos.mennens@gmail.com> wrote:

On Mon, Sep 13, 2010 at 1:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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!

The table owner isn't the same as the db owner. Whoever created the
table owns it. Try \l to see a list of databases.

Also note that instead of reassigning all those table owners by name
you can grant membership of a user to that "role":

grant ide to myrole;

--
To understand recursion, one must first understand recursion.

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/

On Mon, Sep 13, 2010 at 9:58 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

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

So I set a Linux shell password on my newly auto created 'postgres'
system user which is what I use to login to the database as
'superuser'. Now I know my password for 'postgres' in the Linux shell
but I still don't understand what the database password is for
'postgres'. In MySQL there is a root shell user (obviously) and then
rather than 'postgres' for the database super user, there is a 'root'
database user and I can set that password individually from the
matching shell account.

So maybe I am still lost but it appears that the database user
'postgres' has a password unique to PostgreSQL, right?

postgres=# SELECT * from pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd |
valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+----------+-----------
postgres | 10 | t | t | t | ******** |
|
carlos | 16384 | t | t | t | ******** |
|

Obviously there appears to be a specific password for both accounts
which I think are completely seperate from the Linux shell passwords,
right?

Secondly I am unable to find any information in the docs that show me
how to set just the user password for 'carlos'. In MySQL I would use:

SET PASSWORD FOR 'carlos'@'localhost' = PASSWORD('newpass');

On September 14, 2010 09:50:30 am Carlos Mennens wrote:

Obviously there appears to be a specific password for both accounts
which I think are completely seperate from the Linux shell passwords,
right?

PostgreSQL has internal passwords for roles which can be set with "alter role"
or while creating roles.

However ..

The default setup allows "trust" access which means it trusts local system
accounts to login as the same roles in Pg without specifying a password.

It is also possible to setup Pg in such a way that it uses the system
passwords via PAM without consulting the internal password.

On Tue, Sep 14, 2010 at 9:50 AM, Carlos Mennens
<carlos.mennens@gmail.com> wrote:

Secondly I am unable to find any information in the docs that show me
how to set just the user password for 'carlos'. In MySQL I would use:

SET PASSWORD FOR 'carlos'@'localhost' = PASSWORD('newpass');

You'd want to use "ALTER USER"

http://www.postgresql.org/docs/8.4/interactive/sql-alteruser.html

So:

ALTER USER carlos WITH ENCRYPTED PASSWORD 'password';

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Tue, Sep 14, 2010 at 1:52 PM, Richard Broersma <richard.broersma@gmail.com>

You'd want to use "ALTER USER"

http://www.postgresql.org/docs/8.4/interactive/sql-alteruser.html

So:

ALTER USER carlos WITH ENCRYPTED PASSWORD 'password';

I find it strange when I am logged in as super user 'postgres' and
type the exact syntax but after 'carlos' above, for some reason it no
longer auto completes 'WITH' for some reason. Then when I manually
just type the word 'WITH' even though the tab auto-complete didn't
recognize it. I then type 'ENCRY' and press the tab key, PostgreSQL
for some odd reason changes the syntax of 'ENCRY' to 'RECURSIVE'. I
don't understand this database behavior & understand that I can
manually just type everything you posted above and the command works
but I depend on auto complete and this doesn't make any sense.

Am I doing something wrong for why PostgreSQL just randomly alters my
input when I press the 'tab' key?

On 15/09/2010 12:50 AM, Carlos Mennens wrote:

On Mon, Sep 13, 2010 at 9:58 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

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

So I set a Linux shell password on my newly auto created 'postgres'
system user

You can do that, though you don't need to. I usually just sudo to it.

which is what I use to login to the database as
'superuser'. Now I know my password for 'postgres' in the Linux shell
but I still don't understand what the database password is for
'postgres'.

You need to read the manual. It explains how authentication and login
roles work. In particular, it explains pg_hba.conf and the "ident",
"trust" and "md5" authentication modes.

http://www.postgresql.org/docs/current/interactive/client-authentication.html

So maybe I am still lost but it appears that the database user
'postgres' has a password unique to PostgreSQL, right?

Correct. However, it doesn't need to have any password at all; if you're
using ident authentication, postgresql will accept a connection as
"postgres" only from the local unix user "postgres". No need for a
password, you've already convinced the OS you have the access rights.

If you're using "md5" (password) authentication, then you need to set a
password for the postgres database user.

See the manual.

postgres=# SELECT * from pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd |
valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+----------+-----------
postgres | 10 | t | t | t | ******** |
|
carlos | 16384 | t | t | t | ******** |
|

You'd usually use the psql command:

\du

for a better view. See:

\?

in psql

Obviously there appears to be a specific password for both accounts
which I think are completely seperate from the Linux shell passwords,
right?

Correct.

Secondly I am unable to find any information in the docs that show me
how to set just the user password for 'carlos'. In MySQL I would use:

ALTER USER username SET PASSWORD 'somepassword';

See the manual, and the psql \h command

\h -- statement listing
\h ALTER USER -- syntax of alter user

http://www.postgresql.org/docs/current/interactive/sql-alteruser.html

--
Craig Ringer

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