Profile setup in PostgreSQL

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

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
Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Profile setup in PostgreSQL

Michaeldba@sqlexec.com
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 [hidden email] 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

Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]> 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 [hidden email] 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

Reply | Threaded
Open this post in threaded view
|

Re: Profile setup in PostgreSQL

Avin Kavish
In reply to this post by Michaeldba@sqlexec.com
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 <[hidden email]> 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 [hidden email] 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

Reply | Threaded
Open this post in threaded view
|

Re: Profile setup in PostgreSQL

Dhandapani Shanmugam
In reply to this post by Michaeldba@sqlexec.com
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 <[hidden email]> 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 [hidden email] 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