About vipulshukla

It has been bugging a lot of users using the PostgreSQL or EPAS servers, that the database logs will include the clear text password when you use ALTER USER statements with log_statement set to all. This can now be handled in EPAS 11 server using a new GUC. You would need to load edb_filter_log contrib module to enable it.    If edb_filter_log.redact_alter_password is set to true, the system will watch for commands of the form   ALTER {USER|ROLE|GROUP} identifier PASSWORD 'nonempty_string_literal' If such a statement is logged via log_statement, the 'nonempty_string_literal' portion will be changed to say 'x' instead, concealing the password supplied as an argument. If such a statement generates an error message that would normally have a STATEMENT: context line attached, the context line will be hidden for the same reason.   In order to apply these setting a user would need to enable edb_filter_log. Refer the below test case.   >> Edit the postgresql.conf file and add the edb_filter_log to the loaded libraries.   shared_preload_libraries = '$libdir/edb_filter_log'   >> Restart the database server to apply the changes. >> Login to the database and set redact_password_commands to ON.   -bash-4.1$ ./psql -p 5111 edb psql.bin (11.3.10) Type "help" for help. edb=# ALTER SYSTEM SET edb_filter_log.redact_password_commands TO 'on'; ALTER SYSTEM edb=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)  Once the configurations are loaded, the passwords for all the alter commands will be replaced by an 'x'.   For example: ==BEFORE== If you use a statement with a password, it displayed the password in clear text in the database logs.   edb=# alter user vipul identified by 'edb'; ALTER ROLE LOG: 2019-06-04 14:15:01 IST LOG: statement: alter user vipul identified by 'edb';   ==AFTER==   After enabling the redact_password_commands.   edb=# alter user vipul identified by 'newpass'; ALTER ROLE LOG: 2019-06-04 14:17:07 IST LOG: statement: alter user vipul identified by 'x'; ... View more

  EnterpriseDB (EDB) Postgres Advanced Server supports dynamic tuning of the database server to make  the optimal usage of the system resources available on the host machine on which it is  installed.   The two parameters that control this functionality are located in the "postgresql.conf" file which are  :           1.  edb_dynatune :   edb_dynatune determines how much of the host system's resources are to be used by  the             database server based upon the host machine's total available resources and the  intended usage of the host machine.        2.  edb_dynatune_profile :   The edb_dynatune_profile parameter is used to control tuning aspects based upon             the expected workload profile on the database server. This parameter takes effect upon             startup of the database server. Possible values are "oltp", "reporting", "mixed" . ​   A.  The following are parameters affected by Dynatune { Note : This list is as of EDB AS 11, not all parameters are tuned in all configurations, and parameters set manually are not tuned}:            1.  max_connections          2.  work_mem          3.  maintenance_work_mem          4.  shared_buffers          5.  effective_cache_size          6.  random_page_cost   B. Dynatune takes the following input :          1.  System Shared Memory - "sys_shared_mem"        2.  Current allocated Shared Buffers        3.  Current MaxFSMRelations settings         4.  Number of Buffers        5.  Total Memory     C. Dynatune sets the following parameters :         1.  max_connections       2.  work_mem       3.  maintenance_work_mem       4.  shared_buffer wal_buffers       5.  effective_cache_size       6.  random_page_cost       7.  autovacuum_analyze_scale_factor       8.  autovacuum_naptime       9.  autovacuum By default, autovacuum is enabled in Advanced Server and Dynatune is responsible for tuning the autovacuum parameters.   For more information about EDB Dynatune, please consult the EDB Dynatune documentation :  https://www.enterprisedb.com/docs/en/11.0/EPAS_Guide_v11/EDB_Postgres_Advanced_Server_Guide.1.076.html#     ... View more

