Обсуждение: Post Install / Secure PostgreSQL

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.

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

PS - I am only looking to manage PostgreSQL via CLI only. I have no
GUI or access to pgadmin3 or php GUI's.

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 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 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 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/

Thanks all for the help! I have a much better understanding now of how
user accounts are managed via ident authentication.

The only thing I have yet to figure out or understand is how to login
to PostgreSQL as my user account but not to any specific database. I
understand that when I am logged in as my user account, I can simply
login using:

psql -U cmennens <database_name> (I know I can omit the '-U cmennens'
if I am 'cmennens' via Linux shell)

But when if 'cmennens' wants to login to PostgreSQL but not connect to
any specific database? I know in MySQL you can login to the MySQL
server CLI but not be attached to any specific database if you want to
just peek around and do basic administrative tasks. Is this possible
in PostgreSQL?

When I do the following, it works but I think only because it's using
ident credentials and then connects automatically to the 'postgres'
database. I could be wrong however...

[postgres@db1 ~]$ psql
psql (8.4.4)
Type "help" for help.

postgres=# \d
No relations found.
postgres=# \dt
No relations found.
postgres=# \c postgres
psql (8.4.4)
You are now connected to database "postgres".
postgres=# \dt
No relations found.
postgres=# \d

From the above I login as 'postgres' but don't specify a specific
database to connect to. Is this possible to do as my user 'cmennens'
who is listed as a super user?

Also from above, if I connect to the 'postgres' database as 'postgres'
user, why can I not list any tables above? I keep getting "No
relations found."?

  On 09/15/10 10:00 AM, Carlos Mennens wrote:
> But when if 'cmennens' wants to login to PostgreSQL but not connect to
> any specific database? I know in MySQL you can login to the MySQL
> server CLI but not be attached to any specific database if you want to
> just peek around and do basic administrative tasks. Is this possible
> in PostgreSQL?

no, there is no such state in postgres.  you connect and log into a
database.  connecting to a different database requires closing that
connection and opening a new one (which is what the \c command does in
psql).

On Wed, Sep 15, 2010 at 1:34 PM, John R Pierce <pierce@hogranch.com> wrote:
> no, there is no such state in postgres.  you connect and log into a
> database.  connecting to a different database requires closing that
> connection and opening a new one (which is what the \c command does in
> psql).

Thanks but then I am confused why I am getting the following:

postgres=# \d
No relations found.
postgres=# \dt
No relations found.
postgres=# \c postgres
psql (8.4.4)
You are now connected to database "postgres".
postgres=# \dt
No relations found.
postgres=# \d

  On 09/15/10 10:36 AM, Carlos Mennens wrote:
> On Wed, Sep 15, 2010 at 1:34 PM, John R Pierce<pierce@hogranch.com>  wrote:
>> no, there is no such state in postgres.  you connect and log into a
>> database.  connecting to a different database requires closing that
>> connection and opening a new one (which is what the \c command does in
>> psql).
> Thanks but then I am confused why I am getting the following:
>
> postgres=# \d
> No relations found.
> postgres=# \dt
> No relations found.
> postgres=# \c postgres
> psql (8.4.4)
> You are now connected to database "postgres".
> postgres=# \dt
> No relations found.
> postgres=# \d
>

the 'postgres' database on your system is empty.   this is quite
typical, as that database is simply a convenience for the postgres user
to have something to log into while doing his administrative duties.

On Wed, Sep 15, 2010 at 10:36 AM, Carlos Mennens
<carlos.mennens@gmail.com> wrote:

> postgres=# \d
> No relations found.
> postgres=# \dt
> No relations found.
> postgres=# \c postgres
> psql (8.4.4)
> You are now connected to database "postgres".
> postgres=# \dt
> No relations found.
> postgres=# \d

try:

select current_database();



--
Regards,
Richard Broersma Jr.

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

John R Pierce <pierce@hogranch.com> writes:
>   On 09/15/10 10:00 AM, Carlos Mennens wrote:
>> But when if 'cmennens' wants to login to PostgreSQL but not connect to
>> any specific database? I know in MySQL you can login to the MySQL
>> server CLI but not be attached to any specific database if you want to
>> just peek around and do basic administrative tasks. Is this possible
>> in PostgreSQL?

> no, there is no such state in postgres.  you connect and log into a
> database.  connecting to a different database requires closing that
> connection and opening a new one (which is what the \c command does in
> psql).

It might be worth pointing out that what mysql calls a database is more
or less what we call a schema; there isn't any close equivalent in mysql
to a Postgres installation with multiple databases.  The initial state
in mysql is about like having an empty search_path in PG: you can get at
all tables in the database, you just have to qualify their names
explicitly.  And "USE database" corresponds to a "SET search_path"
operation.

            regards, tom lane

On Wed, Sep 15, 2010 at 1:43 PM, John R Pierce <pierce@hogranch.com> wrote:

> the 'postgres' database on your system is empty.   this is quite typical, as
> that database is simply a convenience for the postgres user to have
> something to log into while doing his administrative duties.

OK this makes sense and I couldn't find in the docs or any reading
that by default the 'postgres' database is empty and there for just a
space for the 'postgres' user to login to. That explains a lot but
when I run:

postgres=# SELECT * FROM pg_user;
 usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  |
valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+----------+-----------
 postgres |       10 | t           | t        | t         | ******** |
         |
 webmail  |    16384 | f           | f        | f         | ******** |
         |
 carlos   |    16385 | t           | t        | t         | ******** |
         |
(3 rows)

