Re: [GENERAL] Roles inherited from a role which is the owner of a database can drop it?
Greetings, * Ivan Voras (ivo...@gmail.com) wrote: > On 30 October 2017 at 22:10, David G. Johnston > wrote: > > Not quite following but ownership is an inheritable permission; > > Basically, I'm asking if "ownership" can be revoked from the set of > inherited permissions? If there is a role G which is granted to role A, and > G is the owner of a database, can A be made to not be able to do what only > owners can (specifically in this case, drop databases)? No, that's exactly what role membership means- you have the same rights as the other role. > > and even if it was not SET ROLE is all that would be required. Any owner > > can drop an object that it owns. > > It's kind of the reverse: I'm wondering if ownership can be made > un-inheritable. No, because even if ownership wasn't inheritable the user would simply do 'SET ROLE owner;' and then have all of the ownership rights that way. > Just considering the case of dropping databases for now. I.e. let the > developers do everything except that. It's a start. I think you're assuming far too much about what being a database owner means- I'd suggest you really think about why the developers need to be database owners at all; in other words- what's the *other* privilege that's currently only available to database owners that you need developers to be able to do? I have a hunch that it might be GRANT'ing rights on the database, but there's only a couple such rights (eg: CONNECT) and you might be better off managing those in another way. Thanks! Stephen signature.asc Description: Digital signature
Re: [GENERAL] Roles inherited from a role which is the owner of a database can drop it?
Hello, On 30 October 2017 at 22:10, David G. Johnston wrote: > On Mon, Oct 30, 2017 at 12:25 PM, Ivan Voras wrote: > >> >> 3. But they do log in with "developer" roles which are inherited from the >> owner role. >> >> [...] > >> I've tried it on a dummy database and it apparently works as described >> here. Is this by design? >> >> > Not quite following but ownership is an inheritable permission; > Basically, I'm asking if "ownership" can be revoked from the set of inherited permissions? If there is a role G which is granted to role A, and G is the owner of a database, can A be made to not be able to do what only owners can (specifically in this case, drop databases)? > and even if it was not SET ROLE is all that would be required. Any owner > can drop an object that it owns. > > It's kind of the reverse: I'm wondering if ownership can be made un-inheritable. > > What are the best practices for this sort of scenario where there is a >> single owner of all the schema (which is large), where developers need >> access to everything but cannot do something as drastic as dropping the dbs >> (and possibly tables)? >> > > Don't let developers into production databases... > > Trusted people (and/or software) should be provided membership into > ownership groups. Developers should provide these people/programs with > vetted scripts to execute against production. Developers can do whatever > they want on their local database instance with full schema-modifying > privileges. > > "developers need access to everything" - there is a lot of nuance and > detail behind that fragment that is needed if one is going to develop a > data access and change management policy. > Just considering the case of dropping databases for now. I.e. let the developers do everything except that. It's a start.
Re: [GENERAL] Roles inherited from a role which is the owner of a database can drop it?
On Mon, Oct 30, 2017 at 12:25 PM, Ivan Voras wrote: > > 3. But they do log in with "developer" roles which are inherited from the > owner role. > > [...] > I've tried it on a dummy database and it apparently works as described > here. Is this by design? > > Not quite following but ownership is an inheritable permission; and even if it was not SET ROLE is all that would be required. Any owner can drop an object that it owns. > What are the best practices for this sort of scenario where there is a > single owner of all the schema (which is large), where developers need > access to everything but cannot do something as drastic as dropping the dbs > (and possibly tables)? > Don't let developers into production databases... Trusted people (and/or software) should be provided membership into ownership groups. Developers should provide these people/programs with vetted scripts to execute against production. Developers can do whatever they want on their local database instance with full schema-modifying privileges. "developers need access to everything" - there is a lot of nuance and detail behind that fragment that is needed if one is going to develop a data access and change management policy. David J.
[GENERAL] Roles inherited from a role which is the owner of a database can drop it?
Hello, I just want to verify that what I'm observing is true, and if it is, I'd like to know how to avoid it: 1. There are databases owned by a certain role which is a superuser 2. Nobody logs in with the superuser role unless necessary 3. But they do log in with "developer" roles which are inherited from the owner role. These developer roles are not superusers themselves, but have the CREATEDB flag 4. The developer roles can still drop the databases. I've tried it on a dummy database and it apparently works as described here. Is this by design? If it is, is there a way to prevent the developer roles from dropping the databases? What are the best practices for this sort of scenario where there is a single owner of all the schema (which is large), where developers need access to everything but cannot do something as drastic as dropping the dbs (and possibly tables)?
Re: [GENERAL] roles inheriting configuration values
Adrian Klaver-3 wrote > On 02/07/2014 11:08 AM, Joe Van Dyk wrote: >> I'd like to have join_collapse_limit=20 for all users that belong to a >> certain group. Is there a way to do that without having to alter all the >> roles that are in that group? > > From what I see in the docs no: > > http://www.postgresql.org/docs/9.3/interactive/sql-alterrole.html > > "Whenever the role subsequently starts a new session, the specified > value becomes the session default, overriding whatever setting is > present in postgresql.conf or has been received from the postgres > command line. This only happens at login time; executing SET ROLE or SET > SESSION AUTHORIZATION does not cause new configuration values to be set. " > > Looks like the settings only apply to the role that logs in. This does not, by itself, preclude role-inheritance of variable values. It would simply mean that the inheritance resolution routine would only be resolved at logon. Unlike GRANT/REVOKE variable value inheritance has potential for multiple-inheritance resolution ambiguities - which likely increases cost/benefit equation for anyone looking to implement such a feature. There is like a scripting solution to this problem to at least minimize the burden but I do not recall seeing anything already in place that meets this need. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/roles-inheriting-configuration-values-tp5791011p5791036.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] roles inheriting configuration values
On 02/07/2014 11:08 AM, Joe Van Dyk wrote: I'd like to have join_collapse_limit=20 for all users that belong to a certain group. Is there a way to do that without having to alter all the roles that are in that group? From what I see in the docs no: http://www.postgresql.org/docs/9.3/interactive/sql-alterrole.html "Whenever the role subsequently starts a new session, the specified value becomes the session default, overriding whatever setting is present in postgresql.conf or has been received from the postgres command line. This only happens at login time; executing SET ROLE or SET SESSION AUTHORIZATION does not cause new configuration values to be set. " Looks like the settings only apply to the role that logs in. $ psql monkey psql (9.3.1) Type "help" for help. monkey=# create user f1 login; CREATE ROLE monkey=# create user f2 in role f1 login; CREATE ROLE monkey=# alter role f1 set join_collapse_limit=20; ALTER ROLE $ psql --user f1 monkey psql (9.3.1) Type "help" for help. monkey=> show join_collapse_limit ; join_collapse_limit - 20 (1 row) $ psql --user f2 monkey psql (9.3.1) Type "help" for help. monkey=> show join_collapse_limit ; join_collapse_limit - 8 (1 row) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] roles inheriting configuration values
I'd like to have join_collapse_limit=20 for all users that belong to a certain group. Is there a way to do that without having to alter all the roles that are in that group? $ psql monkey psql (9.3.1) Type "help" for help. monkey=# create user f1 login; CREATE ROLE monkey=# create user f2 in role f1 login; CREATE ROLE monkey=# alter role f1 set join_collapse_limit=20; ALTER ROLE $ psql --user f1 monkey psql (9.3.1) Type "help" for help. monkey=> show join_collapse_limit ; join_collapse_limit - 20 (1 row) $ psql --user f2 monkey psql (9.3.1) Type "help" for help. monkey=> show join_collapse_limit ; join_collapse_limit - 8 (1 row)
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
2012/7/24 Tom Lane > Dmitriy Igrishin writes: > > 2012/7/24 Tom Lane > >> Please note that empty and null are not the same thing... > > > Yes, I know. But why the ALTER ROLE treats '' as NULL and > > as the result all of values of pg_catalog.pg_authid.rolpassword are > always > > NULL even when the password in ALTER ROLE was specified as ''? :-) > > It does not do that for me. What PG version are you testing? > Oops, I am sorry, Tom. It doesn't on my 9.2beta2. Perhaps, it's too hot in Russia now... But maybe it's worth it to add a parameter key word to libpq, e.g. "passwd" which's empty value will be treated as an empty password? -- // Dmitriy.
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
Dmitriy Igrishin writes: > 2012/7/24 Tom Lane >> Please note that empty and null are not the same thing... > Yes, I know. But why the ALTER ROLE treats '' as NULL and > as the result all of values of pg_catalog.pg_authid.rolpassword are always > NULL even when the password in ALTER ROLE was specified as ''? :-) It does not do that for me. What PG version are you testing? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
2012/7/24 Tom Lane
> Dmitriy Igrishin writes:
> > But it's impossible to pass empty (NULL) password to the backend
>
> Please note that empty and null are not the same thing...
>
Yes, I know. But why the ALTER ROLE treats '' as NULL and
as the result all of values of pg_catalog.pg_authid.rolpassword are always
NULL even when the password in ALTER ROLE was specified as ''? :-)
That is the reason why I've considered empty and NULL as the same
thing :-)
>
> > by using libpq, because connectOptions2() defined the
> > fe-connect.c reads a password from the ~/.pgpass even when a password
> > specified as an empty string literal ("").
>
> I rather doubt that we'll change this, because it seems more likely
> to break applications that rely on that behavior than to do anything
> useful. Surely nobody in their right mind uses an empty password.
>
> (If anything, I'd be more inclined to make the backend treat an empty
> password as an error than to try to make libpq safe for the case.
> Even if we did change libpq, there are probably issues with empty
> passwords in jdbc and who knows how many other places.)
>
Then it should be at least documented in the libpq's documentation that
explicitly specified empty password will force libpq to get it from the
file?
>
> regards, tom lane
>
--
// Dmitriy.
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
Dmitriy Igrishin writes:
> But it's impossible to pass empty (NULL) password to the backend
Please note that empty and null are not the same thing...
> by using libpq, because connectOptions2() defined the
> fe-connect.c reads a password from the ~/.pgpass even when a password
> specified as an empty string literal ("").
I rather doubt that we'll change this, because it seems more likely
to break applications that rely on that behavior than to do anything
useful. Surely nobody in their right mind uses an empty password.
(If anything, I'd be more inclined to make the backend treat an empty
password as an error than to try to make libpq safe for the case.
Even if we did change libpq, there are probably issues with empty
passwords in jdbc and who knows how many other places.)
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
On 07/24/2012 05:41 AM, Dmitriy Igrishin wrote:
Hey all,
According to http://www.postgresql.org/docs/9.2/static/sql-alterrole.html
A query:
ALTER ROLE davide WITH PASSWORD NULL;
removes a role's password.
http://www.postgresql.org/docs/9.2/static/sql-createrole.html
PASSWORD password
Sets the role's password. (A password is only of use for roles having
the LOGIN attribute, but you can nonetheless define one for roles
without it.) If you do not plan to use password authentication you can
omit this option. If no password is specified, the password will be set
to null and password authentication will always fail for that user. A
null password can optionally be written explicitly as PASSWORD NULL.
But it's impossible to pass empty (NULL) password to the backend
by using libpq, because connectOptions2() defined the fe-connect.c
reads a password from the ~/.pgpass even when a password
specified as an empty string literal ("").
Also, when connecting to the server via psql(1) by using a role
with removed password psql exists with status 2 and prints the error
message:
psql: fe_sendauth: no password supplied
I do not see much traction in the argument no password == password. I do
see where a warning that you are losing the ability to login would be nice.
Thanks.
--
// Dmitriy.
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dmitriy Igrishin
Sent: Tuesday, July 24, 2012 10:00 AM
To: Guillaume Lelarge
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Roles with empty password (probably bug in libpq and in
psql as well).
2012/7/24 Dmitriy Igrishin
2012/7/24 Guillaume Lelarge
On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote:
> Hey Guillaume,
>
> 2012/7/24 Guillaume Lelarge
> On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote:
> > Hey all,
> >
> > According to
> http://www.postgresql.org/docs/9.2/static/sql-alterrole.html
> >
> > A query:
> > ALTER ROLE davide WITH PASSWORD NULL;
> > removes a role's password.
> >
> > But it's impossible to pass empty (NULL) password to the
> backend
> > by using libpq, because connectOptions2() defined the
> fe-connect.c
> > reads a password from the ~/.pgpass even when a password
> > specified as an empty string literal ("").
> >
> > Also, when connecting to the server via psql(1) by using a
> role
> > with removed password psql exists with status 2 and prints
> the error
> > message:
> > psql: fe_sendauth: no password supplied
> >
>
>
> Yes, and? I don't see how this could be a bug. If your
> authentication
> method asks for a password, you need to have one.
> Yes, I need. I just want to have empty password ("").
>
> If you have resetted
> it, well, you shouldn't have. Or you really want that your
> users could
> connect without a password, and then you need to change your
> authentication method with trust. But no-one will encourage
> you to do
> that.
> Why I need to change an auth. method? If I've used a \password command
> in psql(1) and specified an empty password for my role I need to ask
> a database admin to change an auth. method? :-) Cool!
> Please note, psql(1) allow to do it as well as SQL - too.
>
If your admin sets PostgreSQL so that a password needs to be given while
trying to connect, a "simple user" shouldn't be able to bypass that by
setting no password for his role.
So, yes, if you want to be able to not use a password, you need to
change your authentification method.
dmitigr=> CREATE USER test ENCRYPTED PASSWORD 'test';
CREATE ROLE
dmitigr=> \c dmitigr test
Password for user test:
You are now connected to database "dmitigr" as user "test".
dmitigr=> \password
Enter new password:
Enter it again:
Now the user "test" will not be able to connect to the server.
This behaviour is incorrect.
Full version :-)
dmitigr=> CREATE USER test ENCRYPTED PASSWORD 'test';
CREATE ROLE
dmitigr=> \c dmitigr test
Password for user test:
You are now connected to database "dmitigr" as user "test".
dmitigr=> ALTER ROLE test PASSWORD '';
ALTER ROLE
dmitigr=> \c dmitigr test
FATAL: password authentication failed for user "test"
Previous connection kept
It's an incorrect behaviour because it's a user's decision
what a password to have - empty or not.
I'm dubious that the user of some WEB site should contact
to the site admin to ask him to change the auth. method
because the user sets his password to NULL :-).
On the other hand, it's a developer's decision to allow
empty passwords or not to allow them in the software.
--
// Dmitriy.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
It is reasonable that the system administrator can institute a password policy
regarding whether the empty-string/NULL (i.e., no password) is allowable
regardless of whether the user wants it or not. That said if the system is
going to choke when a password is removed then the system should just not allow
the user to remove the password in the first place -unless you really want the
user to be able to disable their account themselves. Even if you do it would
make sense to prompt the user to confirm that they mean to disable their
account by removing the password. This seems like a psql oversight. The ALTER
ROLE aspect would ideally have an explicit "NO PASSWORD" and then enforce
non-empty/non-null when a password is actually present.
My .02
David J.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
2012/7/24 Dmitriy Igrishin
>
>
> 2012/7/24 Guillaume Lelarge
>
>> On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote:
>> > Hey Guillaume,
>> >
>> > 2012/7/24 Guillaume Lelarge
>> > On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote:
>> > > Hey all,
>> > >
>> > > According to
>> > http://www.postgresql.org/docs/9.2/static/sql-alterrole.html
>> > >
>> > > A query:
>> > > ALTER ROLE davide WITH PASSWORD NULL;
>> > > removes a role's password.
>> > >
>> > > But it's impossible to pass empty (NULL) password to the
>> > backend
>> > > by using libpq, because connectOptions2() defined the
>> > fe-connect.c
>> > > reads a password from the ~/.pgpass even when a password
>> > > specified as an empty string literal ("").
>> > >
>> > > Also, when connecting to the server via psql(1) by using a
>> > role
>> > > with removed password psql exists with status 2 and prints
>> > the error
>> > > message:
>> > > psql: fe_sendauth: no password supplied
>> > >
>> >
>> >
>> > Yes, and? I don't see how this could be a bug. If your
>> > authentication
>> > method asks for a password, you need to have one.
>> > Yes, I need. I just want to have empty password ("").
>> >
>> > If you have resetted
>> > it, well, you shouldn't have. Or you really want that your
>> > users could
>> > connect without a password, and then you need to change your
>> > authentication method with trust. But no-one will encourage
>> > you to do
>> > that.
>> > Why I need to change an auth. method? If I've used a \password command
>> > in psql(1) and specified an empty password for my role I need to ask
>> > a database admin to change an auth. method? :-) Cool!
>> > Please note, psql(1) allow to do it as well as SQL - too.
>> >
>>
>> If your admin sets PostgreSQL so that a password needs to be given while
>> trying to connect, a "simple user" shouldn't be able to bypass that by
>> setting no password for his role.
>>
>
>> So, yes, if you want to be able to not use a password, you need to
>> change your authentification method.
>>
> dmitigr=> CREATE USER test ENCRYPTED PASSWORD 'test';
> CREATE ROLE
> dmitigr=> \c dmitigr test
> Password for user test:
> You are now connected to database "dmitigr" as user "test".
> dmitigr=> \password
> Enter new password:
> Enter it again:
>
> Now the user "test" will not be able to connect to the server.
> This behaviour is incorrect.
>
> Full version :-)
dmitigr=> CREATE USER test ENCRYPTED PASSWORD 'test';
CREATE ROLE
dmitigr=> \c dmitigr test
Password for user test:
You are now connected to database "dmitigr" as user "test".
dmitigr=> ALTER ROLE test PASSWORD '';
ALTER ROLE
dmitigr=> \c dmitigr test
FATAL: password authentication failed for user "test"
Previous connection kept
It's an incorrect behaviour because it's a user's decision
what a password to have - empty or not.
I'm dubious that the user of some WEB site should contact
to the site admin to ask him to change the auth. method
because the user sets his password to NULL :-).
On the other hand, it's a developer's decision to allow
empty passwords or not to allow them in the software.
--
// Dmitriy.
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
2012/7/24 Guillaume Lelarge
> On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote:
> > Hey Guillaume,
> >
> > 2012/7/24 Guillaume Lelarge
> > On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote:
> > > Hey all,
> > >
> > > According to
> > http://www.postgresql.org/docs/9.2/static/sql-alterrole.html
> > >
> > > A query:
> > > ALTER ROLE davide WITH PASSWORD NULL;
> > > removes a role's password.
> > >
> > > But it's impossible to pass empty (NULL) password to the
> > backend
> > > by using libpq, because connectOptions2() defined the
> > fe-connect.c
> > > reads a password from the ~/.pgpass even when a password
> > > specified as an empty string literal ("").
> > >
> > > Also, when connecting to the server via psql(1) by using a
> > role
> > > with removed password psql exists with status 2 and prints
> > the error
> > > message:
> > > psql: fe_sendauth: no password supplied
> > >
> >
> >
> > Yes, and? I don't see how this could be a bug. If your
> > authentication
> > method asks for a password, you need to have one.
> > Yes, I need. I just want to have empty password ("").
> >
> > If you have resetted
> > it, well, you shouldn't have. Or you really want that your
> > users could
> > connect without a password, and then you need to change your
> > authentication method with trust. But no-one will encourage
> > you to do
> > that.
> > Why I need to change an auth. method? If I've used a \password command
> > in psql(1) and specified an empty password for my role I need to ask
> > a database admin to change an auth. method? :-) Cool!
> > Please note, psql(1) allow to do it as well as SQL - too.
> >
>
> If your admin sets PostgreSQL so that a password needs to be given while
> trying to connect, a "simple user" shouldn't be able to bypass that by
> setting no password for his role.
>
> So, yes, if you want to be able to not use a password, you need to
> change your authentification method.
>
dmitigr=> CREATE USER test ENCRYPTED PASSWORD 'test';
CREATE ROLE
dmitigr=> \c dmitigr test
Password for user test:
You are now connected to database "dmitigr" as user "test".
dmitigr=> \password
Enter new password:
Enter it again:
Now the user "test" will not be able to connect to the server.
This behaviour is incorrect.
--
// Dmitriy.
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote:
> Hey Guillaume,
>
> 2012/7/24 Guillaume Lelarge
> On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote:
> > Hey all,
> >
> > According to
> http://www.postgresql.org/docs/9.2/static/sql-alterrole.html
> >
> > A query:
> > ALTER ROLE davide WITH PASSWORD NULL;
> > removes a role's password.
> >
> > But it's impossible to pass empty (NULL) password to the
> backend
> > by using libpq, because connectOptions2() defined the
> fe-connect.c
> > reads a password from the ~/.pgpass even when a password
> > specified as an empty string literal ("").
> >
> > Also, when connecting to the server via psql(1) by using a
> role
> > with removed password psql exists with status 2 and prints
> the error
> > message:
> > psql: fe_sendauth: no password supplied
> >
>
>
> Yes, and? I don't see how this could be a bug. If your
> authentication
> method asks for a password, you need to have one.
> Yes, I need. I just want to have empty password ("").
>
> If you have resetted
> it, well, you shouldn't have. Or you really want that your
> users could
> connect without a password, and then you need to change your
> authentication method with trust. But no-one will encourage
> you to do
> that.
> Why I need to change an auth. method? If I've used a \password command
> in psql(1) and specified an empty password for my role I need to ask
> a database admin to change an auth. method? :-) Cool!
> Please note, psql(1) allow to do it as well as SQL - too.
>
If your admin sets PostgreSQL so that a password needs to be given while
trying to connect, a "simple user" shouldn't be able to bypass that by
setting no password for his role.
So, yes, if you want to be able to not use a password, you need to
change your authentification method.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
Hey Guillaume,
2012/7/24 Guillaume Lelarge
> On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote:
> > Hey all,
> >
> > According to
> http://www.postgresql.org/docs/9.2/static/sql-alterrole.html
> >
> > A query:
> > ALTER ROLE davide WITH PASSWORD NULL;
> > removes a role's password.
> >
> > But it's impossible to pass empty (NULL) password to the backend
> > by using libpq, because connectOptions2() defined the fe-connect.c
> > reads a password from the ~/.pgpass even when a password
> > specified as an empty string literal ("").
> >
> > Also, when connecting to the server via psql(1) by using a role
> > with removed password psql exists with status 2 and prints the error
> > message:
> > psql: fe_sendauth: no password supplied
> >
>
> Yes, and? I don't see how this could be a bug. If your authentication
> method asks for a password, you need to have one.
Yes, I need. I just want to have empty password ("").
> If you have resetted
> it, well, you shouldn't have. Or you really want that your users could
> connect without a password, and then you need to change your
> authentication method with trust. But no-one will encourage you to do
> that.
>
Why I need to change an auth. method? If I've used a \password command
in psql(1) and specified an empty password for my role I need to ask
a database admin to change an auth. method? :-) Cool!
Please note, psql(1) allow to do it as well as SQL - too.
--
// Dmitriy.
Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote:
> Hey all,
>
> According to http://www.postgresql.org/docs/9.2/static/sql-alterrole.html
>
> A query:
> ALTER ROLE davide WITH PASSWORD NULL;
> removes a role's password.
>
> But it's impossible to pass empty (NULL) password to the backend
> by using libpq, because connectOptions2() defined the fe-connect.c
> reads a password from the ~/.pgpass even when a password
> specified as an empty string literal ("").
>
> Also, when connecting to the server via psql(1) by using a role
> with removed password psql exists with status 2 and prints the error
> message:
> psql: fe_sendauth: no password supplied
>
Yes, and? I don't see how this could be a bug. If your authentication
method asks for a password, you need to have one. If you have resetted
it, well, you shouldn't have. Or you really want that your users could
connect without a password, and then you need to change your
authentication method with trust. But no-one will encourage you to do
that.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Roles with empty password (probably bug in libpq and in psql as well).
Hey all,
According to http://www.postgresql.org/docs/9.2/static/sql-alterrole.html
A query:
ALTER ROLE davide WITH PASSWORD NULL;
removes a role's password.
But it's impossible to pass empty (NULL) password to the backend
by using libpq, because connectOptions2() defined the fe-connect.c
reads a password from the ~/.pgpass even when a password
specified as an empty string literal ("").
Also, when connecting to the server via psql(1) by using a role
with removed password psql exists with status 2 and prints the error
message:
psql: fe_sendauth: no password supplied
Thanks.
--
// Dmitriy.
Re: [GENERAL] roles
On Fri, Jul 1, 2011 at 5:06 AM, salah jubeh wrote: > > Hello, > > I have two databases, I need to insure that both databases has the same > roles. tables, schemas, views must have the same permissions and > privileges. you can say and Identical clones. I can synchronize the roles > using these statements I guess it kinda depends on what you are trying to do. If these are db's on the same db cluster, then no action is necessary since roles are shared among databases. If they are on different clusters and servers, I think your approach will fail to synchronize passwords if those are required. I would probably do a pg_dumpall -s and use grep to pullout the create/alter role statements along with grants. Best wishes, Chris Travers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] roles
As I understand things, roles are not specific to databases. In PGAdmin, when I connect to a server, I see five collections of objects: databases, tablespaces, jobs, group roles, and login roles. Roles are separate from databases. So, for a given server, login and group roles apply to all databases on that server. Roles can be exported using pg_dump's "global" option, which I think is "-g". Check the help for pg_dump. Good luck! RobR, whose advice may well be worth what you have paid for it. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of salah jubeh Sent: Friday, July 01, 2011 8:07 AM To: pgsql Subject: Re: [GENERAL] roles Hello, I have two databases, I need to insure that both databases has the same roles. tables, schemas, views must have the same permissions and privileges. you can say and Identical clones. I can synchronize the roles using these statements SELECT DISTINCT 'CREATE USER '||usename||';' FROM pg_user; SELECT 'GRANT '||g.usename||' TO '||u.usename||';' FROM pg_auth_members a JOIN pg_user u ON a.member = u.usesysid JOIN pg_user g ON a.roleid = g.usesysid; SELECT 'ALTER ROLE '||usename||' WITH SUPERUSER;' FROM pg_user WHERE usesuper; However, How can I synchronize the privileges that are assigned to the database entities i.e. schemas , tables, and views. If that solution is not possible, how can I create an identical clone of my database including roles. when I use pg_restore normally the owner of the tables are changed and you need to recreate the roles in advance Regards
Re: [GENERAL] roles
Hello, I have two databases, I need to insure that both databases has the same roles. tables, schemas, views must have the same permissions and privileges. you can say and Identical clones. I can synchronize the roles using these statements SELECT DISTINCT 'CREATE USER '||usename||';' FROM pg_user; SELECT 'GRANT '||g.usename||' TO '||u.usename||';' FROM pg_auth_members a JOIN pg_user u ON a.member = u.usesysid JOIN pg_user g ON a.roleid = g.usesysid; SELECT 'ALTER ROLE '||usename||' WITH SUPERUSER;' FROM pg_user WHERE usesuper; However, How can I synchronize the privileges that are assigned to the database entities i.e. schemas , tables, and views. If that solution is not possible, how can I create an identical clone of my database including roles. when I use pg_restore normally the owner of the tables are changed and you need to recreate the roles in advance Regards
[GENERAL] roles
I have two databases, I need to insure that both databases has the same roles. tables, schemas, views must have the same permissions and privileges. you can say and Identical clones. I can synchronise the roles using these statments SELECT DISTINCT 'CREATE USER '||usename||';' FROM pg_user; SELECT 'GRANT '||g.usename||' TO '||u.usename||';' FROM pg_auth_members a JOIN pg_user u ON a.member = u.usesysid JOIN pg_user g ON a.roleid = g.usesysid; SELECT 'ALTER ROLE '||usename||' WITH SUPERUSER;' FROM pg_user WHERE usesuper;
Re: [GENERAL] Roles with passwords; SET ROLE ... WITH PASSWORD ?
Craig Ringer writes: > On 2/12/2009 11:04 PM, Tom Lane wrote: >> Seems like it would have all the standard problems with cleartext >> passwords being exposed in pg_stat_activity, system logs, etc. > Yeah, I was a bit concerned about that, but it can be worked around with > careful use of parameterised queries (depending, admittedly, on client > library/driver). No, not really, because we don't support parameters in utility commands. Even if we did, parameter values get logged, so the leak to the postmaster log is still there. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Roles with passwords; SET ROLE ... WITH PASSWORD ?
On 2/12/2009 11:04 PM, Tom Lane wrote: Craig Ringer writes: Anyway ... I'm curious about whether `SET ROLE rolename WITH PASSWORD' is something that's technically practical to implement in PostgreSQL and what people think about the idea. Seems like it would have all the standard problems with cleartext passwords being exposed in pg_stat_activity, system logs, etc. Yeah, I was a bit concerned about that, but it can be worked around with careful use of parameterised queries (depending, admittedly, on client library/driver). It's still not pretty. And, of course, you'd have to run over SSL or some other encrypted channel to make it even faintly OK. Also, what about people who are using more-secure-than-password auth methods, like Kerberos? That's more what I was worried about. I thought it might be useful anwyay, though, as it seems that a *lot* of people use Pg's built-in user management. Between the two, though, as I look at it more I'm inclined to agree that it's probably not worth it. Doing it right would need protocol-level support for re-running authentication, which I imagine would be major server- and client-surgery as well as a protocol version change ... hardly worth it for a niche capability. I'll probably achieve roughly the same thing for my particular needs with a function that does a manual lookup in pg_shadow. It's ugly, but should do what I need. I can hide the password in a query parameter, so long as I make sure the params aren't interpolated on the client end. I force the use of SSL to permit users to log in at all, so network sniffing shouldn't be an issue. It's frustrating that there's no way to change priveleges on the fly (without a new connection and potential locking issues) ... but it's not the end of the world. If the above approach doesn't work I can always go back to spawning new connections and living with the issues. Thanks for looking at the notion, though - I thought it *might* be worth a glance. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Roles with passwords; SET ROLE ... WITH PASSWORD ?
Michael Gould writes: > One other topic that is related to this is that we now have a expire date > but it would be nice to have a number of days also. This would make it easy > to force the user to change their passwords every X days if internal > security is being used instead of something like Kerberos or LDAP. There's already a solution to that in CVS HEAD: you can add a plug-in module to enforce password policy, including a limit on how far away the expiration date is. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Roles with passwords; SET ROLE ... WITH PASSWORD ?
One other topic that is related to this is that we now have a expire date but it would be nice to have a number of days also. This would make it easy to force the user to change their passwords every X days if internal security is being used instead of something like Kerberos or LDAP. Best Regards Michael Gould >Tom Lane" wrote: > Craig Ringer writes: >> Anyway ... I'm curious about whether `SET ROLE rolename WITH PASSWORD' >> is something that's technically practical to implement in PostgreSQL and >> what people think about the idea. > > Seems like it would have all the standard problems with cleartext > passwords being exposed in pg_stat_activity, system logs, etc. > Also, what about people who are using more-secure-than-password > auth methods, like Kerberos? > > I'm not really for it. > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Roles with passwords; SET ROLE ... WITH PASSWORD ?
Craig Ringer writes: > Anyway ... I'm curious about whether `SET ROLE rolename WITH PASSWORD' > is something that's technically practical to implement in PostgreSQL and > what people think about the idea. Seems like it would have all the standard problems with cleartext passwords being exposed in pg_stat_activity, system logs, etc. Also, what about people who are using more-secure-than-password auth methods, like Kerberos? I'm not really for it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Roles with passwords; SET ROLE ... WITH PASSWORD ?
At present, a role may have a password but unless it's a login role the docs say that password doesn't get used. I currently have an app where it'd be handy to be able to: SET ROLE rolename WITH PASSWORD 'blah'; to switch to role `rolename' only if the password `blah' is correct for that role. `rolename' might or might not be a login role, though in most of the cases I'm looking at it'd make more sense for it to be a login role. Why would this be useful? The app relies on PostgreSQL for basic user management and authentication. User rights are controlled by role assignments, and are enforced at the database level by appropriate table, column and function permissions plus selective use of SECURITY DEFINER functions and triggers. The app only uses role memberships to decide what UI to hide to avoid confusing the user with permission errors from the DB. This works extremely well, _except_ that occasionally it's desirable to override a user's rights by intervention of a supervisor user. Having a way to do this by switching to a role by providing a password to confirm access would be nice, especially if the user didn't have to be a member of that role already. Right now I'm having to spawn a new connection with the supplied supervisor username & password, then do the work in that connection. This works OK, but: (a) The switch can't be done mid-transaction so that priveleges are held for the minimum time possible. I'm looking at using `SET ROLE' to drop down to lower rights in the supervisor connection instead, but this could be clumsy when the main user has several roles significant for the operation(s) being performed. (b) The new connection doesn't hold the advisory locks the first connection had, which is occasionally problematic. (c) It's a PITA when working through an ORM like Hibernate, whereas a `SET ROLE' would be trivial and convenient. (d) I'd rather not spawn the extra backend, though I guess it doesn't matter much with an event of this rarity. Anyway ... I'm curious about whether `SET ROLE rolename WITH PASSWORD' is something that's technically practical to implement in PostgreSQL and what people think about the idea. I don't see any obvious, gaping security issues with doing this, since anybody who can 'SET ROLE ... WITH PASSWORD ...' should be able to log in with those credentials too. However, there may be issues interacting with external auth systems like ldap or kerberos. Thoughts? Also: I'm currently thinking of writing a `SECURITY DEFINER' function that tests a supplied password against a named role by direct access to pg_shadow, and if the password matches invokes SET ROLE with that role. Crazy? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
