Sunday, March 20, 2016

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;


1 comments:

KISAH SUKSES IBU HERAWATI said...

Saya ingin berbagi cerita kepada anda bahwa saya IBU HERAWATI seorang TKW dari malaysia dan secara tidak sengaja saya buka internet dan saya melihat komentar IBU DARNA yg dari singapur tentan AKI SYHE MAULANA yg telah membantu dia menjadi sukses dan akhirnya saya juga mencoba menghubungi beliau dan alhamdulillah beliau mau membantu saya untuk memberikan nomor Togel toto 4D dr hasil ritual/ghaib dan alhamdulillah itu betul-betul terbukti tembus dan menang RM.230.000 Ringgit ,kini saya kembali indon membeli rumah dan kereta walaupun sy Cuma pembantu rumah tanggah di selangor malaysia, sy sangat berterimakasih kepada AKI SYHE MAULANA dan tidak lupa mengucap syukur kepada ALLAH karna melalui AKI MAULANA saya juga sudah bisa sesukses ini. Jadi kawan2 yg dalam kesusahan jg pernah putus asah, kalau sudah waktunya tuhan pasti kasi jalan asal anda mau berusaha, ini adalah kisah nyata dari seorang TKW, AKI MAULANA adalah guru spiritual terkenal di indonesia. jika anda ingin seperti saya kunjungi situs/website KLIK DISINI RITUAL PESUGIHAN DUNIA GHAIB