Doesn't that show I'm connected to the 'postgres' database and there
is a table called 'pg_user' which holds all my PostgreSQL user info?
That doesn't make sense to me if the database is empty unless I am
missing something here. The only way I knew 'pg_user' was available
was because I ran the command '\dS'.

On September 15, 2010 11:10:45 am Carlos Mennens wrote:
> Doesn't that show I'm connected to the 'postgres' database and there
> is a table called 'pg_user' which holds all my PostgreSQL user info?
> That doesn't make sense to me if the database is empty unless I am
> missing something here. The only way I knew 'pg_user' was available
> was because I ran the command '\dS'.

system tables don't show up in normal views. I don't think they really belong
to particular databases either, but someone might correct me on that.

  On 09/15/10 11:10 AM, Carlos Mennens wrote:
> On Wed, Sep 15, 2010 at 1:43 PM, John R Pierce<pierce@hogranch.com>  wrote:
>
>> the 'postgres' database on your system is empty.   this is quite typical, as
>> that database is simply a convenience for the postgres user to have
>> something to log into while doing his administrative duties.
> OK this makes sense and I couldn't find in the docs or any reading
> that by default the 'postgres' database is empty and there for just a
> space for the 'postgres' user to login to. That explains a lot but
> when I run:
>
> postgres=# SELECT * FROM pg_user;
>   usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  |
> valuntil | useconfig
> ----------+----------+-------------+----------+-----------+----------+----------+-----------
>   postgres |       10 | t           | t        | t         | ******** |
>           |
>   webmail  |    16384 | f           | f        | f         | ******** |
>           |
>   carlos   |    16385 | t           | t        | t         | ******** |
>           |
> (3 rows)
>
> Doesn't that show I'm connected to the 'postgres' database and there
> is a table called 'pg_user' which holds all my PostgreSQL user info?
> That doesn't make sense to me if the database is empty unless I am
> missing something here. The only way I knew 'pg_user' was available
> was because I ran the command '\dS'.

there is an extensive pg_catalog schema containing the system tables
which are shared by all databases in the cluster.    pg_catalog.pg_user
is the same view in all databases.

Alan Hodgson <ahodgson@simkin.ca> writes:
> On September 15, 2010 11:10:45 am Carlos Mennens wrote:
>> Doesn't that show I'm connected to the 'postgres' database and there
>> is a table called 'pg_user' which holds all my PostgreSQL user info?
>> That doesn't make sense to me if the database is empty unless I am
>> missing something here. The only way I knew 'pg_user' was available
>> was because I ran the command '\dS'.

> system tables don't show up in normal views. I don't think they really belong
> to particular databases either, but someone might correct me on that.

There are a small number of "shared" catalogs, in particular the list of
users and the list of databases, that are visible in all databases.
Most of the catalogs are per-database, though, so that they can have
different contents in different databases.

            regards, tom lane

On 16/09/2010 2:10 AM, Carlos Mennens wrote:

> postgres=# SELECT * FROM pg_user;

> Doesn't that show I'm connected to the 'postgres' database and there
> is a table called 'pg_user' which holds all my PostgreSQL user info?
> That doesn't make sense to me if the database is empty unless I am
> missing something here. The only way I knew 'pg_user' was available
> was because I ran the command '\dS'.

pg_user is in the pg_catalog schema. The pg_catalog schema is not on the
default search_path so it is not shown by default in \dt etc. Again, see
the manual:

http://www.postgresql.org/docs/current/interactive/ddl-schemas.html

Since the pg_catalog schema is in all databases automatically, when we
say a database is "empty" what we really mean is that it contains only
the pg_catalog schema, a default "public" schema with no relations in
it, and no other schema.

It might help to think about the pg_catalog schema as somewhat like the
"mysql" database in MySQL in that it contains system information and is
used to control the database system's operation. However, unlike the
"mysql" database, you should never, EVER alter or edit it directly
unless you *really* know what you are doing. Always use the built-in
ALTER/CREATE/DROP commands.

I cannot recommend reading the PostgreSQL manual enough. It's pretty
comprehensive, well written (IMO) and will teach you about SQL in
general as well as PostgreSQL in particular.

--
Craig Ringer

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

Alan Hodgson wrote:
> 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.
>

There's two small inaccuracies with how you're describing this part.
First, having "trust" be the default is the case for PostgreSQL itself.
But many of the packaged versions of it instead default to "ident".  You
really need to look at the pg_hba.conf file after you first do an
installation on a new operating system or packaging type to know for
sure what it did, if you let that package creating the cluster for you.

Second, it's "ident" that lets you login only to the role that matches
your system account.  If you use "trust", you can login as any database
user from any system account.  It's kind of disturbing to some people
when they realize they can be logged into a regular account and go "psql
-U postgres" and they're right in as the database superuser in that
configuration.

As a larger commentary on this somewhat old thread I'm just getting to
now, it's easy to point at MySQL and laugh at the insecure by default
setup.  It's just as easy to point and laugh at how complicated it is
for those new to PostgreSQL to get the basic things most people want
working.  I can imagine a small script similar to the MySQL one--I guess
we could call it postgresql_unsecure_installation--that asked a few
questions and did things like setup the PostgreSQL account with a
password, switch to md5 authentication, set listen_address, and turn on
TCP/IP for the local LAN in the pg_hba.conf.  The saga Carlos has gone
through here is repeated over and over again by those new to PostgreSQL,
and not making it easier to do this extremely common sequence crossed
over into being an advocacy issue a while ago in my mind.  It would be a
great script for someone who wanted to contribute something to
PostgreSQL, but doesn't feel comfortable working on the core code, to write.

--
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"    Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book