Обсуждение: How to stop script executions

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).

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.

revoke usage on language plpythonu from username;

revoke usage on language plperl(u) from username;

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?

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.

​Superuser: yes (specifically I'm thinking the COPY FROM PROGRAM command, but also C language functions)

Ordinary user: not that I can think of.

​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.

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 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?

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.

​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.

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.