PostgreSQL �̃Z�b�V�����̏������o����
PostgreSQL �ɂ� SQL �ŃZ�b�V�����̏����擾������� �F�X�ƒ�`����Ă��܂��B �ȒP�ɏЉ�Ă��������Ǝv���܂��B
version()
�ڑ����� PostgreSQL �T�[�o�̃o�[�W�����̕������Ԃ��܂��B
db1=# SELECT version();
version
-------------------------------------------------------------------
PostgreSQL 8.3.5 on i386-redhat-linux-gnu, compiled by GCC gcc (GC
(1 row)
current_database()
�ڑ����� �f�[�^�x�[�X�̖��O��Ԃ��܂��B
$ psql db1 postgres db1=# SELECT current_database(); current_database ------------------ db1 (1 row)
current_schema()
���݂̃X�L�[�}����Ԃ��܂��B
�ڑ���������́Apostgresql.conf �� search_path �p�����[�^�� ��`�Ɉˑ������l��Ԃ��܂��B �����p�X����̏ꍇ�� NULL ��Ԃ��܂��B
db1=# SELECT current_schema();
current_schema
----------------
public
(1 row)
CREATE TABLE �Ȃǂ�����ۂɁA�X�L�[�}�����I�� ��`���Ȃ��ꍇ�A���̃X�L�[�}�����ݒ肳��܂��B
�����p�X�� set �ŕς��邱�Ƃ��ł��܂��B
db1=# set search_path = 'hogehoge'; db1=# SELECT current_schema(); current_schema ---------------- hogehoge (1 row) db1=# set search_path = 'public','hogehoge'; db1=# SELECT current_schema(); current_schema ---------------- public (1 row)
�����p�X�ɃX�L�[�}���w�肵���ꍇ�́A�擪�̃X�L�[�}�� ���݂̃X�L�[�}�ƂȂ�܂��B
current_schemas(boolean)
�����p�X���̃X�L�[�}��Ԃ��܂��B boolean �� true ���Z�b�g����ƈÖق̃X�L�[�}���Ԃ��܂��B
db1=# SELECT current_schemas(false); current_schemas ----------------- {postgres,public} (1 row) db1=# SELECT current_schemas(true); current_schemas ----------------- {pg_catalog,postgres,public} (1 row)
session_user
�f�[�^�x�[�X�ɐڑ��������[�U����Ԃ��܂��B
$ psql db1 postgres db1=# SELECT session_user; session_user -------------- postgres (1 row) db1=# \q $ psql db1 hogehoge db1=# SELECT session_user; session_user -------------- hogehoge (1 row)
current_user, user
current_user �� user �͓����ł��B
current_user �́A���ݎ��s���Ă��郆�[�U����Ԃ��܂��B �ʏ�� session_user �Ɠ����ł����A SECURITY DEFINER �������t�����������s�����ꍇ�� �Ԃ��l������Ă��܂��B SECURITY DEFINER �����́A�����쐬�������[�U�̌����� ���s����I�v�V�����ł��B
$ psql db1 postgres db1=# SELECT session_user, current_user; session_user | current_user --------------+-------------- postgres | postgres (1 row)
SECURITY DEFINER ������t������ test1() �� �t���Ă��Ȃ� test2() �� hogehoge ���[�U�ō쐬���Ă݂܂��B
$ psql db1 hogehoge db1=# SCREATE FUNCTION test1() RETURNS name AS 'SELECT current_user;' SECURITY DEFINER LANGUAGE SQL ; CREATE FUNCTION db1=# SCREATE FUNCTION test2() RETURNS name AS 'SELECT current_user;' LANGUAGE SQL ; CREATE FUNCTION
����� postgres ���[�U�Ŏ��s���܂��B
$ psql db1 postgres db1=# select test1(); test1 ----------- hogehoge (1 row) db1=# select test2(); test1 ----------- postgres (1 row)
�قȂ�l��Ԃ��܂����B ���̂悤�Ɏ��ۂɎ��s���Ă��錠�����m�F����ꍇ�� current_user ���g�p���܂��B
�܂��Asession_user ���܂߁A���[�U�n�͓���ŁA���� () ��t������ �Ăяo���K�v������̂Œ��ӂ��܂��傤�B
