Обсуждение: Profile setup in PostgreSQL

Поиск
Список
Период
Сортировка

Profile setup in PostgreSQL

От
Dhandapani Shanmugam
Дата:
Hey,

I would like to know, do we have profile concept in PostgreSQL as like in Oracle.
I have a requirement to expire user password in 90 days and in oracle, I can achieve this by creating profile with custom requirements and assign it to the users.  Similarly in PostgreSQL do we have options to restrict the users on password complexity. 

-D

Re: Profile setup in PostgreSQL

От
Pepe TD Vo
Дата:
I want to know too.

I couldn't find the profile setting in Postgres as Oracle and all I do is 
"ALTER ROLE '${USER}' WITH PASSWORD '${password}' VALID UNTIL '${expiration_timestamp}';

thank you.

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Friday, August 2, 2019, 07:26:23 AM EDT, Dhandapani Shanmugam <postgresql95@gmail.com> wrote:


Hey,

I would like to know, do we have profile concept in PostgreSQL as like in Oracle.
I have a requirement to expire user password in 90 days and in oracle, I can achieve this by creating profile with custom requirements and assign it to the users.  Similarly in PostgreSQL do we have options to restrict the users on password complexity. 

-D

Re: Profile setup in PostgreSQL

От
MichaelDBA
Дата:
First of all, it would be good to check the PG DOCs to get easy answers like this. Steps:
1. google it: Postgresql alter role
2. Choose the one that points to the official pg docs. https://www.postgresql.org/docs/9.0/sql-alterrole.html
3. Change the version at top of the page to your PG version.

Now on this page it explicitly shows you the syntax for changing password expiration: VALID UNTIL 'timestamp'
Then the same page gives 2 working examples:

Change a password expiration date, specifying that the password should expire at midday on 4th May 2015 using the time zone which is one hour ahead of UTC:

ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';

Make a password valid forever:

ALTER ROLE fred VALID UNTIL 'infinity';

Please do a little research before submitting questions.  We love to answer questions but it would be nice if you could do a little effort on your own in advance first.

Regards,
Michael Vitale


Pepe TD Vo wrote on 8/2/2019 8:06 AM:
I want to know too.

I couldn't find the profile setting in Postgres as Oracle and all I do is 
"ALTER ROLE '${USER}' WITH PASSWORD '${password}' VALID UNTIL '${expiration_timestamp}';

thank you.

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Friday, August 2, 2019, 07:26:23 AM EDT, Dhandapani Shanmugam <postgresql95@gmail.com> wrote:


Hey,

I would like to know, do we have profile concept in PostgreSQL as like in Oracle.
I have a requirement to expire user password in 90 days and in oracle, I can achieve this by creating profile with custom requirements and assign it to the users.  Similarly in PostgreSQL do we have options to restrict the users on password complexity. 

-D

Re: Profile setup in PostgreSQL

От
Pepe TD Vo
Дата:
I did "alter role" as same the syntax you sent.  I thought there's a profile setting like Oracle, pw_limit, password_life_time, password_reuse_time, etc... and on mysql we can set on my.cnf.  Seem like postgres doesn't have this function.

thank you.


Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Friday, August 2, 2019, 08:42:47 AM EDT, MichaelDBA <MichaelDBA@sqlexec.com> wrote:


First of all, it would be good to check the PG DOCs to get easy answers like this. Steps:
1. google it: Postgresql alter role
2. Choose the one that points to the official pg docs. https://www.postgresql.org/docs/9.0/sql-alterrole.html
3. Change the version at top of the page to your PG version.

Now on this page it explicitly shows you the syntax for changing password expiration: VALID UNTIL 'timestamp'
Then the same page gives 2 working examples:

Change a password expiration date, specifying that the password should expire at midday on 4th May 2015 using the time zone which is one hour ahead of  UTC :

ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';

Make a password valid forever:

ALTER ROLE fred VALID UNTIL 'infinity';

Please do a little research before submitting questions.  We love to answer questions but it would be nice if you could do a little effort on your own in advance first.

Regards,
Michael Vitale


Pepe TD Vo wrote on 8/2/2019 8:06 AM:
I want to know too.

I couldn't find the profile setting in Postgres as Oracle and all I do is 
"ALTER ROLE '${USER}' WITH PASSWORD '${password}' VALID UNTIL '${expiration_timestamp}';

thank you.

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Friday, August 2, 2019, 07:26:23 AM EDT, Dhandapani Shanmugam <postgresql95@gmail.com> wrote:


Hey,

I would like to know, do we have profile concept in PostgreSQL as like in Oracle.
I have a requirement to expire user password in 90 days and in oracle, I can achieve this by creating profile with custom requirements and assign it to the users.  Similarly in PostgreSQL do we have options to restrict the users on password complexity. 

-D

Re: Profile setup in PostgreSQL

От
Avin Kavish
Дата:
Similarly in PostgreSQL do we have options to restrict the users on password complexity.

According to the docs,  there is a module called passwordcheck that will check the password, 



On Fri, Aug 2, 2019 at 6:12 PM MichaelDBA <MichaelDBA@sqlexec.com> wrote:
First of all, it would be good to check the PG DOCs to get easy answers like this. Steps:
1. google it: Postgresql alter role
2. Choose the one that points to the official pg docs. https://www.postgresql.org/docs/9.0/sql-alterrole.html
3. Change the version at top of the page to your PG version.

Now on this page it explicitly shows you the syntax for changing password expiration: VALID UNTIL 'timestamp'
Then the same page gives 2 working examples:

Change a password expiration date, specifying that the password should expire at midday on 4th May 2015 using the time zone which is one hour ahead of UTC:

ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';

Make a password valid forever:

ALTER ROLE fred VALID UNTIL 'infinity';

Please do a little research before submitting questions.  We love to answer questions but it would be nice if you could do a little effort on your own in advance first.

Regards,
Michael Vitale


Pepe TD Vo wrote on 8/2/2019 8:06 AM:
I want to know too.

I couldn't find the profile setting in Postgres as Oracle and all I do is 
"ALTER ROLE '${USER}' WITH PASSWORD '${password}' VALID UNTIL '${expiration_timestamp}';

thank you.

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Friday, August 2, 2019, 07:26:23 AM EDT, Dhandapani Shanmugam <postgresql95@gmail.com> wrote:


Hey,

I would like to know, do we have profile concept in PostgreSQL as like in Oracle.
I have a requirement to expire user password in 90 days and in oracle, I can achieve this by creating profile with custom requirements and assign it to the users.  Similarly in PostgreSQL do we have options to restrict the users on password complexity. 

-D

Re: Profile setup in PostgreSQL

От
Dhandapani Shanmugam
Дата:
Thanks for your response. I am aware of the setting "VALID UNTIL", it is specific for each users.  However I'm looking for profile setting like oracle, pw_limit, password_life_time,expire_time, password_reuse_time etc which can be set on a profile and that can be assigned to custom users. 

-D

On Fri, Aug 2, 2019 at 6:12 PM MichaelDBA <MichaelDBA@sqlexec.com> wrote:
First of all, it would be good to check the PG DOCs to get easy answers like this. Steps:
1. google it: Postgresql alter role
2. Choose the one that points to the official pg docs. https://www.postgresql.org/docs/9.0/sql-alterrole.html
3. Change the version at top of the page to your PG version.

Now on this page it explicitly shows you the syntax for changing password expiration: VALID UNTIL 'timestamp'
Then the same page gives 2 working examples:

Change a password expiration date, specifying that the password should expire at midday on 4th May 2015 using the time zone which is one hour ahead of UTC:

ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';

Make a password valid forever:

ALTER ROLE fred VALID UNTIL 'infinity';

Please do a little research before submitting questions.  We love to answer questions but it would be nice if you could do a little effort on your own in advance first.

Regards,
Michael Vitale


Pepe TD Vo wrote on 8/2/2019 8:06 AM:
I want to know too.

I couldn't find the profile setting in Postgres as Oracle and all I do is 
"ALTER ROLE '${USER}' WITH PASSWORD '${password}' VALID UNTIL '${expiration_timestamp}';

thank you.

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Friday, August 2, 2019, 07:26:23 AM EDT, Dhandapani Shanmugam <postgresql95@gmail.com> wrote:


Hey,

I would like to know, do we have profile concept in PostgreSQL as like in Oracle.
I have a requirement to expire user password in 90 days and in oracle, I can achieve this by creating profile with custom requirements and assign it to the users.  Similarly in PostgreSQL do we have options to restrict the users on password complexity. 

-D