Thread: Basic Questions about PostgreSQL.
-In PostgreSQL, what is the difference between Roles, Groups, and Policies exactly? What are they each, how do they relate to one another, and which one(s) can be assigned to another?
-Is there a simple, immediate, straightforward way to temporarily disable PostgreSQL database
use of all other users, except development users, or in fact just one user (one developer, yourself)
so that maintenance on the data or the data model may be performed?
The way things are, after you revoke connection priviledges for all the other general users,
cancel all the other connections (except for yourself, or developer user access),
and then perform required maintenance on the database. Thereafter, connection priviledges
have to be restored to all the other users, so that they can choose to connect again.
-Is there a simple, immediate, straightforward way to temporarily disable PostgreSQL database
use of all other users, except development users, or in fact just one user (one developer, yourself)
so that maintenance on the data or the data model may be performed?
The way things are, after you revoke connection priviledges for all the other general users,
cancel all the other connections (except for yourself, or developer user access),
and then perform required maintenance on the database. Thereafter, connection priviledges
have to be restored to all the other users, so that they can choose to connect again.
All of this strikes me as being very elaborate. Is there a much more simple
way to disable all and subsequent connections to a database, temporarily, and
then allowing them all again, within the PostgreSQL DBMS?
way to disable all and subsequent connections to a database, temporarily, and
then allowing them all again, within the PostgreSQL DBMS?
Is there a public Extension, for example, that installs and works well, which can be used to do this?
Is there a simple, one hit way to turn a set of user connections all off, and then all on
(regarding their permissions and connections, I presume), again?
On Wednesday, September 15, 2021, A Z <poweruserm@live.com.au> wrote:
-In PostgreSQL, what is the difference between Roles, Groups
There is none, aside from convention. Roles are generic, groups don’t get login rights while users do.
You might want to clarify what you mean by policy since referencing the “create policy” command seems out of place here.
-Is there a simple, immediate, straightforward way to temporarily disable PostgreSQL database
use of all other users, except development users, or in fact just one user (one developer, yourself)
so that maintenance on the data or the data model may be performed?
Manipulating the pg_hba.comf file is usually the most efficient method.
Though since connect privileges are usually left as strictly inherited from public by default revoking connect from public will most likely prevent non-superusers from connecting to databases.
Though it seems unusual that maintenance would require such extreme measures to be taken.
David J.
On Wed, 2021-09-15 at 05:10 +0000, A Z wrote: > -In PostgreSQL, what is the difference between Roles, Groups, and > Policies exactly? What are they each, how do they relate to one > another, and which one(s) can be assigned to another? > > -Is there a simple, immediate, straightforward way to temporarily > disable PostgreSQL database > use of all other users, except development users, or in fact just one > user (one developer, yourself) > so that maintenance on the data or the data model may be performed? > Read https://www.postgresql.org/docs/13/app-postgres.html#id-1.9.5.14.6.5 about single user mode. You shut down the cluster and run psql in single user mode. After you quit out of single user mode, restart. HTH, Rob
rob stone <floriparob@gmail.com> writes: > On Wed, 2021-09-15 at 05:10 +0000, A Z wrote: >> -Is there a simple, immediate, straightforward way to temporarily >> disable PostgreSQL database >> use of all other users, except development users, or in fact just one >> user (one developer, yourself) >> so that maintenance on the data or the data model may be performed? > Read > https://www.postgresql.org/docs/13/app-postgres.html#id-1.9.5.14.6.5 > about single user mode. I think a better recommendation is to temporarily modify pg_hba.conf to disallow logins you don't want. Single-user mode is pretty limited, because what it really is is single-process mode: there will be no background support processes such as bgwriter, autovacuum, replication support, etc. The system runs, more or less, but performance is not going to be great. regards, tom lane
On Wed, 15 Sept 2021 at 06:10, A Z <poweruserm@live.com.au> wrote: > -Is there a simple, immediate, straightforward way to temporarily disable PostgreSQL database > use of all other users, except development users, or in fact just one user (one developer, yourself) > so that maintenance on the data or the data model may be performed? PostgreSQL doesn't require you to eject all other users while such actions are performed, so we haven't spent a lot of time thinking about how to deny people access. What maintenance actions are you thinking are a problem? -- Simon Riggs http://www.EnterpriseDB.com/
On 9/14/21 10:10 PM, A Z wrote: > -In PostgreSQL, what is the difference between Roles, Groups, and > Policies exactly? What are they each, how do they relate to one another, > and which one(s) can be assigned to another? > Roles, groups and users have to do with project history more then anything else. Prior to version 8.1 there where no roles, just users and groups. Roles when introduced they subsumed users and groups. As David mentioned then the convention became that a role that had login was equivalent to a user and one that did not was a group. This is called out in the documentation for the aliases CREATE GROUP and CREATE USER, both of which actually point at CREATE ROLE. Policies comes into play by allowing for more granular control over what a role can do on a table. -- Adrian Klaver adrian.klaver@aklaver.com