Data redaction is a technique that limits sensitive data exposure by dynamically changing data as it is displayed for certain users.  EnterpriseDB Postgres Advanced Server (EPAS) provides data redaction as a feature of the v. 11 release.   Data redaction is implemented by defining a function for each field to which redaction is to be applied. The function returns the value that should be displayed to the users subject to the data redaction.   The edb_data_redaction parameter in the postgresql.conf file then determines whether or not data redaction is to be applied. By default, the parameter is enabled so the redaction policy is in effect and the following occurs: ​ Superusers and the table owner bypass data redaction and see the original data. All other users get the redaction policy applied and see the reformatted data. If the parameter is disabled by having it set to FALSE during the session, then the following occurs :   Superusers and the table owner bypass data redaction and see the original data. All other users get will get an error.   A redaction policy can be added, altered, disabled, enabled or dropped any time. For details on this, you can refer to the EPAS Database Compatibility for Oracle Developers Guide. ​   Below is a simple test case to demonstrat how to create a data redaction policy for a credit card number column in a payments table.   Test Case: ​   1. Log in using the superuser or table owner and check the table where you need to apply the data redaction policy.   postgres=# \c postgres postgres You are now connected to database "postgres" as user "postgres". postgres=# select * from payment_details_tab; customer_id | card_string -------------+--------------------- 4000 | 1234-1234-1234-1234 4001 | 2345-2345-2345-2345 (2 rows)     2.  Add new policy to partially hide the card details.   postgres=# BEGIN postgres=# DBMS_REDACT.add_policy( postgres=# object_schema=> 'public', postgres=# object_name=> 'payment_details_tab', postgres=# policy_name=> 'redactPolicy_001', postgres=# policy_description=> 'redactPolicy_001 for payment_details_tab table', postgres=# column_name=> 'card_string', postgres=# function_type=> DBMS_REDACT.partial, postgres=# function_parameters=> DBMS_REDACT.REDACT_CCN16_F12, postgres=# expression=> '1=1', postgres=# enable=> TRUE postgres=# ); postgres=# END; EDB-SPL Procedure successfully completed     3. Check the table data again to see if the superuser is able to see the data.   postgres=# select * from payment_details_tab; customer_id | card_string -------------+--------------------- 4000 | 1234-1234-1234-1234 4001 | 2345-2345-2345-2345 (2 rows)     4. Connect to a different user who is neither a superuser nor table owner and check the table data.   postgres=# \c postgres vipul You are now connected to database "postgres" as user "vipul". postgres=> select * from payment_details_tab; customer_id | card_string -------------+--------------------- 4000 | ****-****-****-1234 4001 | ****-****-****-2345 (2 rows)   ... View more

