How to stop script executions

On Tue, 26 Jul 2016, 8:54 p.m. Dev Kumkar, <devdas.kumkar@gmail.com> wrote:

Hello Experts,

I want to have my postgreSQL database to only execute SQLs and avoid
execution of perl and python script executions.

You mean you don't want any routines/functions written in Pl/perl or
PL/pythin to get executed?

If that is what you are looking for them simply drop the extension for
these languages or deny privilege to users/public on these extensions.

Infact from what I know, these extensions are not there by default, so
don't create them at all.

Can you please suggest ways to achieve this?

Regards...

--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

On Tue, Jul 26, 2016 at 8:53 AM, Dev Kumkar <devdas.kumkar@gmail.com> wrote:

Hello Experts,

I want to have my postgreSQL database to only execute SQLs and avoid
execution of perl and python script executions.

Can you please suggest ways to achieve this?

The way to go about prohibiting their usage altogether is specific to your
installation method. But by default those languages are not installed into
newly created databases and only a superuser can "CREATE EXTENSION" so
without superuser intervention uses in the database will be unable to
create functions in those languages. If that is an insufficient level of
prevention you would have to somehow get your installation of PostgreSQL to
not include those core extensions. I'm pretty sure that during compilation
you ​can do this. For packaged distributions I do not know what all is
involved (i.e., what you have to choose to not install).

David J.

revoke usage on language plpythonu from username;

revoke usage on language plperl(u) from username;

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Tue, Jul 26, 2016 at 6:35 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

The way to go about prohibiting their usage altogether is specific to your
installation method. But by default those languages are not installed into
newly created databases and only a superuser can "CREATE EXTENSION" so
without superuser intervention uses in the database will be unable to
create functions in those languages. If that is an insufficient level of
prevention you would have to somehow get your installation of PostgreSQL to
not include those core extensions. I'm pretty sure that during compilation
you ​can do this. For packaged distributions I do not know what all is
involved (i.e., what you have to choose to not install).

Thanks David !
Yeah, the extensions for perl and python are not installed. Checked both
pg_available_extensions & \dx output.

Can I run OS commands without these extensions?

Regards...

On Tue, Jul 26, 2016 at 6:29 PM, Sameer Kumar <sameer.kumar@ashnik.com>
wrote:

You mean you don't want any routines/functions written in Pl/perl or

PL/pythin to get executed?

If that is what you are looking for them simply drop the extension for
these languages or deny privilege to users/public on these extensions.

Infact from what I know, these extensions are not there by default, so
don't create them at all.

Thanks Sameer!
Yeah these extensions are not present, are their any chances of running OS
commands from database?

Regards..

On Tue, 26 Jul 2016, 9:20 p.m. Dev Kumkar, <devdas.kumkar@gmail.com> wrote:

On Tue, Jul 26, 2016 at 6:29 PM, Sameer Kumar <sameer.kumar@ashnik.com>
wrote:

You mean you don't want any routines/functions written in Pl/perl or

PL/pythin to get executed?

If that is what you are looking for them simply drop the extension for
these languages or deny privilege to users/public on these extensions.

Infact from what I know, these extensions are not there by default, so
don't create them at all.

Thanks Sameer!
Yeah these extensions are not present, are their any chances of running OS
commands from database?

What do you mean by "from database"? I think you need to lay down your
requirement and goal more clearly.

Regards..

--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

On Tue, Jul 26, 2016 at 9:16 AM, Alex Ignatov <a.ignatov@postgrespro.ru>
wrote:

revoke usage on language plpythonu from username;

revoke usage on language plperl(u) from username;

Actually, the usage permission for an untrusted language appears to be
pointless. Only superusers can create functions in those languages and
superusers bypass the grant system altogether.

Its not documented (that I can see) whether users get their usage authority
for plperl via PUBLIC or explicit grants...

David J.

On Tue, Jul 26, 2016 at 9:19 AM, Dev Kumkar <devdas.kumkar@gmail.com> wrote:

On Tue, Jul 26, 2016 at 6:35 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

The way to go about prohibiting their usage altogether is specific to
your installation method. But by default those languages are not installed
into newly created databases and only a superuser can "CREATE EXTENSION" so
without superuser intervention uses in the database will be unable to
create functions in those languages. If that is an insufficient level of
prevention you would have to somehow get your installation of PostgreSQL to
not include those core extensions. I'm pretty sure that during compilation
you ​can do this. For packaged distributions I do not know what all is
involved (i.e., what you have to choose to not install).

Thanks David !
Yeah, the extensions for perl and python are not installed. Checked both
pg_available_extensions & \dx output.

Can I run OS commands without these extensions?

​Superuser: yes (specifically I'm thinking the COPY FROM PROGRAM command,
but also C language functions)
Ordinary user: not that I can think of.

David J.​

On Tue, Jul 26, 2016 at 9:21 AM, Sameer Kumar <sameer.kumar@ashnik.com>
wrote:

Yeah these extensions are not present, are their any chances of running
OS commands from database?

What do you mean by "from database"? I think you need to lay down your
requirement and goal more clearly.

​Typically this means that given user only having psql, or some other
backend protocol only, connect to the database are they able to execute
arbitrary commands as the user running the PostgreSQL process on the host
system.​

Untrusted langauges are untrusted for specifically this reason. Without
untrusted languages it requires privilege escalation to interact
dynamically with the host operating system.

Assuming raised privileges it is presently impossible to prevent such
dynamic interaction.

David J.

On Tue, Jul 26, 2016 at 6:54 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

