User:Akistler

User Information

• Name: Allen Kistler
• Fedora Account: akistler
• Email: akistler AT fedoraproject DOT org
• Personal: http://akistler.fedorapeople.org/
• IRC: a-k on freenode (#fedora and #fedora-admin mostly)
• GPG key ID: 0D6A9E5B
• Location: Chicago, IL USA

I've been an RHL/Fedora user since RHL 7.0. I'm also an RHCE.

You can leave me messages on my Talk page, although I make no guarantee how soon I'll read it.

Contributions to Fedora

Member of the Fedora Infrastructure team.

I also find bugs and post them to Bugzilla, sometimes with patches.

Current Effort

FedoraProject Search Engine

Notes and Reference

PostgreSQL

The Database Infrastructure SOP includes the following steps to help secure a database:

• db2 $ sudo -u postgres createdb -E utf8 NEWDB
• db2 $ sudo -u postgres createuser -P -E NEWDBUSER
  Password: <randomly generated password>
• db2 $ sudo -u postgres psql NEWDB
• NEWDB=# revoke all on database NEWDB from public;
• NEWDB=# revoke all on schema public from public;
• NEWDB=# grant all on schema public to NEWDBUSER;
• NEWDB=# [grant other permissions to NEWDBUSER as appropriate for your app]

Generally preferred practices that go beyond the above standard:

1. The database user (typically an application or process ID, but possibly an interactive ID) should have a lower privilege level than the database owner. In particular, the database user should not have the ability to CREATE or DROP anything.
2. The database owner should have a lower privilege level than the system or superuser. The superuser is not subject to any privilege checks. Therefore it should be used only sparingly or never. Database owners, instead of superusers, may create users and assign passwords.

Unfortunately, in PostgreSQL, not all superuser tasks can be delegated to database owners, because postgres owns all the schema tables in all the databases. It therefore makes little sense to give DB owners the ability to create databases. Depending on the installation, DB owners may create roles, although that capability is not included in this recommendation. Note that usage access to the public schema only allows resolution of symbolic names into object IDs (OIDs). The following is a suggested standard.

• In pg_hba.conf

local all postgres ident sameuser

local all all md5

• As postgres (one-time setup)

# create a non-superuser admin/owner and leave public usage on the public schema

$ sudo -u postgres createuser -P -E -S -D -R dbowner

$ sudo -u postgres psql

=# revoke all privileges on database postgres from public;

=# revoke create on schema public from public;

(tbd) *** ??? grant all privileges on schema public to dbowner; ??? ***

• As postgres (once per database)

# create the new db and leave public usage on the public schema

$ sudo -u postgres createdb -O dbowner -E utf8 <newDB>

$ sudo -u postgres psql

=# revoke all privileges on database <newDB> from public;

=# \c <newDB>

=# revoke create on schema public from public;

=# grant all privileges on schema public to dbowner; (tbd - ??? need to match in postgres ???)

• As postgres (once per user per database)

# only once, even if multiple databases

$ sudo -u postgres createuser -P -E -S -D -R <newDB-user>

# per database, if required

$ sudo -u postgres psql

=# grant temp on database <newDB> to <newDB-user>;

• As dbowner (once per database or as needed)

$ psql -U dbowner <newDB>

# Run whatever scripts to create/update the schema and dictionary

# Grant whatever Select/Insert/Update/Delete/etc. privileges to <newDB-user>