Re: 】

On 09/16/2017 10:06 AM, postgresql_2016(at)163(dot)com wrote:
> 1、Through our security test, we find the *copy from* command can read any
> files from the server if it has the read permission,for example, /etc/passwd
> file. Although, the pg_read_file or pg_read_binary_file function restricts
> the file read path, which only can read files from the PGDATA directory.
>
> For example, the next example can read the server file.
>
> [postgres(at)X86C136 ~]$ psql postgres -p 5432
> psql (9.2.22)
> Type "help" for help.
>
> postgres=# create table test(va varchar);
> CREATE TABLE
> postgres=# copy test from '/etc/passwd';
> COPY 37
> postgres=# select * from test;
> va
> -----------------------------------------------------------------------------------------------------
> root:x:0:0:root:/root:/bin/bash
> bin:x:1:1:bin:/bin:/sbin/nologin
> daemon:x:2:2:daemon:/sbin:/sbin/nologin
> adm:x:3:4:adm:/var/adm:/sbin/nologin
> lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
> sync:x:5:0:sync:/sbin:/bin/sync
> shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
> halt:x:7:0:halt:/sbin:/sbin/halt
> mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
> operator:x:11:0:operator:/root:/sbin/nologin
> games:x:12:100:games:/usr/games:/sbin/nologin
> ftp:x:14:50:FTP User:/var/ftp:/sbin/nologin
> nobody:x:99:99:Nobody:/:/sbin/nologin
> systemd-bus-proxy:x:999:997:systemd Bus Proxy:/:/sbin/nologin
> systemd-network:x:192:192:systemd Network Management:/:/sbin/nologin
> dbus:x:81:81:System message bus:/:/sbin/nologin
> polkitd:x:998:996:User for polkitd:/:/sbin/nologin
> tss:x:59:59:Account used by the trousers package to sandbox the tcsd
> daemon:/dev/null:/sbin/nologin
> postfix:x:89:89::/var/spool/postfix:/sbin/nologin
> sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
> chrony:x:997:995::/var/lib/chrony:/sbin/nologin
> postgres:x:1000:1000::/home/postgres:/bin/bash
> hsqldb:x:96:96::/var/lib/hsqldb:/sbin/nologin
> davinci:x:1001:1001::/home/davinci:/bin/bash
> tcpdump:x:72:72::/:/sbin/nologin
> cassandra:x:1002:1002::/home/cassandra:/bin/bash
> usbmuxd:x:113:113:usbmuxd user:/:/sbin/nologin
> rpc:x:32:32:Rpcbind Daemon:/var/lib/rpcbind:/sbin/nologin
> qemu:x:107:107:qemu user:/:/sbin/nologin
> apache:x:48:48:Apache:/usr/share/httpd:/sbin/nologin
> pcp:x:996:994:Performance Co-Pilot:/var/lib/pcp:/sbin/nologin
> saslauth:x:995:76:Saslauthd user:/run/saslauthd:/sbin/nologin
> sssd:x:994:993:User for sssd:/:/sbin/nologin
> unbound:x:993:992:Unbound DNS resolver:/etc/unbound:/sbin/nologin
> rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin
> nfsnobody:x:65534:65534:Anonymous NFS User:/var/lib/nfs:/sbin/nologin
> radvd:x:75:75:radvd user:/:/sbin/nologin
> (37 rows)
>
> postgres=# select version();
> version
> ----------------------------------------------------------------------------------------------------------------
> PostgreSQL 9.2.22 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-11), 64-bit
> (1 row)
>
> 2、Also, another command "copy to" can write any files in the server if it
> has the write privileges.
>
> For example, when copy the table test to the postgresql.conf file, it
> will modifiy the postgresql.conf file.
>
> [postgres(at)X86C136 data]$ psql -d postgres -p 5432
> psql (9.2.22)
> Type "help" for help.
>
> postgres=# copy test to '/home/postgres/data/postgresql.conf';
> COPY 37
> postgres=#
>
> 3、So, I think we should restrict the copy directory like pg_read_file or
> pg_read_binary_file function.
>

I'm pretty sure this is why COPY TO/FROM a file is restricted to
superusers, and regular users will get an error. I'm not sure what you
mean by "our security test" but if you're running it under superuser
account, you're probably doing it wrong.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services