0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

PostgreSQL実験室/データベース作成

0
Last updated at Posted at 2025-11-03

PostgreSQL実験室/データベース作成

データベース作成

  • 検証作業用のデータベースを作成します.
    $ LC_ALL=C psql -d postgres -U postgres
    ...
    postgres@postgres=# create database dbx owner dbxadmin template template1 tablespace dbx_tbs1;
    CREATE DATABASE
    postgres@postgres=# \q
    

スキーマ作成

  • 検証作業用のスキーマを作成します.
    $ LC_ALL=C psql -d dbx -U dbxadmin
    ...
    dbxadmin@dbx=# create schema dbxowner1 authorization dbxowner1;
    CREATE SCHEMA
    dbxadmin@dbx=# grant create,usage on schema dbxowner1 to dbxowner1;
    GRANT
    dbxadmin@dbx=# grant usage on schema dbxowner1 to dbxuser1;
    GRANT
    dbxadmin@dbx=# grant usage on schema dbxowner1 to dbxuser2;
    GRANT
    dbxadmin@dbx=# create schema dbxowner2 authorization dbxowner2;
    CREATE SCHEMA
    dbxadmin@dbx=# grant create,usage on schema dbxowner2 to dbxowner2;
    GRANT
    dbxadmin@dbx=# grant usage on schema dbxowner2 to dbxuser1;
    GRANT
    dbxadmin@dbx=# grant usage on schema dbxowner2 to dbxuser2;
    GRANT
    dbxadmin@dbx=# commit;
    COMMIT
    dbxadmin@dbx=# \q
    

ユーザー設定変更

search_path

  • サーチパスを設定します.
    $ LC_ALL=C psql -d dbx -U dbxadmin
    ...
    dbxadmin@dbx=# ALTER ROLE dbxowner1  SET search_path TO "\$user", dbxowner1, dbxowner2, public;
    ALTER ROLE
    dbxadmin@dbx=# ALTER ROLE dbxowner2  SET search_path TO "\$user", dbxowner1, dbxowner2, public;
    ALTER ROLE
    dbxadmin@dbx=# ALTER ROLE dbxuser1 SET search_path TO "\$user", dbxowner1, dbxowner2, public;
    ALTER ROLE
    dbxadmin@dbx=# ALTER ROLE dbxuser2 SET search_path TO "\$user", dbxowner1, dbxowner2, public;
    ALTER ROLE
    dbxadmin@dbx=# commit;
    COMMIT
    dbxadmin@dbx=# \q
    

default tablespace

  • ユーザーのデフォルトテーブル空間を設定します.
    $ LC_ALL=C psql -d dbx -U dbxadmin
    ...
    dbxadmin@dbx=#ALTER USER dbxowner1 SET default_tablespace TO dbx_tbs1;
    ALTER ROLE
    dbxadmin@dbx=#ALTER USER dbxowner2 SET default_tablespace TO dbx_tbs1;
    ALTER ROLE
    dbxadmin@dbx=#commit;
    COMMIT
    dbxadmin@dbx=# \q
    
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?