Re: Post Install / Secure PostgreSQL

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/