Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitbe6bca2

Browse files
committed
Implement has_sequence_privilege()
Add family of functions that did not exist earlier,mainly due to historical omission. Original patch byAbhijit Menon-Sen, with review and modifications byJoe Conway. catversion.h bumped.
1 parent7ffb14f commitbe6bca2

File tree

7 files changed

+304
-6
lines changed

7 files changed

+304
-6
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 29 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.483 2009/07/22 18:07:26 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.484 2009/08/03 21:11:39 joe Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -11788,6 +11788,21 @@ SET search_path TO <replaceable>schema</> <optional>, <replaceable>schema</>, ..
1178811788
<entry><type>boolean</type></entry>
1178911789
<entry>does current user have privilege for foreign server</entry>
1179011790
</row>
11791+
<row>
11792+
<entry><literal><function>has_sequence_privilege</function>(<parameter>user</parameter>,
11793+
<parameter>sequence</parameter>,
11794+
<parameter>privilege</parameter>)</literal>
11795+
</entry>
11796+
<entry><type>boolean</type></entry>
11797+
<entry>does user have privilege for sequence</entry>
11798+
</row>
11799+
<row>
11800+
<entry><literal><function>has_sequence_privilege</function>(<parameter>sequence</parameter>,
11801+
<parameter>privilege</parameter>)</literal>
11802+
</entry>
11803+
<entry><type>boolean</type></entry>
11804+
<entry>does current user have privilege for sequence</entry>
11805+
</row>
1179111806
<row>
1179211807
<entry><literal><function>has_table_privilege</function>(<parameter>user</parameter>,
1179311808
<parameter>table</parameter>,
@@ -11861,6 +11876,9 @@ SET search_path TO <replaceable>schema</> <optional>, <replaceable>schema</>, ..
1186111876
<indexterm>
1186211877
<primary>has_server_privilege</primary>
1186311878
</indexterm>
11879+
<indexterm>
11880+
<primary>has_sequence_privilege</primary>
11881+
</indexterm>
1186411882
<indexterm>
1186511883
<primary>has_table_privilege</primary>
1186611884
</indexterm>
@@ -11900,6 +11918,16 @@ SELECT has_table_privilege('joe', 'mytable', 'INSERT, SELECT WITH GRANT OPTION')
1190011918
</programlisting>
1190111919
</para>
1190211920

11921+
<para>
11922+
<function>has_sequence_privilege</function> checks whether a user
11923+
can access a sequence in a particular way. The possibilities for its
11924+
arguments are analogous to <function>has_table_privilege</function>.
11925+
The desired access privilege type must evaluate to one of
11926+
<literal>USAGE</literal>,
11927+
<literal>SELECT</literal>, or
11928+
<literal>UPDATE</literal>.
11929+
</para>
11930+
1190311931
<para>
1190411932
<function>has_any_column_privilege</function> checks whether a user can
1190511933
access any column of a table in a particular way.

‎src/backend/utils/adt/acl.c

Lines changed: 213 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/acl.c,v 1.148 2009/06/11 14:49:03 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/acl.c,v 1.149 2009/08/03 21:11:39 joe Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -20,6 +20,7 @@
2020
#include"catalog/pg_authid.h"
2121
#include"catalog/pg_auth_members.h"
2222
#include"catalog/pg_type.h"
23+
#include"catalog/pg_class.h"
2324
#include"commands/dbcommands.h"
2425
#include"commands/tablespace.h"
2526
#include"foreign/foreign.h"
@@ -88,6 +89,7 @@ static AclMode convert_any_priv_string(text *priv_type_text,
8889

8990
staticOidconvert_table_name(text*tablename);
9091
staticAclModeconvert_table_priv_string(text*priv_type_text);
92+
staticAclModeconvert_sequence_priv_string(text*priv_type_text);
9193
staticAttrNumberconvert_column_name(Oidtableoid,text*column);
9294
staticAclModeconvert_column_priv_string(text*priv_type_text);
9395
staticOidconvert_database_name(text*databasename);
@@ -1704,6 +1706,216 @@ convert_table_priv_string(text *priv_type_text)
17041706
returnconvert_any_priv_string(priv_type_text,table_priv_map);
17051707
}
17061708

1709+
/*
1710+
* has_sequence_privilege variants
1711+
*These are all named "has_sequence_privilege" at the SQL level.
1712+
*They take various combinations of relation name, relation OID,
1713+
*user name, user OID, or implicit user = current_user.
1714+
*
1715+
*The result is a boolean value: true if user has the indicated
1716+
*privilege, false if not. The variants that take a relation OID
1717+
*return NULL if the OID doesn't exist.
1718+
*/
1719+
1720+
/*
1721+
* has_sequence_privilege_name_name
1722+
*Check user privileges on a sequence given
1723+
*name username, text sequencename, and text priv name.
1724+
*/
1725+
Datum
1726+
has_sequence_privilege_name_name(PG_FUNCTION_ARGS)
1727+
{
1728+
Namerolename=PG_GETARG_NAME(0);
1729+
text*sequencename=PG_GETARG_TEXT_P(1);
1730+
text*priv_type_text=PG_GETARG_TEXT_P(2);
1731+
Oidroleid;
1732+
Oidsequenceoid;
1733+
AclModemode;
1734+
AclResultaclresult;
1735+
1736+
roleid=get_roleid_checked(NameStr(*rolename));
1737+
mode=convert_sequence_priv_string(priv_type_text);
1738+
sequenceoid=convert_table_name(sequencename);
1739+
if (get_rel_relkind(sequenceoid)!=RELKIND_SEQUENCE)
1740+
ereport(ERROR,
1741+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1742+
errmsg("\"%s\" is not a sequence",
1743+
text_to_cstring(sequencename))));
1744+
1745+
aclresult=pg_class_aclcheck(sequenceoid,roleid,mode);
1746+
1747+
PG_RETURN_BOOL(aclresult==ACLCHECK_OK);
1748+
}
1749+
1750+
/*
1751+
* has_sequence_privilege_name
1752+
*Check user privileges on a sequence given
1753+
*text sequencename and text priv name.
1754+
*current_user is assumed
1755+
*/
1756+
Datum
1757+
has_sequence_privilege_name(PG_FUNCTION_ARGS)
1758+
{
1759+
text*sequencename=PG_GETARG_TEXT_P(0);
1760+
text*priv_type_text=PG_GETARG_TEXT_P(1);
1761+
Oidroleid;
1762+
Oidsequenceoid;
1763+
AclModemode;
1764+
AclResultaclresult;
1765+
1766+
roleid=GetUserId();
1767+
mode=convert_sequence_priv_string(priv_type_text);
1768+
sequenceoid=convert_table_name(sequencename);
1769+
if (get_rel_relkind(sequenceoid)!=RELKIND_SEQUENCE)
1770+
ereport(ERROR,
1771+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1772+
errmsg("\"%s\" is not a sequence",
1773+
text_to_cstring(sequencename))));
1774+
1775+
aclresult=pg_class_aclcheck(sequenceoid,roleid,mode);
1776+
1777+
PG_RETURN_BOOL(aclresult==ACLCHECK_OK);
1778+
}
1779+
1780+
/*
1781+
* has_sequence_privilege_name_id
1782+
*Check user privileges on a sequence given
1783+
*name usename, sequence oid, and text priv name.
1784+
*/
1785+
Datum
1786+
has_sequence_privilege_name_id(PG_FUNCTION_ARGS)
1787+
{
1788+
Nameusername=PG_GETARG_NAME(0);
1789+
Oidsequenceoid=PG_GETARG_OID(1);
1790+
text*priv_type_text=PG_GETARG_TEXT_P(2);
1791+
Oidroleid;
1792+
AclModemode;
1793+
AclResultaclresult;
1794+
charrelkind;
1795+
1796+
roleid=get_roleid_checked(NameStr(*username));
1797+
mode=convert_sequence_priv_string(priv_type_text);
1798+
relkind=get_rel_relkind(sequenceoid);
1799+
if (relkind=='\0')
1800+
PG_RETURN_NULL();
1801+
elseif (relkind!=RELKIND_SEQUENCE)
1802+
ereport(ERROR,
1803+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1804+
errmsg("\"%s\" is not a sequence",
1805+
get_rel_name(sequenceoid))));
1806+
1807+
aclresult=pg_class_aclcheck(sequenceoid,roleid,mode);
1808+
1809+
PG_RETURN_BOOL(aclresult==ACLCHECK_OK);
1810+
}
1811+
1812+
/*
1813+
* has_sequence_privilege_id
1814+
*Check user privileges on a sequence given
1815+
*sequence oid, and text priv name.
1816+
*current_user is assumed
1817+
*/
1818+
Datum
1819+
has_sequence_privilege_id(PG_FUNCTION_ARGS)
1820+
{
1821+
Oidsequenceoid=PG_GETARG_OID(0);
1822+
text*priv_type_text=PG_GETARG_TEXT_P(1);
1823+
Oidroleid;
1824+
AclModemode;
1825+
AclResultaclresult;
1826+
charrelkind;
1827+
1828+
roleid=GetUserId();
1829+
mode=convert_sequence_priv_string(priv_type_text);
1830+
relkind=get_rel_relkind(sequenceoid);
1831+
if (relkind=='\0')
1832+
PG_RETURN_NULL();
1833+
elseif (relkind!=RELKIND_SEQUENCE)
1834+
ereport(ERROR,
1835+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1836+
errmsg("\"%s\" is not a sequence",
1837+
get_rel_name(sequenceoid))));
1838+
1839+
aclresult=pg_class_aclcheck(sequenceoid,roleid,mode);
1840+
1841+
PG_RETURN_BOOL(aclresult==ACLCHECK_OK);
1842+
}
1843+
1844+
/*
1845+
* has_sequence_privilege_id_name
1846+
*Check user privileges on a sequence given
1847+
*roleid, text sequencename, and text priv name.
1848+
*/
1849+
Datum
1850+
has_sequence_privilege_id_name(PG_FUNCTION_ARGS)
1851+
{
1852+
Oidroleid=PG_GETARG_OID(0);
1853+
text*sequencename=PG_GETARG_TEXT_P(1);
1854+
text*priv_type_text=PG_GETARG_TEXT_P(2);
1855+
Oidsequenceoid;
1856+
AclModemode;
1857+
AclResultaclresult;
1858+
1859+
mode=convert_sequence_priv_string(priv_type_text);
1860+
sequenceoid=convert_table_name(sequencename);
1861+
if (get_rel_relkind(sequenceoid)!=RELKIND_SEQUENCE)
1862+
ereport(ERROR,
1863+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1864+
errmsg("\"%s\" is not a sequence",
1865+
text_to_cstring(sequencename))));
1866+
1867+
aclresult=pg_class_aclcheck(sequenceoid,roleid,mode);
1868+
1869+
PG_RETURN_BOOL(aclresult==ACLCHECK_OK);
1870+
}
1871+
1872+
/*
1873+
* has_sequence_privilege_id_id
1874+
*Check user privileges on a sequence given
1875+
*roleid, sequence oid, and text priv name.
1876+
*/
1877+
Datum
1878+
has_sequence_privilege_id_id(PG_FUNCTION_ARGS)
1879+
{
1880+
Oidroleid=PG_GETARG_OID(0);
1881+
Oidsequenceoid=PG_GETARG_OID(1);
1882+
text*priv_type_text=PG_GETARG_TEXT_P(2);
1883+
AclModemode;
1884+
AclResultaclresult;
1885+
charrelkind;
1886+
1887+
mode=convert_sequence_priv_string(priv_type_text);
1888+
relkind=get_rel_relkind(sequenceoid);
1889+
if (relkind=='\0')
1890+
PG_RETURN_NULL();
1891+
elseif (relkind!=RELKIND_SEQUENCE)
1892+
ereport(ERROR,
1893+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1894+
errmsg("\"%s\" is not a sequence",
1895+
get_rel_name(sequenceoid))));
1896+
1897+
aclresult=pg_class_aclcheck(sequenceoid,roleid,mode);
1898+
1899+
PG_RETURN_BOOL(aclresult==ACLCHECK_OK);
1900+
}
1901+
1902+
/*
1903+
* convert_sequence_priv_string
1904+
*Convert text string to AclMode value.
1905+
*/
1906+
staticAclMode
1907+
convert_sequence_priv_string(text*priv_type_text)
1908+
{
1909+
staticconstpriv_mapsequence_priv_map[]= {
1910+
{"USAGE",ACL_USAGE },
1911+
{"SELECT",ACL_SELECT },
1912+
{"UPDATE",ACL_UPDATE },
1913+
{NULL,0 }
1914+
};
1915+
1916+
returnconvert_any_priv_string(priv_type_text,sequence_priv_map);
1917+
}
1918+
17071919

