MELIHAT USER YANG SUDAH
ADA
template1=# select * from
pg_catalog.pg_user;
usename
| usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd
| valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+----------+----------+-----------
postgres
| 10 | t | t | t | t | ******** | |
zahra | 16529 | t | f | f | f | ******** | |
fatimah | 16531 | t | f | f | f | ******** | |
(3
rows)
MEMBUAT
USER AHMAD, UMAR, ALI
template1=#
create user ahmad with password 'ahmad' createdb;
CREATE
ROLE
template1=#
create user umar with password 'umar' createdb;
CREATE
ROLE
template1=#
create user ali with password 'ali' createdb;
CREATE
ROLE
MELIHAT
GRUP YANG SUDAH ADA
template1=#
select * from pg_catalog.pg_group;
groname
| grosysid | grolist
-----------+----------+---------
marketing
| 16546 | {}
(1
row)
MEMASUKAN
USER KEDALAM GRUP
template1=#
alter group marketing add user ahmad, fatimah, umar, ali;
ALTER
ROLE
template1=#
select * from pg_catalog.pg_group;
groname
| grosysid | grolist
-----------+----------+---------------------------
marketing
| 16546 | {16543,16531,16544,16545}
(1
row)
MENAMPILKAN
LIST USER YANG ADA DI GRUP MARKETING
template1=#
select groname, usename from pg_group, pg_user where usesysid =
any(grolist);
groname
| usename
-----------+---------
marketing
| fatimah
marketing
| ahmad
marketing
| umar
marketing
| ali
(4
rows)
Melihat
user yang ada didatabase
template1=#
select usename,usesysid from pg_catalog.pg_user;
usename | usesysid ----------+---------- postgres | 10 zahra| 16529 fatimah| 16531 ahmad| 16543 umar | 16544 ali | 16545 (6 rows)
Melihat
group yang ada di database
template1=#
select * from pg_catalog.pg_group;
groname | grosysid | grolist
-----------+----------+---------------------------
marketing
| 16546 | {16543,16531,16544,16545}
(1
row)
melihat
user yang ada digroup marketing
template1=#
select groname,usename from pg_group,pg_user where usesysid = any
(grolist);
groname | usename
-----------+---------
marketing
| fatimah
marketing
| ahmad
marketing
| umar
marketing
| ali
(4
rows)
MENJADIKAN SEBUAH USER YANG AWALNYA TIDAK BISA MEMBUAT SEBUAH DATABASE MENJADI BISA MEMBUAT DATABASE
-Melihat apakah user bisa membuat database atau tidak
template1=#
select usename,usesysid,usecreatedb from pg_catalog.pg_user;
usename
| usesysid | usecreatedb
----------+----------+-------------
postgres| 10 | t
zahra | 16529 | t
fatimah
| 16531 | t
ahmad | 16543 | t
umar | 16544 | t
ali | 16545 | t
(6
rows)
RUMUSNYA:
template=#
alter user bowo with createdb;
KALO
SEBALIKNYA, DARI BISA KE TIDAK BISA, RUMUSNYA:
template=#
alter user bowo with nocreatedb;
MELIHAT
GROUP
template1=#
SELECT * FROM pg_catalog.pg_group;
groname
| grosysid | grolist
-----------+----------+---------------------------------------
dosen | 16547 | {16545,16549}
marketing
| 16546 | {16543,16531,16544,16548,16545,16549}
(2
rows)
KELUAR DARI TEMPLATE 1
template1=#
\q
AHMAD
Masuk kedalam dblatihan
postgres@henny-K84L:~$
psql -U ahmad dblatihan
Password
for user ahmad:
psql
(9.1.9)
Type
"help" for help.
MELIHAT
OWNER PEMILIK TABLE
dblatihan=>
\d
List
of relations
Schema
| Name | Type | Owner
--------+----------------+----------+----------
public
| agama | table | postgres
public
| pegawai | table | postgres
public
| pegawai_id_seq | sequence | postgres
(3
rows)
MEMBUAT
TABEL DEPARTEMEN
dblatihan=>
create table departemen (
dblatihan(>
id serial primary key, nama varchar(30)
dblatihan(>
);
NOTICE:
CREATE TABLE will create implicit sequence "departemen_id_seq"
for serial column "departemen.id"
NOTICE:
CREATE TABLE / PRIMARY KEY will create implicit index
"departemen_pkey" for table "departemen"
CREATE
TABLE
MELIHAT
TABEL AGAMA, NAMUN KARENA OWNERNYA POSTGRES (LIHAT DIATAS YG KUNING)
MAKA PERMISSION DENIED
dblatihan=>
select * from agama;
ERROR:
permission denied for relation agama
dblatihan=>
\q
MASUK
KE DBLATIHAN MENGGUNAKAN POSTGRES
postgres@henny-K84L:~$
psql -U postgres dblatihan;
Password
for user postgres:
psql
(9.1.9)
Type
"help" for help.
MEMBERIKAN
HAK AKSES SEPENUHNYA KE AHMAD
dblatihan=#
grant all on agama,pegawai to ahmad;
GRANT
dblatihan-#
\d
List
of relations
Schema
| Name | Type | Owner
--------+-------------------+----------+----------
public
| agama | table | postgres
public
| departemen | table | ahmad
public
| departemen_id_seq | sequence | ahmad
public
| pegawai | table | postgres
public
| pegawai_id_seq | sequence | postgres
(5
rows)
dblatihan-#
\q
MASUK
KE DBLATIHAN USER AHMAD
postgres@henny-K84L:~$
psql -U ahmad dblatihan;
Password
for user ahmad:
psql
(9.1.9)
Type
"help" for help.
dblatihan=>
\d
List
of relations
Schema
| Name | Type | Owner
--------+-------------------+----------+----------
public
| agama | table | postgres
public
| departemen | table | ahmad
public
| departemen_id_seq | sequence | ahmad
public
| pegawai | table | postgres
public
| pegawai_id_seq | sequence | postgres
(5
rows)
KARENA
SUDAH DIBERIKAN HAK AKSES, MAKA AKAN TAMPIL
dblatihan=>
select * from agama;
id
| nama
----+----------
1
| Islam
2
| Kristen
3
| Khatolik
(3
rows)
MEMBERIKAN
HAK AKSES KE ALI (HANYA SELECT)
dblatihan=>
grant select on departemen to ali;
GRANT
dblatihan=>
\q
postgres@henny-K84L:~$
psql -U ali dblatihan;
Password
for user ali:
psql
(9.1.9)
Type
"help" for help.
dblatihan=>
drop table departemen;
ERROR:
must be owner of relation departemen
AHMAD
MASUK KE DBLATIHAN
postgres@henny-K84L:~$
psql -U ahmad dblatihan;
Password
for user ahmad:
psql
(9.1.9)
Type
"help" for help.
AHMAD
MEMBERIKAN HAK AKSES KE GROUP
dblatihan=>
grant all on departemen to group marketing;
GRANT
AHMAD
MENCABUT HAK AKSES DARI GROUP
dblatihan=>
revoke all on departemen from group marketing;
REVOKE