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
Notes and Reference
PostgreSQL
SOP
The Database Infrastructure SOP includes the following steps to help secure a database:
|
Generally preferred practices that go beyond the above standard:
- 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.
- 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.
|
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'));