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

Commitba33464

Browse files
authored
add regression tests for#1061 (#1062)
* add regression tests for#1061* fix tests (the tests run in parallel => rase)
1 parent9690650 commitba33464

File tree

1 file changed

+287
-0
lines changed

1 file changed

+287
-0
lines changed

‎database/postgres/database_test.go‎

Lines changed: 287 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,11 +3,18 @@
33
package postgres
44

55
import (
6+
"database/sql"
7+
"fmt"
8+
"os"
9+
"strconv"
610
"strings"
711
"testing"
812

13+
_"github.com/lib/pq"
914
"github.com/sqldef/sqldef/v3/database"
1015
"github.com/sqldef/sqldef/v3/testutil"
16+
"github.com/stretchr/testify/assert"
17+
"github.com/stretchr/testify/require"
1118
)
1219

1320
funcTestUnixSocketConnection(t*testing.T) {
@@ -40,3 +47,283 @@ func TestUnixSocketConnection(t *testing.T) {
4047
t.Errorf("expected socket to be used, got: %v",err)
4148
}
4249
}
50+
51+
// TestExtensionOIDCollisionByInjectedDependency verifies that ExportDDLs correctly
52+
// handles OID collisions between user objects and extension objects.
53+
//
54+
// PostgreSQL's pg_depend stores object dependencies using (classid, objid) pairs.
55+
// The classid identifies which catalog the object belongs to (e.g., pg_class for tables,
56+
// pg_proc for functions). Without checking classid, a user table could be incorrectly
57+
// filtered if its OID matches an extension function's OID in pg_depend.
58+
//
59+
// This test injects a fake pg_depend entry with the table's OID but a different classid
60+
// (pg_proc) to simulate such a collision, then verifies the table is still exported.
61+
funcTestExtensionOIDCollisionByInjectedDependency(t*testing.T) {
62+
db:=setupTestDatabase(t)
63+
deferdb.Close()
64+
65+
// Create a user table
66+
_,err:=db.DB().Exec("CREATE TABLE collision_victim (id bigint PRIMARY KEY, name text)")
67+
require.NoError(t,err)
68+
69+
// Get the table's OID
70+
vartableOIDint64
71+
err=db.DB().QueryRow(`
72+
SELECT c.oid FROM pg_class c
73+
JOIN pg_namespace n ON c.relnamespace = n.oid
74+
WHERE c.relname = 'collision_victim' AND n.nspname = 'public'
75+
`).Scan(&tableOID)
76+
require.NoError(t,err)
77+
78+
// Get pg_proc's classid (for functions)
79+
varpgProcClassIDint64
80+
err=db.DB().QueryRow(`SELECT oid FROM pg_class WHERE relname = 'pg_proc'`).Scan(&pgProcClassID)
81+
require.NoError(t,err)
82+
83+
// Get pg_extension's OID (we need a real extension for the refclassid/refobjid)
84+
_,err=db.DB().Exec("CREATE EXTENSION IF NOT EXISTS pgcrypto")
85+
require.NoError(t,err)
86+
87+
varextOIDint64
88+
err=db.DB().QueryRow(`SELECT oid FROM pg_extension WHERE extname = 'pgcrypto'`).Scan(&extOID)
89+
require.NoError(t,err)
90+
91+
varpgExtensionClassIDint64
92+
err=db.DB().QueryRow(`SELECT oid FROM pg_class WHERE relname = 'pg_extension'`).Scan(&pgExtensionClassID)
93+
require.NoError(t,err)
94+
95+
t.Logf("Table OID: %d",tableOID)
96+
t.Logf("pg_proc classid: %d",pgProcClassID)
97+
t.Logf("pgcrypto extension OID: %d",extOID)
98+
99+
// Inject a fake pg_depend entry that simulates an OID collision:
100+
// - objid = table's OID (collision!)
101+
// - classid = pg_proc (as if it were a function, not a table)
102+
// - deptype = 'e' (extension dependency)
103+
// This simulates the scenario where a table's OID matches an extension function's OID
104+
_,err=db.DB().Exec(`
105+
INSERT INTO pg_depend (classid, objid, objsubid, refclassid, refobjid, refobjsubid, deptype)
106+
VALUES ($1, $2, 0, $3, $4, 0, 'e')
107+
`,pgProcClassID,tableOID,pgExtensionClassID,extOID)
108+
require.NoError(t,err,"Failed to inject fake pg_depend entry - need superuser privileges")
109+
110+
t.Log("Injected fake pg_depend entry simulating OID collision")
111+
112+
// Verify the fake entry exists
113+
varfakeEntryExistsbool
114+
err=db.DB().QueryRow(`
115+
SELECT EXISTS (
116+
SELECT 1 FROM pg_depend
117+
WHERE objid = $1 AND classid = $2 AND deptype = 'e'
118+
)
119+
`,tableOID,pgProcClassID).Scan(&fakeEntryExists)
120+
require.NoError(t,err)
121+
require.True(t,fakeEntryExists,"Fake pg_depend entry should exist")
122+
123+
// Verify that a query without classid check would incorrectly see our table
124+
// as extension-dependent, while the actual ExportDDLs (with classid check) works correctly
125+
varqueryWithoutClassidExcludesbool
126+
err=db.DB().QueryRow(`
127+
SELECT EXISTS (
128+
SELECT 1 FROM pg_depend d
129+
WHERE d.objid = $1 AND d.deptype = 'e'
130+
)
131+
`,tableOID).Scan(&queryWithoutClassidExcludes)
132+
require.NoError(t,err)
133+
134+
t.Logf("Query without classid check would exclude table: %v",queryWithoutClassidExcludes)
135+
assert.True(t,queryWithoutClassidExcludes,"Query without classid should see fake extension dependency")
136+
137+
// Verify ExportDDLs correctly includes the table despite the fake pg_depend entry
138+
db.SetGeneratorConfig(database.GeneratorConfig{
139+
LegacyIgnoreQuotes:true,
140+
})
141+
exported,err:=db.ExportDDLs()
142+
require.NoError(t,err)
143+
144+
t.Logf("Exported DDL contains collision_victim: %v",strings.Contains(exported,"collision_victim"))
145+
146+
assert.Contains(t,exported,"collision_victim",
147+
"Table should be exported despite fake pg_depend entry with different classid")
148+
149+
// Clean up the fake dependency
150+
_,_=db.DB().Exec(`
151+
DELETE FROM pg_depend
152+
WHERE objid = $1 AND classid = $2 AND deptype = 'e'
153+
`,tableOID,pgProcClassID)
154+
}
155+
156+
// TestExtensionOIDCollisionForViews verifies that views are correctly exported
157+
// even when a fake extension dependency with a different classid exists.
158+
funcTestExtensionOIDCollisionForViews(t*testing.T) {
159+
db:=setupTestDatabase(t)
160+
deferdb.Close()
161+
162+
// Create a user view
163+
_,err:=db.DB().Exec("CREATE TABLE view_base (id int)")
164+
require.NoError(t,err)
165+
_,err=db.DB().Exec("CREATE VIEW collision_victim_view AS SELECT * FROM view_base")
166+
require.NoError(t,err)
167+
168+
// Get the view's OID
169+
varviewOIDint64
170+
err=db.DB().QueryRow(`
171+
SELECT c.oid FROM pg_class c
172+
JOIN pg_namespace n ON c.relnamespace = n.oid
173+
WHERE c.relname = 'collision_victim_view' AND n.nspname = 'public'
174+
`).Scan(&viewOID)
175+
require.NoError(t,err)
176+
177+
// Get pg_proc's classid and extension info
178+
varpgProcClassIDint64
179+
err=db.DB().QueryRow(`SELECT oid FROM pg_class WHERE relname = 'pg_proc'`).Scan(&pgProcClassID)
180+
require.NoError(t,err)
181+
182+
_,err=db.DB().Exec("CREATE EXTENSION IF NOT EXISTS pgcrypto")
183+
require.NoError(t,err)
184+
185+
varextOIDint64
186+
err=db.DB().QueryRow(`SELECT oid FROM pg_extension WHERE extname = 'pgcrypto'`).Scan(&extOID)
187+
require.NoError(t,err)
188+
189+
varpgExtensionClassIDint64
190+
err=db.DB().QueryRow(`SELECT oid FROM pg_class WHERE relname = 'pg_extension'`).Scan(&pgExtensionClassID)
191+
require.NoError(t,err)
192+
193+
// Inject fake dependency
194+
_,err=db.DB().Exec(`
195+
INSERT INTO pg_depend (classid, objid, objsubid, refclassid, refobjid, refobjsubid, deptype)
196+
VALUES ($1, $2, 0, $3, $4, 0, 'e')
197+
`,pgProcClassID,viewOID,pgExtensionClassID,extOID)
198+
require.NoError(t,err)
199+
200+
// Export and verify view is included
201+
db.SetGeneratorConfig(database.GeneratorConfig{
202+
LegacyIgnoreQuotes:true,
203+
})
204+
exported,err:=db.ExportDDLs()
205+
require.NoError(t,err)
206+
207+
assert.Contains(t,exported,"collision_victim_view",
208+
"View should be exported despite fake pg_depend entry with different classid")
209+
210+
// Cleanup
211+
_,_=db.DB().Exec(`DELETE FROM pg_depend WHERE objid = $1 AND classid = $2 AND deptype = 'e'`,
212+
viewOID,pgProcClassID)
213+
}
214+
215+
// TestExtensionOIDCollisionForFunctions verifies that functions are correctly exported
216+
// even when a fake extension dependency with a different classid exists.
217+
funcTestExtensionOIDCollisionForFunctions(t*testing.T) {
218+
db:=setupTestDatabase(t)
219+
deferdb.Close()
220+
221+
// Create a user function
222+
_,err:=db.DB().Exec(`
223+
CREATE FUNCTION collision_victim_func() RETURNS int AS $$
224+
BEGIN RETURN 1; END;
225+
$$ LANGUAGE plpgsql
226+
`)
227+
require.NoError(t,err)
228+
229+
// Get the function's OID
230+
varfuncOIDint64
231+
err=db.DB().QueryRow(`
232+
SELECT p.oid FROM pg_proc p
233+
JOIN pg_namespace n ON p.pronamespace = n.oid
234+
WHERE p.proname = 'collision_victim_func' AND n.nspname = 'public'
235+
`).Scan(&funcOID)
236+
require.NoError(t,err)
237+
238+
// Get pg_type's classid (different from pg_proc)
239+
varpgTypeClassIDint64
240+
err=db.DB().QueryRow(`SELECT oid FROM pg_class WHERE relname = 'pg_type'`).Scan(&pgTypeClassID)
241+
require.NoError(t,err)
242+
243+
_,err=db.DB().Exec("CREATE EXTENSION IF NOT EXISTS pgcrypto")
244+
require.NoError(t,err)
245+
246+
varextOIDint64
247+
err=db.DB().QueryRow(`SELECT oid FROM pg_extension WHERE extname = 'pgcrypto'`).Scan(&extOID)
248+
require.NoError(t,err)
249+
250+
varpgExtensionClassIDint64
251+
err=db.DB().QueryRow(`SELECT oid FROM pg_class WHERE relname = 'pg_extension'`).Scan(&pgExtensionClassID)
252+
require.NoError(t,err)
253+
254+
// Inject fake dependency with pg_type classid (not pg_proc)
255+
_,err=db.DB().Exec(`
256+
INSERT INTO pg_depend (classid, objid, objsubid, refclassid, refobjid, refobjsubid, deptype)
257+
VALUES ($1, $2, 0, $3, $4, 0, 'e')
258+
`,pgTypeClassID,funcOID,pgExtensionClassID,extOID)
259+
require.NoError(t,err)
260+
261+
// Export and verify function is included
262+
db.SetGeneratorConfig(database.GeneratorConfig{
263+
LegacyIgnoreQuotes:true,
264+
})
265+
exported,err:=db.ExportDDLs()
266+
require.NoError(t,err)
267+
268+
assert.Contains(t,exported,"collision_victim_func",
269+
"Function should be exported despite fake pg_depend entry with different classid")
270+
271+
// Cleanup
272+
_,_=db.DB().Exec(`DELETE FROM pg_depend WHERE objid = $1 AND classid = $2 AND deptype = 'e'`,
273+
funcOID,pgTypeClassID)
274+
}
275+
276+
// Helper functions
277+
278+
funcsetupTestDatabase(t*testing.T)*PostgresDatabase {
279+
t.Helper()
280+
281+
host:="127.0.0.1"
282+
ifh:=os.Getenv("PGHOST");h!="" {
283+
host=h
284+
}
285+
286+
port:=5432
287+
ifp:=os.Getenv("PGPORT");p!="" {
288+
ifpInt,err:=strconv.Atoi(p);err==nil {
289+
port=pInt
290+
}
291+
}
292+
293+
user:="postgres"
294+
ifu:=os.Getenv("PGUSER");u!="" {
295+
user=u
296+
}
297+
298+
password:=os.Getenv("PGPASSWORD")
299+
sslMode:="disable"
300+
ifs:=os.Getenv("PGSSLMODE");s!="" {
301+
sslMode=s
302+
}
303+
304+
dbName:="psqldef_database_test"
305+
306+
// Connect to postgres database to recreate test database
307+
adminDSN:=fmt.Sprintf("postgres://%s:%s@%s:%d/postgres?sslmode=%s",
308+
user,password,host,port,sslMode)
309+
adminDB,err:=sql.Open("postgres",adminDSN)
310+
require.NoError(t,err)
311+
312+
_,_=adminDB.Exec(fmt.Sprintf("DROP DATABASE IF EXISTS %s",dbName))
313+
_,err=adminDB.Exec(fmt.Sprintf("CREATE DATABASE %s",dbName))
314+
require.NoError(t,err)
315+
adminDB.Close()
316+
317+
// Connect to test database
318+
db,err:=NewDatabase(database.Config{
319+
User:user,
320+
Password:password,
321+
Host:host,
322+
Port:port,
323+
DbName:dbName,
324+
SslMode:sslMode,
325+
})
326+
require.NoError(t,err)
327+
328+
returndb.(*PostgresDatabase)
329+
}

0 commit comments

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