Trik User dan Akses Postgresql

Sekedar untuk catatan lagi.

0. CHANGE PASSWORD FOR USER POSTGRES
(by default, Ubuntu don't give the password for postgres while iDempiere somehow ask dbadmin password)
sudo -u postgres psql postgres
\password postgres

1. IF WE WANT DATABASE IS ACCESSIBLE FROM REMOTE HOST
cek /etc/postgresql/[ver]/main/pg_hba.conf
host       database  user  address  auth-method  [auth-options]
dan address untuk menerima semua IP = 0.0.0.0/0
auth-method disarankan md5

cek /etc/postgresql/[ver]/main/postgresql.conf
cek listen_addresses and change localhost to *

2. IF WE WANT READ-ONLY USER
CREATE USER userviewonly WITH NOCREATEDB NOCREATEROLE NOCREATEUSER PASSWORD 'p455w0rd';
CREATE USER username ENCRYPTED PASSWORD 'p455w0rd';

GRANT CONNECT ON DATABASE databasenameTO userviewonly ;
GRANT USAGE ON SCHEMA schemaname TO userviewonly ;
GRANT SELECT ON  ALL TABLES IN SCHEMA schemaname TO userviewonly ;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA schemaname TO userviewonly ;

-- connect as userviewonly ;
-- SHOW search_path;
-- SET search_path TO adempiere, public;

ALTER ROLE userviewonly WITH LOGIN;
ALTER ROLE username WITH LOGIN;

-- if we want -- a role part of member another role group
GRANT group_role TO role1, ... ;
REASSIGN OWNED BY old_role [, ...] TO new_roles

-- example
ALTER ROLE username WITH SUPERUSER;

_________ some discussion _________________________________________________________
The search path that you edit using the SET command only persists for the session.
In order to persist your schema in the list of default schemas permanently,
you will have to edit the following entry in the postgresql.conf
(located at \data\postgresql.conf e.g. C:\edb-2005\8.0.4.15\data\postgresql.conf): 

search_path = '$user,public,sys,dbo' # schema names

Please append your schema to this list, and restart (stop/start) the database service
from your start Menu. You should now be able to refer your
without specifying schema with it. 

Hope this helps...

==============================================================================================

Create the database

==============================================================================================

0. cek database

psql -h localhost -U postgres -W

password: postgres

#check database

\l

\connect databasename

#select schema

select schema_name

from information_schema.schemata;

#akan ada schema

select * from pg_roles;

#show tables;

SELECT * FROM pg_catalog.pg_tables

5. insert database (if binary file?)

pg_restore -U postgres -W -h localhost -d idempiere erp20150105.backup 2> error.log

6. grant access

GRANT ALL PRIVILEGES ON DATABASE databasename TO username;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schemaname to username;

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schemaname to username;

GRANT ALL PRIVILEGES ON SCHEMA schemaname TO username;