Skip to content

Commit 62381ae

Browse files
romg67romg67
committed
add sequence permissions
1 parent 8f3ae99 commit 62381ae

File tree

4 files changed

+23
-25
lines changed

4 files changed

+23
-25
lines changed

‎.github/workflows/docker.yml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@ name: Publish Container Image
33
on:
44
push:
55
branches:
6-
- 'main'
6+
- '*'
77
tags:
88
- '*'
99
workflow_dispatch:

‎pkg/controller/postgres/postgres_controller.go

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -206,7 +206,7 @@ func (r *ReconcilePostgres) Reconcile(request reconcile.Request) (_ reconcile.Re
206206
readerPrivs = "SELECT"
207207
writerPrivs = "SELECT,INSERT,DELETE,UPDATE"
208208
)
209-
for _, schema := range instance.Spec.Schemas {
209+
for _, schema := range append(instance.Spec.Schemas, "public") {
210210
// Schema was previously created
211211
if utils.ListContains(instance.Status.Schemas, schema) {
212212
continue
@@ -220,25 +220,25 @@ func (r *ReconcilePostgres) Reconcile(request reconcile.Request) (_ reconcile.Re
220220
}
221221

222222
// Set privileges on schema
223-
schemaPrivilegesReader := postgres.PostgresSchemaPrivileges{database, owner, reader, schema, readerPrivs, false}
223+
schemaPrivilegesReader := postgres.PostgresSchemaPrivileges{database, reader, schema, readerPrivs, false}
224224
err = r.pg.SetSchemaPrivileges(schemaPrivilegesReader, reqLogger)
225225
if err != nil {
226226
reqLogger.Error(err, fmt.Sprintf("Could not give %s permissions \"%s\"", reader, readerPrivs))
227227
continue
228228
}
229-
schemaPrivilegesWriter := postgres.PostgresSchemaPrivileges{database, owner, writer, schema, writerPrivs, true}
229+
schemaPrivilegesWriter := postgres.PostgresSchemaPrivileges{database, writer, schema, writerPrivs, false}
230230
err = r.pg.SetSchemaPrivileges(schemaPrivilegesWriter, reqLogger)
231231
if err != nil {
232232
reqLogger.Error(err, fmt.Sprintf("Could not give %s permissions \"%s\"", writer, writerPrivs))
233233
continue
234234
}
235-
sequncesPrivilegesWriter := postgres.PostgresSequncesPrivileges{database, owner, writer, schema, writerPrivs}
235+
sequncesPrivilegesWriter := postgres.PostgresSequncesPrivileges{database, writer, schema, "USAGE"}
236236
err = r.pg.SetSequncesPrivileges(sequncesPrivilegesWriter, reqLogger)
237237
if err != nil {
238-
reqLogger.Error(err, fmt.Sprintf("Could not give %s permissions for sequnces \"%s\"", writer, writerPrivs))
238+
reqLogger.Error(err, fmt.Sprintf("Could not give %s permissions for sequnces \"%s\"", writer, "USAGE"))
239239
continue
240240
}
241-
schemaPrivilegesOwner := postgres.PostgresSchemaPrivileges{database, owner, owner, schema, readerPrivs, true}
241+
schemaPrivilegesOwner := postgres.PostgresSchemaPrivileges{database, owner, schema, readerPrivs, true}
242242
err = r.pg.SetSchemaPrivileges(schemaPrivilegesOwner, reqLogger)
243243
if err != nil {
244244
reqLogger.Error(err, fmt.Sprintf("Could not give %s permissions \"%s\"", writer, writerPrivs))

‎pkg/postgres/database.go

Lines changed: 16 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -8,21 +8,21 @@ import (
88
)
99

1010
const (
11-
CREATE_DB = `CREATE DATABASE "%s"`
12-
CREATE_SCHEMA = `CREATE SCHEMA IF NOT EXISTS "%s" AUTHORIZATION "%s"`
13-
CREATE_EXTENSION = `CREATE EXTENSION IF NOT EXISTS "%s"`
14-
ALTER_DB_OWNER = `ALTER DATABASE "%s" OWNER TO "%s"`
15-
DROP_DATABASE = `DROP DATABASE "%s"`
16-
GRANT_USAGE_SCHEMA = `GRANT USAGE ON SCHEMA "%s" TO "%s"`
17-
GRANT_CREATE_TABLE = `GRANT CREATE ON SCHEMA "%s" TO "%s"`
18-
GRANT_ALL_TABLES = `GRANT %s ON ALL TABLES IN SCHEMA "%s" TO "%s"`
11+
CREATE_DB = `CREATE DATABASE "%s"`
12+
CREATE_SCHEMA = `CREATE SCHEMA IF NOT EXISTS "%s" AUTHORIZATION "%s"`
13+
CREATE_EXTENSION = `CREATE EXTENSION IF NOT EXISTS "%s"`
14+
ALTER_DB_OWNER = `ALTER DATABASE "%s" OWNER TO "%s"`
15+
DROP_DATABASE = `DROP DATABASE "%s"`
16+
GRANT_USAGE_SCHEMA = `GRANT USAGE ON SCHEMA "%s" TO "%s"`
17+
GRANT_CREATE_TABLE = `GRANT CREATE ON SCHEMA "%s" TO "%s"`
18+
GRANT_ALL_TABLES = `GRANT %s ON ALL TABLES IN SCHEMA "%s" TO "%s"`
1919
GRANT_ALL_SEQUENCES = `GRANT %s ON ALL SEQUENCES IN SCHEMA "%s" TO "%s"`
20-
DEFAULT_PRIVS_SCHEMA = `ALTER DEFAULT PRIVILEGES FOR ROLE "%s" IN SCHEMA "%s" GRANT %s ON TABLES TO "%s"`
21-
DEFAULT_PRIVS_SEQUENCES = `ALTER DEFAULT PRIVILEGES FOR ROLE "%s" IN SCHEMA "%s" GRANT %s ON SEQUENCES TO "%s"`
22-
REVOKE_CONNECT = `REVOKE CONNECT ON DATABASE "%s" FROM public`
23-
TERMINATE_BACKEND = `SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '%s' AND pid <> pg_backend_pid()`
24-
GET_DB_OWNER = `SELECT pg_catalog.pg_get_userbyid(d.datdba) FROM pg_catalog.pg_database d WHERE d.datname = '%s'`
25-
GRANT_CREATE_SCHEMA = `GRANT CREATE ON DATABASE "%s" TO "%s"`
20+
DEFAULT_PRIVS_SCHEMA = `ALTER DEFAULT PRIVILEGES IN SCHEMA "%s" GRANT %s ON TABLES TO "%s"`
21+
DEFAULT_PRIVS_SEQUENCES = `ALTER DEFAULT PRIVILEGES IN SCHEMA "%s" GRANT %s ON SEQUENCES TO "%s"`
22+
REVOKE_CONNECT = `REVOKE CONNECT ON DATABASE "%s" FROM public`
23+
TERMINATE_BACKEND = `SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '%s' AND pid <> pg_backend_pid()`
24+
GET_DB_OWNER = `SELECT pg_catalog.pg_get_userbyid(d.datdba) FROM pg_catalog.pg_database d WHERE d.datname = '%s'`
25+
GRANT_CREATE_SCHEMA = `GRANT CREATE ON DATABASE "%s" TO "%s"`
2626
)
2727

2828
func (c *pg) CreateDB(dbname, role string) error {
@@ -117,7 +117,7 @@ func (c *pg) SetSchemaPrivileges(schemaPrivileges PostgresSchemaPrivileges, logg
117117
}
118118

119119
// Grant role privs on future tables in schema
120-
_, err = tmpDb.Exec(fmt.Sprintf(DEFAULT_PRIVS_SCHEMA, schemaPrivileges.Creator, schemaPrivileges.Schema, schemaPrivileges.Privs, schemaPrivileges.Role))
120+
_, err = tmpDb.Exec(fmt.Sprintf(DEFAULT_PRIVS_SCHEMA, schemaPrivileges.Schema, schemaPrivileges.Privs, schemaPrivileges.Role))
121121
if err != nil {
122122
return err
123123
}
@@ -148,7 +148,7 @@ func (c *pg) SetSequncesPrivileges(SequncesPrivileges PostgresSequncesPrivileges
148148
}
149149

150150
// Grant role privs on future sequences in schema
151-
_, err = tmpDb.Exec(fmt.Sprintf(DEFAULT_PRIVS_SCHEMA, SequncesPrivileges.Creator, SequncesPrivileges.Schema, SequncesPrivileges.Privs, SequncesPrivileges.Role))
151+
_, err = tmpDb.Exec(fmt.Sprintf(DEFAULT_PRIVS_SEQUENCES, SequncesPrivileges.Schema, SequncesPrivileges.Privs, SequncesPrivileges.Role))
152152
if err != nil {
153153
return err
154154
}

‎pkg/postgres/postgres.go

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -38,7 +38,6 @@ type pg struct {
3838

3939
type PostgresSchemaPrivileges struct {
4040
DB string
41-
Creator string
4241
Role string
4342
Schema string
4443
Privs string
@@ -47,7 +46,6 @@ type PostgresSchemaPrivileges struct {
4746

4847
type PostgresSequncesPrivileges struct {
4948
DB string
50-
Creator string
5149
Role string
5250
Schema string
5351
Privs string

0 commit comments

Comments
 (0)