As a resolution to a few issues raised earlier with the EnterpriseDB clients, that the PEM agent creates sevrer load in case there are too many databases to be monitored, EnterpriseDB has included another feature in PEM , which allows us to configure the PEM agent connections through PgBouncer. This would help us pool the agent connections for better handling of both local and remote agents.   PEM Version: 7.6 ​ Recommended pgBouncer version 1.9 ​ You could use the below set of steps to configure PEM agent to work with pgbouncer. The below example steps are to configure the local PEM agent to connect pgbouncer.   Step 1: Configure Database.   Create a user named pgbouncer on the pem database. CREATE USER pgbouncer PASSWORD 'edb'; ​ Create another user pem_adm1. CREATE USER pem_adm1 PASSWORD 'edb' LOGIN CREATEROLE; ​ Grant privileges to the new users.     GRANT pem_admin TO pem_adm1; GRANT pem_agent_pool TO pem_adm1; GRANT connect ON database pem TO pgbouncer; GRANT usage ON schema pem TO pgbouncer; GRANT execute ON FUNCTION pem.get_agent_pool_auth(text) TO pgbouncer; ​ Create PEM proxy user.   SELECT pem.create_proxy_agent_user('agent_user1'); ​ Add an entry for the new users in the pg_hba.conf file of the PEM server.   local pem pgbouncer,pem_adm1 md5 ​  Restart the PEM database server.   systemctl restart edb-as-10.service C hange PgBouncer directory ownership.   chown postgres:postgres /opt/PgBouncer chmod 700 /opt/PgBouncer ​   Make sure certificate path is correct in pgbouncer.ini Copy the user details in userlist.txt file. Login to pem database and run: COPY ( SELECT 'pgbouncer'::text, 'edb' UNION ALL SELECT 'pem_adm1'::text, 'edb' ) TO '/opt/PgBouncer/etc/userlist.txt' WITH (FORMAT CSV, delimiter ' ', FORCE_QUOTE * ); ​   Step 2: Configure pgbouncer.   Edit hba_file for pgbouncer and add below:   hostssl pem agent_user1 0.0.0.0/0 cert ​ Change owner of userlist to postgres and permission to 600.     chown postgres:postgres /opt/PgBouncer/etc/userlist.txt chmod 600 /opt/PgBouncer/etc/userlist.txt ​  Enable and start pgbouncer service   systemctl enable pgbouncer.service systemctl start pgbouncer.service ​ Step 3: Configure PEM Agent   Register PEM agent using the agent_user1 user.   PGSSLMODE=require PEM_SERVER_PASSWORD=edb /opt/edb/pem/agent/bin/pemworker --register-agent --pem-server <host> --pem-port 6432 --pem-user pem_adm1 --pem- agent-user agent_user1 --display-name PGBAgent1 ​ This will create new certificates at /root/.pem/ and update the agent.cfg with new entries like new port, new agent id.   Update the pem agent table to disable the current agent registration. Connect to pem database and run below:   UPDATE pem.agent SET active='f' WHERE id=1; ​  Add new entry to hba_file to allow access to pgbouncer   local pgbouncer all md5 local pem pem_adm1 md5 ​ Restart pgbouncer service.   systemctl restart pgbouncer.service ​ Connect to the pem database using agent_user1 to confirm connetcivity using below:   PGSSLMODE=require PGSSLCERT=/root/.pem/agent_user1.crt PGSSLKEY=/root/.pem/agent_user1.key PEM_SERVER_PASSWORD=edb /opt/PostgreSQL/10/bin/psql -p 6432 -U agent_user1 -d pem ​  Backup and Remove old certificates from /root/.pem/ Restart the pem agent service systemctl restart pemagent.service ​  Change pem server properties. ( agent tab ) Right click on the "server" > "properties" > "Agent tab" . ​ SELECT the new agent ( PGBAgent1 ) from the drop down. Check the agent in the monitoring tab of PEM client. You should now see the agent" PGBAgent1" instead of the default agent "Postgres Enterprise Manager Host"' . ​ . ... View more

  The statement "the ON CONFLICT DO NOTHING clause is supported, provided a unique index inference specification is omitted." in the document means that if we do not use the unique index reference( in your case id) along with the ON CONFLICT DO NOTHING, then only it will work.   Refer document on the link below for detailed information. https://www.postgresql.org/docs/11/postgres-fdw.html   According to the document the below statement will not work as we have mentioned the unique index reference with the on conflict clause.   targetdb=# insert into test1 values (2,'Amit') on conflict(id) do nothing; ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification   Whereas, the below statement will work, as we do not mention the " id " which was specified as a unique index in the source table.   targetdb=# insert into test1 values (2,'Amit') on conflict do nothing; INSERT 0 0   Below is the complete scenario:   On source server ============== foreign_db=# create table s2.t3 (id integer unique, data text); CREATE TABLE foreign_db=# select * from s2.t3; id | data ----+------ 10 | dfgh 15 | sw 90 | sw foreign_db=# insert into s2.t3 values (10,'dfgh'); 2019-02-02 05:29:39.748 PST [63940] ERROR: duplicate key value violates unique constraint "t3_id_key" 2019-02-02 05:29:39.748 PST [63940] DETAIL: Key (id)=(10) already exists. 2019-02-02 05:29:39.748 PST [63940] STATEMENT: insert into s2.t3 values (10,'dfgh'); ERROR: duplicate key value violates unique constraint "t3_id_key" DETAIL: Key (id)=(10) already exists.   On target server ============== postgres=# CREATE SERVER foreign_server postgres-# FOREIGN DATA WRAPPER postgres_fdw postgres-# OPTIONS (host 'localhost', port '5433', dbname 'foreign_db'); CREATE SERVER   postgres=# CREATE USER MAPPING FOR postgres postgres-# SERVER foreign_server postgres-# OPTIONS (user 'postgres', password 'edb'); CREATE USER MAPPING   postgres=# CREATE FOREIGN TABLE test3 ( postgres(# id integer NOT NULL, postgres(# data text postgres(# ) postgres-# SERVER foreign_server postgres-# OPTIONS (schema_name 's2', table_name 't3'); CREATE FOREIGN TABLE   postgres=# select * from test3; id | data ----+------ 10 | dfgh 15 | sw 90 | sw   The following statement will work, as we do not mention the " id " which was specified as a unique index in the source table. postgres=# insert into test3 values (90,'sw') on conflict do nothing; INSERT 0 0 But the below statement will not work as we have mentioned the unique index reference with the on conflict clause. postgres=# insert into test3 values (90,'sw') on conflict(id) do nothing; ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification ... View more

