Postgresql – user can select all structure from pg_catalog.*, but can’t make a dump -s

postgresql

Given: Postgres RDS 9.3, pg_dump 9.3, RDS user (not SU)

I have:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation rel
pg_dump: [archiver (db)] query was: LOCK TABLE public.rel IN ACCESS SHARE MODE

When I run :

-bash-4.2$ pg_dump -s --section=pre-data -h hostname -d db -U not_su

(tried both --schema-only and --section=pre-data in desperate hope they would differently not require a lock, but no success)

Yet:

When I login to db with same user, I can \dt+ rel and see the structure, so I can see definition from pg_attributes and pg_class for this rel and all related relations…

Is there any way to overcome this limitation?.. Or I have to build DDL based on pg_catalog?.. (seems not reasonable that my user can select all structure, but can't make a structure only dump)

Update:

I must be very unclear In my question. Here is example:

As superuser:

t=# create table so15(i bigserial primary key);
CREATE TABLE
t=# create user so15;
CREATE ROLE

As user without permissions:

postgres@vao-VirtualBox:~$ psql -U so15 -h localhost -d t
psql (9.6.1)

t=> \d+ so15
                                            Table "public.so15"
 Column |  Type  |                    Modifiers                     | Storage | Stats target | Description
--------+--------+--------------------------------------------------+---------+--------------+-------------
 i      | bigint | not null default nextval('so15_i_seq'::regclass) | plain   |              |
Indexes:
    "so15_pkey" PRIMARY KEY, btree (i)

So I can see the structure of so15 relation without read permissions, now as same user I pg_dump -s:

postgres@vao-VirtualBox:~$ pg_dump -s -t so15 -U so15  -h localhost -d t
pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation so15
pg_dump: [archiver (db)] query was: LOCK TABLE public.so15 IN ACCESS SHARE MODE

Best Answer

DUMP pg_catalog

Here's simple script to dump objects' structures in pg_catalog.

pg_dump -p 5432 -U postgres -d postgres -s --table=pg_catalog.* > catalog.sql

And the result looks like that:

CREATE TABLE pg_authid (
    rolname name NOT NULL,
    rolsuper boolean NOT NULL,
    rolinherit boolean NOT NULL,
    rolcreaterole boolean NOT NULL,
    rolcreatedb boolean NOT NULL,
    rolcanlogin boolean NOT NULL,
    rolreplication boolean NOT NULL,
    rolbypassrls boolean NOT NULL,
    rolconnlimit integer NOT NULL,
    rolpassword text,
    rolvaliduntil timestamp with time zone
);
ALTER TABLE ONLY pg_authid REPLICA IDENTITY NOTHING;
ALTER TABLE pg_authid OWNER TO postgres;

UPDATE from Vao Tsun:

From pg_dump doc:

pg_dump internally executes SELECT statements. If you have problems running pg_dump, make sure you are able to select information from the database using, for example, psql

From psql doc

By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.

Note: If \d is used without a pattern argument, it is equivalent to \dtvsE which will show a list of all visible tables, views, sequences and foreign tables

In terms of your case "user without permissions". I guess the so15 table belongs to public schema. That's why so15 user can see table structure (\d+ so15).

For example: if you login admin user and create new table on new schema, then login so15 user to test ( remember to set search_path = new_schema before doing \d+). You cannot see this new table because of permission.

Besides, you cannot dump so15 table due to pg_dump will execute SELECT and it needs permission on this table.

Solution: to dump so15 table you should grant privilege to so15 user.

Related Question