Skip to content

Commit 328dfbd

Browse files
tglsfdctglsfdc
committed
Extend psql's \lo_list/\dl to be able to print large objects' ACLs.
The ACL is printed when you add + to the command, similarly to various other psql backslash commands. Along the way, move the code for this into describe.c, where it is a better fit (and can share some code). Pavel Luzanov, reviewed by Georgios Kokolatos Discussion: https://postgr.es/m/6d722115-6297-bc53-bb7f-5f150e765299@postgrespro.ru
1 parent ee58223 commit 328dfbd

File tree

11 files changed

+144
-100
lines changed

11 files changed

+144
-100
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2146,7 +2146,7 @@ REVOKE ALL ON accounts FROM PUBLIC;
21462146
<entry><literal>LARGE OBJECT</literal></entry>
21472147
<entry><literal>rw</literal></entry>
21482148
<entry>none</entry>
2149-
<entry></entry>
2149+
<entry><literal>\dl+</literal></entry>
21502150
</row>
21512151
<row>
21522152
<entry><literal>SCHEMA</literal></entry>

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1681,11 +1681,14 @@ testdb=&gt;
16811681

16821682

16831683
<varlistentry>
1684-
<term><literal>\dl</literal></term>
1684+
<term><literal>\dl[+]</literal></term>
16851685
<listitem>
16861686
<para>
16871687
This is an alias for <command>\lo_list</command>, which shows a
16881688
list of large objects.
1689+
If <literal>+</literal> is appended to the command name,
1690+
each large object is listed with its associated permissions,
1691+
if any.
16891692
</para>
16901693
</listitem>
16911694
</varlistentry>
@@ -2610,12 +2613,15 @@ lo_import 152801
26102613
</varlistentry>
26112614

26122615
<varlistentry>
2613-
<term><literal>\lo_list</literal></term>
2616+
<term><literal>\lo_list[+]</literal></term>
26142617
<listitem>
26152618
<para>
26162619
Shows a list of all <productname>PostgreSQL</productname>
26172620
large objects currently stored in the database,
26182621
along with any comments provided for them.
2622+
If <literal>+</literal> is appended to the command name,
2623+
each large object is listed with its associated permissions,
2624+
if any.
26192625
</para>
26202626
</listitem>
26212627
</varlistentry>

‎src/bin/psql/command.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -811,7 +811,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
811811
success = describeRoles(pattern, show_verbose, show_system);
812812
break;
813813
case 'l':
814-
success = do_lo_list();
814+
success = listLargeObjects(show_verbose);
815815
break;
816816
case 'L':
817817
success = listLanguages(pattern, show_verbose, show_system);
@@ -1963,7 +1963,9 @@ exec_command_lo(PsqlScanState scan_state, bool active_branch, const char *cmd)
19631963
}
19641964

19651965
else if (strcmp(cmd + 3, "list") == 0)
1966-
success = do_lo_list();
1966+
success = listLargeObjects(false);
1967+
else if (strcmp(cmd + 3, "list+") == 0)
1968+
success = listLargeObjects(true);
19671969