Requirements: >>    wal_level should be >= logical Limitations of Logical Replication: >>    Schema replication is not supported. >>    DDL(s) replication is not supported. >>    Replication of sequences is not supported. >>    TRUNCATE table is not supported. >>    Replication only works for base tables. >>    Replication does not work within the databases on the same cluster. In the test case below, IP 192.168.0.114 is the IP address of my master(publication server) and 192.168.0.115 is for the slave (subscription server). Create Publication: Creating a publication for logical replication is easy. You can either provide the list of the tables that you need to add to publication, or keyword 'ALL TABLES' could be used to include all the tables. To create the publication, login to the source(master) database. >>    CREATE PUBLICATION pub2 FOR TABLE testtab1, testtab2; OR >>    CREATE PUBLICATION pub2 FOR ALL TABLES; Create Subscription: While creating the subscription, you would just need to provide a connection string for the master server and the name of the publication to which you want to connect. >>    CREATE SUBSCRIPTION sub2 CONNECTION 'host=192.168.0.114 dbname=postgres port=5432 password=edb' PUBLICATION pub2; Please make sure that you have allowed connections from the remote machine to the publication database cluster(192.168.0.114).  This could be done configuring 'listen_address' to '*' or an specific IP address. And adding pg_hba.conf entry for the destination(slave) server. for ex: host    all             all             192.168.0.115/32            md5 Adding Tables to Publication: Tables can ve added to the existing publication using ALTER PUBLICATION, as below: >>    ALTER PUBLICATION pub2 ADD TABLE testtab3; In order to apply the changes on an existing subscription, the above command should be followed by below command on slave databases (192.168.0.115).   >>    ALTER SUBSCRIPTION sub2 REFRESH PUBLICATION; TEST CASE: ON MASTER >>       postgres=# create table testtab1(id int primary key);     CREATE TABLE     postgres=# insert into testtab1 values (generate_series(1,100));     INSERT 0 100     postgres=# create table testtab2(id int primary key);     CREATE TABLE     postgres=# insert into testtab2 values (generate_series(1,1000));     INSERT 0 1000     postgres=# create publication pub2 for table testtab1, testtab2;     CREATE PUBLICATION ON SLAVE >>     postgres=# create table testtab1(id int primary key);     CREATE TABLE     postgres=# create table testtab2(id int primary key);     CREATE TABLE     postgres=# create subscription sub2 connection 'host=localhost dbname=postgres port=5432 password=edb' publication pub2;     NOTICE:  created replication slot "sub2" on publisher     CREATE SUBSCRIPTION     postgres=# select count(*) from testtab1 ;      count      -------        100     (1 row)     postgres=# select count(*) from testtab2 ;      count      -------       1000     (1 row) ON MASTER >> (Adding Table)     postgres=# create table testtab3(id int primary key);     CREATE TABLE     postgres=# insert into testtab3 values (1);     INSERT 0 1          postgres=# alter publication pub2 add table testtab3 ;     ALTER PUBLICATION      ON SLAVE >> (Applying Changes)     postgres=# create table testtab3(id int primary key);     CREATE TABLE     postgres=# alter subscription sub2 refresh publication ;     ALTER SUBSCRIPTION     postgres=# select count(*) from testtab3 ;      count      -------          1     (1 row) ... View more