コンピュータクワガタ

かっぱのかっぱによるコンピュータ関連のサイトです

PostgreSQL

SQLの分析関数LAG

分析関数のLAGを最近知ったのでどんなものか紹介します。 適当な例を挙げるのが難しかったのですが、ありそうな例を考えてみました。サンプルは、CentOS 6.3に付属のPostgreSQL 8.4で確認しています。最近のOracleでも動くと思います。MySQLは無理です。一応…

8.1でのROLEの作成。

初めてROLEを作成しましたが、SUPERUSERとCREATEUSERは同一の意味のようで、両方を指定すると ERROR: conflicting or redundant options とエラーになりました。

シーラカンス本

むう、タイミングが悪い。 今月小遣いもうないのに。 http://www.cbook24.com/bm_detail.asp?sku=477412687X ●改訂第5版 PC UNIXユーザのための Postgre SQL 完全攻略ガイド 改訂第5版 PC UNIXユーザのための Postgre SQL 完全攻略ガイド作者: 石井達夫出版…

CREATE ROLE

PostgreSQL 8.1からUSER、GROUPの代わりにROLEというものを使うようです。 より、標準SQLに近い形になると言うことで、それ自体はいいことかと思います。 とりあえず、CREATE USERや、CREATE GROUPといったSQLは残るようですが、CREATE ROLEの別名と言う形に…

スキーマの検索パス。

http://www.postgresql.jp/document/pg812doc/html/ddl-schemas.html より。 スキーマの検索パスを設定可能。これで、より、仕事に使えそうだ。 修飾名を書くのは手間がかかりますし、どちらにしても、アプリケーションに特定のスキーマ名を書き込まない方が…

スキーマの削除

違うことを検索していて引っかかったので、バージョンは古いのですが。 http://www.postgresql.jp/document/pg732doc/user/ddl-schemas.html より、こんなこともできるようです。 スキーマを、含まれているすべてのオブジェクトと一緒にドロップするには次の…

JDBCのSSL接続の確認

http://jdbc.postgresql.org/documentation/80/ssl-client.html のページどおりにやったら、SSL接続できました。 PostgreSQL 7.4系で試したけれども問題なくつながりました。 以前苦労していたのはなんだったんだろう。 これで、ある程度会社でも使えそう。

ずっとやってても頭が冷えないので、運用周りの調査。

SSLについてずっと調査していても、頭が凝り固まるのでPostgreSQLの運用周りの調査をしてみる。 とりあえず、ログ。syslogに投げることは成功(本のとおりにやっただけだが)。 一応、投げたSQL文やその処理時間等もわかるようなのでとりあえず、いろいろロ…

WebアプリケーションからのSSLによるJDBC接続

Webアプリケーションから安全にDB接続できるようにSSL接続の調査をしていました。 3日ほどいろいろやってみましたが、うまくいきません。 psqlによるSSL接続は問題なくできるので、Javaの問題だとは思うのですが。 An I/O error has occured while flushing …

追加のみ可能なテーブル(参照も不可)

とある案件で、追加のみ可能で参照が不可能なテーブルができないかとふと思ったので、やってみました。 まず、テーブルの権限を確認。 testschema.testtableを以下の権限とする。 testdevs=a/testadmin 要するに、SELECT権限がなく、INSERTのみできる状態と…

スキーマの権限

スキーマの中身を参照するには、USAGE権限が必要と。

とりあえず、昨日の。

JDBCドライバからのアクセスでも文字化けはしてなかった。 http://d.hatena.ne.jp/kuwalab/20050808#1123504574

ああ、そうね、システムカタログね。(アフォ)

データベースの権限。 test=> SELECT datname,usename,datacl test-> FROM pg_database,pg_user test-> WHERE usesysid=datdba; datname | usename | datacl - test | testadmin | {testadmin=C*T*/testadmin,"group testadmins=CT/testadmin"} template1 | …

OSがUTF-8の場合のPostgreSQLの円エンコーディング(確認)

とりあえず、postgresql.confの client_encoding = UTF-8 とすると、一応pgAdmin IIIでも文字化けしていなかった。 あとは、JDBCドライバ等からデータを取得して化けていなければ一応大丈夫かな。

OSがUTF-8の場合のPostgreSQLのエンコーディング(未確認)

EUC_JPはだめなのかな? --encoding UTF-8で帰ってからやってみよう。 psql上はまったく文字化けしないけれども、特定の文字(「・」等)が入力できなかったり、pgAdmin IIIで文字化けしていたりといろいろありんす。 いろいろ調べてたら、クライアントエン…

