Nasohi Ciptandani

Problem ERROR: permission denied for relation users Error ini biasanya terjadi karena user database tidak memiliki hak akses (privileges) yang cukup terhadap tabel atau schema di PostgreSQL. Solution 1. Menampilkan Nama Schema Pertama, hubungkan ke database PostgreSQL Anda, lalu jalankan perintah berikut untuk melihat daftar tabel beserta schema-nya: \dt Dari output tersebut, Anda dapat mengetahui nama schema yang digunakan (biasanya public , tetapi bisa berbeda tergantung konfigurasi). 2. Memberikan Hak Akses (Grant Privileges) Setelah mengetahui nama schema, jalankan perintah berikut sebagai user dengan hak admin (misalnya postgres ): GRANT ALL PRIVILEGES ON DATABASE name_db TO remote_user; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> TO <username>; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema_name> TO <username>; Ganti: name_db → dengan nama database Anda <schema_name> → dengan nama schema (misalnya public ...

When trust authentication is specified, PostgreSQL assumes that anyone who can connect to the server is authorized to access the database with whatever database user name they specify (even superuser names). Of course, restrictions made in the database and user columns still apply. This method should only be used when there is adequate operating-system-level protection on connections to the server. vi /var/lib/pgsql/data/pg_hba.conf # "local" is for Unix domain socket connections only local   all             all                                     trust login psql -U postgres

OS: Centos7 Database server: PostgreSQL 9 Install database server, membuat user dan databasenya https://nciptandani.blogspot.com/2019/01/rangkuman-install-database-postgresql.html Berikut adalah script untuk melakukan backup pg_dump -U db_user -h ip_address -F t db_name > /path/to/your/file/dump_name.tar -U = untuk user apa yang akan konek ke database -F = untuk format file p – plain-text SQL script c – custom-format archive d – directory-format archive t – tar-format archive Sekedar contoh database dibackup misalnya setiap menit: pg_dump -U dev10 -h 10.148.0.6 -F -t  dev10db > /backup/dev10db.$(date +%Y-%b-%d-%H-%).tar Hasil nama file backup dev10db.2019-Sep-03-15-10-03.sql (file pada tanggal 3 september 2019 pada jam 15 menit 10 dan detik ke 3 Simpan password postgresql server di file vi ~/.pgpass *:5432:*:username:password chmod 0600 ~/.pgpass Kemudian akan membuat crontab nya masuk ke crontab file vi /etc/crontab kemudian ...

Default peer authentication is configured on pg_hba.conf I tried on postgresql version 9 RootTerminal#adduser dev11 RootTerminal#passwd dev11 Login with default postgres user first RootTerminal#su - postgres Login to postgres terminal with the following command -bash-4.2$psql postgres=#create user dev11 with password '$3curE'; CREATE ROLE postgres=#create database dev11db; CREATE DATABASE postgres=#grant all privileges on database dev11db to dev11; GRANT Back to RootTerminal and try login with user dev11 to access its database. RootTerminal#su - dev11 [dev11@centos7-vm1 ~]$ psql -U dev11 -d dev11db psql (9.2.24) Type "help" for help. dev11db=>

Example: db-srv-10 with ip 192.168.1.1 user database: default postgres Configure: find / -name "postgresql.conf" /var/lib/pgsql/data/postgresql.conf vi /var/lib/pgsql/data/postgresql.conf listen_addresses = 'localhost' with listen_addresses = '*' find / -name "pg_hba.conf" vi /var/lib/pgsql/data/pg_hba.conf host    all             all              192.168.1.024                       md5 Test:   psql -h 192.168.1.10 -U postgres

Example SQL Syntax: INSERT INTO student (id, name)     VALUES (5, 'Nasohi Ciptandani')     ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name; Example Node.js code queries.js file:    const Pool = require('pg').Pool    const pool = new Pool({     user: 'monitoring',     host: 'localhost',     database: 'monitoring',     password: 'p4ssw0rd',     port: 5432,    })      pool.query("DELETE FROM linux_server_status", (error, results) => {      console.log(error, results);   });   const createLinux = (request, response) => {    const {hostname, ip, os, hdd_total, hdd_used, hdd_free, mem_total, mem_free, mem_used, core } = request.body    const now = new Date();    pool.query('INSERT INTO linux_server_status (hostname, ip, os, hdd_total, hdd_used, hdd_free, mem_total, mem_free, mem_used, co...

Configure Yum Repository #rpm -Uvh https://yum.postgresql.org/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm Repository info: https://yum.postgresql.org/repopackages.php#pg11 Install PostgreSQL11 #yum install postgresql11-server Initialize PGDATA #/usr/pgsql-11/bin/postgresql-11-setup initdb Start PostgreSQL #systemctl enable postgresql-11.service #systemctl start postgresql-11.service Verify su - postgres -c "psql" psql (11.0) Type "help" for help. postgres=# Create a password for user postgres for security purpose.  \password postgres Enable remote Acess to PostgreSQL Edit the file /var/lib/pgsql/11/data/postgresql.conf and set Listen address to your server IP address or “*” for all interfaces. listen_addresses = '192.168.18.9' Also set PostgreSQL to accept remote connections #vim /var/lib/pgsql/11/data/pg_hba.conf # Accept from anywhere host all all 0.0.0.0/0 md5 # Accept from trusted subnet host all al...

Task: Buatlah username dengan nama sysadmin7 dengan database monitoringdb7 Solution: Setelah PostgreSQL Database Server sudah terinstall dengan baik kemudian login dengan perintah berikut: #su - postgres maka akan masuk ke terminal berikut -bash-4.2$ Kemudian kerjakan langkah-langkah berikut Creating user -bash-4.2$createuser <username> Creating Database -bash-4.2$createdb <dbname> Giving the user a password -bash-4.2$postgres psql postgres=#alter user <username> with encrypted password '<password>'; Granting privileges on database postgres=#grant all privileges on database <dbname> to <username > ; Exit postgres=#\q Untuk login sesuai dengan username yang tadi sudah dibuat gunakan perintah berikut: Login dengan nama db dan usernamenya -bash-4.2$psql monitoringdb7 -U sysadmin7 psql (10.4) Type "help" for help. Melihat tabel monitoringdb7=> \d Did not find any relations. Exit mo...