New Role drop with Grant/Revokes stop working after subsequent runs
От | AC Gomez |
---|---|
Тема | New Role drop with Grant/Revokes stop working after subsequent runs |
Дата | |
Msg-id | CABtmK-jstmf64F2Wnpmhz97+FiR+YSGcO8dX2rfeDkZBJJG5zw@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: New Role drop with Grant/Revokes stop working after subsequent runs
("David G. Johnston" <david.g.johnston@gmail.com>)
|
Список | pgsql-general |
- START
- We begin with the db owner role as the bootstrap seed - but subsequent runs feed in successive users.
- With this role we create a new user/password, for example: CREATE USER UUU WITH PASSWORD 'PPpp' CREATEDB CREATEROLE
- GRANTS
- GRANT <prior user> TO <new user>
- For each Data
- For each Schema
- GRANT USAGE ON SCHEMA <schema> TO <new user>
- GRANT CREATE ON SCHEMA <schema> TO <new user>
- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema> TO <new user>
- GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema> TO <new user>
- GRANT EXECUTE ON ALL FUNCTIONS
ii. GRANT ALL DEFAULT PRIVILEGES
iii. GRANT POSTGRES_FDW
iv. GRANT FOREIGN SERVER
end loop; end loop;
- REVOKES
i. GRANT <prior user> TO <new user>
ii. REASSIGN OWNED BY <prior user> TO <new user>
iii. DROP OWNED BY <prior user> TO <new user>
- For each Database
- For each Schema
- REVOKE USAGE ON SCHEMA <schema> TO <new user>
- REVOKE CREATE ON SCHEMA <schema> TO <new user>
- REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema> TO <new user>
- REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema> TO <new user>
- REVOKE EXECUTE ON ALL FUNCTIONS
ii. REVOKE ALL DEFAULT PRIVILEGES
iii. REVOKE POSTGRES_FDW
iv. REVOKE FOREIGN SERVERS
end loop; end loop;
- DROP ROLE <prior user> (if it's not the db owner)
В списке pgsql-general по дате отправления:
Предыдущее
От: Geoff WinklessДата:
Сообщение: Re: Lock Postgres account after X number of failed logins?
Следующее
От: "David G. Johnston"Дата:
Сообщение: Re: New Role drop with Grant/Revokes stop working after subsequent runs