17081920
/*
17091921
* has_any_column_privilege variants

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.535 2009/08/02 22:14:52 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.536 2009/08/03 21:11:39 joe Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200908021
56+
#defineCATALOG_VERSION_NO200908031
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.547 2009/07/29 20:56:20 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.548 2009/08/03 21:11:39 joe Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -2918,6 +2918,19 @@ DESCR("current user privilege on relation by rel name");
29182918
DATA(insertOID=1927 (has_table_privilegePGNSPPGUID12100ffftfs2016"26 25"_null__null__null__null_has_table_privilege_id_null__null__null_ ));
29192919
DESCR("current user privilege on relation by rel oid");
29202920

2921+
DATA(insertOID=2181 (has_sequence_privilegePGNSPPGUID12100ffftfs3016"19 25 25"_null__null__null__null_has_sequence_privilege_name_name_null__null__null_ ));
2922+
DESCR("user privilege on sequence by username, seq name");
2923+
DATA(insertOID=2182 (has_sequence_privilegePGNSPPGUID12100ffftfs3016"19 26 25"_null__null__null__null_has_sequence_privilege_name_id_null__null__null_ ));
2924+
DESCR("user privilege on sequence by username, seq oid");
2925+
DATA(insertOID=2183 (has_sequence_privilegePGNSPPGUID12100ffftfs3016"26 25 25"_null__null__null__null_has_sequence_privilege_id_name_null__null__null_ ));
2926+
DESCR("user privilege on sequence by user oid, seq name");
2927+
DATA(insertOID=2184 (has_sequence_privilegePGNSPPGUID12100ffftfs3016"26 26 25"_null__null__null__null_has_sequence_privilege_id_id_null__null__null_ ));
2928+
DESCR("user privilege on sequence by user oid, seq oid");
2929+
DATA(insertOID=2185 (has_sequence_privilegePGNSPPGUID12100ffftfs2016"25 25"_null__null__null__null_has_sequence_privilege_name_null__null__null_ ));
2930+
DESCR("current user privilege on sequence by seq name");
2931+
DATA(insertOID=2186 (has_sequence_privilegePGNSPPGUID12100ffftfs2016"26 25"_null__null__null__null_has_sequence_privilege_id_null__null__null_ ));
2932+
DESCR("current user privilege on sequence by seq oid");
2933+
29212934
DATA(insertOID=3012 (has_column_privilegePGNSPPGUID12100ffftfs4016"19 25 25 25"_null__null__null__null_has_column_privilege_name_name_name_null__null__null_ ));
29222935
DESCR("user privilege on column by username, rel name, col name");
29232936
DATA(insertOID=3013 (has_column_privilegePGNSPPGUID12100ffftfs4016"19 25 21 25"_null__null__null__null_has_column_privilege_name_name_attnum_null__null__null_ ));

‎src/include/utils/builtins.h

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.336 2009/08/01 19:59:41 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.337 2009/08/03 21:11:39 joe Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -46,6 +46,12 @@ extern Datum has_table_privilege_id_name(PG_FUNCTION_ARGS);
4646
externDatumhas_table_privilege_id_id(PG_FUNCTION_ARGS);
4747
externDatumhas_table_privilege_name(PG_FUNCTION_ARGS);
4848
externDatumhas_table_privilege_id(PG_FUNCTION_ARGS);
49+
externDatumhas_sequence_privilege_name_name(PG_FUNCTION_ARGS);
50+
externDatumhas_sequence_privilege_name_id(PG_FUNCTION_ARGS);
51+
externDatumhas_sequence_privilege_id_name(PG_FUNCTION_ARGS);
52+
externDatumhas_sequence_privilege_id_id(PG_FUNCTION_ARGS);
53+
externDatumhas_sequence_privilege_name(PG_FUNCTION_ARGS);
54+
externDatumhas_sequence_privilege_id(PG_FUNCTION_ARGS);
4955
externDatumhas_database_privilege_name_name(PG_FUNCTION_ARGS);
5056
externDatumhas_database_privilege_name_id(PG_FUNCTION_ARGS);
5157
externDatumhas_database_privilege_id_name(PG_FUNCTION_ARGS);

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -815,8 +815,30 @@ SELECT has_table_privilege('regressuser1', 'atest4', 'SELECT WITH GRANT OPTION')
815815
t
816816
(1 row)
817817

818+
-- has_sequence_privilege tests
819+
\c -
820+
CREATE SEQUENCE x_seq;
821+
GRANT USAGE on x_seq to regressuser2;
822+
SELECT has_sequence_privilege('regressuser1', 'atest1', 'SELECT');
823+
ERROR: "atest1" is not a sequence
824+
SELECT has_sequence_privilege('regressuser1', 'x_seq', 'INSERT');
825+
ERROR: unrecognized privilege type: "INSERT"
826+
SELECT has_sequence_privilege('regressuser1', 'x_seq', 'SELECT');
827+
has_sequence_privilege
828+
------------------------
829+
f
830+
(1 row)
831+
832+
SET SESSION AUTHORIZATION regressuser2;
833+
SELECT has_sequence_privilege('x_seq', 'USAGE');
834+
has_sequence_privilege
835+
------------------------
836+
t
837+
(1 row)
838+
818839
-- clean up
819840
\c
841+
drop sequence x_seq;
820842
DROP FUNCTION testfunc2(int);
821843
DROP FUNCTION testfunc4(boolean);
822844
DROP VIEW atestv1;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp