Re: default_tablespace

Hi Hackers,

After reviewing docs and searching web
cannot find out how to determine the default tablespace
of a user?

Like:

select spcname from blab where roloid = 1111;

Is this possible?

Thank you for your time.

On Wed, Feb 16, 2011 at 03:59:13PM -0800, carl clemens wrote:
- Hi Hackers,
-
- After reviewing docs and searching web
- cannot find out how to determine the default tablespace
- of a user?
-
- Like:
-
- select spcname from blab where roloid = 1111;
-
- Is this possible?
-
- Thank you for your time.
-

It doesn't appear to me that default tablespaces are assigned to a user, they're
assigned to a database.

A user can set the variable default_tablespace in their session to over-ride the
database default, but that wouldn't be stored anywhere in the database (it's a client
variable).

you can find the OID for the default tablespace for a specific database in pg_database.

more info:
http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-DEFAULT-TABLESPACE

Dave

On Feb17, 2011, at 18:32 , David Kerr wrote:
> On Wed, Feb 16, 2011 at 03:59:13PM -0800, carl clemens wrote:
> - After reviewing docs and searching web
> - cannot find out how to determine the default tablespace
> - of a user?
>
> It doesn't appear to me that default tablespaces are assigned to a user, they're
> assigned to a database.
>
> A user can set the variable default_tablespace in their session to over-ride the
> database default, but that wouldn't be stored anywhere in the database (it's a client
> variable).

You can, however, do ALTER USER <user> SET default_tablespace=<whatever>, which will
cause default_tablespace to be set automatically upon login for that user.

I don't know of the top of my head how we store that in the system catalogs, though.
You'll have to check the documentation to find that out...

best regards,
Florian Pflug

On Thu, Feb 17, 2011 at 08:55:55PM +0100, Florian Pflug wrote:
- On Feb17, 2011, at 18:32 , David Kerr wrote:
- > On Wed, Feb 16, 2011 at 03:59:13PM -0800, carl clemens wrote:
- > - After reviewing docs and searching web
- > - cannot find out how to determine the default tablespace
- > - of a user?
- >
- > It doesn't appear to me that default tablespaces are assigned to a user, they're
- > assigned to a database.
- >
- > A user can set the variable default_tablespace in their session to over-ride the
- > database default, but that wouldn't be stored anywhere in the database (it's a client
- > variable).
-
-
- You can, however, do ALTER USER <user> SET default_tablespace=<whatever>, which will
- cause default_tablespace to be set automatically upon login for that user.
-
- I don't know of the top of my head how we store that in the system catalogs, though.
- You'll have to check the documentation to find that out...
-
- best regards,
- Florian Pflug

oh, you're right. my mistake.

and it looks like it's stored in useconfig in pg_user.

select * from pg_user where usename = 'david.kerr';
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig
------------+----------+-------------+----------+-----------+----------+----------+---------------------------
david.kerr | 16482 | f | t | t | ******** | | {default_tablespace=test}

Dave

Excerpts from Florian Pflug's message of jue feb 17 16:55:55 -0300 2011:

> You can, however, do ALTER USER <user> SET default_tablespace=<whatever>, which will
> cause default_tablespace to be set automatically upon login for that user.
>
> I don't know of the top of my head how we store that in the system catalogs, though.
> You'll have to check the documentation to find that out...

It's in pg_db_role_settings.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support