19681970
else if (strcmp(cmd + 3, "unlink") == 0)
19691971
{

‎src/bin/psql/describe.c

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6463,3 +6463,49 @@ listOpFamilyFunctions(const char *access_method_pattern,
64636463
PQclear(res);
64646464
return true;
64656465
}
6466+
6467+
/*
6468+
* \dl or \lo_list
6469+
* Lists large objects
6470+
*/
6471+
bool
6472+
listLargeObjects(bool verbose)
6473+
{
6474+
PQExpBufferData buf;
6475+
PGresult *res;
6476+
printQueryOpt myopt = pset.popt;
6477+
6478+
initPQExpBuffer(&buf);
6479+
6480+
printfPQExpBuffer(&buf,
6481+
"SELECT oid as \"%s\",\n"
6482+
" pg_catalog.pg_get_userbyid(lomowner) as \"%s\",\n ",
6483+
gettext_noop("ID"),
6484+
gettext_noop("Owner"));
6485+
6486+
if (verbose)
6487+
{
6488+
printACLColumn(&buf, "lomacl");
6489+
appendPQExpBufferStr(&buf, ",\n ");
6490+
}
6491+
6492+
appendPQExpBuffer(&buf,
6493+
"pg_catalog.obj_description(oid, 'pg_largeobject') as \"%s\"\n"
6494+
"FROM pg_catalog.pg_largeobject_metadata\n"
6495+
"ORDER BY oid",
6496+
gettext_noop("Description"));
6497+
6498+
res = PSQLexec(buf.data);
6499+
termPQExpBuffer(&buf);
6500+
if (!res)
6501+
return false;
6502+
6503+
myopt.nullPrint = NULL;
6504+
myopt.title = _("Large objects");
6505+
myopt.translate_header = true;
6506+
6507+
printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
6508+
6509+
PQclear(res);
6510+
return true;
6511+
}

‎src/bin/psql/describe.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -139,5 +139,7 @@ extern bool listOpFamilyOperators(const char *accessMethod_pattern,
139139
extern bool listOpFamilyFunctions(const char *access_method_pattern,
140140
const char *family_pattern, bool verbose);
141141

142+
/* \dl or \lo_list */
143+
extern bool listLargeObjects(bool verbose);
142144

143145
#endif /* DESCRIBE_H */

‎src/bin/psql/help.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -248,7 +248,7 @@ slashUsage(unsigned short int pager)
248248
fprintf(output, _(" \\dFt[+] [PATTERN] list text search templates\n"));
249249
fprintf(output, _(" \\dg[S+] [PATTERN] list roles\n"));
250250
fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n"));
251-
fprintf(output, _(" \\dl list large objects, same as \\lo_list\n"));
251+
fprintf(output, _(" \\dl[+] list large objects, same as \\lo_list\n"));
252252
fprintf(output, _(" \\dL[S+] [PATTERN] list procedural languages\n"));
253253
fprintf(output, _(" \\dm[S+] [PATTERN] list materialized views\n"));
254254
fprintf(output, _(" \\dn[S+] [PATTERN] list schemas\n"));
@@ -325,7 +325,7 @@ slashUsage(unsigned short int pager)
325325
fprintf(output, _("Large Objects\n"));
326326
fprintf(output, _(" \\lo_export LOBOID FILE\n"
327327
" \\lo_import FILE [COMMENT]\n"
328-
" \\lo_list\n"
328+
" \\lo_list[+]\n"
329329
" \\lo_unlink LOBOID large object operations\n"));
330330

331331
ClosePager(output);

‎src/bin/psql/large_obj.c

Lines changed: 0 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -262,42 +262,3 @@ do_lo_unlink(const char *loid_arg)
262262

263263
return true;
264264
}
265-
266-
267-
268-
/*
269-
* do_lo_list()
270-
*
271-
* Show all large objects in database with comments
272-
*/
273-
bool
274-
do_lo_list(void)
275-
{
276-
PGresult *res;
277-
char buf[1024];
278-
printQueryOpt myopt = pset.popt;
279-
280-
snprintf(buf, sizeof(buf),
281-
"SELECT oid as \"%s\",\n"
282-
" pg_catalog.pg_get_userbyid(lomowner) as \"%s\",\n"
283-
" pg_catalog.obj_description(oid, 'pg_largeobject') as \"%s\"\n"
284-
" FROM pg_catalog.pg_largeobject_metadata "
285-
" ORDER BY oid",
286-
gettext_noop("ID"),
287-
gettext_noop("Owner"),
288-
gettext_noop("Description"));
289-
290-
res = PSQLexec(buf);
291-
if (!res)
292-
return false;
293-
294-
myopt.topt.tuples_only = false;
295-
myopt.nullPrint = NULL;
296-
myopt.title = _("Large objects");
297-
myopt.translate_header = true;
298-
299-
printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
300-
301-
PQclear(res);
302-
return true;
303-
}

‎src/bin/psql/large_obj.h

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,5 @@
1111
bool do_lo_export(const char *loid_arg, const char *filename_arg);
1212
bool do_lo_import(const char *filename_arg, const char *comment_arg);
1313
bool do_lo_unlink(const char *loid_arg);
14-
bool do_lo_list(void);
1514

1615
#endif /* LARGE_OBJ_H */

‎src/test/regress/expected/largeobject.out

Lines changed: 34 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -6,31 +6,46 @@
66
\getenv abs_builddir PG_ABS_BUILDDIR
77
-- ensure consistent test output regardless of the default bytea format
88
SET bytea_output TO escape;
9+
-- Test ALTER LARGE OBJECT OWNER, GRANT, COMMENT
10+
CREATE ROLE regress_lo_user;
11+
SELECT lo_create(42);
12+
lo_create
13+
-----------
14+
42
15+
(1 row)
16+
17+
ALTER LARGE OBJECT 42 OWNER TO regress_lo_user;
18+
GRANT SELECT ON LARGE OBJECT 42 TO public;
19+
COMMENT ON LARGE OBJECT 42 IS 'the ultimate answer';
20+
-- Test psql's \lo_list et al (we assume no other LOs exist yet)
21+
\lo_list
22+
Large objects
23+
ID | Owner | Description
24+
----+-----------------+---------------------
25+
42 | regress_lo_user | the ultimate answer
26+
(1 row)
27+
28+
\lo_list+
29+
Large objects
30+
ID | Owner | Access privileges | Description
31+
----+-----------------+------------------------------------+---------------------
32+
42 | regress_lo_user | regress_lo_user=rw/regress_lo_user+| the ultimate answer
33+
| | =r/regress_lo_user |
34+
(1 row)
35+
36+
\lo_unlink 42
37+
\dl
38+
Large objects
39+
ID | Owner | Description
40+
----+-------+-------------
41+
(0 rows)
42+
943
-- Load a file
1044
CREATE TABLE lotest_stash_values (loid oid, fd integer);
1145
-- lo_creat(mode integer) returns oid
1246
-- The mode arg to lo_creat is unused, some vestigal holdover from ancient times
1347
-- returns the large object id
1448
INSERT INTO lotest_stash_values (loid) SELECT lo_creat(42);
15-
-- Test ALTER LARGE OBJECT
16-
CREATE ROLE regress_lo_user;
17-
DO $$
18-
BEGIN
19-
EXECUTE 'ALTER LARGE OBJECT ' || (select loid from lotest_stash_values)
20-
|| ' OWNER TO regress_lo_user';
21-
END
22-
$$;
23-
SELECT
24-
rol.rolname
25-
FROM
26-
lotest_stash_values s
27-
JOIN pg_largeobject_metadata lo ON s.loid = lo.oid
28-
JOIN pg_authid rol ON lo.lomowner = rol.oid;
29-
rolname
30-
-----------------
31-
regress_lo_user
32-
(1 row)
33-
3449
-- NOTE: large objects require transactions
3550
BEGIN;
3651
-- lo_open(lobjId oid, mode integer) returns integer

‎src/test/regress/expected/largeobject_1.out

Lines changed: 34 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -6,31 +6,46 @@
66
\getenv abs_builddir PG_ABS_BUILDDIR
77
-- ensure consistent test output regardless of the default bytea format
88
SET bytea_output TO escape;
9+
-- Test ALTER LARGE OBJECT OWNER, GRANT, COMMENT
10+
CREATE ROLE regress_lo_user;
11+
SELECT lo_create(42);
12+
lo_create
13+
-----------
14+
42
15+
(1 row)
16+
17+
ALTER LARGE OBJECT 42 OWNER TO regress_lo_user;
18+
GRANT SELECT ON LARGE OBJECT 42 TO public;
19+
COMMENT ON LARGE OBJECT 42 IS 'the ultimate answer';
20+
-- Test psql's \lo_list et al (we assume no other LOs exist yet)
21+
\lo_list
22+
Large objects
23+
ID | Owner | Description
24+
----+-----------------+---------------------
25+
42 | regress_lo_user | the ultimate answer
26+
(1 row)
27+
28+
\lo_list+
29+
Large objects
30+
ID | Owner | Access privileges | Description
31+
----+-----------------+------------------------------------+---------------------
32+
42 | regress_lo_user | regress_lo_user=rw/regress_lo_user+| the ultimate answer
33+
| | =r/regress_lo_user |
34+
(1 row)
35+
36+
\lo_unlink 42
37+
\dl
38+
Large objects
39+
ID | Owner | Description
40+
----+-------+-------------
41+
(0 rows)
42+
943
-- Load a file
1044
CREATE TABLE lotest_stash_values (loid oid, fd integer);
1145
-- lo_creat(mode integer) returns oid
1246
-- The mode arg to lo_creat is unused, some vestigal holdover from ancient times
1347
-- returns the large object id
1448
INSERT INTO lotest_stash_values (loid) SELECT lo_creat(42);
15-
-- Test ALTER LARGE OBJECT
16-
CREATE ROLE regress_lo_user;
17-
DO $$
18-
BEGIN
19-
EXECUTE 'ALTER LARGE OBJECT ' || (select loid from lotest_stash_values)
20-
|| ' OWNER TO regress_lo_user';
21-
END
22-
$$;
23-
SELECT
24-
rol.rolname
25-
FROM
26-
lotest_stash_values s
27-
JOIN pg_largeobject_metadata lo ON s.loid = lo.oid
28-
JOIN pg_authid rol ON lo.lomowner = rol.oid;
29-
rolname
30-
-----------------
31-
regress_lo_user
32-
(1 row)
33-
3449
-- NOTE: large objects require transactions
3550
BEGIN;
3651
-- lo_open(lobjId oid, mode integer) returns integer

‎src/test/regress/sql/largeobject.sql

Lines changed: 13 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -9,28 +9,26 @@
99
-- ensure consistent test output regardless of the default bytea format
1010
SET bytea_output TO escape;
1111

12+
-- Test ALTER LARGE OBJECT OWNER, GRANT, COMMENT
13+
CREATE ROLE regress_lo_user;
14+
SELECT lo_create(42);
15+
ALTER LARGE OBJECT 42 OWNER TO regress_lo_user;
16+
GRANT SELECT ON LARGE OBJECT 42 TO public;
17+
COMMENT ON LARGE OBJECT 42 IS 'the ultimate answer';
18+
19+
-- Test psql's \lo_list et al (we assume no other LOs exist yet)
20+
\lo_list
21+
\lo_list+
22+
\lo_unlink 42
23+
\dl
24+
1225
-- Load a file
1326
CREATE TABLE lotest_stash_values (loid oid, fd integer);
1427
-- lo_creat(mode integer) returns oid
1528
-- The mode arg to lo_creat is unused, some vestigal holdover from ancient times
1629
-- returns the large object id
1730
INSERT INTO lotest_stash_values (loid) SELECT lo_creat(42);
1831

19-
-- Test ALTER LARGE OBJECT
20-
CREATE ROLE regress_lo_user;
21-
DO $$
22-
BEGIN
23-
EXECUTE 'ALTER LARGE OBJECT ' || (select loid from lotest_stash_values)
24-
|| ' OWNER TO regress_lo_user';
25-
END
26-
$$;
27-
SELECT
28-
rol.rolname
29-
FROM
30-
lotest_stash_values s
31-
JOIN pg_largeobject_metadata lo ON s.loid = lo.oid
32-
JOIN pg_authid rol ON lo.lomowner = rol.oid;
33-
3432
-- NOTE: large objects require transactions
3533
BEGIN;
3634

0 commit comments

Comments
 (0)