めんどくさくてもコメントは必須。

データベース、スキーマ、テーブル、列のすべてにコメントはつけましょう。 test=> COMMENT ON DATABASE test IS 'テスト用データベース'; COMMENT test=> COMMENT ON SCHEMA testschema IS 'テスト用スキーマ'; COMMENT test=> COMMENT ON TABLE testschema…

テーブルの権限

最後にテーブルの権限。これは数が多くて完全ではない感じ。 まずは、テスト用のテーブルを作る。 test=> CREATE TABLE testschema.testtable( test(> id integer, test(> name varchar(20) test(> ); CREATE TABLE お約束で、PUBLICの権限をはずす。 test=>…

スキーマの権限

次に、スキーマの権限について。 まずは、適当にスキーマを作成する。作成はデータベース管理者でいいかなと。 test=> CREATE SCHEMA testschema; CREATE SCHEMA まずは、デフォルト権限になるので、PUBLICに対する権限を取り除く。 test=> REVOKE ALL PRIVI…

データベースの権限。

スキーマを勝手に作られないようにデータベース管理者だけに権限を与えると。 まずは、デフォルト権限からPUBLICの権限をはずす。 template1=# REVOKE ALL PRIVILEGES ON DATABASE test FROM PUBLIC RESTRICT; REVOKE 権限をはずすと、今回は以下のような権…

パスワードの有効期限切れ。

パスワードを変えていないはずなのに、認証に失敗する。 psql: FATAL: Password authentication failed for user "testadmin" おかしいなあと思ってパスワードを変更してもうまくいかない。 と思っていたら、パスワードの有効期限が切れていました。 以下の…

データベースの権限も(7.4.6)

家の7.4.6でも権限チェック。 test=# REVOKE ALL PRIVILEGES ON DATABASE test FROM PUBLIC RESTRICT; REVOKE でデフォルト権限から変更すると、pgAdmin IIIで同様に確認できた。 いちいちpgAdminで見ないといけないのか? 出張から帰ってきたら調べよう。体…

データベースの権限も

データベースの権限もpgAdmin IIIで確認するとわかる。 ということは、きっとどこかに情報が保管されているはずだ。

SSL?、SSH?どっちだ。

http://www.postgresql.jp/document/pg721doc/admin/ssh-tunnels.html

確かに7.4.6では権限は出ない。

会社の8.0.3で\dn+を試したので、家の7.4.6で実験。 test=# \dn+ List of schemas Name | Owner - information_schema | postgres pg_catalog | postgres pg_temp_1 | postgres pg_toast | postgres public | postgres testschema | testadmin (6 rows) 確か…

権限がわかれば便利便利

testschema作成直後の権限。 testschema | testadmin | | 次に、PUBLICの権限をすべて剥奪する。 test=# REVOKE ALL PRIVILEGES ON SCHEMA testschema FROM PUBLIC RESTRICT; そうすると、デフォルトの権限ではなくなるので以下のようになる。 testschema | …

バックアップ

データベース単位でのバックアップは以下の通り。 通常権限の問題等が発生しないようにpostgresユーザで処理した方がよさそう。 pg_dump test -U postgres > test.backup ただのSQL文が吐き出されるので復元は自由に。 すべてのデータベースというか、ユーザ…

8.0からはスキーマの権限の詳細が。

http://osb.sra.co.jp/PostgreSQL/8.0/changes.html 8.0から\dn+でスキーマの権限と詳細が確認できるようになったそうです。 test=# \dn+ スキーマの一覧 名前 | 所有者 | アクセス権 | 説明 - information_schema | postgres | {postgres=UC/postgres,=U/po…

初期権限

8.0.3のマニュアルより オブジェクトの種類によって、初期のデフォルト権限としていくつかの権限がPUBLICに付与されていることがあります。デフォルトでは、テーブル、スキーマ、および、テーブル空間に関するPUBLICアクセス権限はなく、データベースのTEMP…

Windows版の8.0.3でも同様の気が

昨日の記述通り、Windows版でもALTER GROUPでユーザを割り当てないとpg_hba.conf通りに動いていません。

スキーマの権限の削除

すべてのユーザのスキーマのオブジェクトの作成権限の削除。 REVOKE ALL PRIVILEGES ON SCHEMA public FROM PUBLIC; このあとに、必要な権限を付与していく。 GRANT ALL PRIVILEGES ON SCHEMA public TO GROUP testadmins; 権限がないユーザだと、以下のよう…

広告