Author Archives: viswamitra

port 5432 failed: FATAL: PAM authentication failed for user

I was facing this issue in RDS PostgreSQL after I grant all roles to postgres user. To resolve this issue run below command

revoke rds_iam from postgres;

Query to kill idle connections in PostgreSQL

PostgreSQL is process oriented architecture, for evey user connection one process will be created in background. If the user forget to close the connection the process will still consume some resources. Below is the query to close the idle connections

--- Close the connections which are idle for more than 15 mins

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'databasename'
   AND pid <> pg_backend_pid()
   AND state = 'idle'
   and extract(epoch from (current_timestamp - query_start)) > 15*60;

Identify a role has execute permission on function in PostgreSQL

To identify a role has execute permission on function, PostgreSQL has a catalog function(has_function_privilege) which makes it easy. Below is the simple example.

I have created a simple schema test with a function row_count() which return the row count of employee table. I have grated execut permission on the function to reader role.

postgres=# create schema test;
CREATE SCHEMA
postgres=# revoke USAGE on SCHEMA test from PUBLIC ;
REVOKE
postgres=# revoke execute on all functions in schema test from public;
REVOKE
postgres=# set search_path to test, public;
SET
postgres=# CREATE OR REPLACE FUNCTION row_count()
postgres-#  RETURNS integer AS $total$
postgres$#  declare
postgres$#  total integer;
postgres$#  BEGIN
postgres$#  SELECT count(1) into total FROM public.employee;
postgres$#  RETURN total;
postgres$#  END;
postgres$#  $total$
postgres-#  LANGUAGE plpgsql;
CREATE FUNCTION
postgres=#
postgres=# create role reader;
CREATE ROLE
postgres=# create role temp;
CREATE ROLE
postgres=# grant usage on schema test to reader ;
GRANT
postgres=# grant USAGE on SCHEMA test to temp;
GRANT
postgres=# grant SELECT on TABLE public.employee to reader ;
GRANT
postgres=# grant EXECUTE on FUNCTION test.row_count to reader ;
GRANT

To know the reader and temp roles has permissions on the row_count() function

postgres=# SELECT has_function_privilege('reader', 'test.row_count()', 'execute');
 has_function_privilege 
------------------------
 t
(1 row)

postgres=# SELECT has_function_privilege('temp', 'test.row_count()', 'execute');
 has_function_privilege 
------------------------
 f
(1 row)

The reader role has permissions to execute but not the temp.

postgres=# set role reader ;
SET
postgres=> 
postgres=> select test.row_count();
 row_count 
-----------
         1
(1 row)

postgres=> set role temp;
SET
postgres=> 
postgres=> select test.row_count();
ERROR:  permission denied for function row_count
postgres=> 

Inconsistent behavior of json_to_recordset/jsonb_to_recordset in PostgreSQL >=10 version

Recently we have upgraded from PostgreSQL 9.6 to 11 version. Post upgradation we started observing inconsistent behavior of json/jsonb_to_recordset functions.

JSON embedded inside the JSON as a string can be converted to records using json/jsonb_to_recordset functions.

In PostgreSQL 9.6, it is able to parse the json inside json and parse the escape characters as special symbols.

postgres=# select * from json_to_recordset('[{"a":"{\"type_id\":\"9\",\"test\":\"test\",\"currency\":\"INR\"}","b":1},{"a":"2","b": 2}]') as x(a jsonb, b int);
a | b
-----------------------------------------------------+---
{"test": "test", "type_id": "9", "currency": "INR"} | 1
2 | 2
(2 rows)

In PostgreSQL 10/11/12, It is parsing the escape symbols as a part of json string giving them in output.

postgres=# select * from json_to_recordset('[{"a":"{\"type_id\":\"9\",\"test\":\"test\",\"currency\":\"INR\"}","b":1},{"a":"2","b": 2}]') as x(a jsonb, b int);
a | b
--------------------------------------------------------------+---
"{\"type_id\":\"9\",\"test\":\"test\",\"currency\":\"INR\"}" | 1
"2" | 2
(2 rows)

To achieve the same behavior as PostgreSQL 9.6 in PostgreSQL 10/11/12, convert the json string to text and then convert to jsonb/json.

postgres=# select x.a::json,b from json_to_recordset('[{"a":"{\"type_id\":\"9\",\"test\":\"test\",\"currency\":\"INR\"}","b":1},{"a":"2","b": 2}]') as x(a text, b int);
a | b
------------------------------------------------+---
{"type_id":"9","test":"test","currency":"INR"} | 1
2 | 2
(2 rows)

https://www.postgresql.org/message-id/flat/6c07d61e-be88-a2d2-f158-023175b9e03f%40dunslane.net#a3518a193da8c7b87547a4694608d062

Get FIRST DAY AND LAST DAY OF A MONTH IN Postgresql

Below is the functions to get first day and last day of month in PostgreSQL.

To get first day of month, below is the plpgsql function, this function take the date value as input and return the first day of that month.

CREATE OR REPLACE FUNCTION first_day(date)
RETURNS date AS
$$
SELECT (date_trunc('MONTH', $1))::date;
$$ LANGUAGE 'sql'
IMMUTABLE STRICT;

part=# select first_day('2020-07-03');
 first_day
------------
 2020-07-01
(1 row)

To get the last of month, below is the plpgsql function, this function take the date value as input and return the last day of month.

CREATE OR REPLACE FUNCTION last_day(date)
RETURNS date AS
$$
SELECT (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day')::date;
$$ LANGUAGE 'sql'
IMMUTABLE STRICT;

part=# select last_day('2020-07-03');
  last_day
------------
 2020-07-31
(1 row)

ERROR: You must not be logged in as root to run this tool.

Symptoms

During the installation of Oracle database software getting this error even when running through root user.

Cause

Oracle installation need to be run as a sudo user not with root user.

Solution

Create user with sudo permissions and install Oracle using that user.

In CentOS, use below commands to create a user with sudo permissions

adduser oginstall
passwd oginstall
usermod -aG wheel oginstall

org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.

Symptoms
org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:350)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
	at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:236)
	at PostgreSqlExample.main(PostgreSqlExample.java:15)
........................
........................
Cause

Client connection got dropped before the query execution completed by PostgreSQL Server. Below are some of the major reasons for this.

  1. Socket timeout set at the application (driver) level
  2. Connection dropped due to issues at network/firewall
Solution
  • Check the timeout parameters configured in the application, below is an example where the socket timeout is set for 10 seconds.
jdbc:postgresql://192.168.2.3:5432/postgres?socketTimeout=10&ssl=true&sslmode=prefer
  • Check the firewall/network related settings

« Older Entries