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

SOP

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 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.
2. The database user (typically an application or process ID, but possibly an interactive ID) should have a lower privilege level than the database owner. For example, the database user should not have the ability to CREATE or DROP anything.

Unfortunately, in PostgreSQL, not all superuser tasks can be delegated to database owners, because postgres owns all the schema tables in all the databases upon creation. 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 any schema only allows resolution of symbolic names into object IDs (OIDs). Access directly by OID is not mediated. 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; As postgres (once per database) # create the new db with a schema owned by dbowner $ sudo -u postgres createdb -O dbowner -E utf8 <newDB> $ sudo -u postgres psql =# revoke all privileges on database <newDB> from public; =# \c <newDB> =# drop schema public cascade; =# create schema dbowner authorization dbowner; =# alter database <newDB> set search_path to dbowner; 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 or as needed $ sudo -u postgres psql =# grant temp on database <newDB> to <newDB-user>; =# \c <newDB> =# grant usage on schema dbowner 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>

Handy queries

To list tables
SELECT table_name FROM information_schema.tables WHERE table_schema='public';
or
SELECT DISTINCT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

To retrieve some information about the columns of a table
SELECT column_name, data_type, column_default, is_nullable FROM information_schema.columns WHERE table_name='whatever-table-name';

To obtain online information about the available schema components in the psql client
=# SET search_path TO information_schema;
=# \d

To see database ACLs
SELECT * FROM pg_database;

http://www.thegeekstuff.com/2009/05/15-advanced-postgresql-commands-with-examples/ has a few useful queries, too
Among them is, to see the size of one DB on a server
=> SELECT pg_size_pretty(pg_database_size('whatever-database-name'));