Unable to setup oracle_fdw

[wasiualhasib]

I found an issue to make install oracle_fdw package getting the following error.

Also set at /etc/ld.so.conf file below configuration but not working. Could you please help me on this? include ld.so.conf.d/*.conf /usr/lib/oracle/21/client64/lib

[postgres@postgersql oracle_fdw]$ make install
/usr/bin/mkdir -p '/usr/pgsql-15/lib'
/usr/bin/mkdir -p '/usr/pgsql-15/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-15/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-15/doc/extension'
/usr/bin/install -c -m 755  oracle_fdw.so '/usr/pgsql-15/lib/oracle_fdw.so'
/usr/bin/install: cannot remove '/usr/pgsql-15/lib/oracle_fdw.so': Permission denied
make: *** [/usr/pgsql-15/lib/pgxs/src/makefiles/../../src/Makefile.shlib:461: install-lib-shared] Error 1

[mkgrgis]

What about sudo, @wasiualhasib? In FDW compilation works make install is only operation needs OS superuser rights.

[wasiualhasib]

No , sudo will not work. Here I did a trick, just give permission to that directory for postgres user. But I don't think this is the right approach because by default those directory under root user. @laurenz could you please tell me how can I solve this issue without changing ownership? Waiting for your kind reply.

[laurenz]

sudo is the solution. Please explain how "it will not work". Perhaps the PATH environment variable is not set properly in your root subshell:

sudo make install PATH=/usr/pgsql-15/bin:$PATH

[wasiualhasib]

@laurenz I have tried all possible ways to solve it but in my case, it does not work with sudo. All necessary information given below. If you have any question please let me know.

I said no because: I found that, when I changed /usr/pgsql-15/lib and /usr/pgsql-15/share/extension directory into postgres only then it is accessable. But by default those directory is accessable from root user which is little confusing for me. Though I configured PATH=/usr/pgsql-15/bin:$PATH this for both root and postgres user.

Could you please explain based on my below command output why it not worked ?

With only sudo command:

[postgres@postgersql oracle_fdw]$ sudo make install
/usr/bin/mkdir -p '/usr/pgsql-15/lib'
/usr/bin/mkdir -p '/usr/pgsql-15/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-15/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-15/doc/extension'
/usr/bin/install -c -m 755  oracle_fdw.so '/usr/pgsql-15/lib/oracle_fdw.so'
/usr/bin/install: cannot remove '/usr/pgsql-15/lib/oracle_fdw.so': Permission denied
make: *** [/usr/pgsql-15/lib/pgxs/src/makefiles/../../src/Makefile.shlib:461: install-lib-shared] Error 1

pg_config is accessible from postgres user:

[postgres@postgersql oracle_fdw]$ pg_config --version
PostgreSQL 15.6

Command Executed with the mentioned format with PATH:

[postgres@postgersql oracle_fdw]$ sudo make install PATH=/usr/pgsql-15/bin:$PATH
make: pg_config: Command not found
make: *** No rule to make target 'install'.  Stop.

.bash_profile output

[postgres@postgersql oracle_fdw]$ cat ~/.bash_profile
[ -f /etc/profile ] && source /etc/profile
PGDATA=/var/lib/pgsql/15/data
export PGDATA
# If you want to customize your settings,
# Use the file below. This is not overridden
# by the RPMS.
[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile
PATH=$PATH:/usr/pgsql-15/bin/
export PATH

Oracle Client Configuration:

[postgres@postgersql oracle_fdw]$ cat /etc/profile.d/client.sh
export ORACLE_HOME=/usr/lib/oracle/21/client64
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin

Also set at /etc/ld.so.conf file below configuration

include ld.so.conf.d/*.conf
/usr/lib/oracle/21/client64/lib

[laurenz]

"Permission denied" to the root user is interesting. Is this a container or something? Some ideas:

• perhaps the executable is in use — did you shut down the PostgreSQL server?
• perhaps some extended file attribute like "immutable" was set; examine the file with lsattr

[wasiualhasib]

Output of lsattr command:

[postgres@postgersql oracle_fdw]$ lsattr -------------------- ./CHANGELOG -------------------- ./LICENSE -------------------- ./Makefile lsattr: Operation not supported While reading flags on ./README.md -------------------- ./README.oracle_fdw -------------------- ./TODO -------------------- ./expected -------------------- ./msvc -------------------- ./oracle_fdw--1.0--1.1.sql -------------------- ./oracle_fdw--1.1--1.2.sql -------------------- ./oracle_fdw--1.2.sql -------------------- ./oracle_fdw.c -------------------- ./oracle_fdw.control -------------------- ./oracle_fdw.h -------------------- ./oracle_gis.c -------------------- ./oracle_utils.c -------------------- ./sql -------------------- ./oracle_fdw.o -------------------- ./oracle_utils.o -------------------- ./oracle_gis.o -------------------- ./oracle_fdw.so

Only worked after changing directory permission to postgres user :

sudo setfacl -m u:postgres:rwx /usr/pgsql-15/lib
sudo setfacl -m u:postgres:rwx /usr/pgsql-15/share/extension/

[laurenz]

Perhaps the ACLs were set strangely before that... Anyway, it works on other Linux machines, so there must be something unusual about your setup (again, container?). Perhaps you can figure out what is going on, but I am out of guesses.

[wasiualhasib]

No, there is no docker container I use here, I tried it in my Oracle Virtual Box Centos 8. I will check in another machine if I find any discrepancy, I will let you know.

[laurenz]

Did you find anything?

[wasiualhasib]

No, I have tried in different versions also, it is not working with sudo. After changing those directories ( /usr/pgsql-14/lib,/usr/pgsql-14/share/extension) and files (oracle_fdw.so,oracle_fdw.control) permission to postgres it worked.

Could you please make a rpm-based installation for oracle_fdw? I think in that case it will solve those issues. rpm based install most case solve dependencies issue.

[laurenz]

There are RPMs available here.

I doubt that an RPM will make much difference — after all, installing an RPM just copies files as user root.

The lsattr output above is from the oracle_fdw software; what would be interesting to see is the lsattr output from the PostgreSQL library directory that you are trying to overwrite: /usr/pgsql-15/lib/oracle_fdw.so and the directory /usr/pgsql-15/lib.