​Superuser: yes (specifically I'm thinking the COPY FROM PROGRAM command,
but also C language functions)
Ordinary user: not that I can think of.

OK, is there a way to restrict usage of COPY FROM PROGRAM command?

Regards...

On Tue, Jul 26, 2016 at 6:59 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

​Typically this means that given user only having psql, or some other
backend protocol only, connect to the database are they able to execute
arbitrary commands as the user running the PostgreSQL process on the host
system.​

Untrusted langauges are untrusted for specifically this reason. Without
untrusted languages it requires privilege escalation to interact
dynamically with the host operating system.

Assuming raised privileges it is presently impossible to prevent such
dynamic interaction.

Just thinking if untrusted language like plperlu is not installed then
executing arbitrary commands is not possible.
So the other possible which you did mention was COPY FROM PROGRAM command,
is this understanding correct?

Regards...

On Tue, Jul 26, 2016 at 9:52 AM, Dev Kumkar <devdas.kumkar@gmail.com> wrote:

On Tue, Jul 26, 2016 at 6:59 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

​Typically this means that given user only having psql, or some other
backend protocol only, connect to the database are they able to execute
arbitrary commands as the user running the PostgreSQL process on the host
system.​

Untrusted langauges are untrusted for specifically this reason. Without
untrusted languages it requires privilege escalation to interact
dynamically with the host operating system.

Assuming raised privileges it is presently impossible to prevent such
dynamic interaction.

Just thinking if untrusted language like plperlu is not installed then
executing arbitrary commands is not possible.
So the other possible which you did mention was COPY FROM PROGRAM command,
is this understanding correct?

Regards...

COPY FROM PROGRAM

I think at this point it would be most beneficial if
1. You stated your version of PostgreSQL & O/S
2. Gave a comprehensive explanation of exactly what you are trying to do.
IOW, What exactly are you trying to prevent users from doing and why?

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Tue, Jul 26, 2016 at 9:48 AM, Dev Kumkar <devdas.kumkar@gmail.com> wrote:

On Tue, Jul 26, 2016 at 6:54 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

​Superuser: yes (specifically I'm thinking the COPY FROM PROGRAM command,
but also C language functions)
Ordinary user: not that I can think of.

OK, is there a way to restrict usage of COPY FROM PROGRAM command?

​For superusers, no. You'd have to protect the system using external tools
to limit what areas and commands the postgres (or whatever PostgreSQL runs
as) user ​can access.

​David J.​

On Tue, Jul 26, 2016 at 9:05 AM, Melvin Davidson <melvin6925@gmail.com>
wrote:

On Tue, Jul 26, 2016 at 9:52 AM, Dev Kumkar <devdas.kumkar@gmail.com>
wrote:

On Tue, Jul 26, 2016 at 6:59 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

​Typically this means that given user only having psql, or some other
backend protocol only, connect to the database are they able to execute
arbitrary commands as the user running the PostgreSQL process on the host
system.​

Untrusted langauges are untrusted for specifically this reason. Without
untrusted languages it requires privilege escalation to interact
dynamically with the host operating system.

Assuming raised privileges it is presently impossible to prevent such
dynamic interaction.

Just thinking if untrusted language like plperlu is not installed then
executing arbitrary commands is not possible.
So the other possible which you did mention was COPY FROM PROGRAM
command, is this understanding correct?

Regards...

COPY FROM PROGRAM

I think at this point it would be most beneficial if
1. You stated your version of PostgreSQL & O/S
2. Gave a comprehensive explanation of exactly what you are trying to do.
IOW, What exactly are you trying to prevent users from doing and why?

​I agree. From my reading at:
https://www.postgresql.org/docs/9.5/static/sql-copy.html the COPY FROM
PROGRAM is only available to a PostgreSQL user who is database superuser.
That, sort of, implies to me that said user is trusted not to do "evil",
but abide by the restrictions place upon him/her/it. In some strange
reality where this is not the case, and I was running on Linux, I would use
SeLinux in enforcing mode to really restrict what the id under which the
server is running could do. That is, a "don't allow unless explicitly
allowed" type policy. Or I'd "sandbox" the PostgreSQL server code using
something like docker, or under in a virtual machine with little access to
other services.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

--
Klein bottle for rent -- inquire within.

Maranatha! <><
John McKown

On Tue, Jul 26, 2016 at 7:49 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

​For superusers, no. You'd have to protect the system using external
tools to limit what areas and commands the postgres (or whatever PostgreSQL
runs as) user ​can access.

Thanks David for the information so far!

On Tue, Jul 26, 2016 at 7:53 PM, John McKown <john.archie.mckown@gmail.com>
wrote:

I agree. From my reading at:
https://www.postgresql.org/docs/9.5/static/sql-copy.html the COPY FROM
PROGRAM is only available to a PostgreSQL user who is database superuser.
That, sort of, implies to me that said user is trusted not to do "evil",
but abide by the restrictions place upon him/her/it. In some strange
reality where this is not the case, and I was running on Linux, I would use
SeLinux in enforcing mode to really restrict what the id under which the
server is running could do. That is, a "don't allow unless explicitly
allowed" type policy. Or I'd "sandbox" the PostgreSQL server code using
something like docker, or under in a virtual machine with little access to
other services.

PostgreSQL version is 9.3 and O/s is both linux and windows.

I was trying to understand from both O/S perspectives that what kind of
commands can be run using COPY FROM PROGRAM which can have an impact.
Thanks for all the information!

Regards...