Since several developers share the same QNAP machine to write PHP and PostgreSQL programs, you should obey following steps to create individual db account. From now on,
each programmer uses your own db account to develop PHP/DB Apps.
1. createuser
To run psql and other commands, first change directory to /home and execute pgs shell script with root permission: ". pgs". Apply postgres account to "
createuser" for creating your own db user account, named "
nas" here. The account
has the permission to create database, but without superuser and role manager permissions.
[~] # cd /home
[/home] # . pgs
PSQL=/share/HDA_DATA/.qpkg/PostgreSQL
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/bin/X11:/usr/local/sbin:/opt/bin:/opt/sbin:/share/HDA_DATA/.qpkg/PostgreSQL/lib
_=PATH
[/share/HDA_DATA/.qpkg/PostgreSQL] # createuser -U postgres -d -EP nas
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE
[/share/HDA_DATA/.qpkg/PostgreSQL] #
Realize following options of "createuser".
[/share/HDA_DATA/.qpkg/PostgreSQL] # createuser --help | grep "\-U,\|-d,\|-E,\|-P,"
-d, --createdb role can create new databases
-P, --pwprompt assign a password to new role
-E, --encrypted encrypt stored password
-U, --username=USERNAME user name to connect as (not the one to create)
Up to now, the db account "nas" can not login PostgreSQL due to no allowable database created for "nas". The default db is pg_catalog that is not accessible by "nas". Let's make sure that "nas" was successfully created.
[/share/HDA_DATA/.qpkg/PostgreSQL] # psql -U nas
psql: FATAL: database "nas" does not exist
[/share/HDA_DATA/.qpkg/PostgreSQL] # psql -U postgres
postgres=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
nas | 16384 | t | f | f | f | ******** | |
(2 rows)
postgres=#
2. createdb
First, understand options that are used in the
createdb case. In this case, we create a db named test with "nas" account so that "nas" is the dbowner of test. Then, "nas" can login PostgreSQL by using the db "test":
psql -d testdb -U nas. Finally, we simply run SQL queries "create table, insert into, select" to verify the usability.
[/share/HDA_DATA/.qpkg/PostgreSQL] # createdb --help
createdb creates a PostgreSQL database.
Usage:
createdb [OPTION]... [DBNAME] [DESCRIPTION]
Options:
-D, --tablespace=TABLESPACE default tablespace for the database
-E, --encoding=ENCODING encoding for the database
-O, --owner=OWNER database user to own the new database
-T, --template=TEMPLATE template database to copy
-e, --echo show the commands being sent to the server
-q, --quiet don't write any messages
--help show this help, then exit
--version output version information, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as
-W, --password prompt for password
By default, a database with the same name as the current user is created.
Report bugs to <pgsql-bugs@postgresql.org>.
[/share/HDA_DATA/.qpkg/PostgreSQL] # createdb -U nas testdb
CREATE DATABASE
[/share/HDA_DATA/.qpkg/PostgreSQL] # psql -d testdb -U nas
Welcome to psql 8.2.13 (server 9.2.1), the PostgreSQL interactive terminal.
.....
testdb=> create table Class(CID int not null, Name varchar(100) null, primary key (CID));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "class_pkey" for table "class"
CREATE TABLE
testdb=> insert into Class values(1, 'First'); insert into Class values(2, 'Second');
INSERT 0 1
INSERT 0 1
testdb=> select * from Class
testdb-> ;
cid | name
-----+--------
1 | First
2 | Second
(2 rows)
testdb=>
Finished! You have your own db account in the QNAP NAS.
No comments :
Post a Comment